Create DB table from dataset table

Question

Is it possible (in Vb.Net 2005), without manually parsing the dataset table properties, to create the table and add it to the database?

We have old versions of our program on some machines, which obviously has our old database, and we are looking for a way to detect if there is a missing table and then generate the table based on the current status of the table in the dataset. We were re-scripting the table every time we released a new version (if new columns were added) but we would like to avoid this step if possible.

Yaakov Ellis link is now broken, it has changed to this.

P-L
– P-L

2011-07-21 14:45:43 +00:00
Commented Jul 21, 2011 at 14:45 — P-L
– P-L, Commented Jul 21, 2011 at 14:45

stakx - no longer contributing · Accepted Answer · 2012-11-05 14:25:59Z

7

See this MSDN Forum Post: Creating a new Table in SQL Server from ADO.net DataTable.

Here the poster seems to be trying to do the same thing as you, and provides code that generates a Create Table statement using the schema contained in a DataTable.

Assuming this works as it should, you could then take that code, and submit it to the database through SqlCommand.ExecuteNonQuery() in order to create your table.

edited Nov 5, 2012 at 14:25

stakx - no longer contributing

85.1k20 gold badges176 silver badges277 bronze badges

answered Aug 19, 2008 at 20:09

Yaakov Ellis

41.6k27 gold badges135 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jimi · Accepted Answer · 2018-12-14 12:19:27Z

Here is the code:

SqlConnection con = new SqlConnection("Data Source=.;uid=sa;pwd=sa123;database=Example1");
con.Open();
string sql = "Create Table abcd (";

foreach (DataColumn column in dt.Columns)
{
    sql += "[" + column.ColumnName + "] " + "nvarchar(50)" + ",";
}

sql = sql.TrimEnd(new char[] { ',' }) + ")";
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
cmd.ExecuteNonQuery();

using (var adapter = new SqlDataAdapter("SELECT * FROM abcd", con)) 
using(var builder = new SqlCommandBuilder(adapter))
{
    adapter.InsertCommand = builder.GetInsertCommand();
    adapter.Update(dt);
}
con.Close();

I hope you got the problem solved.
Here dt is the name of the DataTable.
Alternatively you can replace:

adapter.update(dt);

with

//if you have a DataSet
adapter.Update(ds.Tables[0]);

Collectives™ on Stack Overflow

Create DB table from dataset table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related