将 ADO.NET DataTable 作为 XML 参数传递给 Oracle/SQL Server 数据库存储过程

引言

在 .NET 开发人员中，一个非常常见的问题是如何将 ADO.NET DataTable 作为输入参数传递给数据库存储过程。这不仅最大限度地减少了不必要的数据库交互，而且降低了开发的复杂性。这也是提高应用程序性能的关键点之一。

在我上一个项目中，我以 Excel 表格的方式自定义了 ASP.NET GridView，允许用户以可编辑的格式访问整个数据。当用户单击“提交”按钮时，我必须捕获所有修改后的数据并将其更新到数据库中。

我采用的解决方案是遍历每个网格行并更新数据库中的每一行。 后来，我注意到数据库连接中存在很多障碍，因为网格中的 100 行导致了 100 个连续的数据库连接。

分辨率

当我了解到将整个 ADO.NET DataTable 转换为 XML 并将其传递给存储过程是合适且最简单的方法时，我感到震惊。我立即修改了我的代码，这大大减少了数据库连接开销并加快了性能。

处理 XML 的一个优点是它的广泛接受性。 我已经将这个概念与 SQL Server 2005 和 Oracle 9i 结合使用，并且坚信它也适用于其他数据库，如 DB2 和 MySQL。

注意：SQL Server 2008 有一个很棒的功能，允许将 ADO.NET DataTable 直接作为输入参数传递给存储过程。 在这种情况下，不需要 XML 转换。 我很快就会写另一篇文章。

将 ADO.NET DataTable 作为 XML 传递给 SQL Server 存储过程

步骤 1：在 SQL Server 数据库中创建一个表“EmpDetails”。

步骤 2：编写以下存储过程

注意：过程 sp_InsertEmpDetails 接受 XML 类型作为输入参数。SQL Server XML 解析机制用于解析 XML 数据。

步骤 3：编写以下 .NET 代码

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) - 此代码从 ADO.NET DataTable 发出 XML 字符串。
• parameter.DbType = DbType.Xml - 表示存储过程参数是 XML 类型。

步骤 4：编写以下实用程序函数。 在生产中，这将代表真实数据。

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;
}

现在，运行应用程序并转到 SQL Server 查询窗口。 我们可以看到插入了以下记录

如果我们在运行应用程序时设置一个调试点，我们可以在快速监视窗口中看到以下 XML 字符串

将 ADO.NET DataTable 作为 XML 传递给 Oracle 存储过程

在此示例中，我使用了 Microsoft 提供的 Oracle 提供程序 (System.Data.OracleClient)。

步骤 1：在 Oracle 数据库中创建一个表“EmpDetails”。

步骤 2：编写以下存储过程

注意：数据库过程 InsertEmpDetails 接受 XML 字符串作为输入参数，该参数将被解析，数据将存储到 EmpDetails 表中。

注意：Microsoft 提供的 Oracle 提供程序不允许将 XMLTYPE 作为直接参数传递。 我们需要传递一个 XML 字符串，并在存储过程内部将其转换为 XMLTYPE。

步骤 3：在窗体加载事件中，编写以下代码

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();
        } 
     } 
}

现在，运行应用程序并转到 Oracle 查询窗口。 我们可以看到插入了以下记录

希望你喜欢这个技术。