dynamic table in data base
-
hi This is my code in which i upload a excel file using file dialog box and store it in to the sql server as table name emp but problem is that when i run the program secound time all the values again store in same table so my question is that is possible to create a table at run time in bata base NEED HELP or any logic regaring that. using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.OleDb; using System.Data.SqlClient; namespace openfi { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { OpenFileDialog FD = new OpenFileDialog(); FD.Filter = "Excel|*.xls"; if (FD.ShowDialog() == DialogResult.OK) { textBox1.Text = " " + FD.FileName; } string filename = textBox1.Text; String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection connection = new OleDbConnection(connectionString); connection.Open(); OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [sheet1$]", connection); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); dataAdapter.SelectCommand = selectCommand; DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); System.Data.DataTable dataTable = dataSet.Tables[0]; dataGridView1.DataSource = dataSet.Tables[0]; // textBox1.Text = ""; MessageBox.Show(dataGridView1.RowCount.ToString()); connection.Close(); } private void button2_Click(object sender, EventArgs e) { OpenFileDialog FD = new OpenFileDialog(); FD.Filter = "Excel|*.xls"; if (FD.ShowDialog() == DialogResult.OK) { textBox2.Text = " " + FD.FileName; } string filename = textBox2.Text; String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection connection = new Ol
-
hi This is my code in which i upload a excel file using file dialog box and store it in to the sql server as table name emp but problem is that when i run the program secound time all the values again store in same table so my question is that is possible to create a table at run time in bata base NEED HELP or any logic regaring that. using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.OleDb; using System.Data.SqlClient; namespace openfi { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { OpenFileDialog FD = new OpenFileDialog(); FD.Filter = "Excel|*.xls"; if (FD.ShowDialog() == DialogResult.OK) { textBox1.Text = " " + FD.FileName; } string filename = textBox1.Text; String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection connection = new OleDbConnection(connectionString); connection.Open(); OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [sheet1$]", connection); OleDbDataAdapter dataAdapter = new OleDbDataAdapter(); dataAdapter.SelectCommand = selectCommand; DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); System.Data.DataTable dataTable = dataSet.Tables[0]; dataGridView1.DataSource = dataSet.Tables[0]; // textBox1.Text = ""; MessageBox.Show(dataGridView1.RowCount.ToString()); connection.Close(); } private void button2_Click(object sender, EventArgs e) { OpenFileDialog FD = new OpenFileDialog(); FD.Filter = "Excel|*.xls"; if (FD.ShowDialog() == DialogResult.OK) { textBox2.Text = " " + FD.FileName; } string filename = textBox2.Text; String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection connection = new Ol
Before doing your insert you should check first to see if the table exists, at which point you can specify another name for the table. I would dynamically set the table name in your code as a variable, and do a count on how many tables with that name already exist, and then create a new table based on that name and the count. This link may help you with the code on checking first to see if the table exists. http://dotnetmonk.blogspot.com/2007/07/check-if-table-already-exists-in-sql.html[^] hope this helps.
Matthew Vass QA Analyst mvass@hostmysite.com HostMySite.com
-
Before doing your insert you should check first to see if the table exists, at which point you can specify another name for the table. I would dynamically set the table name in your code as a variable, and do a count on how many tables with that name already exist, and then create a new table based on that name and the count. This link may help you with the code on checking first to see if the table exists. http://dotnetmonk.blogspot.com/2007/07/check-if-table-already-exists-in-sql.html[^] hope this helps.
Matthew Vass QA Analyst mvass@hostmysite.com HostMySite.com
Something about this question just seems wrong to me. There should be one table in your database that contains all your "Customers" for example. Seems like a bad design to have "Customer_1_Jan_2009", and "Customers_2_Jan_2009", will you really want to query 'x' Customer tables for given date range as in the example provided? Alternatively, if you have for example a Customer table in your database, and each customer might need some additional data for each individual customer, i would highly suggest storeing the additional information within an xml column if possible. Or better yet figure out exactly what data you need to store, track, and maintain. You might have some specialized need to store data in this manor, but i simply can't think if a reason for it, seems like a bad design, and if you need to store daily inform for a given customer, then the date value should simply become a value withing the record.
-
Something about this question just seems wrong to me. There should be one table in your database that contains all your "Customers" for example. Seems like a bad design to have "Customer_1_Jan_2009", and "Customers_2_Jan_2009", will you really want to query 'x' Customer tables for given date range as in the example provided? Alternatively, if you have for example a Customer table in your database, and each customer might need some additional data for each individual customer, i would highly suggest storeing the additional information within an xml column if possible. Or better yet figure out exactly what data you need to store, track, and maintain. You might have some specialized need to store data in this manor, but i simply can't think if a reason for it, seems like a bad design, and if you need to store daily inform for a given customer, then the date value should simply become a value withing the record.
hi dano2K3 i am working on the project in which i upload excel sheet from local disk and con vert it in to datagride after that all excel data stored in the database table for some further logic when i run my program each time all same information in data gride store in same file that's why i want to create a table dynamically to avoide repitation in table need help