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 SQLcommand and the selected columns of the DataTable.
Standard SQL queries can be specified to construct the precise
DataTable of the data to generate theSQL statements for. Also, the name of the target table and the columns of the resultant generated SQLstatements 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 DELETESQL statements.Using the Application
The SQL Statement Generator is quick and easy to use - just select the database and/or table/SQLquery 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 SQLstatements!
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, sinceDELETEs typically use the primary key to identify which records to delete. Deleting rows by a complexWHERE 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).
Hide Shrink 
Copy Code
Copy Codepublic 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!
No comments:
Post a Comment