UpdateDataSet更新資料大全
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