private string GenerateTableBodySql(string query, GenerateOptions options)
{
StringBuilder sb = new StringBuilder();
using (SqlConnection conn = new SqlConnection(m_connStr)) {
conn.Open();
using (SqlCommand cmd = new SqlCommand(query, conn)) {
DataTable table = GetSchemaTable(cmd);
bool compositePK = table.Select("IsKey = 1").Length > 1;
bool first = true;
foreach (DataRow row in table.Rows)
{
if (!first)
{
sb.Append(",\n");
}
sb.AppendFormat("\t{0}", GetColumnDefinition(row, compositePK, options));
first = false;
}
if (compositePK)
{
sb.AppendFormat(",\n\t{0}", GetCompositePK(table, options.PrimaryKeyName));
}
}
}
return(sb.ToString());
}
string GetColumnDefinition(DataRow row, bool isCompositeKey, GenerateOptions options)
{
StringBuilder sb = new StringBuilder((string)row["ColumnName"]);
sb.AppendFormat(" {0}", (string)row["DataTypeName"]);
string size = (string)row["Size"];
if (!string.IsNullOrEmpty(size))
{
sb.AppendFormat("({0})", size);
}
bool isKey = (bool)row["IsKey"];
if (!isCompositeKey && options.GenerateKeys && isKey)
{
if (string.IsNullOrEmpty(options.PrimaryKeyName))
{
sb.Append(" PRIMARY KEY");
}
else
{
sb.AppendFormat(" CONSTRAINT {0} PRIMARY KEY", options.PrimaryKeyName);
}
}
sb.AppendFormat(" {0}", (bool)row["AllowDBNull"] ? "NULL" : "NOT NULL");
return(sb.ToString());
}
public string GenerateTableVariable(string variableName, string query, GenerateOptions options)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("declare {0} table\n", variableName);
sb.Append("(\n");
sb.Append(GenerateTableBodySql(query, options));
sb.AppendLine("\n);");
return(sb.ToString());
}