Example #1
0
        /// <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);
        }
Example #2
0
    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";
    }
Example #3
0
        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);
            }
        }