Click here to Skip to main content
15,867,453 members
Articles / Web Development / ASP.NET

ADO.NET DataTable as XML parameter to an Oracle/SQL Server Database Stored Procedure

Rate me:
Please Sign up or sign in to vote.
4.87/5 (19 votes)
27 Jan 2010CPOL3 min read 102.9K   45   18
ADO.NET DataTable as an XML parameter to an Oracle/SQL Server database Stored Procedure.

Introduction

A very common question among .NET developers is how to pass an ADO.NET DataTable to a database Stored Procedure as an input parameter. This not only minimizes unwanted database interaction but also reduces development complexity. This is also one of the key points to boost up application performance.

In my last project, I customized the ASP.NET GridView in an Excel sheet manner which allowed the user to access the entire data in editable format. When users clicked on the "Submit" button, I had to capture all the modified data and update it in the database.

The solution I adopted was to iterate through each grid row and update each row in the database. Later, I noticed a lot of obstruction in the database connectivity since 100 rows in the grid was causing 100 consecutive database connections.

Resolution

I was taken aback when I came to know that converting the whole ADO.NET DataTable as XML and passing it to the Stored Procedure is the suitable and easiest way. I immediately modified my code, which drastically reduced database connectivity overhead and expedited performance.

A good point of dealing with XML is its wide acceptability. I already incorporated this concept with SQL Server 2005 and Oracle 9i, and strongly believe this will work for other databases like DB2 and MySQL also.

Note: SQL Server 2008 has a cool feature which allows an ADO.NET DataTable to be directly passed as an input parameter to a Stored Procedure. In this case, no XML conversion is required. I am about to write another article very soon.

ADO.NET DataTable as XML to a SQL Server Stored Procedure

Step 1: Create a table "EmpDetails" in the SQL Server database.

image1.png

Step 2: Write the following stored Procedure:

image2.png

Notice: The procedure sp_InsertEmpDetails accepts an XML type as an input parameter. The SQL Server XML parsing mechanism is used to parse the XML data.

Step 3: Write the following .NET code:

C#
protected void Page_Load(object sender, EventArgs e)
{
    //Database connection string
    string strConString = 
      @"Data Source=.\SQLEXPRESS;Integrated Security=True;Initial Catalog=Test";
    using (StringWriter swStringWriter = new StringWriter())
    {
       // Emp details datatable â€" ADO.NET DataTable 
       DataTable dtEmpDetails = GetEmpDetails();
       // Datatable as XML format 
       dtEmpDetails.WriteXml(swStringWriter);
       // Datatable as XML string 
       string strEmpDetails = swStringWriter.ToString();
       using (SqlConnection dbConnection = new SqlConnection(strConString))
       //Create database connection  
       {
           // Database command with stored - procedure  
           using (SqlCommand dbCommand = 
                  new SqlCommand("sp_InsertEmpDetail",dbConnection))
           {
              // we are going to use store procedure  
              dbCommand.CommandType = CommandType.StoredProcedure;
              // Add input parameter and set its properties.
              SqlParameter parameter = new SqlParameter();
              // Store procedure parameter name  
              parameter.ParameterName = "@EmpDetails";
              // Parameter type as XML 
              parameter.DbType = DbType.Xml;
              parameter.Direction = ParameterDirection.Input; // Input Parameter  
              parameter.Value = strEmpDetails; // XML string as parameter value  
              // Add the parameter in Parameters collection.
              dbCommand.Parameters.Add(parameter);
              dbConnection.Open();
              int intRetValue = dbCommand.ExecuteNonQuery(); 
            }
       }
   }
}
  • dtEmpDetails.WriteXml(swStringWriter) - This code emits an XML string from an ADO.NET DataTable.
  • parameter.DbType = DbType.Xml - Signifies a Stored Procedure parameter is the XML type.

Step 4: Write the following utility function. In production, this will represent real data.

C#
private DataTable GetEmpDetails()
{
   DataTable dtEmpDetails = new DataTable("EmpDetails");
   // Emp table and its schema 
    
   dtEmpDetails.Columns.Add(new DataColumn("EmpName", 
                Type.GetType("System.String"))); //EmpName Colume 

   dtEmpDetails.Columns.Add(new DataColumn("EmpAddress", 
                Type.GetType("System.String"))); //EmpAddress Colume
  
   DataRow drRow = dtEmpDetails.NewRow(); // First Employee  
   drRow["EmpName"] = "Emp-1";
   drRow["EmpAddress"] = "Emp1-Addr-1";
   dtEmpDetails.Rows.Add(drRow);
   drRow = dtEmpDetails.NewRow(); // Second Employee  
   drRow["EmpName"] = "Emp-2";
   drRow["EmpAddress"] = "Emp2-Addr-2";
   dtEmpDetails.Rows.Add(drRow);
   return dtEmpDetails;
}

Now, run the application and go to the SQL Server query window. We can see the following records are inserted:

image3.png

If we set a debug point while running the application, we can see following XML string in the quick watch window:

Image 4

ADO.NET DataTable as XML to an Oracle Stored Procedure

In this example, I have used the Microsoft provided Oracle provider (System.Data.OracleClient).

Step 1: Create a table "EmpDetails" in the Oracle database.

image5.png

Step 2: Write the following Stored Procedure:

image6.png

Notice: The database procedure InsertEmpDetails accepts an XML string as an input parameter, which will be parsed and the data will be stored into the EmpDetails table.

Note: The Microsoft provided Oracle provider doesn't allow to pass XMLTYPE as a direct parameter. We need to pass an XML string, and inside the Stored Procedure, we will convert it to XMLTYPE.

