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