Exemplo n.º 1
0
        /// <summary>
        /// SqlCE and other db are different.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ResetIdentityColumnCmd_Execute(object sender, ExecutedRoutedEventArgs e)
        {
            if ("Do you confirm you want to reset the identity column ?".Confirm())
            {
                try
                {
                    BaseTableSchema schema = App.MainEngineer.GetTableSchemaInfoObject(CurrentTreeArgs.TableName);
                    var             result = schema.Columns.Where(c => c.IsIdentity == true);

                    if (result.Count() > 0)
                    {
                        BaseColumnSchema columnSchema = result.First();

                        string cmd = App.MainEngineer.CurrentCommandTextHandler.GetResetIdentityColumn(CurrentTreeArgs.TableName, columnSchema.ColumnName);
                        App.MainEngineer.DoExecuteNonQuery(cmd);
                        //
                        RefreshColumnsInfo(CurrentTreeArgs.TableName);
                    }
                    else
                    {
                        "NoDataToProcess".GetFromResourece().Show();
                    }
                }
                catch (Exception ee)
                {
                    ee.HandleMyException();
                }
            }
        }
Exemplo n.º 2
0
        public void TestSqlCE_AddColumn()
        {
            CoreEA.ICoreEAHander srcHandle = new CoreEA.CoreE(CoreEA.CoreE.UsedDatabaseType.SqlCE35).X_Handler;
            srcHandle.Open(new LoginInfo_SSCE()
            {
                DbName = GlobalInfo.SqlCE_TestFile
            });
            int columnCount    = 0;
            int nowColumnCount = 0;

            if (srcHandle.IsOpened)
            {
                columnCount = srcHandle.GetColumnNameListFromTable(GlobalInfo.SqlCE_TestTable).Count;

                BaseColumnSchema columnSchema = new BaseColumnSchema();

                columnSchema.ColumnName         = "TestColumn11";
                columnSchema.ColumnType         = "nvarchar";
                columnSchema.CharacterMaxLength = 50;

                //columnSchema.IsNullable = true;
                columnSchema.IsNullable = false;

                bool ret = srcHandle.AddColumnToTable(GlobalInfo.SqlCE_TestTable, columnSchema);

                //Should create column ok
                Assert.AreEqual(true, ret);
                nowColumnCount = srcHandle.GetColumnNameListFromTable(GlobalInfo.SqlCE_TestTable).Count;
            }
            srcHandle.Close();

            Assert.AreEqual(columnCount, nowColumnCount - 1);
        }
Exemplo n.º 3
0
        /// <summary>
        /// Used when creating the CREATE TABLE DDL. Creates a single row
        /// for the specified column.
        /// </summary>
        /// <param name="col">The column schema</param>
        /// <returns>A single column line to be inserted into the general CREATE TABLE DDL statement</returns>
        private string BuildColumnStatement(BaseColumnSchema col)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("\t[" + col.ColumnName + "]\t\t");
            sb.Append("[" + col.ColumnType + "]");
            if (col.IsNullable)
            {
                sb.Append(" NULL");
            }
            else
            {
                sb.Append(" NOT NULL");
            }
            string defval = StripParens(col.DefaultValue);

            defval = DiscardNational(defval);

            if (defval != string.Empty && defval.ToUpper().Contains("GETDATE"))
            {
                sb.Append(" DEFAULT (CURRENT_TIMESTAMP)");
            }
            else if (defval != string.Empty && IsValidDefaultValue(defval))
            {
                sb.Append(" DEFAULT (" + col.DefaultValue + ")");
            }

            return(sb.ToString());
        }
Exemplo n.º 4
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void okCmd_Execute(object sender, ExecutedRoutedEventArgs e)
        {
            string           columnName   = txtColumnName.Text;
            BaseColumnSchema columnSchema = new BaseColumnSchema();

            if (txtLength.Text.IsEmpty())
            {
                columnSchema.CharacterMaxLength = 0;
            }
            else
            {
                columnSchema.CharacterMaxLength = long.Parse(txtLength.Text);
            }
            columnSchema.ColumnName = columnName;
            columnSchema.ColumnType = columnType.Text.ToString();


            try
            {
                if (App.MainEngineer.AddColumnToTable(Args.TableName, columnSchema))
                {
                    DialogResult = true;
                }
            }
            catch (Exception ee)
            {
                ee.Notify();
            }
        }
