public SqlTableParams GetTableFormat(string tableName)
{
DataTable dtFmt = null;
try
{
this.LoadDataTable("SELECT TOP 1 * FROM " + tableName + " (nolock)", dtFmt);
SqlTableParams p = new SqlTableParams(dtFmt);
return p;
}
catch
{ throw; }
finally
{
if (dtFmt != null)
dtFmt.Dispose();
}
}
public static string CreateTable(SqlTableParams tableFormat, SqlConnection conn)
{ return rsDbSql.CreateTable(tableFormat, conn, null); }
public static string CreateTable(SqlTableParams tableFormat, SqlConnection conn, SqlTransaction transaction)
{
try
{
if (conn.State != ConnectionState.Open)
throw new ArgumentException("Connection must be valid and in an 'Open' state.", "conn");
// Check to see if the specified table already exists on the SQL server.
if (rsDbSql.TableExists(conn, tableFormat.TableName, tableFormat.TableOwner, transaction))
throw new Exception("Specified table already exists");
// Build the create table script.
StringBuilder sb = new StringBuilder();
//sb.AppendLine(string.Format("USE {0}", tableFormat.DatabaseName));
sb.AppendLine(string.Format("CREATE TABLE [{0}].[{1}] ( ", tableFormat.TableOwner, tableFormat.TableName));
// Loop through the tableFormat's column list
for (int i = 0; i < tableFormat.Columns.Count; i++)
{
ColumnParams col = tableFormat.Columns[i];
sb.AppendLine(string.Format("\t{0}[{1}] [{2}] {3} {4} {5} NULL {5}",
(i > 0) ? "," : " ",
col.ColumnName,
col.DataType.ToString(),
(col.DataType == SqlDbType.VarChar && (col.FieldSize > 0 || col.FieldSize == -2))
? "(" + ((col.FieldSize == -2) ? "MAX" : col.FieldSize.ToString()) + ")"
: "",
(col.IsIdentity)
? "IDENTITY(" + col.IdentitySeed.ToString() + "," + col.IdentityIncrement.ToString() + ")"
: "",
(col.IsNullable)
? ""
: "NOT",
(col.DefaultValue != null)
? "DEFAULT('" + col.DefaultValue.ToString() + "')"
: ""));
}
//sb.AppendLine(string.Format(") ON [{0}]", tableFormat.DatabaseName));
sb.AppendLine(") ON [PRIMARY]");
using (SqlCommand cmd = new SqlCommand(sb.ToString(), conn))
{
if (transaction != null)
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
}
return sb.ToString();
}
catch (Exception ex)
{
DebugOnly.DebugWrite("ERROR: CreateTable -- " + ex.Message);
throw;
}
}
public string CreateTable(SqlTableParams tableFormat)
{
try
{ return rsDbSql.CreateTable(tableFormat, this.DbConnection, this.OpenTransaction); }
catch
{ throw; }
}