Step 3: In the form load event, write the following code:

C#
using (StringWriter swStringWriter = new StringWriter())
{
   //DB connection string  
   string strConString = 
     @"Data Source=lops;UserID=lops;Password=lops;Unicode=True";
   DataTable dtEmpDetails = GetEmpDetails(); // Emp details datatable
   // Datatable as XML format
   dtEmpDetails.WriteXml(swStringWriter);
   // Datatable as XML string  
   string strEmpDetails = swStringWriter.ToString();
  //Create database connection
  using (OracleConnection dbConnection = 
             new OracleConnection(strConString))
  {
     // Database command
     using (OracleCommand dbCommand = 
             new OracleCommand("InsertEmpDetail", dbConnection))
     {
         // we are going to use store procedure  
         dbCommand.CommandType = CommandType.StoredProcedure;
         // Add input parameter and set its properties.
         OracleParameter parameter = new OracleParameter();
         // Store procedure parameter name 
         parameter.ParameterName = "p_EmpDetails";
         // Parameter type as XML 
         parameter.DbType = DbType.String;
         // Input Parameter
         parameter.Direction = ParameterDirection.Input;
         // XML string as parameter value
         parameter.Value = strEmpDetails;
         // Add the parameter to the Parameters collection.
         dbCommand.Parameters.Add(parameter);
         dbConnection.Open(); 
         int intRetValue = dbCommand.ExecuteNonQuery();
        } 
     } 
}

Now, run the application and go to the Oracle query window. We can see the following records are inserted:

image7.png

Hope you enjoy the technique.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Reputed MNC in Kolkata
India India
Bibhas has 8 years of extensive experience in application development with exposure to business requirement study, system analysis and designing, coding ,testing,
implementation, end user training and client Interaction.

He was the part of application development team and worked for industry leading organizations like "ConocoPhillips", "Abbey National Bank" and “DHL".

Posses sound experience as a technical architect and all part of software development lifecycle.

His interest includes in Microsoft Technologies (ASP.NET 3.5 & SQL Server), Design Pattern and wide variety of internet technologies like AJAX, JQUERY etc.

Comments and Discussions

 
QuestionThrowing error on oracle. Pin
Member 1057542116-Feb-15 23:14
Member 1057542116-Feb-15 23:14 
GeneralMy vote of 5 Pin
SagnikMukherjee12-Feb-13 22:15
SagnikMukherjee12-Feb-13 22:15 
Questionmy vote of 5 Pin
zzymt23-Sep-11 19:29
zzymt23-Sep-11 19:29 
GeneralMy vote of 4 Pin
anees sabir10-Jan-11 18:44
anees sabir10-Jan-11 18:44 
General5 stars mate - well written article Pin
App Software5-Oct-10 23:46
App Software5-Oct-10 23:46 
GeneralMy vote of 5 Pin
App Software5-Oct-10 23:40
App Software5-Oct-10 23:40 
GeneralMy vote of 5 Pin
moparthisai6-Aug-10 3:22
moparthisai6-Aug-10 3:22 
GeneralAbout stored procedure Pin
moparthisai6-Aug-10 3:12
moparthisai6-Aug-10 3:12 
GeneralNeed Help to get it done Pin
bonny shah23-Jul-10 12:01
bonny shah23-Jul-10 12:01 
GeneralThank you for this idea. Great SAVE of application - DB traffic. Waiting for multiple insert of rows 10 years. Pin
mishkasp13-Jun-10 3:51
mishkasp13-Jun-10 3:51 
QuestionDoes the oracle procedure really works???? Pin
sureshmathan22-Apr-10 1:44
sureshmathan22-Apr-10 1:44 
GeneralNice usage to save database trip Pin
saha_sanjib27-Jan-10 19:58
saha_sanjib27-Jan-10 19:58 
GeneralWhy do this way Pin
gaurav_verma_mca26-Jan-10 23:50
gaurav_verma_mca26-Jan-10 23:50 
GeneralRe: Why do this way Pin
gaurav_verma_mca26-Jan-10 23:51
gaurav_verma_mca26-Jan-10 23:51 
GeneralRe: Why do this way Pin
tegdevelop1-Feb-10 21:12
tegdevelop1-Feb-10 21:12 
For Bibhas Paul:
I apriciate the sample you provide, even if "someone" doesn't like it, most of all because I've used this tecnique some years ago, and then because it was a method suggested by some MSVP to have the same interface for the stored procedures instead of pass many parameters as single entities.

For gaurav_verma_mca:
I know that the world is going on, and that with SqlServer 2008 now you can do things in a better way, but the use of "Table Value Parameters" would be better for data types, but you have to do much more work instead of use the XML tecnique (define the Table type in the database, create the same data table type in the code to match "exactly" the Table type in the database, and so the maintenance is hard-typed).
Then, in your article, you provide an example only for SqlServer 2008, and Oracle ?

In my opinion, both the tecniques are valid, it depends how and where you need to use one of them.
Have a nice day


Respect other people as you respect yourself
GeneralRe: Why do this way Pin
gaurav_verma_mca2-Feb-10 0:02
gaurav_verma_mca2-Feb-10 0:02 
GeneralRe: Why do this way Pin
Bibhas Paul2-Feb-10 16:38
Bibhas Paul2-Feb-10 16:38 
GeneralRe: Why do this way Pin
Bibhas Paul2-Feb-10 16:32
Bibhas Paul2-Feb-10 16:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.