Passing DataTable as Input Parameter in C#

The Prologue

Often there are times when you need to insert a large list of data or a datatable into your database. In later versions of SQL you can do that easily by sending a datatable as a parameter, but for previous versions (or in my case MySQL) you cannot do that.

I am providing you an alternate way of sending data to a db procedure by changing the data table to a list, a code to convert that list to XML and finally a procedure that can change the XML into a temporary table within a procedure.

Implementation

First of all create a property in which you can save your values. To do so use the following procedure:

• Create a new class named as you require. In my example it's MyClass.
• Set the class as Serializable because we will be using this file structure in our Stored Procedure.
• Define all the properties. In my example it's Id (int) and name (string) and set them as [XmlAttribute], since in a later procedure this will become  the attribute of your XML file.

Refer to the following Image for the class structure.

 

      The following will fill in the data to the class.

• First of all create a list instance of the class you have created. This list will store all the data/values present in your datatable. In my example:
  

  List<MyClass> myList = new List<MyClass>()

• Since you want to send a datatable to the procedure we will require a loop so that we call fill the list with the value of the datatable. Suppose we have a datatable named tempTable with the columns Id and Name.
  
  
• Inside the loop we will create one more instance of the class so that we can access properties of the class.
  

  for(int i = 0;i<dt.Rows.Count;i++)
  {
     MyClass mySingle = new MyClass();
     mySingle.id =Convert.ToInt32( dt.Rows[i]["Id"].toString());
     mySingle.Name = dt.Rows[i]["Name"].toString();
     myList.Add(mySingle);
  }

• The preceding Code will copy the data of the datatable to our list. 

Create a function to convert this list into a XML file. Use the following code to do that.

The XML is created using the following code:

      Pass this XML file to the procedure using the parameter. I am using MySQL LongText as the value type. Use the longest possible string value available for your dbtype.

mysqlCommand.Parameters.Add("_XMLValue",MySqlDbType.LongText);
mysqlCommand.Parameters["_XMLValue"].Value = GenerateToXml(myList);

As you can see from the preceding code, in the value field of the parameter we are using our GenerateToXml(...)  function along with the list (created and filled in in the preceding procedure) as the argument.

 

Writing procedure to create a temporary table and fill in the value from your XML file.

 

In this query you will see that I have used “MyClass” (that is the name of the class I have created for properties) for counting the number of rows and also to extract values from the XML file.

In line 14 of the preceding code you will observe a line of code as in the following:

extractvalue(_XMLValue, 'count(//MyClass)')

The preceding code will count the of number of rows available in your XML file. Internally it is done by counting the number of nodes present with the name “MyClass”.

You will see that I have used a while loop to loop through the values of the XML file. With a simple insert query you can insert the values present in your XML file into the newly created temp table.

extractvalue(_XMLValue, '//MyClass[$xmlIndex]/@Id'),

That will return the value of the Id property present within the MyClass of the row positioned by the $xmlIndex variable.

Once the Loop execution is over you will get all the data present in the XML file in your newly created temp table.

You can check that using a simple select query.