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