UpdateDataSet更新資料（三層架構）

IDAL層：(IUpdateDataSet.cs)

using System;

using System.Data;

using System.Data.SqlClient;

 

using HR.SystemFramework.Common;

 

namespace HR.IDAL.Common

{

    ///<summary>

    /// IUpdateDataSet_ 的摘要描述。

    ///</summary>

    public interface IUpdateDataSet

    {

        void updateDataSet(DataSet dsChanges,string tableName,SqlDataAdapter myDA);

 

        void updateDataSet(DataSet dsChanges, string[] tableName, SqlDataAdapter[] myDA);

    }

}

 

 

SQLDAL層：(UpdateDataSet.cs)

 

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

 

using HR.IDAL.Common;

using HR.SystemFramework.Common;

namespace HR.SQLDAL.Common

{

    ///<summary>

    /// UpdateDataSet_ 的摘要描述。

    ///</summary>

    public class UpdateDataSet :IUpdateDataSet

    {

        private static SqlConnection conn ; // 此條非常重要，不允許修改

        SqlConnection Sysconn;

        SqlConnection Democonn;

 

        public UpdateDataSet()

        {

           

        }

 

        public UpdateDataSet(ActionTable _actionTable)

        {

            Sysconn = new SqlConnection(ConfigurationSettings.AppSettings["SQLConnection"]);

            Democonn = new SqlConnection(HR.Model.Common.LoginInfo.userLoginInfo.companyConnstring);

 

            if(_actionTable == ActionTable.SYS)

                conn = Sysconn;

            else if(_actionTable == ActionTable.DEMO)

                conn = Democonn;

        }

 

        #region IUpdateDataSet 成員

 

        ///<summary>

        /// 更新資料

        ///</summary>

        ///<param name="dsChanges">DataSet 改變值 </param>

        ///<param name="tableName">DataSet 更新的Table </param>

        ///<param name="myDA">SqlDataAdapter 適配器 </param>

        ///<param name="_ActionTable"> 選用何資料庫 </param>

        public void updateDataSet(DataSet dsChanges,string tableName, SqlDataAdapter myDA)

        {

            // TODO:  加入 UpdateDataSet.updateDataSet 實作

           

            try

            {

                conn.Open();

                myDA.Update(dsChanges,tableName);

            }

            catch(System.Exception ex)

            {

                throw ex;

            }

            finally

            {

                if(conn.State == ConnectionState.Open)

                    conn.Close();

            }

        }

 

        ///<summary>

        /// 更新多表關聯資料

        ///</summary>

        ///<param name="dsChanges">DataSet</param>

        ///<param name="tableName">TableName</param>

        ///<param name="myDA">SqlDataAdapter</param>

        public void updateDataSet(DataSet dsChanges, string[] tableName, SqlDataAdapter[] myDA)

        {      

            SqlTransaction trans;

            conn.Open();       

            trans = conn.BeginTransaction();

 

            try

            {

                for(int i=0;i<myDA.Length;i++)

                {

                    myDA[i].SelectCommand.Transaction = trans;

                    myDA[i].UpdateCommand.Transaction = trans;

                    myDA[i].InsertCommand.Transaction = trans;

                    myDA[i].DeleteCommand.Transaction = trans;

 

                    myDA[i].Update(dsChanges,tableName[i]);

                }

                trans.Commit();

            }

            catch(System.Exception ex)

            {

                trans.Rollback();

                throw ex;

            }

            finally

            {

                if(conn.State == ConnectionState.Open)

                    conn.Close();

            }

        }

 

        ///<summary>

        /// 配制SqlDataAdapter更新適配器

        ///</summary>

        ///<param name="SQL_Select">SQL 查詢命令 </param>

        ///<param name="SQL_Update">SQL 更新命令 </param>

        ///<param name="SQL_Insert">SQL 插入命令 </param>

        ///<param name="SQL_Delete">SQL 刪除命令 </param>

        ///<param name="GetParmsUpdate"> 更新 參數 </param>

        ///<param name="GetParmsInsert"> 插入 參數 </param>

        ///<param name="GetParmsDelete"> 刪除 參數 </param>

        ///<returns>SqlDataAdapter</returns>

