由于在此之前我没有用过Access数据库的，当需要想数据库中插入多条数据时，我们不妨先按照sql server的做法：“insert into tablename(column1,column2) values (a,b),(c,d),(e,f)”。于是按照这个思路，我的第一个方案出来了。
尝试一：
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关系
/// </summary>
/// <param name="roleContact">角色关系 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下就会出现这样的错误：SQL 语句的结束位置缺少分号 (;)。
Access对sql的支持果然是大大精简，到此尝试一失败！， 很快在我有另外idea。sql server 多表查询对select table 的支持！我可以直接传一个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);
}
当代码到这里我就知道此方法行不通，因为OledbType中没有对应的table类型，如果是SQL server由于支持xml可以设置为 SqlDbType.Xml类型来传递DataTable数据，由于sql本事对xml的支持 ，可以用sql基于xml的查询，本文主要讨论Access，此处暂不讨论了！尝试二宣布失败！。接下来我又想到了零时表，Access是不是也支持零时表的查询呢？
尝试三
The Demo: 取出我想要的数据格式 （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中定义一个零时表，然后向其中插入尽可能全的数据，然后在基于零时表查询出想要的数据放入到我想要的数据中执行！exec下结果又出问错了！此处抛出这样的错误：无效的 SQL语句；期待 'DELETE'、'INSERT'、'PROCEDURE'、'SELECT'、或 'UPDATE'。其实会出错完全可以想想的到，毕竟Access中连insert into table values (1,2),(1,3) 这样的语句都不支持。此时尝试三也不得不宣告失败！尝试了这么多，我不得不使用早就准备用的方法 多条insert一起执行。
尝试四
The Demo: 先获取我想要的数据形式 ：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);
}
执行下结果打出我的意外：在 SQL 语句结尾之后找到字符。 竟然连这种语句都不支持，没相当Access会对数据支持的这么少。既然这样也不行，难道只有循环执行每一天SQL语句吗？我看可行！循环是必要的，只要不多次连接数据库，也不会占用太多资源的，此时引出了我的本文的重点，如何向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;
}
}
}
注意当插入多条语句时我们不要忘了一旦发生异常，我们需要撤销操作，就必须要用到事务。执行Aceess的insert时，我们需要在connection关闭前循环插入，这样才能减少资源暂用，提升性能。这里宣告尝试五成功！