當(dāng)下Access數(shù)據(jù)庫可能要被遺忘了!比較這個(gè)輕量級(jí)數(shù)據(jù)庫被精簡了許多功能,個(gè)人感覺用起來還是挺麻煩的!當(dāng)前Access在企業(yè)項(xiàng)目中就是一塊雞肋。數(shù)據(jù)量小使用SQL Server是浪費(fèi),既然選擇了Access就別抱怨了!如果用好了還是一塊寶!廢話不多說了,今天就來探討下如何向Access中插入多條數(shù)據(jù)把!
由于在此之前我沒有用過Access數(shù)據(jù)庫的,當(dāng)需要想數(shù)據(jù)庫中插入多條數(shù)據(jù)時(shí),我們不妨先按照sql server的做法:“insert into tablename(column1,column2) values (a,b),(c,d),(e,f)”。于是按照這個(gè)思路,我的第一個(gè)方案出來了。
嘗試一:
The Demo:
StringBuilder BuiList = new StringBuilder(string.Format("({0},0)", UserID)); foreach (RepeaterItem item in Rpt_AdminRole.Items) { if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem) { HtmlInputCheckBox cbRole = item.FindControl("cb_Role") as HtmlInputCheckBox; if (cbRole.Checked) { BuiList.Append(",("); BuiList.Append(UserID); BuiList.Append(","); BuiList.Append(cbRole.Value); BuiList.Append(")"); } } }
The Dal:
/// <summary> /// 添加Role關(guān)系 /// </summary> /// <param name="roleContact">角色關(guān)系 eg: "(1,1),(1,2)"</param> /// <returns></returns> public static int InsertRoleContact(string roleContact) { string sql = "insert into Sky_Admin_Role(AdminID,RoleID) values "+roleContact; return Common.OleDbHelper.ExecuteNonQuery(CommandType.Text, sql, null); }
exec下就會(huì)出現(xiàn)這樣的錯(cuò)誤:SQL 語句的結(jié)束位置缺少分號(hào) (;)。
Access對sql的支持果然是大大精簡,到此嘗試一失敗!, 很快在我有另外idea。sql server 多表查詢對select table 的支持!我可以直接傳一個(gè)DataTable到sql語句中,說干就干!
嘗試二
The Demo : 獲取DataTable
public DataTable GetInsertSQL(Repeater rep,string controlID) { DataTable data = new DataTable(); data.Columns.Add("AdminID"); data.Columns.Add("RoleID"); foreach (RepeaterItem item in rep.Items) { if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem) { HtmlInputCheckBox cb = item.FindControl(controlID) as HtmlInputCheckBox; if (cb.Checked) { DataRow row = data.NewRow(); row.ItemArray = new object[] { UserID, cb.Value}; data.Rows.Add(row); } } } return data; }
The Dal:
public static int InsertRoleContact(DataTable dt) { string sql = "insert into Sky_Admin_Role(AdminID,RoleID) select * from @Data"; OleDbParameter[] param = new OleDbParameter[] { new OleDbParameter("@Data",?){Value =dt} }; return Common.OleDbHelper.ExecuteNonQuery(CommandType.Text, sql, param); }
當(dāng)代碼到這里我就知道此方法行不通,因?yàn)镺ledbType中沒有對應(yīng)的table類型,如果是SQL server由于支持xml可以設(shè)置為 SqlDbType.Xml類型來傳遞DataTable數(shù)據(jù),由于sql本事對xml的支持 ,可以用sql基于xml的查詢,本文主要討論Access,此處暫不討論了!嘗試二宣布失。。接下來我又想到了零時(shí)表,Access是不是也支持零時(shí)表的查詢呢?
嘗試三
The Demo: 取出我想要的數(shù)據(jù)格式 (1,2,3)
public string GetInserCollection(Repeater rep, string controlID) { StringBuilder buiCollecton = new StringBuilder(""); buiCollecton.Append("(0"); foreach (RepeaterItem item in rep.Items) { if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem) { HtmlInputCheckBox cb = item.FindControl(controlID) as HtmlInputCheckBox; if (cb.Checked) { buiCollecton.Append(string.Format(",{0}", cb.Value)); } } } buiCollecton.Append(")"); return buiCollecton.ToString(); }
The Dal :
public static int InsertRoleContact(int UserID,string RoleCollection)
{
StringBuilder BuiSQL = new StringBuilder("");
BuiSQL.Append("declare @SkyContact table(userID int,roleID int);");
BuiSQL.Append("insert into @SkyContact values select " + UserID + ",R_ID from Sky_Role;");
BuiSQL.Append(string.Format("insert into Sky_Admin_Role values (select * from @SkyContact where roleID in {0})",RoleCollection));
return Common.OleDbHelper.ExecuteNonQuery(CommandType.Text, BuiSQL.ToString(), null);
}
這里模仿SQL Server中定義一個(gè)零時(shí)表,然后向其中插入盡可能全的數(shù)據(jù),然后在基于零時(shí)表查詢出想要的數(shù)據(jù)放入到我想要的數(shù)據(jù)中執(zhí)行!exec下結(jié)果又出問錯(cuò)了!此處拋出這樣的錯(cuò)誤:無效的 SQL語句;期待 'DELETE'、'INSERT'、'PROCEDURE'、'SELECT'、或 'UPDATE'。其實(shí)會(huì)出錯(cuò)完全可以想想的到,畢竟Access中連insert into table values (1,2),(1,3) 這樣的語句都不支持。此時(shí)嘗試三也不得不宣告失敗!嘗試了這么多,我不得不使用早就準(zhǔn)備用的方法 多條insert一起執(zhí)行。
嘗試四
The Demo: 先獲取我想要的數(shù)據(jù)形式 :1,2,3 此處略。看sql:
public static int InsertRoleContact2(int UserID, string RoleCollection) { string[] arr = RoleCollection.Split(','); StringBuilder BuilSQL = new StringBuilder(""); foreach (string item in arr) { BuilSQL.Append(string.Format("insert into Sky_Admin_Role(AdminID,RoleID) values ({0},{1});",UserID,Convert.ToInt32(item))); } return Common.OleDbHelper.ExecuteNonQuery(CommandType.Text, BuilSQL.ToString(), null); }
執(zhí)行下結(jié)果打出我的意外:在 SQL 語句結(jié)尾之后找到字符。 竟然連這種語句都不支持,沒相當(dāng)Access會(huì)對數(shù)據(jù)支持的這么少。既然這樣也不行,難道只有循環(huán)執(zhí)行每一天SQL語句嗎?我看可行!循環(huán)是必要的,只要不多次連接數(shù)據(jù)庫,也不會(huì)占用太多資源的,此時(shí)引出了我的本文的重點(diǎn),如何向Access中插入多條記錄。
嘗試五
The Demo:
public static void InsertMultipleSQL(int UserID,string RoleCollection) { string[] arr = RoleCollection.Split(','); using (OleDbConnection conn = new OleDbConnection(Common.config.AccessConnStr)) { OleDbCommand cmd = conn.CreateCommand(); OleDbTransaction trans = null; try { conn.Open(); trans = conn.BeginTransaction(); cmd.Transaction = trans; foreach (string item in arr) { cmd.CommandText = string.Format(string.Format("insert into Sky_Admin_Role(AdminID,RoleID) values ({0},{1});", UserID, Convert.ToInt32(item))); cmd.ExecuteNonQuery(); } trans.Commit(); } catch (Exception ex) { trans.Rollback(); throw ex; } } }
注意當(dāng)插入多條語句時(shí)我們不要忘了一旦發(fā)生異常,我們需要撤銷操作,就必須要用到事務(wù)。執(zhí)行Aceess的insert時(shí),我們需要在connection關(guān)閉前循環(huán)插入,這樣才能減少資源暫用,提升性能。這里宣告嘗試五成功!