Exemplo n.º 5
0
        /// <summary>
        /// This method will get primarykey , column ,indexes,
        /// </summary>
        /// <param name="ts"></param>
        /// <returns></returns>
        public sealed override string GetCreateTableString(BaseTableSchema ts)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("CREATE TABLE " + GetMaskedTableName(ts.TableName) + " (\n");

            for (int i = 0; i < ts.Columns.Count; i++)
            {
                BaseColumnSchema col   = ts.Columns[i];
                string           cline = BuildColumnStatement(col);
                sb.Append(cline);
                if (i < ts.Columns.Count - 1)
                {
                    sb.Append(",\n");
                }
            } // foreach

            if (ts.PrimaryKey != null && ts.PrimaryKey.Count > 0)
            {
                sb.Append(",\n");
                sb.Append("PRIMARY KEY (");
                for (int i = 0; i < ts.PrimaryKey.Count; i++)
                {
                    sb.Append(GetMaskedColumnName(ts.PrimaryKey[i].ColumnName));
                    if (i < ts.PrimaryKey.Count - 1)
                    {
                        sb.Append(", ");
                    }
                } // for
                sb.Append(")\n");
            }
            else
            {
                sb.Append("\n");
            }

            sb.Append(");\n");

            //Leon Marked 2009-10-27
            //Old code to proecess index one by one
            //but some scenario need create index with two or up columns
            //so replace these code

            //// Create any relevant indexes
            //if (ts.Indexes != null)
            //{
            //    for (int i = 0; i < ts.Indexes.Count; i++)
            //    {
            //        string stmt = BuildCreateIndex(ts.TableName, ts.Indexes[i]);
            //        sb.Append(stmt + ";\n");
            //    } // for
            //} // if

            string stmt = BuildCreateIndex(ts.TableName, ts.Indexes);

            sb.Append(stmt);

            return(sb.ToString());
        }
Exemplo n.º 6
0
        public sealed override BaseTableSchema GetTableSchemaInfoObject(string tableName)
        {
            if (!IsOpened)
            {
                throw new ConnectErrorException();
            }


            BaseTableSchema tableSchema = new BaseTableSchema();

            tableSchema.TableName = tableName;

            DataTable dt = GetColumnInfoFromTable(tableName);

            foreach (DataRow item in dt.Rows)
            {
                BaseColumnSchema schmeaInfo = new BaseColumnSchema();

                schmeaInfo.ColumnName = item["COLUMN_NAME"].ToString();
                schmeaInfo.ColumnType = item["DATA_TYPE"].ToString();

                Debug.WriteLine(schmeaInfo.ColumnType);
                //Different
                //schmeaInfo.AutoIncrementSeed = bool.Parse(item["AUTOINCREMENT"].ToString()) == true ? 1 : 0;

                schmeaInfo.CharacterMaxLength = item["CHARACTER_MAXIMUM_LENGTH"].IsDBNull() ? 0 :
                                                Int64.Parse(item["CHARACTER_MAXIMUM_LENGTH"].ToString());
                //As well as set this property
                //This property is the common property
                //About property is special property
                //We recommend use this property rather than above one.
                schmeaInfo.ColumnLength = schmeaInfo.CharacterMaxLength;

                schmeaInfo.DefaultValue = item["COLUMN_DEFAULT"].ToString();

                schmeaInfo.IsNullable = (item["IS_NULLABLE"].ToString().ToLower() == "yes" ? true : false);

                schmeaInfo.NumericPrecision = item["NUMERIC_PRECISION"].IsDBNull() ?
                                              0 : int.Parse(item["NUMERIC_PRECISION"].ToString());

                schmeaInfo.NumericScale = item["NUMERIC_SCALE"].IsDBNull() ?
                                          0 : int.Parse(item["NUMERIC_SCALE"].ToString());

                schmeaInfo.OrdinalPosition = item["ORDINAL_POSITION"].IsDBNull() ?
                                             0 : int.Parse(item["ORDINAL_POSITION"].ToString());

                tableSchema.Columns.Add(schmeaInfo);
            }

            tableSchema.Indexes = GetIndexSchemaInfoFromTable(tableName);

            return(tableSchema);
        }
Exemplo n.º 7
0
        /// <summary>
        /// <TABLE_NAME>Sheet1$</TABLE_NAME>
        //<COLUMN_NAME>F1</COLUMN_NAME>
        //<ORDINAL_POSITION>1</ORDINAL_POSITION>
        //<COLUMN_HASDEFAULT>false</COLUMN_HASDEFAULT>
        //<COLUMN_FLAGS>106</COLUMN_FLAGS>
        //<IS_NULLABLE>true</IS_NULLABLE>
        //<DATA_TYPE>130</DATA_TYPE>
        //<CHARACTER_MAXIMUM_LENGTH>255</CHARACTER_MAXIMUM_LENGTH>
        //<CHARACTER_OCTET_LENGTH>510</CHARACTER_OCTET_LENGTH>
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public sealed override BaseTableSchema GetTableSchemaInfoObject(string tableName)
        {
            if (!IsOpened)
            {
                throw new ConnectErrorException();
            }

            BaseTableSchema tableSchema = new BaseTableSchema();

            tableSchema.TableName = tableName;
            //DataTable dt = GetAllDataFromTable(tableName);
            DataTable dt = baseConn.GetSchema("Columns", new string[] { null, null, tableName, null });

            //dt.TableName = "afsdfasdf";
            //dt.WriteXml("C:\\testdfsd.xml");

            foreach (DataRow item in dt.Rows)
            {
                try
                {
                    BaseColumnSchema schmeaInfo = new BaseColumnSchema();
                    schmeaInfo.ColumnName = item["COLUMN_NAME"].ToString();
                    //
                    schmeaInfo.ColumnType = GetExcelColumnType(item["DATA_TYPE"].ToString());
                    Debug.WriteLine(schmeaInfo.ColumnType);

                    schmeaInfo.CharacterMaxLength = long.Parse(item["CHARACTER_MAXIMUM_LENGTH"].ToString());
                    //As well as set this property
                    //This property is the common property
                    //About property is special property
                    //We recommend use this property rather than above one.
                    schmeaInfo.ColumnLength = schmeaInfo.CharacterMaxLength;

                    schmeaInfo.IsNullable = (item["IS_NULLABLE"].ToString().ToLower() == "true" ? true : false);

                    schmeaInfo.OrdinalPosition = item["ORDINAL_POSITION"].IsDBNull() ?
                                                 0 : int.Parse(item["ORDINAL_POSITION"].ToString());

                    tableSchema.Columns.Add(schmeaInfo);
                }
                catch (Exception ee)
                {
                }
            }
#if DEBUG
            dt.TableName = "dfd";
            dt.WriteXml("ExcelColumnInfo.xml");
#endif


            return(tableSchema);
        }
