public void SetupServer()
{
string path = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\Data\\coolstorage.mdb"));
if (File.Exists(path))
File.Delete(path);
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Jet OLEDB:Engine Type=5");
CSConfig.SetDB(new CSDataProviderAccess(path));
CSDatabase.ExecuteNonQuery(
"CREATE TABLE tblCustomers (CustomerID COUNTER PRIMARY KEY,Name TEXT(50) NOT NULL)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblCustomers_Name ON tblCustomers (Name)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCustomerPaymentMethodLinks (
CustomerID integer NOT NULL,
PaymentMethodID integer NOT NULL,
primary key (CustomerID,PaymentMethodID)
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrderItems (
OrderItemID counter PRIMARY KEY,
OrderID integer NOT NULL,
Qty integer NOT NULL,
Price double NOT NULL,
Description TEXT(200) NOT NULL
)
");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrderItems_OrderID ON tblOrderItems (OrderID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrders (
OrderID counter PRIMARY KEY,
[Date] datetime NOT NULL DEFAULT DATE()+TIME(),
CustomerID integer NOT NULL,
SalesPersonID integer NULL,
DataState text(50))");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_CustomerID ON tblOrders (CustomerID)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_SalesPersonID ON tblOrders (SalesPersonID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblPaymentMethods (
PaymentMethodID counter primary key,
Name text(50) NOT NULL,
MonthlyCost integer NOT NULL
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblSalesPeople (
SalesPersonID counter primary key,
Name text(50) NOT NULL,
SalesPersonType integer NULL)
");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCoolData (
CoolDataID guid NOT NULL PRIMARY KEY,
Name text(50) NULL)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblColdData (Name text(50) NULL)");
cat.let_ActiveConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path);
ADOX.Column column = new ADOX.Column();
column.Name = "ColdDataID";
column.Type = ADOX.DataTypeEnum.adGUID;
column.ParentCatalog = cat;
column.Properties["AutoIncrement"].Value = false;
column.Properties["Fixed Length"].Value = true;
column.Properties["Jet OLEDB:AutoGenerate"].Value = true;
column.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
cat.Tables["tblColdData"].Columns.Append(column, ADOX.DataTypeEnum.adGUID, 0);
CSDatabase.ExecuteNonQuery("ALTER TABLE tblColdData ADD CONSTRAINT PK_COLD_DATA PRIMARY KEY (ColdDataID)");
}
private string GetColumnDescription(ADOX.Column pcol, bool pfStripFlags)
{
string strDesc = "";
try {
strDesc = pcol.Properties["Description"].Value.ToString();
}
catch (Exception) { }
if (strDesc.Length != 0 && pfStripFlags)
{
int intPos = 0;
while ((intPos = strDesc.IndexOf("#")) >= 0)
{
int intPosEnd = strDesc.IndexOf(" ", intPos + 1);
if (intPosEnd >= 0)
{
strDesc = strDesc.Substring(0, intPos) + strDesc.Substring(intPosEnd + 1);
}
else
{
strDesc = strDesc.Substring(0, intPos);
break;
}
}
}
return(strDesc);
}
public override void SetFieldMark(string tableName, string tableMemo, TableField[] fields)
{
try
{
ADODB.Connection adodb_conn = new ADODB.Connection();
adodb_conn.Open(base.connector.ConnectionString, null, null, -1);
ADOX.Catalog catalog = new ADOX.Catalog();
catalog.ActiveConnection = adodb_conn;
ADOX.Table table = catalog.Tables[tableName];
foreach (TableField field in fields)
{
ADOX.Column col = table.Columns[field.Name];
}
Marshal.FinalReleaseComObject(catalog.ActiveConnection);
Marshal.FinalReleaseComObject(catalog);
}
catch (Exception e)
{
throw (e);
}
//---------------------
// 作者：重庆 - 传说
//来源：CSDN
//原文：https://blog.csdn.net/zdb330906531/article/details/49420991
//版权声明：本文为博主原创文章，转载请附上博文链接！
}
public object IndexDefinition(ADOX.Table tblDef, ADOX.Index idxDef)
{
int intLoop = 0;
string strIndex = null;
ADOX.Column colDef = null;
if (idxDef.PrimaryKey == true)
{
strIndex = strIndex + "PRIMARY KEY ";
}
else if (idxDef.Unique)
{
strIndex = strIndex + "UNIQUE INDEX " + MySQLName(idxDef.Name);
}
else
{
strIndex = strIndex + "INDEX " + MySQLName(idxDef.Name);
}
strIndex = strIndex + "(";
for (intLoop = 0; intLoop < idxDef.Columns.Count; intLoop++)
{
colDef = idxDef.Columns[intLoop];
strIndex = strIndex + MySQLName(colDef.Name);
if (intLoop < idxDef.Columns.Count - 1)
{
strIndex = strIndex + ",";
}
}
strIndex = strIndex + ")";
return(strIndex);
}
public object ColDefinition(ADOX.Column col)
{
string strDefine = null;
strDefine = MySQLName(col.Name) + " " + DataType(col);
if (col.Properties["Autoincrement"].Value.ToString() == "True")
{
strDefine += " NOT NULL AUTO_INCREMENT";
}
else
{
if (col.Properties["nullable"].Value.ToString() == "True")
{
strDefine += " NULL";
}
else
{
strDefine += " NOT NULL";
}
}
if (col.Properties["Default"].Value != null)
{
if (col.Type == ADOX.DataTypeEnum.adBoolean)
{
Console.WriteLine("\tColumn: " + col.Name.ToString() + " : " + col.Properties["Default"].Value.ToString());
if (col.Properties["Default"].Value.ToString() == "Yes" ||
col.Properties["Default"].Value.ToString() == "True" ||
col.Properties["Default"].Value.ToString() == "1" ||
col.Properties["Default"].Value.ToString() == "-1")
{
strDefine += " DEFAULT -1";
}
else
{
strDefine += " DEFAULT 0";
}
}
else if (col.Type == ADOX.DataTypeEnum.adVarWChar)
{
String strDefValue = col.Properties["Default"].Value.ToString().Trim();
strDefValue = strDefValue.Replace("\"", "");
strDefValue = strDefValue.Replace("\'", "");
if ("NULL" == strDefValue.ToUpper())
{
strDefValue = "NULL";
}
strDefine += " DEFAULT '" + strDefValue + "'";
}
else
{
strDefine += " DEFAULT " + col.Properties["Default"].Value.ToString().ToUpper();
}
}
return(strDefine);
}
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 int GetColumnCustomVarcharSize(ADOX.Column pcol)
{
string description = GetColumnDescription(pcol, false);
int size = 0;
string sizeString = GetColumnDescriptionProperty(description, "#size");
if (sizeString.Length != 0)
{
try {
size = int.Parse(sizeString);
}
catch (Exception) { }
}
return(size);
}
public string CreateAccessTable(string json)
{
//公共方法
ResultInfo resultInfo = new ResultInfo();
try
{
JObject obj = (JObject)JsonConvert.DeserializeObject(json);
string filePath = Convert.ToString(obj["filePath"]);
string tableName = Convert.ToString(obj["tableName"]);
string colums = Convert.ToString(obj["colums"]);//bool
JArray obj1 = (JArray)JsonConvert.DeserializeObject(colums);
int sum = obj1.Count();
ADOX.Column[] csf = new ADOX.Column[sum];
for (int i = 0; i < sum; i++)
{
string name = Convert.ToString(obj1[i]["Name"].ToString());
string type = Convert.ToString(obj1[i]["Type"].ToString());
int definedSize = Convert.ToInt32(obj1[i]["DefinedSize"].ToString());
csf[i] = new ADOX.Column()
{
Name = name, Type = comm(type), DefinedSize = definedSize
};
}
bool isContainKeyID = Convert.ToBoolean(obj["isContainKeyID"].ToString());
// ADOX.Column[] waveDataColumns;
DataAccess.AccessHelper.CreateAccessTable(filePath, tableName, csf, isContainKeyID);
resultInfo.flag = 1;
resultInfo.msg = "";
resultInfo.data = filePath;
}
catch (Exception ex)
{
resultInfo.flag = 0;
resultInfo.msg = ex.Message;
}
return(JsonConvert.SerializeObject(resultInfo));
}
/// <summary>
/// Adds fields to table.
/// </summary>
/// <param name="tableDefinition">Table definition.</param>
/// <param name="tableDescription">Table Description.</param>
/// <param name="columns">Database columns.</param>
private void _AddFieldsToTable(ITableDefinition tableDefinition,
TableDescription tableDescription,
ADOX.Columns columns)
{
Debug.Assert(null != tableDefinition);
Debug.Assert(null != tableDescription);
Debug.Assert(null != columns);
ICollection <string> fields = tableDefinition.Fields;
foreach (string field in fields)
{
FieldInfo info = tableDescription.GetFieldInfo(field);
Debug.Assert(null != info);
columns.Append(info.Name, _ConvertType(info.Type), info.Size);
// make field not required
ADOX.Column column = columns[info.Name];
column.Attributes = ADOX.ColumnAttributesEnum.adColNullable;
}
}
public static void CreateAccessDatabase(string file)
{
string connectionString = string.Format("Provider={0}; Data Source={1}; Jet OLEDB:Engine Type={2}",
"Microsoft.Jet.OLEDB.4.0",
file,
5);
ADOX.Catalog catalog = new ADOX.Catalog();
catalog.Create(connectionString);
ADOX.Table table = new ADOX.Table();
table.Name = "Manufacturers"; // Table name
// Column 1 (BSSID)
ADOX.Column BSSIDCol = new ADOX.Column();
BSSIDCol.Name = "BSSID";
BSSIDCol.ParentCatalog = catalog;
BSSIDCol.Type = ADOX.DataTypeEnum.adVarWChar;
BSSIDCol.DefinedSize = 6;
// Column 2 (Manufacturer)
ADOX.Column ManuCol = new ADOX.Column();
ManuCol.Name = "Manufacturer";
ManuCol.ParentCatalog = catalog;
ManuCol.Type = ADOX.DataTypeEnum.adVarWChar;
ManuCol.DefinedSize = 255;
table.Columns.Append(BSSIDCol);
table.Columns.Append(ManuCol);
catalog.Tables.Append(table);
// Close the connection to the database after we are done creating it and adding the table to it.
ADODB.Connection con = (ADODB.Connection)catalog.ActiveConnection;
if (con != null && con.State != 0)
{
con.Close();
}
}
/// <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");
}
}
private void LoadExtraDataForTable()
{
try
{
if (this._array.Count > 0)
{
ADODB.Connection cnn = new ADODB.Connection();
ADOX.Catalog cat = new ADOX.Catalog();
// Open the Connection
cnn.Open(dbRoot.ConnectionString, null, null, 0);
cat.ActiveConnection = cnn;
ADOX.Columns cols = null;
cols = cat.Tables[this.Table.Name].Columns;
Column col = this._array[0] as Column;
f_TypeName = new DataColumn("TYPE_NAME", typeof(string));
col._row.Table.Columns.Add(f_TypeName);
f_IsAutoKey = new DataColumn("AUTO_INCREMENT", typeof(Boolean));
col._row.Table.Columns.Add(f_IsAutoKey);
f_AutoKeySeed = new DataColumn("AUTO_KEY_SEED", typeof(System.Int32));
col._row.Table.Columns.Add(f_AutoKeySeed);
f_AutoKeyIncrement = new DataColumn("AUTO_KEY_INCREMENT", typeof(System.Int32));
col._row.Table.Columns.Add(f_AutoKeyIncrement);
int count = this._array.Count;
Column c = null;
ADOX.Column cx = null;
for (int index = 0; index < count; index++)
{
cx = cols[index];
c = (Column)this[cx.Name];
string hyperlink = "False";
try
{
hyperlink = cx.Properties["Jet OLEDB:Hyperlink"].Value.ToString();
}
catch {}
string name = cx.Name;
Console.WriteLine("-----------------------------------------");
foreach (ADOX.Property prop in cx.Properties)
{
Console.WriteLine(prop.Attributes.ToString());
Console.WriteLine(prop.Name);
if (null != prop.Value)
{
Console.WriteLine(prop.Value.ToString());
}
}
c._row["TYPE_NAME"] = hyperlink == "False" ? cx.Type.ToString() : "Hyperlink";
try
{
if (c.Default == "GenGUID()")
{
c._row["AUTO_INCREMENT"] = Convert.ToBoolean(cx.Properties["Jet OLEDB:AutoGenerate"].Value);
}
else
{
c._row["AUTO_INCREMENT"] = Convert.ToBoolean(cx.Properties["Autoincrement"].Value);
c._row["AUTO_KEY_SEED"] = Convert.ToInt32(cx.Properties["Seed"].Value);
c._row["AUTO_KEY_INCREMENT"] = Convert.ToInt32(cx.Properties["Increment"].Value);
}
}
catch {}
}
cnn.Close();
}
}
catch {}
}
public override bool CreateColumns(ADOX.Catalog cat, string tableName)
{
try
{
ADOX.Table tbl = new ADOX.Table
{
ParentCatalog = cat,
Name = tableName
};
ADOX.Column col0 = new ADOX.Column
{
ParentCatalog = cat,
Type = ADOX.DataTypeEnum.adInteger,
Name = "索引"
};
col0.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tbl.Columns.Append(col0, ADOX.DataTypeEnum.adInteger, 1);
ADOX.Column col1 = new ADOX.Column
{
ParentCatalog = cat,
Type = ADOX.DataTypeEnum.adDate,
Name = "日期"
};
col1.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tbl.Columns.Append(col1, ADOX.DataTypeEnum.adDate, 1);
ADOX.Column col2 = new ADOX.Column
{
ParentCatalog = cat,
Name = "产能总量",
Type = ADOX.DataTypeEnum.adInteger
};
col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tbl.Columns.Append(col2, ADOX.DataTypeEnum.adInteger, 1);
ADOX.Column col3 = new ADOX.Column
{
ParentCatalog = cat,
Name = "贝壳",
Type = ADOX.DataTypeEnum.adInteger
};
col3.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tbl.Columns.Append(col3, ADOX.DataTypeEnum.adInteger, 1);
ADOX.Column col4 = new ADOX.Column
{
ParentCatalog = cat,
Name = "破角",
Type = ADOX.DataTypeEnum.adInteger
};
col4.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tbl.Columns.Append(col4, ADOX.DataTypeEnum.adInteger, 1);
ADOX.Column col5 = new ADOX.Column
{
ParentCatalog = cat,
Name = "凸边",
Type = ADOX.DataTypeEnum.adInteger
};
col5.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tbl.Columns.Append(col5, ADOX.DataTypeEnum.adInteger, 1);
tbl.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "索引", "", "");
cat.Tables.Append(tbl);;
tbl = null;
return(true);
}
catch (Exception ex)
{
return(false);
}
}
private dbdefinitionEntityAttribute GenerateColumn(ADOX.Column pcol, int pColIndex)
{
dbdefinitionEntityAttribute attr = new dbdefinitionEntityAttribute();
attr.name = pcol.Name;
attr.type = GetColumnType(pcol);
if (attr.type == type_AttributeType.VARCHAR)
{
int intSize = GetColumnCustomVarcharSize(pcol);
if (intSize == 0)
{
intSize = pcol.DefinedSize;
}
attr.size = intSize.ToString();
}
if (attr.type == type_AttributeType.DECIMAL)
{
attr.size = pcol.Precision.ToString();
if (pcol.Type == ADOX.DataTypeEnum.adCurrency)
{
attr.scale = "4";
}
else
{
attr.scale = pcol.NumericScale.ToString();
}
}
string strDefault = GetColumnDefaultValue(pcol, attr.type);
if (strDefault.Length != 0)
{
attr.defaultvalue = strDefault;
}
string strDescription = GetColumnDescription(pcol, true);
if (strDescription.Length != 0)
{
attr.description = strDescription;
}
attr.required = (pColIndex == 0) || ((pcol.Attributes & ADOX.ColumnAttributesEnum.adColNullable) == 0);
string strDesc = GetColumnDescription(pcol, false);
if ((strDesc.IndexOf("#identity") >= 0) || (strDesc.IndexOf("#auto-increment") >= 0))
{
attr.autoincrementSpecified = true;
attr.autoincrement = true;
}
if ((strDesc.IndexOf("#notest") >= 0))
{
attr.nounittestSpecified = true;
attr.nounittest = true;
}
if ((strDesc.IndexOf("#deprecated") >= 0))
{
attr.deprecatedSpecified = true;
attr.deprecated = true;
}
if ((strDesc.IndexOf("#xmlmapping") >= 0))
{
string xmlMapping = GetColumnDescriptionProperty(strDesc, "#xmlmapping");
if (xmlMapping.Length != 0)
{
attr.xmlmapping = xmlMapping;
}
}
if ((strDesc.IndexOf("#searchindex") >= 0))
{
string indexMode = GetColumnDescriptionProperty(strDesc, "#searchindex");
if (indexMode.Length == 0)
{
indexMode = "tokenized";
}
attr.searchindex = indexMode;
}
if ((strDesc.IndexOf("#searchstore") >= 0))
{
string searchStore = GetColumnDescriptionProperty(strDesc, "#searchstore");
if (searchStore.Length != 0)
{
attr.searchstore = searchStore;
}
}
if ((strDesc.IndexOf("#searchembeddepth") >= 0))
{
string searchEmbedDepthString = GetColumnDescriptionProperty(strDesc, "#searchembeddepth");
if (searchEmbedDepthString.Length != 0)
{
try {
int searchEmbedDepth = int.Parse(searchEmbedDepthString, CultureInfo.GetCultureInfo("en-US"));
attr.searchembeddepthSpecified = true;
attr.searchembeddepth = searchEmbedDepth;
}
catch (Exception) {
// ignore
}
}
}
if ((strDesc.IndexOf("#searchboost") >= 0))
{
string searchBoostString = GetColumnDescriptionProperty(strDesc, "#searchboost");
if (searchBoostString.Length != 0)
{
try {
float searchBoost = float.Parse(searchBoostString, CultureInfo.GetCultureInfo("en-US"));
attr.searchboostSpecified = true;
attr.searchboost = searchBoost;
}
catch (Exception) {
// ignore
}
}
}
if ((strDesc.IndexOf("#searchdateresolution") >= 0))
{
string searchDateResolution = GetColumnDescriptionProperty(strDesc, "#searchdateresolution");
if (searchDateResolution.Length != 0)
{
attr.searchdateresolution = searchDateResolution;
}
}
if ((strDesc.IndexOf("#searchfieldbridge") >= 0))
{
string searchFieldBridge = GetColumnDescriptionProperty(strDesc, "#searchfieldbridge");
if (searchFieldBridge.Length != 0)
{
attr.searchfieldbridge = searchFieldBridge;
}
}
return(attr);
}
public static bool CreateUserDatabase(ADOX.Catalog catalog)
{
ADOX.Table table = new ADOX.Table();
table.Name = "Message";
catalog.Tables.Append(table);
ADOX.Column col = newColumn("sender", ADOX.DataTypeEnum.adWChar, catalog);
col.DefinedSize = 50;
col.Attributes = ADOX.ColumnAttributesEnum.adColNullable;
table.Columns.Append(col);
col = newColumn("receiver", ADOX.DataTypeEnum.adWChar, catalog);
col.DefinedSize = 50;
col.Attributes = ADOX.ColumnAttributesEnum.adColNullable;
table.Columns.Append(col);
col = newColumn("content", ADOX.DataTypeEnum.adLongVarWChar, catalog);
col.DefinedSize = 5000;
col.Attributes = ADOX.ColumnAttributesEnum.adColNullable;
table.Columns.Append(col);
col = newColumn("message_date", ADOX.DataTypeEnum.adDate, catalog);
table.Columns.Append(col);
col = newColumn("type", ADOX.DataTypeEnum.adWChar, catalog);
col.DefinedSize = 50;
col.Attributes = ADOX.ColumnAttributesEnum.adColNullable;
table.Columns.Append(col);
col = newColumn("group_message", ADOX.DataTypeEnum.adBoolean, catalog);
table.Columns.Append(col);
table = new ADOX.Table();
table.Name = "Friend";
catalog.Tables.Append(table);
col = newColumn("friend_username", ADOX.DataTypeEnum.adWChar, catalog);
col.DefinedSize = 50;
col.Attributes = ADOX.ColumnAttributesEnum.adColNullable;
table.Columns.Append(col);
table = new ADOX.Table();
table.Name = "Group";
catalog.Tables.Append(table);
col = newColumn("group_id", ADOX.DataTypeEnum.adWChar, catalog);
col.DefinedSize = 50;
col.Attributes = ADOX.ColumnAttributesEnum.adColNullable;
table.Columns.Append(col);
table = new ADOX.Table();
table.Name = "friend_request";
catalog.Tables.Append(table);
col = newColumn("sender", ADOX.DataTypeEnum.adWChar, catalog);
col.DefinedSize = 50;
col.Attributes = ADOX.ColumnAttributesEnum.adColNullable;
table.Columns.Append(col);
col = newColumn("receiver", ADOX.DataTypeEnum.adWChar, catalog);
col.DefinedSize = 50;
col.Attributes = ADOX.ColumnAttributesEnum.adColNullable;
table.Columns.Append(col);
col = newColumn("request_date", ADOX.DataTypeEnum.adDate, catalog);
table.Columns.Append(col);
return(true);
}
private type_AttributeType GetColumnType(ADOX.Column pcol)
{
switch (pcol.Type)
{
case ADOX.DataTypeEnum.adBoolean:
return(type_AttributeType.BIT);
case ADOX.DataTypeEnum.adTinyInt:
return(type_AttributeType.BYTE);
case ADOX.DataTypeEnum.adDate:
return(type_AttributeType.TIMESTAMP);
case ADOX.DataTypeEnum.adSingle:
case ADOX.DataTypeEnum.adDouble:
return(type_AttributeType.FLOAT);
case ADOX.DataTypeEnum.adSmallInt:
return(type_AttributeType.SMALLINT);
case ADOX.DataTypeEnum.adInteger:
if (pcol.Name.EndsWith("ID") || pcol.Name.EndsWith("Id"))
{
return(type_AttributeType.ID);
}
else if (pcol.Name.ToLower().Equals("vstamp"))
{
return(type_AttributeType.VSTAMP);
}
else
{
return(type_AttributeType.INTEGER);
}
case ADOX.DataTypeEnum.adLongVarChar:
case ADOX.DataTypeEnum.adLongVarWChar:
if (GetColumnCustomVarcharSize(pcol) > 0)
{
return(type_AttributeType.VARCHAR);
}
else
{
return(type_AttributeType.CLOB);
}
case ADOX.DataTypeEnum.adCurrency:
return(type_AttributeType.DECIMAL);
case ADOX.DataTypeEnum.adNumeric:
if (pcol.Precision == 18 && pcol.NumericScale == 0)
{
return(type_AttributeType.BIGINT);
}
else
{
return(type_AttributeType.DECIMAL);
}
case ADOX.DataTypeEnum.adVarChar:
case ADOX.DataTypeEnum.adWChar:
case ADOX.DataTypeEnum.adVarWChar:
return(type_AttributeType.VARCHAR);
case ADOX.DataTypeEnum.adLongVarBinary:
return(type_AttributeType.BLOB);
default:
throw new Exception("Column '" + pcol.Name + "': Unsupported Field Type #" + pcol.Type + ".");
}
}
public void SetupServer()
{
string path = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\Data\\coolstorage.mdb"));
if (File.Exists(path))
{
File.Delete(path);
}
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Jet OLEDB:Engine Type=5");
CSConfig.SetDB(new CSDataProviderAccess(path));
CSDatabase.ExecuteNonQuery(
"CREATE TABLE tblCustomers (CustomerID COUNTER PRIMARY KEY,Name TEXT(50) NOT NULL)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblCustomers_Name ON tblCustomers (Name)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCustomerPaymentMethodLinks (
CustomerID integer NOT NULL,
PaymentMethodID integer NOT NULL,
primary key (CustomerID,PaymentMethodID)
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrderItems (
OrderItemID counter PRIMARY KEY,
OrderID integer NOT NULL,
Qty integer NOT NULL,
Price double NOT NULL,
Description TEXT(200) NOT NULL
)
");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrderItems_OrderID ON tblOrderItems (OrderID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblOrders (
OrderID counter PRIMARY KEY,
[Date] datetime NOT NULL DEFAULT DATE()+TIME(),
CustomerID integer NOT NULL,
SalesPersonID integer NULL,
DataState text(50))");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_CustomerID ON tblOrders (CustomerID)");
CSDatabase.ExecuteNonQuery(
@"CREATE INDEX tblOrders_SalesPersonID ON tblOrders (SalesPersonID)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblPaymentMethods (
PaymentMethodID counter primary key,
Name text(50) NOT NULL,
MonthlyCost integer NOT NULL
)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblSalesPeople (
SalesPersonID counter primary key,
Name text(50) NOT NULL,
SalesPersonType integer NULL)
");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblCoolData (
CoolDataID guid NOT NULL PRIMARY KEY,
Name text(50) NULL)");
CSDatabase.ExecuteNonQuery(
@"CREATE TABLE tblColdData (Name text(50) NULL)");
cat.let_ActiveConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path);
ADOX.Column column = new ADOX.Column();
column.Name = "ColdDataID";
column.Type = ADOX.DataTypeEnum.adGUID;
column.ParentCatalog = cat;
column.Properties["AutoIncrement"].Value = false;
column.Properties["Fixed Length"].Value = true;
column.Properties["Jet OLEDB:AutoGenerate"].Value = true;
column.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
cat.Tables["tblColdData"].Columns.Append(column, ADOX.DataTypeEnum.adGUID, 0);
CSDatabase.ExecuteNonQuery("ALTER TABLE tblColdData ADD CONSTRAINT PK_COLD_DATA PRIMARY KEY (ColdDataID)");
}
public object DataType(ADOX.Column colDef)
{
int intLength = 0;
int intPrecision = 0;
int intScale = 0;
string strNewType = null;
intLength = colDef.DefinedSize;
intPrecision = colDef.Precision;
intScale = colDef.NumericScale;
strNewType = colDef.Name;
switch (colDef.Type)
{
case ADOX.DataTypeEnum.adBigInt:
strNewType = "BIGINT";
break;
case ADOX.DataTypeEnum.adBoolean:
strNewType = "TINYINT(1)";
break;
case ADOX.DataTypeEnum.adDouble:
strNewType = "FLOAT";
break;
case ADOX.DataTypeEnum.adInteger:
strNewType = "INTEGER";
break;
case ADOX.DataTypeEnum.adNumeric:
strNewType = "NUMERIC (" + intPrecision + ", " + intScale + ")";
break;
case ADOX.DataTypeEnum.adSingle:
strNewType = "REAL";
break;
case ADOX.DataTypeEnum.adUnsignedTinyInt:
strNewType = "TINYINT";
break;
case ADOX.DataTypeEnum.adSmallInt:
strNewType = "SMALLINT";
break;
case ADOX.DataTypeEnum.adTinyInt:
strNewType = "TINYINT";
break;
case ADOX.DataTypeEnum.adVarChar:
strNewType = "VARCHAR (" + intLength + ")";
break;
case ADOX.DataTypeEnum.adVarWChar:
strNewType = "VARCHAR (" + intLength + ")";
break;
case ADOX.DataTypeEnum.adLongVarWChar:
strNewType = "LONGTEXT";
break;
case ADOX.DataTypeEnum.adDate:
strNewType = "DATETIME";
break;
case ADOX.DataTypeEnum.adCurrency:
strNewType = "FLOAT";
break;
default:
strNewType = "UNKNOWN";
break;
}
return(strNewType);
}
private string GetColumnDefaultValue(ADOX.Column pcol, type_AttributeType pType)
{
string strDefault = "";
try {
if (pcol.Properties["Default"] != null && pcol.Properties["Default"].Value != null)
{
strDefault = pcol.Properties["Default"].Value.ToString();
}
}
catch (Exception) {}
if (strDefault.Length != 0)
{
switch (pType)
{
case type_AttributeType.VARCHAR:
case type_AttributeType.CLOB:
if (strDefault.StartsWith("\"") && strDefault.EndsWith("\""))
{
strDefault = strDefault.Substring(1, strDefault.Length - 2);
}
break;
case type_AttributeType.INTEGER:
case type_AttributeType.SMALLINT:
case type_AttributeType.BYTE:
case type_AttributeType.BIGINT:
case type_AttributeType.ID:
case type_AttributeType.VSTAMP:
// keep default value
break;
case type_AttributeType.FLOAT:
strDefault = strDefault.Replace(",", ".");
break;
case type_AttributeType.BIT:
if (strDefault.Equals("1") || strDefault.ToLower().Equals("true") || strDefault.ToLower().Equals("yes"))
{
strDefault = "true";
}
else
{
strDefault = "false";
}
break;
case type_AttributeType.TIMESTAMP:
strDefault = strDefault.Substring(1, strDefault.Length - 2);
try {
DateTime dat = DateTime.Parse(strDefault, System.Globalization.CultureInfo.CreateSpecificCulture("en"));
strDefault = dat.ToString("s");
}
catch (Exception) {}
break;
case type_AttributeType.BLOB:
strDefault = "";
break;
}
}
return(strDefault);
}
