Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server

Generate SQL INSERT commands programmatically

Rate me:
Please Sign up or sign in to vote.
4.48/5 (20 votes)
29 Jun 2006CPOL2 min read 269.2K   3.7K   56   31
A class for automatically generating SQL INSERT for Typed Datasets.

Introduction

When you use TableAdapters in C#, VS generates INSERT, SELECT, and UPDATE etc. commands for you. Other commands can be added by going into the DataSet Designer and adding commands via the Add SQL Wizard. Sometimes, however, you need additional SQL commands that contain a complete list of all the fields in the DataSet. You would also like these lists to be automatically updated whenever a change is made to the database.

The code presented here does this by using functions in the Designer code to generate such strings.

Background

I actually developed these functions because I needed to INSERT rows into a database and get the value of the Identity column on the fly as I tab through a DataGridView adding new rows.

Note that this has only been developed for simple functions, but the basics are generally applicable for other applications.

Description

The file GenerateSQL.cs contains the code for a static class GenerateSQL, which has the following functions:

  • public static string BuildAllFieldsSQL ( DataTable table )
  • Returns a list of all the columns in the DataTable in SQL format which can be used in a SELECT command etc. E.g.: CustomerID, CustomerName, ....

  • public static string BuildInsertSQL ( DataTable table )
  • Returns an INSERT command with an optional SELECT CAST statement to get the SCOPE_IDENTITY if required. E.g.: INSERT INTO tableName ( CustomerName,...) VALUES (@CustomerName,...); SELECT CAST(scope_identity() AS int ). (Note that in this example, CustomerID is an Identity so it isn't included in the string.)

  • public static SqlCommand CreateInsertCommand ( DataRow row )
  • Given a DataRow, creates an instance of SqlCommand to insert the data into the DataSet.

  • public static object InsertDataRow ( DataRow row, string connectionString )
  • Given the DataRow and a connection string, creates the SqlCommand as above and executes it, returning the identity of the record.

    For example, if the Dataset Sesigner has defined a row like:

    C#
    QfrsDataSet.MembersRow row;

    I can insert it into the database, getting the identity with the statement:

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

Here is the complete code:

C#
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

Just include the source file in your program and call the functions. The (extremely basic) sample program uses the Northwind code files, but does not connect to it.

License

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



Comments and Discussions

 
Questiontry this. Pin
Sushil Mate18-Mar-16 2:18
Sushil Mate18-Mar-16 2:18 
QuestionSqlBulkCopy Pin
obratim8-Jun-15 22:37
obratim8-Jun-15 22:37 
GeneralMy vote of 5 !! Pin
Member 474037816-Dec-13 20:18
Member 474037816-Dec-13 20:18 
GeneralMy vote of 5 Pin
grigorij8913-Sep-12 3:53
grigorij8913-Sep-12 3:53 
Suggestion1 small change Pin
leote11-Apr-12 1:22
leote11-Apr-12 1:22 
GeneralMy vote of 5 Pin
eibbed10-Aug-11 15:26
eibbed10-Aug-11 15:26 
GeneralThanks a lot Pin
ashu khanna22-Apr-11 7:43
ashu khanna22-Apr-11 7:43 
GeneralUnable to use InsertDataRow Pin
kellylc20-Sep-07 21:26
kellylc20-Sep-07 21:26 
GeneralRe: Unable to use InsertDataRow Pin
Ian Semmel25-Sep-07 10:16
Ian Semmel25-Sep-07 10:16 
GeneralVisual Basic Version of the Code (included) Pin
Dan_Bruton5-Aug-07 15:55
Dan_Bruton5-Aug-07 15:55 
GeneralThank You Pin
Lev Vayner.31-Jul-07 4:45
professionalLev Vayner.31-Jul-07 4:45 
Questiondestination field is of a different data type Pin
kissa4929-Jun-07 8:23
kissa4929-Jun-07 8:23 
Generalinsert datatable into SQL SERVER table using single query Pin
rajnish_haldiya12-Jun-07 22:48
rajnish_haldiya12-Jun-07 22:48 
GeneralRe: insert datatable into SQL SERVER table using single query Pin
Ian Semmel13-Jun-07 10:28
Ian Semmel13-Jun-07 10:28 
GeneralRe: insert datatable into SQL SERVER table using single query Pin
rajnish_haldiya14-Jun-07 0:26
rajnish_haldiya14-Jun-07 0:26 
GeneralRe: insert datatable into SQL SERVER table using single query Pin
Lev Vayner.30-Jul-07 10:01
professionalLev Vayner.30-Jul-07 10:01 
GeneralRe: insert datatable into SQL SERVER table using single query Pin
rajnish_haldiya31-Jul-07 21:06
rajnish_haldiya31-Jul-07 21:06 
GeneralRe: insert datatable into SQL SERVER table using single query Pin
Lev Vayner.24-Sep-07 5:30
professionalLev Vayner.24-Sep-07 5:30 
GeneralUsing GUID Pin
ppro2-Feb-07 14:54
ppro2-Feb-07 14:54 
QuestionProblem with Identity Pin
gregoryayca29-Nov-06 4:10
gregoryayca29-Nov-06 4:10 
QuestionWhat about binary data? Pin
mcgahanfl20-Oct-06 9:46
mcgahanfl20-Oct-06 9:46 
NewsTry SqlCommandBuilder class Pin
Abi Bellamkonda25-Jun-06 20:36
Abi Bellamkonda25-Jun-06 20:36 
GeneralRe: Try SqlCommandBuilder class Pin
Ian Semmel27-Jun-06 21:29
Ian Semmel27-Jun-06 21:29 
GeneralRe: Try SqlCommandBuilder class Pin
Abi Bellamkonda27-Jun-06 21:43
Abi Bellamkonda27-Jun-06 21:43 
GeneralRe: Try SqlCommandBuilder class Pin
Stumper7-Jul-06 3:45
Stumper7-Jul-06 3:45 

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.