private static ADOX.Column newColumn(string name, ADOX.DataTypeEnum type, ADOX.Catalog cat)
{
ADOX.Column temp = new ADOX.Column();
temp.Name = name;
temp.ParentCatalog = cat;
temp.Type = type;
return(temp);
}
private static ColumnType GetColumnType(ADOX.DataTypeEnum dataTypeEnum)
{
switch (dataTypeEnum)
{
case ADOX.DataTypeEnum.adBoolean:
return(ColumnType.Boolean);
case ADOX.DataTypeEnum.adUnsignedTinyInt:
return(ColumnType.UnsignedTinyInt);
case ADOX.DataTypeEnum.adSmallInt:
return(ColumnType.SmallInt);
case ADOX.DataTypeEnum.adInteger:
return(ColumnType.Integer);
case ADOX.DataTypeEnum.adSingle:
return(ColumnType.SinglePrecision);
case ADOX.DataTypeEnum.adDouble:
return(ColumnType.DoublePrecision);
case ADOX.DataTypeEnum.adCurrency:
return(ColumnType.Currency);
case ADOX.DataTypeEnum.adDecimal:
case ADOX.DataTypeEnum.adNumeric:
case ADOX.DataTypeEnum.adVarNumeric:
return(ColumnType.Decimal);
case ADOX.DataTypeEnum.adDate:
case ADOX.DataTypeEnum.adDBTimeStamp:
return(ColumnType.DateTime);
case ADOX.DataTypeEnum.adVarChar:
case ADOX.DataTypeEnum.adVarWChar:
return(ColumnType.NVarChar);
case ADOX.DataTypeEnum.adLongVarChar:
case ADOX.DataTypeEnum.adLongVarWChar:
return(ColumnType.NText);
case ADOX.DataTypeEnum.adLongVarBinary:
return(ColumnType.Blob);
case ADOX.DataTypeEnum.adGUID:
return(ColumnType.Guid);
default:
return(ColumnType.Unknown);
}
}
///////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////
#region Private creating helpers
/// <summary>
/// Converts <c>OleDbType</c> to <c>DataTypeEnum</c>.
/// </summary>
/// <param name="type">Type to converting.</param>
/// <returns>Converted type.</returns>
private ADOX.DataTypeEnum _ConvertType(OleDbType type)
{
ADOX.DataTypeEnum outType = ADOX.DataTypeEnum.adWChar;
switch (type)
{
case OleDbType.SmallInt:
outType = ADOX.DataTypeEnum.adSmallInt;
break;
case OleDbType.Integer:
outType = ADOX.DataTypeEnum.adInteger;
break;
case OleDbType.Single:
outType = ADOX.DataTypeEnum.adSingle;
break;
case OleDbType.Double:
outType = ADOX.DataTypeEnum.adDouble;
break;
case OleDbType.Date:
outType = ADOX.DataTypeEnum.adDate;
break;
case OleDbType.Guid:
outType = ADOX.DataTypeEnum.adGUID;
break;
case OleDbType.WChar:
outType = ADOX.DataTypeEnum.adWChar;
break;
case OleDbType.LongVarWChar:
outType = ADOX.DataTypeEnum.adLongVarWChar;
break;
case OleDbType.LongVarBinary:
outType = ADOX.DataTypeEnum.adLongVarBinary;
break;
default:
Debug.Assert(false); // NOTE: not supported
break;
}
return(outType);
}
private ADOX.DataTypeEnum comm(string type)
{
ADOX.DataTypeEnum comm = new ADOX.DataTypeEnum();
if (type.Equals("vachar"))
{
comm = ADOX.DataTypeEnum.adVarChar;
}
else if (type == "int" || type.Equals("Integer"))
{
comm = ADOX.DataTypeEnum.adInteger;
}
else if (type.Equals("double"))
{
comm = ADOX.DataTypeEnum.adDouble;
}
else
{
comm = ADOX.DataTypeEnum.adIUnknown;
}
return(comm);
}
private DbType GetDbType(ADOX.DataTypeEnum type)
{
switch (type)
{
case ADOX.DataTypeEnum.adBigInt: return(DbType.Int64);
case ADOX.DataTypeEnum.adBinary: return(DbType.Binary);
case ADOX.DataTypeEnum.adBoolean: return(DbType.Boolean);
case ADOX.DataTypeEnum.adBSTR: return(DbType.AnsiString);
case ADOX.DataTypeEnum.adChapter: return(DbType.Object); // TODO: Investigate data type.
case ADOX.DataTypeEnum.adChar: return(DbType.AnsiStringFixedLength);
case ADOX.DataTypeEnum.adCurrency: return(DbType.Currency);
case ADOX.DataTypeEnum.adDate: return(DbType.DateTime);
case ADOX.DataTypeEnum.adDBDate: return(DbType.DateTime);
case ADOX.DataTypeEnum.adDBTime: return(DbType.Time);
case ADOX.DataTypeEnum.adDBTimeStamp: return(DbType.Binary);
case ADOX.DataTypeEnum.adDecimal: return(DbType.Decimal);
case ADOX.DataTypeEnum.adDouble: return(DbType.Decimal);
case ADOX.DataTypeEnum.adEmpty: return(DbType.Object); // TODO: Investigate data type.
case ADOX.DataTypeEnum.adError: return(DbType.Object); // TODO: Investigate data type.
case ADOX.DataTypeEnum.adFileTime: return(DbType.Object); // TODO: Investigate data type.
case ADOX.DataTypeEnum.adGUID: return(DbType.Guid);
case ADOX.DataTypeEnum.adIDispatch: return(DbType.Object); // TODO: Investigate data type.
case ADOX.DataTypeEnum.adInteger: return(DbType.Int32);
case ADOX.DataTypeEnum.adIUnknown: return(DbType.Object); // TODO: Investigate data type.
case ADOX.DataTypeEnum.adLongVarBinary: return(DbType.Binary);
case ADOX.DataTypeEnum.adLongVarChar: return(DbType.String);
case ADOX.DataTypeEnum.adLongVarWChar: return(DbType.String);
case ADOX.DataTypeEnum.adNumeric: return(DbType.Decimal);
case ADOX.DataTypeEnum.adPropVariant: return(DbType.Object);
case ADOX.DataTypeEnum.adSingle: return(DbType.Decimal);
case ADOX.DataTypeEnum.adSmallInt: return(DbType.Int16);
case ADOX.DataTypeEnum.adTinyInt: return(DbType.SByte);
case ADOX.DataTypeEnum.adUnsignedBigInt: return(DbType.UInt64);
case ADOX.DataTypeEnum.adUnsignedInt: return(DbType.UInt32);
case ADOX.DataTypeEnum.adUnsignedSmallInt: return(DbType.UInt16);
case ADOX.DataTypeEnum.adUnsignedTinyInt: return(DbType.Byte);
case ADOX.DataTypeEnum.adUserDefined: return(DbType.Object);
case ADOX.DataTypeEnum.adVarBinary: return(DbType.Binary);
case ADOX.DataTypeEnum.adVarChar: return(DbType.AnsiString);
case ADOX.DataTypeEnum.adVariant: return(DbType.Object);
case ADOX.DataTypeEnum.adVarNumeric: return(DbType.Decimal);
case ADOX.DataTypeEnum.adVarWChar: return(DbType.String);
case ADOX.DataTypeEnum.adWChar: return(DbType.StringFixedLength);
default: return(DbType.Object);
}
}
/// <summary>
/// 添加表.
/// </summary>
/// <returns></returns>
public string CreateTable(string dbPath, string tableName, string[] fieldNames, string[] fieldTypes)
{
if (fieldNames.Length != fieldTypes.Length)
{
return("field name count discordance with type count -->CreateTable");
}
string names = GetTableName();
if (names.Contains(tableName))
{
return("this data base already exist the table -->CreateTable");
}
ADOX.DataTypeEnum[] ADOXFieldTypes = new ADOX.DataTypeEnum[fieldTypes.Length];
for (int i = 0; i < fieldTypes.Length; i++)
{
switch (fieldTypes[i])
{
case "int":
ADOXFieldTypes[i] = ADOX.DataTypeEnum.adInteger;
break;
case "string":
ADOXFieldTypes[i] = ADOX.DataTypeEnum.adVarWChar;
break;
case "double":
ADOXFieldTypes[i] = ADOX.DataTypeEnum.adDouble;
break;
case "bool":
ADOXFieldTypes[i] = ADOX.DataTypeEnum.adBoolean;
break;
default:
return("nonsupport the data type -->CreateTable");
}
}
try
{
ADOX.Catalog cataLog = new ADOX.Catalog();
ADODB.Connection cn = new ADODB.Connection();
cn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dbPath + ";" + "Jet OLEDB:Engine Type=5");
cataLog.ActiveConnection = cn;
ADOX.Table table = new ADOX.Table();
table.ParentCatalog = cataLog;
table.Name = tableName;
for (int i = 0; i < fieldNames.Length; i++)
{
ADOX.Column col = new ADOX.Column();
col.ParentCatalog = cataLog;
col.Type = ADOXFieldTypes[i];
col.Name = fieldNames[i];
col.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
col.Properties["AutoIncrement"].Value = false; //自动编号,注意此处不允许自动编号
table.Columns.Append(col, ADOX.DataTypeEnum.adDouble, 50);
}
cataLog.Tables.Append(table);
ShowTableNames(combox_table_names);
return("OK");
}
catch (Exception ex)
{
return(ex.Message + " -->CreateTable");
}
}