Exemplo n.º 8
0
        public sealed override string GetCreateTableString(BaseTableSchema ts)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("CREATE TABLE " + GetMaskedTableName(ts.TableName) + " (\n");

            for (int i = 0; i < ts.Columns.Count; i++)
            {
                BaseColumnSchema col   = ts.Columns[i];
                string           cline = BuildColumnStatement(col);
                sb.Append(cline);
                if (i < ts.Columns.Count - 1)
                {
                    sb.Append(",\n");
                }
            } // foreach

            if (ts.PrimaryKey != null && ts.PrimaryKey.Count > 0)
            {
                sb.Append(",\n");
                sb.Append("PRIMARY KEY (");
                for (int i = 0; i < ts.PrimaryKey.Count; i++)
                {
                    sb.Append(GetMaskedColumnName(ts.PrimaryKey[i].ColumnName));
                    if (i < ts.PrimaryKey.Count - 1)
                    {
                        sb.Append(", ");
                    }
                } // for
                sb.Append(")\n");
            }
            else
            {
                sb.Append("\n");
            }

            sb.Append(");\n");

            // Create any relevant indexes
            //if (ts.Indexes != null)
            //{
            //    for (int i = 0; i < ts.Indexes.Count; i++)
            //    {
            //        string stmt = BuildCreateIndex(ts.TableName, ts.Indexes[i]);
            //        sb.Append(stmt + ";\n");
            //    } // for
            //} // if

            return(sb.ToString());
        }
Exemplo n.º 9
0
        public sealed override string GetCreateTableString(BaseTableSchema ts)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("CREATE TABLE " + GetMaskedTableName(ts.TableName) + " (\n");

            for (int i = 0; i < ts.Columns.Count; i++)
            {
                BaseColumnSchema col   = ts.Columns[i];
                string           cline = BuildColumnStatement(col);
                sb.Append(cline);
                if (i < ts.Columns.Count - 1)
                {
                    sb.Append(",\n");
                }
            } // foreach

            if (ts.PrimaryKey != null && ts.PrimaryKey.Count > 0)
            {
                sb.Append(",\n");
                sb.Append("PRIMARY KEY (");
                for (int i = 0; i < ts.PrimaryKey.Count; i++)
                {
                    sb.Append(GetMaskedColumnName(ts.PrimaryKey[i].ColumnName));
                    if (i < ts.PrimaryKey.Count - 1)
                    {
                        sb.Append(", ");
                    }
                } // for
                sb.Append(")\n");
            }
            else
            {
                sb.Append("\n");
            }

            sb.Append(");\n");
            string end = sb.ToString();

            return(end);
        }
Exemplo n.º 10
0
        public sealed override bool AddColumnToTable(string tableName, BaseColumnSchema columnSchema)
        {
            bool ret = false;

            try
            {
                string sqlCmd = string.Format("Alter table {0} add {1} {2} ",
                                              tableName, columnSchema.ColumnName, columnSchema.ColumnType);
                if (columnSchema.CharacterMaxLength != 0)
                {
                    sqlCmd += string.Format("({0})", columnSchema.CharacterMaxLength);
                }
                DoExecuteNonQuery(sqlCmd);

                ret = true;
            }
            catch (Exception ee)
            {
                throw ee;
            }

            return(ret);
        }
