class ADOXer
2 {
3 /// <summary>
4 /// 激活Access数据库(创建或打开)
5 /// </summary>
6 /// <param name="dbName">数据库全路径</param>
7 /// <param name="pwd">密码</param>
8 /// <returns>数据库</returns>
9 private static Catalog ActivateDB(
string dbName,
string pwd,
out string msg)
10 {
11 msg =
"";
12 Catalog catalog =
null;
13
14 string conn =
"";
15 if (
string.IsNullOrEmpty(pwd))
16 conn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
dbName;
17 else
18 conn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName +
";Jet OLEDB:Database Password=" + pwd +
";Jet OLEDB:Engine Type=5";
19
20 try
21 {
22 catalog =
new Catalog();
23 if (File.Exists(dbName))
24 {
25 catalog.let_ActiveConnection(conn);
/*绑定连接,获取对象*/
26 }
27 else
28 catalog.Create(conn);
/*绑定连接,创建对象*/
29 }
30 catch (Exception ex)
31 { msg =
"数据库激活失败:" +
ex.Message; }
32
33 return catalog;
34 }
35 /// <summary>
36 /// 创建Access数据表
37 /// </summary>
38 /// <param name="dbName">数据库实例名(即数据库文件)</param>
39 /// <param name="pwd">数据库密码</param>
40 /// <param name="tbName">数据表名</param>
41 /// <param name="columnNames">数据表列名序列</param>
42 /// <returns></returns>
43 public static string CreateTB(
string dbName,
string pwd,
string tbName,
params string[] columnNames)
44 {
45 string msg =
"";
46 if (columnNames.Length <=
1)
return "数据列不足";
47 Catalog catalog = ActivateDB(dbName, pwd,
out msg);
48 if (!
string.IsNullOrEmpty(msg))
return msg;
49
50 if (catalog.Tables.Count>
0)
51 foreach (ADOX.Table t
in catalog.Tables)
52 {
53 if (t.Name.Equals(tbName))
54 return "数据表已存在";
55 }
56
57 ADODB.Connection cnn =
new ADODB.Connection();
58 cnn=
catalog.ActiveConnection;
59
60 ADOX.Table tb =
new ADOX.Table();
61 tb.Name =
tbName;
62 //主键列
63 ADOX.Column keyCol =
new ADOX.Column();
64 keyCol.ParentCatalog =
catalog;
65 keyCol.Name =
"ID";
66 keyCol.Type =
ADOX.DataTypeEnum.adInteger;
67 keyCol.DefinedSize =
9;
68 keyCol.Properties[
"AutoIncrement"].Value =
true;
69 //添加主键
70 tb.Columns.Append(keyCol, ADOX.DataTypeEnum.adInteger,
0);
71 tb.Keys.Append(
"PrimatyKey", ADOX.KeyTypeEnum.adKeyPrimary,
"ID",
"",
"");
72
73 foreach (
string colName
in columnNames)
74 {
75 tb.Columns.Append(colName, DataTypeEnum.adVarWChar,
64);
//DataTypeEnum.adDate;
76 }
77 try { catalog.Tables.Append(tb); }
78 catch (Exception ex) { msg =
ex.Message; }
79 finally { cnn.Close(); catalog =
null; tb =
null; }
80
81 return msg;
82 }
83 /// <summary>
84 /// ADOX,ADODB修改数据表(失败)
85 /// </summary>
86 /// <param name="dbName"></param>
87 /// <param name="pwd"></param>
88 /// <param name="tbName"></param>
89 /// <param name="sql"></param>
90 /// <returns></returns>
91 public static string ExecuteSql2Db(
string dbName,
string pwd,
string tbName,
string sql)
92 {
93 string msg =
"";
94 #region 通过获取实例的表,直接对表进行数据操作,失败;这已经超出了ADOX的范围,应该用OleDB操作
95 //Catalog catalog = ActivateDB(dbName, pwd, out msg);
96 //if (!string.IsNullOrEmpty(msg)) return msg;
97
98 //ADOX.Table tb = null;
99 //if (catalog.Tables.Count <= 0)
100 // return "数据库实例尚无数据表";
101 //else
102 // foreach (ADOX.Table t in catalog.Tables)
103 // {
104 // if (t.Name.Equals(tbName))
105 // { tb = t; break; }
106 // }
107 //if (tb == null) return "未初始化数据表:" + tbName;
108 #endregion
109 //OleDb操作
110
111 return msg;
112 }
113 /// <summary>
114 /// 修改Access数据库实例密码
115 /// </summary>
116 /// <param name="dbName">数据库实例名(数据库文件)</param>
117 /// <param name="oldPwd">原密码</param>
118 /// <param name="newPwd">新密码</param>
119 /// <returns></returns>
120 public static string ChangePwd(
string dbName,
string oldPwd,
string newPwd)
121 {
122 string conn =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName +
";Jet OLEDB:Database password=" +
oldPwd;
123 string sql =
"ALTER DATABASE PASSWORD " + newPwd +
" " +
oldPwd;
124 string msg =
"";
125 ADODB.Connection cn =
null;
126 try
127 {
128 cn =
new ADODB.Connection();
129 cn.Mode =
ADODB.ConnectModeEnum.adModeShareExclusive;
130 cn.Open(conn,
null,
null, -
1);
131 // 执行 SQL 语句以更改密码。
132 object num;
133 cn.Execute(sql,
out num, -
1);
134 }
135 catch (Exception ex) { msg =
ex.Message; }
136 finally { cn.Close(); }
137 return msg;
138 }
139 }
操作数据库
1 class OleDBer
2 {
3 private static string conStr = "";
4 private static OleDbConnection _cnn = null;
5
6 private static OleDbConnection OleCnn
7 {
8 get
9 {
10 if (_cnn == null)
11 {
12 _cnn = new OleDbConnection(conStr);
13 _cnn.Open();
14 }
15 else if (_cnn.State == System.Data.ConnectionState.Closed)
16 {
17 _cnn.Open();
18 }
19 else if (_cnn.State == System.Data.ConnectionState.Broken)
20 {
21 _cnn.Close();
22 _cnn.Open();
23 }
24 return _cnn;
25 }
26 }
27 /// <summary>
28 /// 据Sql修改数据表
29 /// </summary>
30 /// <param name="dbName"></param>
31 /// <param name="pwd"></param>
32 /// <param name="sql"></param>
33 /// <returns></returns>
34 public static string ExecuteSql(string dbName, string pwd, string sql)
35 {
36 string msg = "";
37 conStr=string.IsNullOrEmpty(pwd)?"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";Persist Security Info=False":
38 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";Persist Security Info=False;Jet OLEDB:Database password=" + pwd;
39
40 using (OleDbCommand oCmd = new OleDbCommand(sql,OleCnn))
41 {
42 try
43 {
44 oCmd.ExecuteNonQuery();
45 }
46 catch (Exception ex) { msg = ex.Message; }
47 }
48 return msg;
49 }
50
51 public static DataTable GetDataTable(string dbName, string pwd, string sql,ref string msg)
52 {
53 msg = "";
54 conStr = string.IsNullOrEmpty(pwd) ? "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";Persist Security Info=False" :
55 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";Persist Security Info=False;Jet OLEDB:Database password=" + pwd;
56
57 try
58 {
59 DataSet dataset = new DataSet();
60 using (OleDbCommand com = new OleDbCommand(sql, OleCnn))
61 {
62 OleDbDataAdapter da = new OleDbDataAdapter(com);
63 da.Fill(dataset);
64 }
65 return dataset.Tables[0];
66 }
67 catch (Exception ex) { msg = ex.Message; return null; }
68 }
69
70 public static bool CheckExists(string dbName, string pwd, string sql)
71 {
72 string msg="";
73 DataTable dt = GetDataTable(dbName, pwd, sql, ref msg);
74 if (dt != null && dt.Rows.Count > 0) return true;
75 else return false;
76 }
77 public static object GetDataValue(string dbName, string pwd, string sql)
78 {
79 string msg = "";
80 DataTable dt = GetDataTable(dbName, pwd, sql, ref msg);
81 if (dt != null && dt.Rows.Count > 0) return dt.Rows[0][0];
82 else return DBNull.Value;
83 }
84
85 public string AddData(string fileName, string pwd)
86 {
87 string msg = "";
88 string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Persist Security Info=False;Jet OLEDB:Database password=" + pwd;
89
90 OleDbConnection conn = new OleDbConnection(strConnection);
91 string strSql = "select * from AdPlayList";
92 OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
93 DataSet ds = new DataSet();
94 try
95 {
96 conn.Open();
97 da.Fill(ds, "AdPlayList");
98 DataRow dr = ds.Tables["AdPlayList"].NewRow();
99 dr["FileName"] = "A.wmv";
100 dr["FileDate"] = DateTime.Now.ToShortDateString();
101 dr["FileSize"] = 25;
102 dr["OrderID"] = 1;
103 dr["Sha1"] = "2q34lkadsflaoiulkj34";
104 ds.Tables["AdPlayList"].Rows.Add(dr);
105 OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
106 da.Update(ds, "AdPlayList");
107 ds.AcceptChanges();
108 }
109 catch (Exception ex)
110 {
111 //MessageBox.Show(ex.Message);
112 msg = ex.Message;
113 }
114 finally
115 {
116 da.Dispose();
117 conn.Dispose();
118 conn.Close();
119 }
120 return msg;
121 }
122
123 }
操作数据表
C# 创建Access数据库及数据表的增删改查
标签：ted clu dataset ddd build cnn 初始化 修改 ase
