使用ODBC连接从DataTable批量插入到SQL Server表

问题描述：

我被要求研究如何找到最有效的方式来获取DataTable输入并使用C＃将其写入SQL Server表。问题在于解决方案必须始终使用ODBC连接，这会排除sqlBulkCopy。该解决方案还必须适用于SQL Server 2008 R2的所有SQL Server版本。使用ODBC连接从DataTable批量插入到SQL Server表

我想，最好的方法是使用下面的SQL语法使用的1000行批量插入在一个时间：

INSERT INTO dbo.Table1（字段1，字段2） SELECT值1，值 UNION SELECT Value1，Value2

我已经编写了代码，检查SQL Server上是否存在与DataTable输入对应的表，如果没有，则创建一个。

我也写了代码来创建INSERT语句本身。我所苦恼的是如何从数据表中的行中动态构建SELECT语句。我如何访问行中的值来构建我的SELECT语句？我想我还需要检查每列的数据类型，以确定值是否需要用单引号（'）括起来。

这里是我当前的代码：

 public bool CopyDataTable(DataTable sourceTable, OdbcConnection targetConn, string targetTable) 
    { 
     OdbcTransaction tran = null; 
     string[] selectStatement = new string[sourceTable.Rows.Count]; 

     // Check if targetTable exists, create it if it doesn't 
     if (!TableExists(targetConn, targetTable)) 
     { 
      bool created = CreateTableFromDataTable(targetConn, sourceTable); 

      if (!created) 
       return false; 
     } 

     try 
     { 
      // Prepare insert statement based on sourceTable 
      string insertStatement = string.Format("INSERT INTO [dbo].[{0}] (", targetTable); 

      foreach (DataColumn dataColumn in sourceTable.Columns) 
      { 
       insertStatement += dataColumn + ","; 
      } 

      insertStatement += insertStatement.TrimEnd(',') + ") "; 

      // Open connection to target db 
      using (targetConn) 
      { 
       if (targetConn.State != ConnectionState.Open) 
        targetConn.Open(); 

       tran = targetConn.BeginTransaction(); 

       for (int i = 0; i < sourceTable.Rows.Count; i++) 
       { 
        DataRow row = sourceTable.Rows[i]; 

        // Need to iterate through columns in row, getting values and data types and building a SELECT statement 

        selectStatement[i] = "SELECT "; 
       } 

       insertStatement += string.Join(" UNION ", selectStatement); 

       using (OdbcCommand cmd = new OdbcCommand(insertStatement, targetConn, tran)) 
       { 
        cmd.ExecuteNonQuery(); 
       } 

       tran.Commit(); 
       return true; 
      } 
     }  
     catch 
     { 
      tran.Rollback(); 
      return false; 
     } 
    }

任何建议，将不胜感激。另外，如果比我建议的方法更简单，那么任何细节都会很棒。

你是否也排除了ADO.net表参数？这是一个奇怪的限制，因为将表参数传递给存储过程，并且在传入表上使用MERGE命令是实现此目的的最有效方法。 – PhillipH

问题是我们将在我们写入的SQL Server上拥有非常有限的权限。我们在桌上有CRUD，就是这个。任何涉及存储过程和/或TVP的解决方案都不在我的范围内。 –

答

好吧，因为我们不能使用存储过程或批量复制;当我在几年前对各种方法进行建模时，性能的关键决定因素是对服务器的调用数量。因此，将一组MERGE或INSERT语句分配到以分号分隔的单个调用中是最快的方法。我结束了我的SQL语句批处理。我认为SQL语句的最大大小是32k，所以我把我的批量切成了这个大小的单位。

（注 - 使用StringBuilder，而不是手动连接字符串 - 这对性能有有益的影响）

Psuedo-code 
string sqlStatement = "INSERT INTO Tab1 VALUES {0},{1},{2}"; 
StringBuilder sqlBatch = new StringBuilder(); 
foreach(DataRow row in myDataTable) 
{ 
    sqlBatch.AppendLine(string.Format(sqlStatement, row["Field1"], row["Field2"], row["Field3"])); 
    sqlBatch.Append(";"); 
} 
myOdbcConnection.ExecuteSql(sqlBatch.ToString());

您需要处理批量大小的并发症，并在字符串中正确的字段的数据类型的格式更换步骤，但否则这将是最佳性能。

您是否能够共享您用于批量生成SQL语句的代码？那就是我目前被卡住的地方。 –

非常感谢，很好。 –

答

PhillipH的标记解决方案是开放的几个错误和SQL注入。

通常情况下，您应该使用参数构建一个DbCommand并执行此操作，而不是执行自建SQL语句。

对于ODBC和OLEDB，CommandText必须为"INSERT INTO Tab1 VALUES ?,?,?"，SqlClient需要命名参数（“@ <名称>”）。

参数应添加与下垫柱的尺寸。

使用ODBC连接从DataTable批量插入到SQL Server表

相关推荐