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