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

Wait before leaving. why can’t you follow me on twitter or be a friend on Facebook or googlePlus or linkedn to get in touch me. or join our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Arunkumar Gudelli
I am “One among a million” Software engineers of India. I write beautiful markup.I make the Web useful. You can connect me via @twitter or @facebook or Google+ or e-mail.
http://www.arungudelli.com

Lets have chat