Exemplo n.º 11
0
        public sealed override BaseTableSchema GetTableSchemaInfoObject(string tableName)
        {
            if (!IsOpened)
            {
                throw new ConnectErrorException();
            }

            BaseTableSchema tableSchema = new BaseTableSchema();

            try
            {
                Dictionary <string, List <string> > identifyColumns = GetIdentifyColumnsFromCurrentDatabase();

                tableSchema.TableName  = tableName;
                tableSchema.PrimaryKey = GetPrimaryKeysFromTable(tableName);
                #region Column Info
                DataTable dt = GetColumnInfoFromTable(tableName);

                foreach (DataRow item in dt.Rows)
                {
                    BaseColumnSchema schmeaInfo = new BaseColumnSchema();

                    schmeaInfo.ColumnName = item["COLUMN_NAME"].ToString();
                    schmeaInfo.ColumnType = item["DATA_TYPE"].ToString();
                    Debug.WriteLine(schmeaInfo.ColumnType);
                    //schmeaInfo.AutoIncrementSeed = (item["AUTOINC_SEED"].IsDBNull() ? 0 : 1);

                    schmeaInfo.CharacterMaxLength = item["CHARACTER_MAXIMUM_LENGTH"].IsDBNull() ? 0 :
                                                    Int64.Parse(item["CHARACTER_MAXIMUM_LENGTH"].ToString());
                    //As well as set this property
                    //This property is the common property
                    //About property is special property
                    //We recommend use this property rather than above one.
                    schmeaInfo.ColumnLength = schmeaInfo.CharacterMaxLength;

                    schmeaInfo.DefaultValue = item["COLUMN_DEFAULT"].ToString();
                    //Different
                    //schmeaInfo.IsNullable = (item["IS_NULLABLE"].ToString().ToLower() == "yes" ? true : false);
                    schmeaInfo.IsNullable = (item["IS_NULLABLE"].ToString().ToLower() == "yes" ? true : false);

                    schmeaInfo.NumericPrecision = item["NUMERIC_PRECISION"].IsDBNull() ?
                                                  0 : int.Parse(item["NUMERIC_PRECISION"].ToString());

                    schmeaInfo.NumericScale = item["NUMERIC_SCALE"].IsDBNull() ?
                                              0 : int.Parse(item["NUMERIC_SCALE"].ToString());

                    schmeaInfo.OrdinalPosition = item["ORDINAL_POSITION"].IsDBNull() ?
                                                 0 : int.Parse(item["ORDINAL_POSITION"].ToString());

                    if (identifyColumns.ContainsKey(tableName))
                    {
                        schmeaInfo.IsIdentity = identifyColumns[tableName].Contains(schmeaInfo.ColumnName);
                    }

                    tableSchema.Columns.Add(schmeaInfo);
                }
                #endregion

                #region Index Info
                DataTable dtForIndex = GetIndexInfoFromTable(tableName);

                //#if DEBUG
                //                dtForIndex.WriteXml("C:\\test111.xml");
                //#endif

                foreach (DataRow row in dtForIndex.Rows)
                {
                    BaseIndexSchema indexInfo = new BaseIndexSchema();
                    indexInfo.IndexName = row["INDEX_NAME"].ToString();
                    indexInfo.TableName = row["TABLE_NAME"].ToString();
                    tableSchema.Indexes.Add(indexInfo);
                }
                #endregion Endof IndexInfo
            }

            catch (Exception ee)
            {
                throw ee;
            }

            return(tableSchema);
        }
Exemplo n.º 12
0
        /// <summary>
        /// Used when creating the CREATE TABLE DDL. Creates a single row
        /// for the specified column.
        /// </summary>
        /// <param name="eachColumn">The column schema</param>
        /// <returns>A single column line to be inserted into the general CREATE TABLE DDL statement</returns>
        private string BuildColumnStatement(BaseColumnSchema eachColumn)
        {
            //This way can be used when create simple schema from SSCE
            //BUT when the basecolumnschema is from sqlserver or other db type
            //This way is not powerful to create .

            #region Not Completed Way
            //StringBuilder sb = new StringBuilder();
            //sb.Append("\t[" + col.ColumnName + "]\t\t");
            //sb.Append("[" + col.ColumnType + "]");
            //if (col.IsNullable)
            //    sb.Append(" NULL");
            //else
            //    sb.Append(" NOT NULL");
            //string defval = StripParens(col.DefaultValue);
            //defval = DiscardNational(defval);

            //if (defval != string.Empty && defval.ToUpper().Contains("GETDATE"))
            //{
            //    sb.Append(" DEFAULT (CURRENT_TIMESTAMP)");
            //}
            //else if (defval != string.Empty && IsValidDefaultValue(defval))
            //    sb.Append(" DEFAULT (" + col.DefaultValue + ")");

            //return sb.ToString();

            //Here should convert to SSCEColumnSchema
            //because here we only support sync data to SSCE currently
            //Maybe will refactor ,but not sure.
            #endregion

            StringBuilder createSchemaScript = new StringBuilder();
            switch (eachColumn.ColumnType.ToLower())
            {
            case "char":
                eachColumn.ColumnType = "nchar";
                break;

            case "varchar":
                eachColumn.ColumnType = "nvarchar";
                break;

            case "text":
                eachColumn.ColumnType = "ntext";
                break;
            }

            switch (eachColumn.ColumnType.ToLower())
            {
            case "nvarchar":
                createSchemaScript.AppendFormat("[{0}] {1}({2}) {3} {4} {5} ",
                                                eachColumn.ColumnName,
                                                eachColumn.ColumnType,
                                                eachColumn.CharacterMaxLength,
                                                eachColumn.IsNullable == true ? "NULL" : "NOT NULL",
                                                eachColumn.DefaultValue.是空的() ? "DEFAULT " + eachColumn.DefaultValue.ToString() : string.Empty,
                                                System.Environment.NewLine);
                break;

            case "nchar":
                createSchemaScript.AppendFormat("[{0}] {1}({2}) {3} {4} {5}",
                                                eachColumn.ColumnName,
                                                "nchar",
                                                eachColumn.CharacterMaxLength,
                                                eachColumn.IsNullable == true ? "NULL" : "NOT NULL",
                                                eachColumn.DefaultValue.是空的() ? "DEFAULT " + eachColumn.DefaultValue.ToString() : string.Empty,
                                                System.Environment.NewLine);
                break;

            case "numeric":
                createSchemaScript.AppendFormat("[{0}] {1}({2},{3}) {4} {5} {6} ",
                                                eachColumn.ColumnName,
                                                eachColumn.ColumnType,
                                                eachColumn.NumericPrecision,
                                                eachColumn.NumericScale,
                                                eachColumn.IsNullable == true ? "NULL" : "NOT NULL",
                                                eachColumn.DefaultValue.是空的() ? "DEFAULT " + eachColumn.DefaultValue.ToString() : string.Empty,
                                                System.Environment.NewLine);
                break;

            //Not support such type
            case "timestamp":

                break;

            case "enum":

                break;

            default:
                createSchemaScript.AppendFormat("[{0}] {1} {2} {3} {4}{5} {6} ",
                                                eachColumn.ColumnName,
                                                eachColumn.ColumnType,
                                                eachColumn.IsNullable == true ? "NULL" : "NOT NULL",
                                                eachColumn.DefaultValue.是空的() ? "DEFAULT " + eachColumn.DefaultValue.ToString() : string.Empty,
                                                eachColumn.RowGuidCol ? "ROWGUIDeachColumn" : string.Empty,
                                                (eachColumn.AutoIncrementBy > 0 ? string.Format("IDENTITY ({0},{1})", eachColumn.AutoIncrementSeed, eachColumn.AutoIncrementBy) : string.Empty),
                                                System.Environment.NewLine);
                break;
            }

            return(createSchemaScript.ToString());
        }
