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();
}

Query to generate Single XML from multiple tables in SQL Server

Here is the sample code to generate a single xml from multiple table query in Sql Server. In this example I am querying data from two tables.

Sample Query :

SELECT
    (SELECT * FROM dbo.TableA FOR XML PATH('TableA'), TYPE),
    (SELECT * FROM dbo.TableB FOR XML PATH('TableB'), TYPE)
FOR XML PATH(''), ROOT('root')

XML Output :

<root>
   <TableA>
	 <Column1>Value1</Column1>
    	 <Column2>Value2</Column2>
   </TableA>
   <TableB>
	 <Column1>Value1</Column1>
    	 <Column2>Value2</Column2>
   </TableB>
</root>

You can load this Xml into DataSet using below C# code.

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