两个关联的表的增删查改
主要用了参数 SqlParameter,事务执行多条sql
表Users
ID int
Name varchar
表UsersDetail
ID int
UserId int (对应表Users的ID)
Phone varchar
Address varchar
----------------------------------------------------------------------------------------------
Web.Config
<connectionStrings> <add name="ConnectionString" connectionString="Data Source=local;Initial Catalog=Test;User ID=sa;Password=" providerName="System.Data.SqlClient" /> </connectionStrings>
----------------------------------------------------------------------------------------------
AppCode/DataBase.cs
view plaincopy to clipboardprint? using System; using System.Collections; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration; /// <summary> /// 用于数据访问的类 /// </summary> public class DataBase:IDisposable { protected SqlConnection Connection; protected String ConnectionString; public DataBase() { ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; } ~DataBase() { try { if (Connection != null) Connection.Close(); } catch { } try { Dispose(); } catch { } } protected void Open() { if (Connection == null) { Connection = new SqlConnection(ConnectionString); } if (Connection.State.Equals(ConnectionState.Closed)) { Connection.Open(); } } public void Close() { if (Connection != null) Connection.Close(); } public void Dispose() { if (Connection != null) { Connection.Dispose(); Connection = null; } } public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, object Value) { SqlParameter Param; if (Size > 0) Param = new SqlParameter(ParamName, DbType, Size); else Param = new SqlParameter(ParamName, DbType); if (Value != null) Param.Value = Value; return Param; } public DataSet GetDataSet(String SqlString, SqlParameter[] param) { Open(); SqlCommand cmd = new SqlCommand(SqlString, Connection); if (param != null) { cmd.Parameters.AddRange(param); } SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd; DataSet dataset = new DataSet(); adapter.Fill(dataset); Close(); return dataset; } public DataTable GetDataTable(String SqlString, SqlParameter[] param) { DataSet dataset = GetDataSet(SqlString, param); dataset.CaseSensitive = false; return dataset.Tables[0]; } public int ExecuteSQL(string SqlString, SqlParameter[] param) { Open(); try { SqlCommand cmd = new SqlCommand(SqlString, Connection); if (param != null) { cmd.Parameters.AddRange(param); } return cmd.ExecuteNonQuery(); } catch (Exception e) { throw e; } finally { Close(); } } public int ExecuteSQL(String[] SqlStrings, SqlParameter[][] param) { int count = -1; Open(); SqlCommand cmd = new SqlCommand(); SqlTransaction trans = Connection.BeginTransaction(); cmd.Connection = Connection; cmd.Transaction = trans; try { int i = 0; foreach (String str in SqlStrings) { cmd.CommandText = str; cmd.Parameters.AddRange(param[i]); count = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); i++; } trans.Commit(); } catch { trans.Rollback(); count = -1; } finally { Close(); } return count; } public SqlDataReader ExecuteReader(String SqlString, SqlParameter[] param) { Open(); try { SqlCommand cmd = new SqlCommand(SqlString, Connection); if (param != null) { cmd.Parameters.AddRange(param); } SqlDataReader myReader = cmd.ExecuteReader(); return myReader; } catch (Exception e) { throw e; } } public int ExecuteScalar(string SqlString, SqlParameter[] param) { Open(); try { SqlCommand cmd = new SqlCommand(SqlString, Connection); if (param != null) { cmd.Parameters.AddRange(param); } object o = cmd.ExecuteScalar(); return int.Parse(o.ToString()); } catch (Exception e) { throw e; } } } using System; using System.Collections; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration; /// <summary> /// 用于数据访问的类 /// </summary> public class DataBase:IDisposable { protected SqlConnection Connection; protected String ConnectionString; public DataBase() { ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; } ~DataBase() { try { if (Connection != null) Connection.Close(); } catch { } try { Dispose(); } catch { } } protected void Open() { if (Connection == null) { Connection = new SqlConnection(ConnectionString); } if (Connection.State.Equals(ConnectionState.Closed)) { Connection.Open(); } } public void Close() { if (Connection != null) Connection.Close(); } public void Dispose() { if (Connection != null) { Connection.Dispose(); Connection = null; } } public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, object Value) { SqlParameter Param; if (Size > 0) Param = new SqlParameter(ParamName, DbType, Size); else Param = new SqlParameter(ParamName, DbType); if (Value != null) Param.Value = Value; return Param; } public DataSet GetDataSet(String SqlString, SqlParameter[] param) { Open(); SqlCommand cmd = new SqlCommand(SqlString, Connection); if (param != null) { cmd.Parameters.AddRange(param); } SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd; DataSet dataset = new DataSet(); adapter.Fill(dataset); Close(); return dataset; } public DataTable GetDataTable(String SqlString, SqlParameter[] param) { DataSet dataset = GetDataSet(SqlString, param); dataset.CaseSensitive = false; return dataset.Tables[0]; } public int ExecuteSQL(string SqlString, SqlParameter[] param) { Open(); try { SqlCommand cmd = new SqlCommand(SqlString, Connection); if (param != null) { cmd.Parameters.AddRange(param); } return cmd.ExecuteNonQuery(); } catch (Exception e) { throw e; } finally { Close(); } } public int ExecuteSQL(String[] SqlStrings, SqlParameter[][] param) { int count = -1; Open(); SqlCommand cmd = new SqlCommand(); SqlTransaction trans = Connection.BeginTransaction(); cmd.Connection = Connection; cmd.Transaction = trans; try { int i = 0; foreach (String str in SqlStrings) { cmd.CommandText = str; cmd.Parameters.AddRange(param[i]); count = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); i++; } trans.Commit(); } catch { trans.Rollback(); count = -1; } finally { Close(); } return count; } public SqlDataReader ExecuteReader(String SqlString, SqlParameter[] param) { Open(); try { SqlCommand cmd = new SqlCommand(SqlString, Connection); if (param != null) { cmd.Parameters.AddRange(param); } SqlDataReader myReader = cmd.ExecuteReader(); return myReader; } catch (Exception e) { throw e; } } public int ExecuteScalar(string SqlString, SqlParameter[] param) { Open(); try { SqlCommand cmd = new SqlCommand(SqlString, Connection); if (param != null) { cmd.Parameters.AddRange(param); } object o = cmd.ExecuteScalar(); return int.Parse(o.ToString()); } catch (Exception e) { throw e; } } }
----------------------------------------------------------------------------------------------
Default3.aspx
view plaincopy to clipboardprint? <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Untitled Page</title> <mce:script type="text/javascript" language="javascript"><!-- function DoCheck() { var inputs = document.getElementById("mytable").getElementsByTagName("input"); for (var i=0; i < inputs.length; i++) if (inputs[i].type == 'checkbox') { inputs[i].checked = document.getElementById("chkall").checked; } } // --></mce:script> <mce:style type="text/css"><!-- #mytable { padding: 0; margin: 0; border-collapse:collapse;} td { border: 1px solid #C1DAD7; background: #fff; font-size:12px; padding: 6px 6px 6px 12px; color: #4f6b72;} td.alt { background: #F5FAFA; color: #797268;} --></mce:style><style type="text/css" mce_bogus="1"> #mytable { padding: 0; margin: 0; border-collapse:collapse;} td { border: 1px solid #C1DAD7; background: #fff; font-size:12px; padding: 6px 6px 6px 12px; color: #4f6b72;} td.alt { background: #F5FAFA; color: #797268;} </style> </head> <body> <form /> </form> </body> </html>
----------------------------------------------------------------------------------------------
Default3.aspx.cs
using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient;
public partial class Default3 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { btnDel.Attributes.Add("onclick", "return confirm('确定进行删除操作吗?');"); BindGV(); } }
void BindGV() { DataBase db = new DataBase(); DataSet ds = db.GetDataSet("select * from Users a inner join UsersDetail b on a.ID=b.UserId", null); rptUsers.DataSource = ds; rptUsers.DataBind(); }
protected void btnAdd_Click(object sender, EventArgs e) { DataBase db = new DataBase(); if (btnAdd.Text == "添加") { SqlParameter[] Params = new SqlParameter[1]; Params[0] = db.MakeParam("@Name", SqlDbType.VarChar, 20, txtName.Text); string sql = "insert into Users(Name) values(@Name);select @@identity;"; int UserId = db.ExecuteScalar(sql, Params);
SqlParameter[] Params2 = new SqlParameter[3]; Params2[0] = db.MakeParam("@UserId", SqlDbType.Int, 4, UserId); Params2[1] = db.MakeParam("@Phone", SqlDbType.VarChar, 20, txtPhone.Text); Params2[2] = db.MakeParam("@Address", SqlDbType.VarChar, 50, txtAddress.Text); string sql2 = "insert into UsersDetail(UserId,Phone,Address) values(@UserId,@Phone,@Address)"; if (db.ExecuteSQL(sql2, Params2) > 0) { lblMsg.Text = "信息添加成功!"; BindGV(); } else { lblMsg.Text = "信息添加失败!"; } } else { SqlParameter[][] Params = new SqlParameter[2][]; SqlParameter[] Params1 = new SqlParameter[2]; Params1[0] = db.MakeParam("@ID", SqlDbType.Int, 4, int.Parse(hfIDEdit.Value)); Params1[1] = db.MakeParam("@Name", SqlDbType.VarChar, 20, txtName.Text); SqlParameter[] Params2 = new SqlParameter[3]; Params2[0] = db.MakeParam("@UserId", SqlDbType.Int, 4, int.Parse(hfIDEdit.Value)); Params2[1] = db.MakeParam("@Phone", SqlDbType.VarChar, 20, txtPhone.Text); Params2[2] = db.MakeParam("@Address", SqlDbType.VarChar, 50, txtAddress.Text); Params[0] = Params1; Params[1] = Params2;
string sql1 = "update Users set Name=@Name where ID=@ID"; string sql2 = "update UsersDetail set Phone=@Phone,Address=@Address where UserId=@UserId"; string[] sql = { sql1, sql2 };
if (db.ExecuteSQL(sql, Params) > 0) { lblMsg.Text = "信息修改成功!"; txtName.Text = txtPhone.Text = txtAddress.Text = ""; btnAdd.Text = "添加"; BindGV(); } else { lblMsg.Text = "信息修改失败!"; } } } protected void btnSearch_Click(object sender, EventArgs e) { DataBase db = new DataBase(); SqlParameter[] Params = new SqlParameter[1]; Params[0] = db.MakeParam("@Name", SqlDbType.VarChar, 20, txtSearchName.Text); string sql = "select * from Users a inner join UsersDetail b on a.ID=b.UserId where a.Name=@Name"; DataSet ds = db.GetDataSet(sql, Params); rptUsers.DataSource = ds; rptUsers.DataBind(); } protected void btnDel_Click(object sender, EventArgs e) { DataBase db = new DataBase(); int num = 0; for (int i = 0; i < rptUsers.Items.Count; i++) { HiddenField hfID = (HiddenField)rptUsers.Items[i].FindControl("hfID"); CheckBox chkDel = (CheckBox)rptUsers.Items[i].FindControl("chkDel"); if (chkDel.Checked) { num++;
SqlParameter[][] Params = new SqlParameter[2][]; SqlParameter[] Params1 = new SqlParameter[1]; Params1[0] = db.MakeParam("@ID", SqlDbType.Int, 4, int.Parse(hfID.Value)); SqlParameter[] Params2 = new SqlParameter[1]; Params2[0] = db.MakeParam("@UserId", SqlDbType.Int, 4, int.Parse(hfID.Value)); Params[0] = Params1; Params[1] = Params2;
string sql1 = "delete from Users where ID=@ID"; string sql2 = "delete from UsersDetail where UserId=@UserId"; string[] sql = { sql1, sql2 };
db.ExecuteSQL(sql, Params); } } if (num > 0) { BindGV(); this.ClientScript.RegisterClientScriptBlock(this.GetType(), "script", "alert('成功删除了!" + num + "条数据');", true); } else { this.ClientScript.RegisterClientScriptBlock(this.GetType(), "script", "alert('没有选择数据!');", true); } } protected void rptUsers_ItemCommand(object source, RepeaterCommandEventArgs e) { HiddenField hfID = (HiddenField)e.Item.FindControl("hfID"); HiddenField hfName = (HiddenField)e.Item.FindControl("hfName"); HiddenField hfPhone = (HiddenField)e.Item.FindControl("hfPhone"); HiddenField hfAddress = (HiddenField)e.Item.FindControl("hfAddress"); switch (e.CommandName) { case "btnEdit": btnAdd.Text = "编辑"; hfIDEdit.Value = hfID.Value; txtName.Text = hfName.Value; txtPhone.Text = hfPhone.Value; txtAddress.Text = hfAddress.Value; break; } } }
|
请发表评论