Sample CSharp (C#) Program to Insert DataSet values Into SQL Server Database

Using this sample program you can insert multiple table data from DataSet to SQL Server database. The Insert query will be generated automatically based on the columns in the DataSet Tables. This sample also has transaction and exception handling implemented.

Here data loaded from xml.

As I implemented transaction, Data will be saved to Database only when all table inserted successfuly. If insertion failes inbetween data will be rollbacked.

C# Code:

static void Main(string[] args)
{
    var connectionString = "SQLSERVER_DATABSE_CONNECTIONSTRING_HERE";

    DataSet ds = new DataSet();
    ds.ReadXml("data.xml");

    using (var con = new SqlConnection(connectionString))
    using (var cmd = new SqlCommand())
    {
        con.Open();
        SqlTransaction transaction;
        transaction = con.BeginTransaction("SampleTransaction");
        cmd.Transaction = transaction;
        string tableName = string.Empty;

        try
        {
            foreach (DataTable table in ds.Tables)
            {
                string columns = string.Join(",", table.Columns.Cast().Select(c => c.ColumnName));
                string values = string.Join(",", table.Columns.Cast().Select(c => string.Format("@{0}", c.ColumnName)));
                tableName = table.TableName;

                string sqlCommandInsert = string.Format("INSERT INTO dbo.{0}({1}) VALUES ({2});", tableName, columns, values);

                cmd.CommandText = sqlCommandInsert;
                cmd.Connection = con;

                foreach (DataRow row in table.Rows)
                {
                    cmd.Parameters.Clear();
                    foreach (DataColumn col in table.Columns)
                        cmd.Parameters.AddWithValue("@" + col.ColumnName, row[col]);
                    var inserted = cmd.ExecuteNonQuery();
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(tableName);
            Console.WriteLine(ex.Message);
            Console.WriteLine(ex.StackTrace);

            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                Console.WriteLine(ex2.Message);
                Console.WriteLine(ex2.StackTrace);
            }
        }
    }

    Console.ReadLine();
}