Home > Asp .Net > Save datatable information into database

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”;
    dr = dt.NewRow();
    dr[“Name”] = “Ravi”;
    dr[“Address”] = “Mumbai”;
    dr[“Phone    “] = “+91-9888894444”;
    return ds    ;

Convert Dataset Into Xml String:

public static string DataSetToXMLString(DataTable dt)
    DataSet ds = new DataSet();
    string XMLformat;
        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);

Stored Procedure:

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

       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

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

Arunkumar Gudelli
I am a Simple Guy, Standing Infront of the World, asking you to Follow Me. Don't miss any posts from Me,Subscribe to the RSS Feed. You can connect me via @twitter or @facebook or Google+ or e-mail.