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.
Step 2: Write the following stored Procedure:
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:
protected void Page_Load(object sender, EventArgs e)
{
string strConString =
@"Data Source=.\SQLEXPRESS;Integrated Security=True;Initial Catalog=Test";
using (StringWriter swStringWriter = new StringWriter())
{
DataTable dtEmpDetails = GetEmpDetails();
dtEmpDetails.WriteXml(swStringWriter);
string strEmpDetails = swStringWriter.ToString();
using (SqlConnection dbConnection = new SqlConnection(strConString))
{
using (SqlCommand dbCommand =
new SqlCommand("sp_InsertEmpDetail",dbConnection))
{
dbCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@EmpDetails";
parameter.DbType = DbType.Xml;
parameter.Direction = ParameterDirection.Input;
parameter.Value = strEmpDetails;
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.
private DataTable GetEmpDetails()
{
DataTable dtEmpDetails = new DataTable("EmpDetails");
dtEmpDetails.Columns.Add(new DataColumn("EmpName",
Type.GetType("System.String")));
dtEmpDetails.Columns.Add(new DataColumn("EmpAddress",
Type.GetType("System.String")));
DataRow drRow = dtEmpDetails.NewRow();
drRow["EmpName"] = "Emp-1";
drRow["EmpAddress"] = "Emp1-Addr-1";
dtEmpDetails.Rows.Add(drRow);
drRow = dtEmpDetails.NewRow();
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:
If we set a debug point while running the application, we can see following XML string in the quick watch window:
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.
Step 2: Write the following Stored Procedure:
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:
using (StringWriter swStringWriter = new StringWriter())
{
string strConString =
@"Data Source=lops;UserID=lops;Password=lops;Unicode=True";
DataTable dtEmpDetails = GetEmpDetails();
dtEmpDetails.WriteXml(swStringWriter);
string strEmpDetails = swStringWriter.ToString();
using (OracleConnection dbConnection =
new OracleConnection(strConString))
{
using (OracleCommand dbCommand =
new OracleCommand("InsertEmpDetail", dbConnection))
{
dbCommand.CommandType = CommandType.StoredProcedure;
OracleParameter parameter = new OracleParameter();
parameter.ParameterName = "p_EmpDetails";
parameter.DbType = DbType.String;
parameter.Direction = ParameterDirection.Input;
parameter.Value = strEmpDetails;
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:
Hope you enjoy the technique.