1.创建数据库
2.判断表是否存在
3.创建表
1. #region access数据库操作 之 创建数据库
private void creatMDB(string dbName)
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";");
//ADOX.Catalog cat = new Catalog();
//cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";Jet OLEDB:Engine Type=5");s
}
#endregion
2. #region access数据库操作 之 判断数据库中是否存在某表
public bool GetTables(OleDbConnection conn)
{
int result = 0;
DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] {null, null, null, "TABLE"});
if (schemaTable != null)
{
for (Int32 row = 0; row < schemaTable.Rows.Count; row++)
{
string col_name = schemaTable.Rows[row]["TABLE_NAME"].ToString();
if (col_name == "MyChooseStock")
{
result++;
}
}
}
if (result == 0)
return false;
return true;
}
#endregion
3. #region access数据库操作 之 创建表
//新建mdb的表,C#操作Access之创建表
//mdbHead是一个ArrayList，存储的是table表中的具体列名。
private void CreateMDBTable(string mdbPath, string tableName, ArrayList mdbHead)
{
ADOX.CatalogClass cat = new ADOX.CatalogClass();
string sAccessConnection = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + mdbPath;
connection = new OleDbConnection(sAccessConnection);
connection.Open();
ADODB.Connection cn = new ADODB.Connection();
cn.Open(sAccessConnection, null, null, -1);
cat.ActiveConnection = cn;
//新建一个表,C#操作Access之创建表
ADOX.TableClass tbl = new ADOX.TableClass();
tbl.ParentCatalog = cat;//目录
tbl.Name = tableName;
//增加一个自动增长的字段
ADOX.ColumnClass col = new ADOX.ColumnClass();
col.ParentCatalog = cat;
col.Type = ADOX.DataTypeEnum.adInteger; // 必须先设置字段类型
col.Name = "id";
col.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
col.Properties["AutoIncrement"].Value = true;
tbl.Columns.Append(col, ADOX.DataTypeEnum.adInteger, 0);
// 增加一个文本字段
int size = mdbHead.Count;
for (int i = 0; i < size; i++)
{
//增加一个文本字段
ADOX.ColumnClass col2 = new ADOX.ColumnClass();
col2.ParentCatalog = cat;
col2.Name = mdbHead[i].ToString(); //列的名称
col2.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
tbl.Columns.Append(col2, ADOX.DataTypeEnum.adVarWChar, 500);
}
//设置主键
tbl.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "id", "", "");
cat.Tables.Append(tbl); //这句把表加入数据库(非常重要) ,C#操作Access之创建表
tbl = null;
cat = null;
connection.Close();
}
#endregion
4.
using System.Data;
using System.Data.OleDb;
using ADOX; //需添加 Microsoft ADO Ext. 6.0 ***Security
protected override void buttonOk_Click(object sender, EventArgs e)
{
private OleDbConnection connection;
private OleDbDataAdapter dataAdapter;
string systemPath = Environment.GetFolderPath(Environment.SpecialFolder.System);
if (Directory.Exists(systemPath + "\\data") == false)//如果不存在就创建file文件夹
{
Directory.CreateDirectory(systemPath + "\\data");
}
string favoriteStockMDB = systemPath + "\\data\\Data.mdb";
if (!File.Exists(favoriteStockMDB)) creatMDB(favoriteStockMDB);//判断是否存在数据库，不存在，则创建
connection =
new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + favoriteStockMDB);
connection.Open();
if (!GetTables(connection))
{
string table_name = "MyChooseStock";
ArrayList col_names = new ArrayList();
col_names.Add("StockCode");
CreateMDBTable(favoriteStockMDB, table_name, col_names);
connection.Open();
}
dataAdapter = new OleDbDataAdapter("select * from MyChooseStock", connection);
DataSet dataSetSelect = new DataSet();
dataAdapter.Fill(dataSetSelect, "sel");
DataTable dataTable = dataSetSelect.Tables["sel"];
for (int i = 0; i < dataTable.Rows.Count; i++)
{
listSelect.Add(dataTable.Rows[i][1].ToString());
}
OleDbCommand insertCommand = connection.CreateCommand();
insertCommand.CommandText = "insert into MyChooseStock(StockCode) values (?)";
insertCommand.Parameters.Add("StockCode", OleDbType.VarChar, 1024);
insertCommand.Prepare();
insertCommand.Parameters[0].Value = list[0];
Int32 row = insertCommand.ExecuteNonQuery();
if (row != 0)
{
MessageBoxHelper.ShowInfo("收藏成功！");
}
else
{
MessageBoxHelper.ShowInfo("收藏失败！");
}
connection.Close();
}