Save Datatable Information Into Database

This Post will explain How we can save Datatable or Data Set information to Data base. I tried to explain this in detail,but a primary knowledge on C#,Xml,Sql is required.

Steps as follows.

  1. Save Data to a datatable
  2. insert Datatable into a dataset
  3. Convert the dataset into XmlString
  4. Send this Xml string to a StoredProcedure Which will parse the Xml and Save the data into DataBase.

Creating a Table: 

Create table Student{
Name varachar2(50),
Address varachar2(50),
Phone varachar2(50),
}

Data Table Creation And DataSet: 

public static DataSet GetDataSetInfo()
{
    DataSet ds = new DataSet();
    DataTable dt = new DataTable(“Sample”);
    
    dt.Columns.Add(“Name”, Type.GetType(“System.String”));
    dt.Columns.Add(“Address”, Type.GetType(“System.String”));
    dt.Columns.Add(“Phone”, Type.GetType(“System.String”));
    
    DataRow dr = dt.NewRow();
    dr[“Name”] = “Srinivas”;
    dr[“Address”] = “Banglore”;
    dr[“Phone”] = “+91-9999912345”;
    dt.Rows.Add(dr);
    
    dr = dt.NewRow();
    dr[“Name”] = “Ravi”;
    dr[“Address”] = “Mumbai”;
    dr[“Phone    “] = “+91-9888894444”;
    dt.Rows.Add(dr);
    
    ds.Tables.Add(dt);
    return ds    ;
}

Convert Dataset Into Xml String: 

public static string DataSetToXMLString(DataTable dt)
{
    DataSet ds = new DataSet();
    string XMLformat;
    try
    {
        StringBuilder sb = new StringBuilder();
        StringWriter sw = new StringWriter(sb);
        ds.Merge(dt, true, MissingSchemaAction.AddWithKey);
        ds.Tables[0].TableName = “SampleTable”;
        foreach (DataColumn column in ds.Tables[0].Columns)
        {
            column.ColumnMapping = MappingType.Attribute;
        }
        ds.WriteXml(sw, XmlWriteMode.WriteSchema);
        XMLformat = sb.ToString();
        return XMLformat;
    }
    catch (Exception Exception)
    {
        throw Exception;
    }
}

The Main Method: 

static void Main(string[] args)
{
    DataSet ds = GetDataSet();
    String xmlData = DataSetToXMLString(ds.Tables[0]);
    SqlConnection conn = new SqlConnection
    (“Data Source=.;Initial Catalog=DBName;Integrated Security=SSPI;”);
    SqlCommand command = new SqlCommand
    (“InsertData ‘” + xmlData + “‘”, conn);
     conn.Open();
     command.ExecuteNonQuery();
     conn.Close();
}

Stored Procedure: 

This is very Important as it parses the xml String And Save into Database.

CREATE PROCEDURE InsertData (@xmlString VARCHAR(MAX)) AS BEGIN DECLARE @xmlHandle INT DECLARE @dummyTable TABLE ( [Name] VARCHAR(50), [Address] VARCHAR(50), [Phone] VARCHAR(50) )

    EXEC sp_xml_preparedocument @xmlHandle output, @xmlString

INSERT INTO @dummyTable SELECT [Name] ,[Address],[Phone] FROM OPENXML (@xmlHandle, ‘/NewDataSet/SampleDataTable’,1) WITH ( [Name] varchar(50) ‘@Name’, [Address] varchar(50) ‘@Address’, [Phone] varchar(50) ‘@Phone’ ) INSERT INTO SampleData ([Name],[Address], [Phone]) (SELECT [Name] ,[Address],[Phone] FROM @dummyTable) EXEC sp_xml_removedocument @xmlHandle END


If you have any doubts feel free to contact me..