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