        public SqlDataAdapter GetDataAdapter(string SQL_Select,string SQL_Update,string SQL_Insert,string SQL_Delete,

            SqlParameter[] GetParmsUpdate,SqlParameter[] GetParmsInsert,SqlParameter[] GetParmsDelete)

        {

 

            SqlDataAdapter myAdapter = new SqlDataAdapter(SQL_Select,conn);

            SqlCommand myCmd;

 

            myCmd = new SqlCommand(SQL_Update,conn);

            SqlParameter[] updatePamrs = GetParmsUpdate;

            foreach(SqlParameter parms1 in updatePamrs)

                myCmd.Parameters.Add(parms1);

            myAdapter.UpdateCommand = myCmd;

 

            myCmd= new SqlCommand(SQL_Insert,conn);

            SqlParameter[] insertParms = GetParmsInsert;

            foreach(SqlParameter parms2 in insertParms)

                myCmd.Parameters.Add(parms2);

            myAdapter.InsertCommand = myCmd;

 

            myCmd = new SqlCommand(SQL_Delete,conn);

            SqlParameter[] deleteParms = GetParmsDelete;

            foreach(SqlParameter parms3 in deleteParms)

                myCmd.Parameters.Add(parms3);

            myAdapter.DeleteCommand = myCmd;

 

            return myAdapter;

        }

 

        #endregion

    }

}

 

BLL層：(事務處理層)

 

public void UpdateData(dsUserData dsChanges)

        {

            IUpdateDataSet dal = new SQLDAL.Common.UpdateDataSet(ActionTable.SYS);

            SqlDataAdapter myDA = GetDataAdapter();

            dal.updateDataSet(dsChanges,"UsersData",myDA);

        }

 

        #region 配制 SqlDataAdapter

 

        private SqlDataAdapter GetDataAdapter()

        {

            string SQL_Select = "Select * from UsersData";

            string SQL_Update = "Update UsersData set UserName = @UserName,UserPassword = @UserPassword,Company=@Company Where UserID = @UserID";

            string SQL_Insert = "Insert UsersData values(@UserID,@UserName,@UserPassword,@Company)";

            string SQL_Delete = "Delete UsersData Where UserID=@UserID";

 

            SqlParameter[] _ParmsUpdate = GetParmsUpdate();

            SqlParameter[] _ParmsInsert = GetParmsInsert();

            SqlParameter[] _ParmsDelete = GetParmsDelete();

 

            UpdateDataSet myUpdate = new UpdateDataSet(ActionTable.SYS);

            SqlDataAdapter _myDA = myUpdate.GetDataAdapter(SQL_Select,SQL_Update,SQL_Insert,SQL_Delete,

                _ParmsUpdate,_ParmsInsert,_ParmsDelete);

           

            return _myDA;

        }

 

        #region SqlParameter 參數

 

        private SqlParameter[] GetParmsUpdate()

        {

            SqlParameter[] _parms1 = new SqlParameter[]

            {

                new SqlParameter("@UserID",SqlDbType.NVarChar,20,"UserID"),

                new SqlParameter("@UserName",SqlDbType.NVarChar,20,"UserName"),

                new SqlParameter("@UserPassword",SqlDbType.NVarChar,50,"UserPassword"),

                new SqlParameter("@Company",SqlDbType.NVarChar,50,"Company")

            };

            return _parms1;

        }

 

        private SqlParameter[] GetParmsInsert()

        {

            SqlParameter[] _parms2 = new SqlParameter[]

            {

                new SqlParameter("@UserID",SqlDbType.NVarChar,20,"UserID"),

                new SqlParameter("@UserName",SqlDbType.NVarChar,20,"UserName"),

                new SqlParameter("@UserPassword",SqlDbType.NVarChar,50,"UserPassword"),

                new SqlParameter("@Company",SqlDbType.NVarChar,50,"Company")

            };

            return _parms2;

        }

 

        private SqlParameter[] GetParmsDelete()

        {

            SqlParameter[] _parms3 = new SqlParameter[]

            {

                new SqlParameter("@UserID",SqlDbType.NVarChar,20,"UserID")

            };

            return _parms3;

        }

 

        #endregion

 

        #endregion