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”;
    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..

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.
http://www.arungudelli.com