自动生成ＳＱＬ
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("'", "''");
}
}</PRE>
