程式師世界 >> 數據庫知識 >> Access數據庫 >> 關於Access數據庫 >> Access數據庫多條數據Insert

Access數據庫多條數據Insert

編輯：關於Access數據庫

當下Access數據庫可能要被遺忘了！比較這個輕量級數據庫被精簡了許多功能，個人感覺用起來還是挺麻煩的！當前Access在企業項目中就是一塊雞肋。數據量小使用SQL Server是浪費，既然選擇了Access就別抱怨了！如果用好了還是一塊寶！廢話不多說了，今天就來探討下如何向Access中插入多條數據把！

由於在此之前我沒有用過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關閉前循環插入，這樣才能減少資源暫用，提升性能。這裡宣告嘗試五成功！