Exemplo n.º 13
0
        /// <summary>
        /// This method will get primarykey , column ,indexes,
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public sealed override BaseTableSchema GetTableSchemaInfoObject(string tableName)
        {
            if (!IsOpened)
            {
                throw new ConnectErrorException();
            }

            BaseTableSchema tableSchema = new BaseTableSchema();

            tableSchema.PrimaryKey = GetPrimaryKeysFromTable(tableName);

            tableSchema.TableName = tableName;

            DataTable dt = GetColumnInfoFromTable(tableName);

            #region Column Info
            foreach (DataRow item in dt.Rows)
            {
                BaseColumnSchema schmeaInfo = new BaseColumnSchema();

                schmeaInfo.ColumnName        = item["COLUMN_NAME"].ToString();
                schmeaInfo.ColumnType        = item["DATA_TYPE"].ToString();
                schmeaInfo.AutoIncrementSeed = (item["AUTOINC_SEED"].IsDBNull() ? 0 : 1);

                schmeaInfo.CharacterMaxLength = item["CHARACTER_MAXIMUM_LENGTH"].IsDBNull() ? 0 :
                                                Int64.Parse(item["CHARACTER_MAXIMUM_LENGTH"].ToString());
                //As well as set this property
                //This property is the common property
                //About property is special property
                //We recommend use this property rather than above one.
                schmeaInfo.ColumnLength = schmeaInfo.CharacterMaxLength;

                schmeaInfo.DefaultValue = item["COLUMN_DEFAULT"].ToString();

                schmeaInfo.IsNullable = (item["IS_NULLABLE"].ToString().ToLower() == "yes" ? true : false);

                schmeaInfo.NumericPrecision = item["NUMERIC_PRECISION"].IsDBNull() ?
                                              0 : int.Parse(item["NUMERIC_PRECISION"].ToString());

                schmeaInfo.NumericScale = item["NUMERIC_SCALE"].IsDBNull() ?
                                          0 : int.Parse(item["NUMERIC_SCALE"].ToString());

                schmeaInfo.OrdinalPosition = item["ORDINAL_POSITION"].IsDBNull() ?
                                             0 : int.Parse(item["ORDINAL_POSITION"].ToString());

                schmeaInfo.IsIdentity = item["AUTOINC_SEED"].IsDBNull() ? false : true;

                tableSchema.Columns.Add(schmeaInfo);
            }

            #endregion Index Info

            #region Index Info
            DataTable dtForIndex = GetIndexInfoFromTable(tableName);
            foreach (DataRow row in dtForIndex.Rows)
            {
                BaseIndexSchema indexInfo = new BaseIndexSchema();
                indexInfo.ColumnName = row["COLUMN_NAME"].ToString();
                indexInfo.IndexName  = row["INDEX_NAME"].ToString();
                //indexInfo.IsAscending
                indexInfo.IsClustered  = bool.Parse(row["CLUSTERED"].ToString());
                indexInfo.IsPrimaryKey = bool.Parse(row["PRIMARY_KEY"].ToString());
                indexInfo.IsUnique     = bool.Parse(row["UNIQUE"].ToString());
                indexInfo.TableName    = row["TABLE_NAME"].ToString();

                indexInfo.OrdinalPosition = row["ORDINAL_POSITION"].IsDBNull() ?
                                            0 : int.Parse(row["ORDINAL_POSITION"].ToString());


                tableSchema.Indexes.Add(indexInfo);
            }
            #endregion Endof IndexInfo

            return(tableSchema);
        }
