一个方便的数据更新器-SqlDataUpdater
/// <summary>
/// 文件名称:SqlDataUpdater.cs
/// 摘 要:SQL数据更新器,可方便对单数据表进行读,写,删操作,构造函数按引用传出结果集.
/// SqlDataUpdater du = new SqlDataUpdater(sqlHere,ref yourDataSet)
/// 调用Update(ds)更新数据,自动事务处理,出错信息存在 ErrorMessage.
///
/// 完成日期:2005-4-21
/// 作 者:魏孙鼎
/// 当前版本:1.0
/// </summary>
public class SqlDataUpdater
{
private SqlConnection cn;
private SqlCommand cmd;
private SqlDataAdapter da;
private SqlTransaction tran;
private SqlCommandBuilder cmdBuilder;
private string err;
public string ErrorMessage
{
get
{
return err;
}
}
/// <summary>
/// 初始化数据更新器
/// </summary>
/// <param name="sql">用于返回schema的SQL</param>
/// <param name="dataSet">按引用传递的数据集</param>
public SqlDataUpdater(string sql,ref DataSet dataSet)
{
err = "";
if (dataSet == null) dataSet = new DataSet();
cn = new SqlConnection(ZMarketData.DataSetting.ConnectionString);
cmd = new SqlCommand(sql,cn);
da = new SqlDataAdapter(cmd);
cmdBuilder = new SqlCommandBuilder(da);
da.Fill(dataSet);
}
public bool Update(DataSet ds)
{
bool success = false;
cn.Open();
tran = cn.BeginTransaction();
cmd.Transaction = tran;
try
{
if(ds.GetChanges() != null)
{
da.Update(ds.GetChanges());
}
success = true;
tran.Commit();
}
catch(Exception ex)
{
tran.Rollback();
err = ex.Message;
success = false;
}
return success;
}
~SqlDataUpdater()
{
da.Dispose();
cmd.Dispose();
cn.Dispose();
}
}
// 应用示例
DataSet ds = new DataSet();
SqlDataUpdater du = new SqlDataUpdater("select * from Public_Feedback where 1=2",ref ds);
DataRow dr = ds.Tables[0].NewRow();
dr["CreateDate"] = DateTime.Now;
dr["FirstName"] = Request["txtName"];
dr["CompanyName"] = Request["txtCompany"];
dr["Email"] = Request["txtEmail"];
ds.Tables[0].Rows.Add(dr);
if (du.Update(ds))
{
//成功
}
else
{
Response.Write(du.ErrorMessage);
}