以编程方式生成 SQL INSERT 命令

• 下载演示项目和源代码 - 25.4 KB

引言

在使用 C# 中的 TableAdapters 时，VS 会为您生成 INSERT、SELECT 和 UPDATE 等命令。可以通过进入 DataSet Designer 并通过添加 SQL 向导添加命令来添加其他命令。但是，有时您需要包含数据集中所有字段完整列表的附加 SQL 命令。您还希望这些列表在数据库发生更改时自动更新。

此处提供的代码通过使用 Designer 代码中的函数来生成此类字符串来实现此目的。

背景

我实际上开发了这些函数，因为我需要在通过 DataGridView 添加新行时，动态地将行插入数据库并获取 Identity 列的值。

请注意，这仅针对简单函数进行了开发，但基本原理通常适用于其他应用程序。

描述

文件 GenerateSQL.cs 包含静态类 GenerateSQL 的代码，该类具有以下函数

• public static string BuildAllFieldsSQL ( DataTable table )

返回 SELECT 命令等中可以使用的 SQL 格式的 DataTable 中所有列的列表。例如：CustomerID, CustomerName, ....

• public static string BuildInsertSQL ( DataTable table )

返回一个 INSERT 命令，并带有可选的 SELECT CAST 语句，以获取 SCOPE_IDENTITY（如果需要）。例如：INSERT INTO tableName ( CustomerName,...) VALUES (@CustomerName,...); SELECT CAST(scope_identity() AS int )。（请注意，在此示例中，CustomerID 是一个 Identity，因此未将其包含在字符串中。）

• public static SqlCommand CreateInsertCommand ( DataRow row )

给定一个 DataRow，创建一个 SqlCommand 实例，以将数据插入到 DataSet 中。

• public static object InsertDataRow ( DataRow row, string connectionString )

给定 DataRow 和连接字符串，创建上述 SqlCommand 并执行它，返回记录的 Identity。

例如，如果 DataSet Sesigner 定义了一个行，如下所示

QfrsDataSet.MembersRow row;

我可以将其插入数据库，并使用以下语句获取 Identity

int id = (int) GenSQL.GenerateSQL.InsertDataRow ( row, connectionString );

这是完整的代码

using System;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace GenSQL
{
  public static class GenerateSQL
  {
    // Returns a string containing all the fields in the table

    public static string BuildAllFieldsSQL ( DataTable table )
    {
      string sql = "";
      foreach ( DataColumn column in table.Columns )
      {
        if ( sql.Length > 0 )
          sql += ", ";
         sql += column.ColumnName;
      }
      return sql;
                }

    // Returns a SQL INSERT command. Assumes autoincrement is identity (optional)

  public static string BuildInsertSQL ( DataTable table )
  {
    StringBuilder sql = new StringBuilder ( "INSERT INTO " + table.TableName + " (" );
    StringBuilder values = new StringBuilder ( "VALUES (" );
    bool bFirst = true;
    bool bIdentity = false;
    string identityType = null;

    foreach ( DataColumn column in table.Columns )
    {
      if ( column.AutoIncrement )
      {
        bIdentity = true;

        switch ( column.DataType.Name )
        {
          case "Int16":
            identityType = "smallint";
            break;
          case "SByte":
            identityType = "tinyint";
            break;
          case "Int64":
            identityType = "bigint";
            break;
          case "Decimal":
            identityType = "decimal";
            break;
          default:
            identityType = "int";
          break;
         }
      }
      else
      {
        if ( bFirst )
          bFirst = false;
        else
        {
          sql.Append ( ", " );
          values.Append ( ", " );
        }

        sql.Append ( column.ColumnName );
       values.Append ( "@" );
        values.Append ( column.ColumnName );
      }
    }
    sql.Append ( ") " );
    sql.Append ( values.ToString () );
    sql.Append ( ")" );

    if ( bIdentity )
    {
      sql.Append ( "; SELECT CAST(scope_identity() AS " );
      sql.Append ( identityType );
      sql.Append ( ")" );
    }

    return sql.ToString (); ;
  }


    // Creates a SqlParameter and adds it to the command

    public static void InsertParameter ( SqlCommand command,
                                         string parameterName,
                                         string sourceColumn,
                                         object value )
    {
      SqlParameter parameter = new SqlParameter ( parameterName, value );

      parameter.Direction = ParameterDirection.Input;
      parameter.ParameterName = parameterName;
      parameter.SourceColumn = sourceColumn;
      parameter.SourceVersion = DataRowVersion.Current;

      command.Parameters.Add ( parameter );
    }

    // Creates a SqlCommand for inserting a DataRow
    public static SqlCommand CreateInsertCommand ( DataRow row )
    {
      DataTable table = row.Table;
      string sql = BuildInsertSQL ( table );
      SqlCommand command = new SqlCommand ( sql );
      command.CommandType = System.Data.CommandType.Text;

      foreach ( DataColumn column in table.Columns )
      {
        if ( !column.AutoIncrement )
        {
          string parameterName = "@" + column.ColumnName;
          InsertParameter ( command, parameterName, 
                            column.ColumnName, 
                            row [ column.ColumnName ] );
        }
      }
      return command;
    }

    // Inserts the DataRow for the connection, returning the identity
    public static object InsertDataRow ( DataRow row, string connectionString )
    {
      SqlCommand command = CreateInsertCommand ( row );

      using ( SqlConnection connection = new SqlConnection ( connectionString ) )
      {
        command.Connection = connection;
        command.CommandType = System.Data.CommandType.Text;
        connection.Open ();
        return command.ExecuteScalar ();
      }
    }

  }
}

Using the Code

只需将源文件包含在您的程序中并调用这些函数即可。 （非常基础的）示例程序使用 Northwind 代码文件，但不连接到它。