Exemplo n.º 14
0
        public sealed override BaseTableSchema GetTableSchemaInfoObject(string tableName)
        {
            if (!IsOpened)
            {
                throw new ConnectErrorException();
            }

            BaseTableSchema tableSchema = new BaseTableSchema();

            tableSchema.TableName = tableName;

            #region Oldway
            //DataTable dt = GetAllDataFromTable(tableName);

            //foreach (DataColumn item in dt.Columns)
            //{
            //    BaseColumnSchema schmeaInfo = new BaseColumnSchema();

            //    schmeaInfo.ColumnName = item.ColumnName;
            //    schmeaInfo.ColumnType = item.DataType.ToString();

            //    Debug.WriteLine(schmeaInfo.ColumnType);
            //    schmeaInfo.IsAutoIncrement = item.AutoIncrement;
            //    schmeaInfo.AutoIncrementBy = (int)item.AutoIncrementStep;
            //    schmeaInfo.AutoIncrementSeed = (int)item.AutoIncrementSeed;

            //    schmeaInfo.CharacterMaxLength = item.MaxLength;
            //    schmeaInfo.DefaultValue = item.DefaultValue.ToString();

            //    schmeaInfo.IsNullable = item.AllowDBNull;

            //    tableSchema.Columns.Add(schmeaInfo);
            //}
            #endregion

            DataTable dt = GetColumnInfoFromTable(tableName);
            foreach (DataRow item in dt.Rows)
            {
                BaseColumnSchema columnInfo = new BaseColumnSchema();

                columnInfo.ColumnName = item["COLUMN_NAME"].ToString();
                string tempColumnType = MatchAdoDbType((int)(item["DATA_TYPE"]));

                columnInfo.ColumnType = tempColumnType;

                Debug.WriteLine(columnInfo.ColumnType);
                //schmeaInfo.AutoIncrementSeed = (item["AUTOINC_SEED"].IsDBNull() ? 0 : 1);

                columnInfo.CharacterMaxLength = item["CHARACTER_MAXIMUM_LENGTH"].IsDBNull() ? 0 :
                                                Int64.Parse(item["CHARACTER_MAXIMUM_LENGTH"].ToString());
                //As well as set this property
                //This property is the common property
                //About property is special property
                //We recommend use this property rather than above one.
                columnInfo.ColumnLength = columnInfo.CharacterMaxLength;
                columnInfo.DefaultValue = item["COLUMN_DEFAULT"].ToString();
                //Different
                //schmeaInfo.IsNullable = (item["IS_NULLABLE"].ToString().ToLower() == "yes" ? true : false);

                columnInfo.NumericPrecision = item["NUMERIC_PRECISION"].IsDBNull() ?
                                              0 : int.Parse(item["NUMERIC_PRECISION"].ToString());

                columnInfo.NumericScale = item["NUMERIC_SCALE"].IsDBNull() ?
                                          0 : int.Parse(item["NUMERIC_SCALE"].ToString());

                columnInfo.OrdinalPosition = item["ORDINAL_POSITION"].IsDBNull() ?
                                             0 : int.Parse(item["ORDINAL_POSITION"].ToString());

                tableSchema.Columns.Add(columnInfo);
            }

            #region ADO Way

            //string sqlCeTableName = tableName;
            //string strconnection = string.Format("provider=microsoft.jet.oledb.4.0;data source={0}",
            //    CurDatabase);
            //ADODB.Connection conn = new ADODB.ConnectionClass();
            //if (string.IsNullOrEmpty(CurPwd))
            //{
            //    conn.Open(strconnection, "Admin", "", 0);
            //}
            //else
            //{
            //    conn.Open(strconnection, "Admin", CurPwd, 0);
            //}

            //ADOX.Catalog catelog = new ADOX.CatalogClass();
            //catelog.let_ActiveConnection(conn);

            //ADOX.Table tempTable = catelog.Tables[tableName];

            //for (int i = 0; i < tempTable.Columns.Count; i++)
            //{
            //    tableSchema.Columns.Add(new BaseColumnSchema()
            //    {
            //        ColumnName = tempTable.Columns[i].Name,
            //        ColumnType = CoreEA.Utility.TypeConvertor.ParseADODbTypeToSqlCeDbType(tempTable.Columns[i].Type.ToString(),
            //                    tempTable.Columns[i].DefinedSize),

            //        CharacterMaxLength = tempTable.Columns[i].DefinedSize,
            //        NumericScale = tempTable.Columns[i].NumericScale,
            //        IsNullable = true,
            //        NumericPrecision = tempTable.Columns[i].Precision,
            //    });
            //}

            #endregion

            return(tableSchema);
        }
