當(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)插入，這樣才能減少資源暫用，提升性能。這里宣告嘗試五成功！
喜歡 
頂
難過
囧
圍觀
無聊
