SQL Statement Generator

• Download source files - 50.3 Kb

Introduction

The SQL Statement Generator is a Windows application that loads a DataTable from the specified query and then generates INSERT, UPDATE, or DELETE SQL statements based upon the type of SQL command and the selected columns of the DataTable.

Standard SQL queries can be specified to construct the precise DataTable of the data to generate the SQL statements for. Also, the name of the target table and the columns of the resultant generated SQL statements can be selected to contruct the exact SQL statements you want to generate.

Background

I'll admit it - I hate writing INSERT SQL statements, especially when it involves tables of more than three columns and two-three rows (a rarity...not!). I had a task that involved migrating data from a poorly-architected database to a new database, and later the data would need to be exported again to the production database after the development and testing phases were completed. Initially, this utility only handled INSERT statements, and then was expanded to also include UPDATE and DELETE SQL statements.

Using the Application

The SQL Statement Generator is quick and easy to use - just select the database and/or table/SQL query and the load the results into the grid. Then specify the type of SQL statement to generate and the columns to include in the SQL statements, click the GENERATE button, and voila! - instant SQL statements!

For INSERT statements, all the columns in the result-set are auto-selected by default, except for the zeroth column (since that is typically the primary key).

For UPDATE statements, all the columns in the result-set are auto-selected by default, except for the zeroth column (similar to the INSERT statements). However, the generation process for UPDATE statements has an added step - the WHERE column(s) for the resulting SQL statements need to be specified, because the identity value of the primary keys between the same two tables in different databases is usually different.

For DELETE statements, only the zeroth column in the result-set is auto-selected by default, since DELETEs typically use the primary key to identify which records to delete. Deleting rows by a complex WHERE clause is not always the safest method...

Note: The SQL Server to enumerate the databases and their tables from is specified in the configuration file, and you may need to leave off the Database/Initial Catalog attribute, since all the databases in the datasource/SQL Server are enumerated.

SQL Statement Generator Examples

INSERT Statements

INSERT Results

DELETE Statements and Results

UPDATE Statements

UPDATE Results

The SQL Statement Generator Engine - How It Works

The main engine/processor of the SQL Statement Generator is the SqlScriptGenerator class, which reads the columns of the DataTable and generates the SQL statements based upon the columns selected by the user. Once all the SQL statements are generated, they are dumped to a temp file and then displayed in a text editor (Notepad, by default).

public class SqlScriptGenerator
{
    public static string GenerateSqlInserts(ArrayList aryColumns, 
                                            DataTable dtTable, 
                                            string sTargetTableName)
    {
        string sSqlInserts = string.Empty;
        StringBuilder sbSqlStatements = new StringBuilder(string.Empty);

        // create the columns portion of the INSERT statement
        string sColumns = string.Empty;
        foreach (string colname in aryColumns)
        {
            if (sColumns != string.Empty)
                sColumns += ", ";

            sColumns += colname;
        }

        // loop thru each record of the datatable
        foreach (DataRow drow in dtTable.Rows)
        {
            // loop thru each column, and include
            // the value if the column is in the array
            string sValues = string.Empty;
            foreach (string col in aryColumns)
            {
                if (sValues != string.Empty)
                    sValues += ", ";

                // need to do a case to check the column-value types
                // (quote strings(check for dups first), convert bools)
                string sType = string.Empty;
                try
                {
                    sType = drow[col].GetType().ToString();
                    switch (sType.Trim().ToLower())
                    {
                        case "system.boolean":
                            sValues += (Convert.ToBoolean(drow[col]) 
                                        == true ? "1" : "0");
                            break;

                        case "system.string":
                            sValues += string.Format("'{0}'", 
                                       QuoteSQLString(drow[col]));
                            break;

                        case "system.datetime":
                            sValues += string.Format("'{0}'", 
                                       QuoteSQLString(drow[col]));
                            break;

                        default:
                            if (drow[col] == System.DBNull.Value)
                                sValues += "NULL";
                            else
                                sValues += Convert.ToString(drow[col]);
                            break;
                    }
                }
                catch
                {
                    sValues += string.Format("'{0}'", 
                               QuoteSQLString(drow[col]));
                }
            }

            //   INSERT INTO Tabs(Name) 
            //      VALUES('Referrals')
            // write the insert line out to the stringbuilder
            string snewsql = string.Format("INSERT INTO {0}({1}) ", 
                                            sTargetTableName, sColumns);
            sbSqlStatements.Append(snewsql);
            sbSqlStatements.AppendLine();
            sbSqlStatements.Append('\t');
            snewsql = string.Format("VALUES({0});", sValues);
            sbSqlStatements.Append(snewsql);
            sbSqlStatements.AppendLine();
            sbSqlStatements.AppendLine();
        }

        sSqlInserts = sbSqlStatements.ToString();
        return sSqlInserts;
    }

