Copy SQL Table Schema into DataTable.
I have a SQL table with over 100 rows and I want to create a copy of this table within my C# program to manage it. At first I started doing it the wrong way by adding each column one by one.
TableName.Columns.Add("ColumnName", typeof(int));
This was a stupid idea for a few reasons. Not only is it tiresome to add 100 columns (I gave up around the 10th one) but as the table begins to change it becomes another piece of code to maintain. I basically want to copy the schema of the table into this Data Table. I use ADO.net DataAdapter to do the job.
SqlConnection schemaConn = new SqlConnection("ConnectionString");
schemaConn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Table", schemaConn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
DataTable DataTableName = new DataTable();
adapter.FillSchema(ds, SchemaType.Mapped, "SqlTableName");
DataTableName = ds.Tables["SqlTableName"];
schemaConn.Close();
This is not exactly a complicated problem and solution but I could not find any examples online and a lot of solutions lead me down the wrong direction. So here it is. I could probably use the table from the DataSet but everything else is calling the DataTable directly so I just copy it. The program then goes on to fill the DataTable and then use SQL Bulk Copy to dump it back into the SQL Server at specific intervals.