/// <summary>
/// Creates a CREATE INDEX DDL for the specified table and index schema.
/// </summary>
/// <param name="tableName">The name of the indexed table.</param>
/// <param name="indexSchema">The schema of the index object</param>
/// <returns>A CREATE INDEX DDL (SQLite format).</returns>
private string BuildCreateIndex(string tableName, BaseIndexSchema indexSchema)
{
StringBuilder sb = new StringBuilder();
sb.Append("CREATE ");
if (indexSchema.IsUnique)
{
sb.Append("UNIQUE ");
}
sb.Append("INDEX [" + tableName + "_" + indexSchema.IndexName + "]\n");
sb.Append("ON [" + tableName + "]\n");
sb.Append("(");
sb.Append("[" + indexSchema.ColumnName + "]");
if (!indexSchema.IsAscending)
{
sb.Append(" DESC");
}
//for (int i = 0; i < indexSchema.Columns.Count; i++)
//{
// sb.Append("[" + indexSchema.Columns[i].ColumnName + "]");
// if (!indexSchema.Columns[i].IsAscending)
// sb.Append(" DESC");
// if (i < indexSchema.Columns.Count - 1)
// sb.Append(", ");
//} // for
sb.Append(")");
return(sb.ToString());
}
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>
/// 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>
/// 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);
}