Exemplo n.º 15
0
        /// <summary>
        /// Below is the column schema
        /// <TABLE_CAT>PUBLIC</TABLE_CAT>
        /// <TABLE_SCHEM>PUBLIC</TABLE_SCHEM>
        /// <TABLE_NAME>TEST</TABLE_NAME>
        /// <COLUMN_NAME>NAME</COLUMN_NAME>
        /// <DATA_TYPE>16</DATA_TYPE>
        /// <TYPE_NAME>VARCHAR2</TYPE_NAME>
        /// <COLUMN_SIZE>100</COLUMN_SIZE>
        /// <NUM_PREC_RADIX>0</NUM_PREC_RADIX>
        /// <NULLABLE>1</NULLABLE>
        /// <SQL_DATA_TYPE>12</SQL_DATA_TYPE>
        /// <CHAR_OCTET_LENGTH>100</CHAR_OCTET_LENGTH>
        /// <ORDINAL_POSITION>3</ORDINAL_POSITION>
        /// <IS_NULLABLE>true</IS_NULLABLE>
        /// <IS_AUTOINCREMENT>false</IS_AUTOINCREMENT>
        ///<TYPE_SUB>1</TYPE_SUB>
        ///<IS_PRIMARY_KEY>false</IS_PRIMARY_KEY>
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public sealed override BaseTableSchema GetTableSchemaInfoObject(string tableName)
        {
            if (!IsOpened)
            {
                throw new ConnectErrorException();
            }

            BaseTableSchema tableSchema = new BaseTableSchema();

            try
            {
                tableSchema.TableName  = tableName;
                tableSchema.PrimaryKey = GetPrimaryKeysFromTable(tableName);

                #region Column Info
                DataTable dt = GetColumnInfoFromTable(tableName);

#if DEBUG
                dt.WriteXml("C:\\column1.xml");
#else
#endif
                foreach (DataRow item in dt.Rows)
                {
                    BaseColumnSchema schmeaInfo = new BaseColumnSchema();

                    schmeaInfo.ColumnName = item["COLUMN_NAME"].ToString();
                    //
                    schmeaInfo.ColumnType = item["TYPE_NAME"].ToString();
                    Debug.WriteLine(schmeaInfo.ColumnType);

                    schmeaInfo.CharacterMaxLength = long.Parse(item["COLUMN_SIZE"].ToString());
                    //As well as set this property
                    //This property is the common property
                    //About property is special property
                    //We recommend use this property rather than above one.
                    schmeaInfo.ColumnLength = schmeaInfo.CharacterMaxLength;

                    schmeaInfo.IsNullable = (item["IS_NULLABLE"].ToString().ToLower() == "true" ? true : false);

                    schmeaInfo.OrdinalPosition = item["ORDINAL_POSITION"].IsDBNull() ?
                                                 0 : int.Parse(item["ORDINAL_POSITION"].ToString());

                    schmeaInfo.IsAutoIncrement = (item["IS_AUTOINCREMENT"].ToString().ToLower() == "true" ? true : false);
                    schmeaInfo.IsIdentity      = (item["IS_PRIMARY_KEY"].ToString().ToLower() == "true" ? true : false);
                    tableSchema.Columns.Add(schmeaInfo);
                }
                #endregion

                #region Index Info
                DataTable dtForIndex = GetIndexInfoFromTable(tableName);

                foreach (DataRow row in dtForIndex.Rows)
                {
                    BaseIndexSchema indexInfo = new BaseIndexSchema();
                    indexInfo.IndexName = row["INDEX_NAME"].ToString();
                    indexInfo.TableName = row["TABLE_NAME"].ToString();
                    tableSchema.Indexes.Add(indexInfo);
                }
                #endregion Endof IndexInfo
            }

            catch (Exception ee)
            {
                throw ee;
            }

            return(tableSchema);
        }
Exemplo n.º 16
0
        /// <summary>
        /// Used when creating the CREATE TABLE DDL. Creates a single row
        /// for the specified column.
        /// </summary>
        /// <param name="eachColumn">The column schema</param>
        /// <returns>A single column line to be inserted into the general CREATE TABLE DDL statement</returns>
        private string BuildColumnStatement(BaseColumnSchema eachColumn)
        {
            StringBuilder createSchemaScript = new StringBuilder();

            switch (eachColumn.ColumnType.ToLower())
            {
            case "varchar":
                createSchemaScript.AppendFormat("\"{0}\" {1}({2}) {3} {4} {5} ",
                                                eachColumn.ColumnName,
                                                eachColumn.ColumnType,
                                                eachColumn.CharacterMaxLength,
                                                eachColumn.IsNullable == true ? "NULL" : "NOT NULL",
                                                eachColumn.DefaultValue.是空的() ? "DEFAULT " + eachColumn.DefaultValue.ToString() : string.Empty,
                                                System.Environment.NewLine);
                break;

            case "char":
                createSchemaScript.AppendFormat("\"{0}\" {1}({2}) {3} {4} {5} ",
                                                eachColumn.ColumnName,
                                                eachColumn.ColumnType,
                                                eachColumn.CharacterMaxLength,
                                                eachColumn.IsNullable == true ? "NULL" : "NOT NULL",
                                                eachColumn.DefaultValue.是空的() ? "DEFAULT " + eachColumn.DefaultValue.ToString() : string.Empty,
                                                System.Environment.NewLine);
                break;

            case "varchar2":
                createSchemaScript.AppendFormat("\"{0}\" {1}({2}) {3} {4} {5}",
                                                eachColumn.ColumnName,
                                                eachColumn.ColumnType,
                                                eachColumn.CharacterMaxLength,
                                                eachColumn.IsNullable == true ? "NULL" : "NOT NULL",
                                                eachColumn.DefaultValue.是空的() ? "DEFAULT " + eachColumn.DefaultValue.ToString() : string.Empty,
                                                System.Environment.NewLine);
                break;

            case "numeric":
                createSchemaScript.AppendFormat("\"{0}\" {1}({2},{3}) {4} {5} {6} ",
                                                eachColumn.ColumnName,
                                                eachColumn.ColumnType,
                                                eachColumn.NumericPrecision,
                                                eachColumn.NumericScale,
                                                eachColumn.IsNullable == true ? "NULL" : "NOT NULL",
                                                eachColumn.DefaultValue.是空的() ? "DEFAULT " + eachColumn.DefaultValue.ToString() : string.Empty,
                                                System.Environment.NewLine);
                break;

            //Not support such type
            case "timestamp":

                break;

            case "enum":

                break;

            default:
                createSchemaScript.AppendFormat("\"{0}\" {1} {2} {3} {4}{5} {6} ",
                                                eachColumn.ColumnName,
                                                eachColumn.ColumnType,
                                                eachColumn.IsNullable == true ? "NULL" : "NOT NULL",
                                                eachColumn.DefaultValue.是空的() ? "DEFAULT " + eachColumn.DefaultValue.ToString() : string.Empty,
                                                eachColumn.RowGuidCol ? "ROWGUIDeachColumn" : string.Empty,
                                                (eachColumn.AutoIncrementBy > 0 ? string.Format("IDENTITY ({0},{1})", eachColumn.AutoIncrementSeed, eachColumn.AutoIncrementBy) : string.Empty),
                                                System.Environment.NewLine);
                break;
            }

            return(createSchemaScript.ToString());
        }
