SQL Bulk copy from DataTable using XML

To copy data from DataTable or DataSet to SQL Server we need to use either foreach or for loop. It is OK when the count of rows is reasonable. But if the data is like 1 lakh or 1 core, then it is not possible to round the loop for such that time. Then what to do? A simple and easy way to follow is use XML. I am sure you all are more or less aware of this XML. Its a like the database with tags. Or I better to say its a database. It keeps data into a file with extension of  ".xml".

Our intention to convert the DataTable's data into an XML file and send it to server, where with the help of a stored procedure we will extract data and insert into the database table. So lets come see, how to do this...

First of all create a new database and create a new table, named it as you want. Database table structure will be like this.

Name NVarChar(255) not null
Adderss NVarChar(255) not null
Phone NvarChar(12) not null

OK, now we need to create a new DataTable  and put some data on it. Make sure your column and the database table's column are same.

DataTable dt = new DataTable();

dt.Columns.Add("Name");

dt.Columns.Add("Address");

dt.Columns.Add("Phone");

dt.Rows.Add("Arkadeep", "Kolkata", "123456890");

dt.Rows.Add("Saikat", "Chennai", "99999999");

dt.Rows.Add("Sucheta", "Delhi", "9876543210");

Now you have to convert this DataTable into XML. To do this copy and paste the following code after the DataTable section. 

private static string ConvertToXML(DataTable dt)

{

      DataSet dsBuildSQL = new DataSet();

      StringBuilder sbSQL;

      StringWriter swSQL;

      string XMLformat;

      try

      {

           sbSQL = new StringBuilder();

           swSQL = new StringWriter(sbSQL);

           dsBuildSQL.Merge(dt, true, MissingSchemaAction.AddWithKey);

           dsBuildSQL.Tables[0].TableName = "DataTable";

           foreach (DataColumn col in dsBuildSQL.Tables[0].Columns)

           {

               col.ColumnMapping = MappingType.Attribute;

           }

           dsBuildSQL.WriteXml(swSQL, XmlWriteMode.WriteSchema);

           XMLformat = sbSQL.ToString();

           return XMLformat;

       }

       catch (Exception sysException)

       {

           throw sysException;

       }

}

Call this method to convert the DataTable to XML. 

String xmlData = ConvertToXML(dt);

Now pass the value to the stored procedure like the following way.

SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["connection"].ToString());

SqlCommand command = new SqlCommand("sp_InsertData '" + xmlData + "'", conn);

conn.Open();

command.ExecuteNonQuery();

conn.Close();

Now lets check the stored procedure sp_InsertData.

CREATE PROCEDURE sp_InsertData

(@xmlString VARCHAR(MAX))

AS

BEGIN

      DECLARE @xmlHandle INT

      DECLARE @stagingTable TABLE

      (

         [Name]               VARCHAR(50),

         [Address]            VARCHAR(50),

         [Phone]              VARCHAR(50)

      )

              

      EXEC sp_xml_preparedocument @xmlHandle output, @xmlString 

      INSERT INTO @stagingTable

      SELECT  [Name]    ,

                  [Address],

                  [Phone]    

      FROM  OPENXML (@xmlHandle, '/DataTable',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 @stagingTable)

     

      EXEC sp_xml_removedocument @xmlHandle

END

Now run your project and after run this check your database table whether data has been inserted or not...

CodeProject

Read More

Generate Genealogy view in ASP.NET C# using Google Organizational Chart

Previously I  have show you how to create a genealogy view of a family or a tree view in ASP.NET using C#. You can go through that link here.  But that process was some kind of orthodox concept to show the data, because we have used a primary structure of HTML table and put a huge code of all the buttons, labels and images present in the HTML table.

But in this article I will show you how to create a genealogy view of data coming from database with coding of just 20-25 lines. We will use Google organizer chart to create the tree view. Our only motive is to generate the parent and position (left & right) wise data presentation by fetching it from database.

So lets come to the database part first. I cut all other thing except name and parent no. If only one child is present it will show direct under the parent(neither in left nor in right) and two children will show to left and right according to their position in database. So you need to be careful to handle these kinds of small but very effective things when you will do yours.

I will first discuss about the Google organizational chart. Lets check how it works first. Here I am just pasting the  code which will generate the tree view. For more visit this link.

<script>

google.setOnLoadCallback(drawChart);

function drawChart() {

     var data = new google.visualization.DataTable();

     data.addColumn('string', 'Name');

     data.addColumn('string', 'Manager');

         

     data.addRows([['Mike',''], ['Jim', 'Mike'], ['Alice', 'Mike'],['Bob','Jim'],['Carol', 'Jim']]);

     var chart = new google.visualization.OrgChart(document.getElementById('chart_div'));

     chart.draw(data, { allowHtml: true });

}

</script>

This code is not similar to the code present in the website. I took only the things that will help me to do the primary things. Others can be added later.

All these codes are basically based on the data. Data containing of Mike, Jim, Alice, Bob etc. So if we can replace it with our value, which is coming from database then our work will be done. So first lets fetch the data from database and form a string like does here.

string s = "";

DataTable table = new DataTable();

table.Columns.Add("name", typeof(string));

table.Columns.Add("parent", typeof(string));

table.Rows.Add("Mike", "");

table.Rows.Add("Jim", "Mike");

table.Rows.Add("Alice", "Mike");

table.Rows.Add("Carol", "Jim");

for (int i = 0; i < table.Rows.Count; i++)

{

     s = s + "['"+table.Rows[i][0].ToString()+"','"+table.Rows[i][1].ToString()+"'],";

}

s = s.TrimEnd(',');

I only took a DataTable and bind it some dummy data. You need to bind it with SQL to fetch it from DB.

Now our string "s" is ready to launch as a bunch of data to Google organizational chart. Now how to add this data to javascript fucntion to call it or pass it.

The way I did it here, call a javascript fucntion which is created within code behind. Lets see how I did this.

String csname1 = "PopupScript";

Type cstype = this.GetType();

ClientScriptManager cs = Page.ClientScript;

if (!cs.IsStartupScriptRegistered(cstype, csname1))

{

     StringBuilder cstext1 = new StringBuilder();

     cstext1.Append("<script>");

     cstext1.Append("google.setOnLoadCallback(drawChart);");

     cstext1.Append("function drawChart() {");

     cstext1.Append("var data = new google.visualization.DataTable();");

     cstext1.Append("data.addColumn('string', 'Name'); data.addColumn('string', 'Manager');");

     cstext1.Append("data.addRows(["+s+"]);");

     cstext1.Append("var chart = new google.visualization.OrgChart(document.getElementById('chart_div'));");

     cstext1.Append("chart.draw(data, { allowHtml: true });");

     cstext1.Append("}");

     cstext1.Append("</script>");

      cs.RegisterStartupScript(cstype, csname1, cstext1.ToString());

 }

And one more thing don't forget to add a "chart_div" in the ASPX page. Its the thing which is holding all the tree view.

Output :

Download the whole source code here. 

Download Now

CodeProject

Read More