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