    public static string GenerateSqlUpdates(ArrayList aryColumns, 
                         ArrayList aryWhereColumns, 
                         DataTable dtTable, string sTargetTableName)
    {
        string sSqlUpdates = string.Empty;
        StringBuilder sbSqlStatements = new StringBuilder(string.Empty);

        // UPDATE table SET col1 = 3, col2 = 4 WHERE (select cols)
        // loop thru each record of the datatable
        foreach (DataRow drow in dtTable.Rows)
        {
            // VALUES clause:  loop thru each column, and include 
            // the value if the column is in the array
            string sValues = string.Empty;
            foreach (string col in aryColumns)
            {
                string sNewValue = col + " = ";
                if (sValues != string.Empty)
                    sValues += ", ";

                // need to do a case to check the column-value types 
                // (quote strings(check for dups first), convert bools)
                string sType = string.Empty;
                try
                {
                    sType = drow[col].GetType().ToString();
                    switch (sType.Trim().ToLower())
                    {
                        case "system.boolean":
                            sNewValue += (Convert.ToBoolean(drow[col]) ==
                                          true ? "1" : "0");
                            break;

                        case "system.string":
                            sNewValue += string.Format("'{0}'", 
                                         QuoteSQLString(drow[col]));
                            break;

                        case "system.datetime":
                            sNewValue += string.Format("'{0}'", 
                                         QuoteSQLString(drow[col]));
                            break;

                        default:
                            if (drow[col] == System.DBNull.Value)
                                sNewValue += "NULL";
                            else
                                sNewValue += Convert.ToString(drow[col]);
                            break;
                    }
                }
                catch
                {
                    sNewValue += string.Format("'{0}'", 
                                 QuoteSQLString(drow[col]));
                }

                sValues += sNewValue;
            }

            // WHERE clause: loop thru each column, and include
            // the value if column is in array
            string sWhereValues = string.Empty;
            foreach (string col in aryWhereColumns)
            {
                string sNewValue = col + " = ";
                if (sWhereValues != string.Empty)
                    sWhereValues += " AND ";

                // need to do a case to check the column-value types
                // (quote strings(check for dups first), convert bools)
                string sType = string.Empty;
                try
                {
                    sType = drow[col].GetType().ToString();
                    switch (sType.Trim().ToLower())
                    {
                        case "system.boolean":
                            sNewValue += (Convert.ToBoolean(drow[col]) == 
                                          true ? "1" : "0");
                            break;

                        case "system.string":
                            sNewValue += string.Format("'{0}'", 
                                         QuoteSQLString(drow[col]));
                            break;

                        case "system.datetime":
                            sNewValue += string.Format("'{0}'", 
                                         QuoteSQLString(drow[col]));
                            break;

                        default:
                            if (drow[col] == System.DBNull.Value)
                                sNewValue += "NULL";
                            else
                                sNewValue += Convert.ToString(drow[col]);
                            break;
                    }
                }
                catch
                {
                    sNewValue += string.Format("'{0}'", 
                                 QuoteSQLString(drow[col]));
                }

                sWhereValues += sNewValue;
            }

            // UPDATE table SET col1 = 3, col2 = 4 WHERE (select cols)
            // write the line out to the stringbuilder
            string snewsql = string.Format("UPDATE {0} SET {1} WHERE {2};", 
                                            sTargetTableName, sValues, 
                                            sWhereValues);
            sbSqlStatements.Append(snewsql);
            sbSqlStatements.AppendLine();
            sbSqlStatements.AppendLine();
        }

        sSqlUpdates = sbSqlStatements.ToString();
        return sSqlUpdates;
    }

    public static string GenerateSqlDeletes(ArrayList aryColumns, 
                                            DataTable dtTable, 
                                            string sTargetTableName)
    {
        string sSqlDeletes = string.Empty;
        StringBuilder sbSqlStatements = new StringBuilder(string.Empty);

        // loop thru each record of the datatable
        foreach (DataRow drow in dtTable.Rows)
        {
            // loop thru each column, and include 
            // the value if the column is in the array
            string sValues = string.Empty;
            foreach (string col in aryColumns)
            {
                string sNewValue = col + " = ";
                if (sValues != string.Empty)
                    sValues += " AND ";

                // need to do a case to check the column-value types
                // (quote strings(check for dups first), convert bools)
                string sType = string.Empty;
                try
                {
                    sType = drow[col].GetType().ToString();
                    switch (sType.Trim().ToLower())
                    {
                        case "system.boolean":
                            sNewValue += (Convert.ToBoolean(drow[col]) ==
                                          true ? "1" : "0");
                            break;

                        case "system.string":
                            sNewValue += string.Format("'{0}'", 
                                         QuoteSQLString(drow[col]));
                            break;

                        case "system.datetime":
                            sNewValue += string.Format("'{0}'", 
                                         QuoteSQLString(drow[col]));
                            break;

                        default:
                            if (drow[col] == System.DBNull.Value)
                                sNewValue += "NULL";
                            else
                                sNewValue += Convert.ToString(drow[col]);
                            break;
                    }
                }
                catch
                {
                    sNewValue += string.Format("'{0}'", 
                                 QuoteSQLString(drow[col]));
                }

                sValues += sNewValue;
            }

            // DELETE FROM table WHERE col1 = 3 AND col2 = '4'
            // write the line out to the stringbuilder
            string snewsql = string.Format("DELETE FROM {0} WHERE {1};", 
                                            sTargetTableName, sValues);
            sbSqlStatements.Append(snewsql);
            sbSqlStatements.AppendLine();
            sbSqlStatements.AppendLine();
        }

        sSqlDeletes = sbSqlStatements.ToString();
        return sSqlDeletes;
    }

    public static string QuoteSQLString(object ostr)
    {
        return ostr.ToString().Replace("'", "''");
    }
    
    public static string QuoteSQLString(string str)
    {
        return str.Replace("'", "''");
    }
}

Conclusion

I hope you find this article and utility useful - it has made working with SQL statements and data migration a snap! Enjoy!