Exemplo n.º 17
0
        internal static string GetCreateSSCESchemaStringCmd(BaseTableSchema tableSchmea)
        {
            string tableName = tableSchmea.TableName;

            StringBuilder createSchemaScript = new StringBuilder();

            createSchemaScript.AppendFormat("CREATE TABLE [{0}] (", tableName);

            //Here should convert to SSCEColumnSchema
            //because here we only support sync data to SSCE currently
            //Maybe will refactor ,but not sure.
            tableSchmea.Columns.ForEach(delegate(BaseColumnSchema pColumn)
            {
                BaseColumnSchema eachColumn = pColumn as BaseColumnSchema;
                if (eachColumn == null)
                {
                    return;
                }

                switch (eachColumn.ColumnType.ToLower())
                {
                case "char":
                    eachColumn.ColumnType = "nchar";
                    break;

                case "varchar":
                    eachColumn.ColumnType = "nvarchar";
                    break;

                case "text":
                    eachColumn.ColumnType = "ntext";
                    break;
                }

                switch (eachColumn.ColumnType.ToLower())
                {
                case "nvarchar":
                    createSchemaScript.AppendFormat("[{0}] {1}({2}) {3} {4} {5}, ",
                                                    eachColumn.ColumnName,
                                                    eachColumn.ColumnType,
                                                    eachColumn.CharacterMaxLength,
                                                    eachColumn.IsNullable == true ? "NULL" : "NOT NULL",
                                                    eachColumn.DefaultValue.IsNotEmpty() ? "DEFAULT " + eachColumn.DefaultValue.ToString() : string.Empty,
                                                    System.Environment.NewLine);
                    break;

                case "nchar":
                    createSchemaScript.AppendFormat("[{0}] {1}({2}) {3} {4} {5}, ",
                                                    eachColumn.ColumnName,
                                                    "nchar",
                                                    eachColumn.CharacterMaxLength,
                                                    eachColumn.IsNullable == true ? "NULL" : "NOT NULL",
                                                    eachColumn.DefaultValue.IsNotEmpty() ? "DEFAULT " + eachColumn.DefaultValue.ToString() : string.Empty,
                                                    System.Environment.NewLine);
                    break;

                case "numeric":
                    createSchemaScript.AppendFormat("[{0}] {1}({2},{3}) {4} {5} {6}, ",
                                                    eachColumn.ColumnName,
                                                    eachColumn.ColumnType,
                                                    eachColumn.NumericPrecision,
                                                    eachColumn.NumericScale,
                                                    eachColumn.IsNullable == true ? "NULL" : "NOT NULL",
                                                    eachColumn.DefaultValue.IsNotEmpty() ? "DEFAULT " + eachColumn.DefaultValue.ToString() : string.Empty,
                                                    System.Environment.NewLine);
                    break;

                //Not support such type
                case "timestamp":

                    break;

                case "enum":

                    break;

                default:
                    createSchemaScript.AppendFormat("[{0}] {1} {2} {3} {4}{5} {6}, ",
                                                    eachColumn.ColumnName,
                                                    eachColumn.ColumnType,
                                                    eachColumn.IsNullable == true ? "NULL" : "NOT NULL",
                                                    eachColumn.DefaultValue.IsNotEmpty() ? "DEFAULT " + eachColumn.DefaultValue.ToString() : string.Empty,
                                                    eachColumn.RowGuidCol ? "ROWGUIDeachColumn" : string.Empty,
                                                    (eachColumn.AutoIncrementBy > 0 ? string.Format("IDENTITY ({0},{1})", eachColumn.AutoIncrementSeed, eachColumn.AutoIncrementBy) : string.Empty),
                                                    System.Environment.NewLine);
                    break;
                }
            });

            createSchemaScript.Remove(createSchemaScript.Length - 2, 2);
            createSchemaScript.Append(");");

            return(createSchemaScript.ToString());
        }