/// <summary> /// Creates a database table for each DataTable that's part of a BacksightDataSet, /// inserts initial rows, and defines constraints. /// </summary> /// <param name="logger">Something to display progress messages (not null)</param> /* * public void CreateTables(ILog logger) * { * try * { * if (logger == null) * throw new ArgumentNullException(); * * //Transaction.Execute(delegate * //{ * CreateBacksightTables(logger); * //}); * } * * catch (Exception ex) * { * logger.LogMessage(ex.Message); * throw ex; * } * } */ /* * void CreateBacksightTables(ILog logger) * { * DropForeignKeyConstraints(); * * // Create the ced schema * logger.LogMessage("CREATE SCHEMA ced"); * Smo.Schema s = new Smo.Schema(m_Database, "ced"); * s.Create(); * * BacksightDataSet ds = new BacksightDataSet(); * foreach (DataTable dt in ds.Tables) * { * logger.LogMessage("CREATE TABLE " + GetTableName(dt)); * CreateTable(s, dt); * } * * // Add simple checks (unfortunately, this info isn't held as part of the * // generated DataSet, so need to explicitly identify each table involved) * * AddSimpleChecks(logger, ds.EntityType, ds.EntityType.Checks); * AddSimpleChecks(logger, ds.EntityTypeSchema, ds.EntityTypeSchema.Checks); * AddSimpleChecks(logger, ds.Font, ds.Font.Checks); * AddSimpleChecks(logger, ds.IdAllocation, ds.IdAllocation.Checks); * AddSimpleChecks(logger, ds.IdFree, ds.IdFree.Checks); * AddSimpleChecks(logger, ds.IdGroup, ds.IdGroup.Checks); * AddSimpleChecks(logger, ds.Layer, ds.Layer.Checks); * AddSimpleChecks(logger, ds.Schema, ds.Schema.Checks); * AddSimpleChecks(logger, ds.SchemaTemplate, ds.SchemaTemplate.Checks); * AddSimpleChecks(logger, ds.Template, ds.Template.Checks); * AddSimpleChecks(logger, ds.Theme, ds.Theme.Checks); * * // Insert initial rows & save to database * logger.LogMessage("Inserting initial rows"); * ds.AddInitialRows(); * ds.Save(ConnectionString); * * // Define foreign key constraints * AddForeignKeyConstraints(logger); * } */ /* * void AddSimpleChecks(ILog logger, DataTable dt, string[] checks) * { * string tableName = GetTableName(dt); * Smo.Table t = m_Database.Tables[tableName, "ced"]; * if (t==null) * throw new Exception("Cannot locate table ced."+tableName); * * for (int i=0; i<checks.Length; i++) * { * string checkName = String.Format("{0}Check{1}", t.Name, i+1); * logger.LogMessage("ADD CHECK "+checkName); * AddCheck(t, checkName, checks[i]); * } * } */ /* * void AddForeignKeyConstraints(ILog logger) * { * BacksightDataSet ds = new BacksightDataSet(); * * foreach (DataRelation dr in ds.Relations) * { * DataTable parent = dr.ParentTable; * DataTable child = dr.ChildTable; * * string parentTableName = GetTableName(parent); * string childTableName = GetTableName(child); * * Smo.Table parentTable = m_Database.Tables[parentTableName, "ced"]; * Smo.Table childTable = m_Database.Tables[childTableName, "ced"]; * * if (parentTable!=null && childTable!=null) * { * if (logger!=null) * logger.LogMessage("ADD CONSTRAINT "+dr.RelationName); * * AddForeignKeyConstraint(dr); * } * } * } */ /* * void DropForeignKeyConstraints() * { * BacksightDataSet ds = new BacksightDataSet(); * List<Smo.ForeignKey> fks = new List<Smo.ForeignKey>(); * * foreach (DataTable dt in ds.Tables) * { * string tableName = GetTableName(dt); * Smo.Table t = m_Database.Tables[tableName, "ced"]; * if (t!=null) * { * foreach (Smo.ForeignKey fk in t.ForeignKeys) * fks.Add(fk); * } * } * * foreach (Smo.ForeignKey fk in fks) * fk.Drop(); * } */ void CreateTable(Smo.Schema s, DataTable dt) { // Drop any previously created version of the table string tableName = GetTableName(dt); Smo.Table t = m_Database.Tables[tableName, "ced"]; if (t != null) { t.Drop(); } // Create the table t = new Smo.Table(m_Database, tableName); t.Schema = s.Name; foreach (DataColumn c in dt.Columns) { AddColumn(t, c); } t.Create(); // Define primary key CreatePrimaryKey(t, dt); // Define pk & any unique constraints CreateIndexes(t, dt); }
public static void create_table(string a, string name, out string mess, ref int count) { database(); count = 0; if (a == "NEW EXAM") { tb = new Table(db, name, "dbo"); } else { tb = new Table(db, a + name, "dbo"); } col7 = new Column(tb, "id", DataType.VarChar(5)); tb.Columns.Add(col7); col1 = new Column(tb, "Question", DataType.VarChar(200)); tb.Columns.Add(col1); col2 = new Column(tb, "CHOICE1", DataType.VarChar(200)); tb.Columns.Add(col2); col3 = new Column(tb, "CHOICE2", DataType.VarChar(200)); tb.Columns.Add(col3); col4 = new Column(tb, "CHOICE3", DataType.VarChar(200)); tb.Columns.Add(col4); col5 = new Column(tb, "CHOICE4", DataType.VarChar(200)); tb.Columns.Add(col5); col6 = new Column(tb, "Ans", DataType.VarChar(200)); tb.Columns.Add(col6); tb.Create(); db.Tables.Refresh(); mess = "Table Create Successfully"; }
public static bool CreateIndexFileTable(string TableName, DataTable IndexDataTable, string ServerName, string DBName, string ConnectionString) { try { using (SqlConnection SqlCnx = new SqlConnection(ConnectionString)) { SqlCnx.Open(); SqlCommand SqlCmd = new SqlCommand(); SqlCmd.Connection = SqlCnx; SqlCmd.CommandType = CommandType.Text; SqlCmd.CommandText = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')) DROP TABLE " + TableName; SqlCmd.ExecuteNonQuery(); try { // Connect to Server/database ServerConnection aCnx = new ServerConnection(); aCnx.LoginSecure = false; aCnx.ServerInstance = ServerName; aCnx.Login = ConfigurationManager.AppSettings["SQLUserName"].ToString(); aCnx.Password = ConfigurationManager.AppSettings["SQLPassword"].ToString(); Server aServer = new Server(aCnx); Microsoft.SqlServer.Management.Smo.Database DB = aServer.Databases[DBName]; // Create Structure Microsoft.SqlServer.Management.Smo.Table newTable = new Microsoft.SqlServer.Management.Smo.Table(DB, TableName, "dbo"); foreach (DataColumn aCol in IndexDataTable.Columns) { Column anewCol = new Column(newTable, aCol.ColumnName); if (aCol.MaxLength > 0) { anewCol.DataType = Microsoft.SqlServer.Management.Smo.DataType.VarChar(aCol.MaxLength); } else { anewCol.DataType = Microsoft.SqlServer.Management.Smo.DataType.VarChar(8000); } newTable.Columns.Add(anewCol); } // Physically create the table in the database newTable.Create(); // Transfer the data using (SqlConnection Cnx = new SqlConnection(ConnectionString)) { Cnx.Open(); SqlBulkCopyOptions options = SqlBulkCopyOptions.Default; using (SqlBulkCopy bcp = new SqlBulkCopy(Cnx, options, null)) { foreach (DataColumn aCol in IndexDataTable.Columns) { bcp.ColumnMappings.Add(aCol.ColumnName, aCol.ColumnName); } bcp.DestinationTableName = TableName; bcp.BulkCopyTimeout = 360000; bcp.WriteToServer(IndexDataTable); bcp.Close(); } } } catch (Exception err) { Functions.SendErrorEmail(err, "GLOBAL002 CreateIndexFileTable (BCP) [TableName:" + TableName + "]"); return(false); } return(true); } } catch (Exception err) { Functions.SendErrorEmail(err, "GLOBAL003 CreateIndexFileTable [TableName:" + TableName + "]"); return(false); } }