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; } }