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.

Answer 1

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.

Answer 2

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]);