• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号
登陆 注册

ASP.NET两个关联的表的增删查改

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

两个关联的表的增删查改

主要用了参数 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;
        }
    }
}

 

鲜花
握手
雷人
路过
鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
More+
上一篇：
ASP.NET系统+Access数据库发布时间：2022-07-10
下一篇：
AutomaticallyPrintingCrystalReportsinASP.NET发布时间：2022-07-10
热门推荐
More+
热门话题
More+
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服（服务时间 9:00～18:00）

在线QQ客服
地址：深圳市南山区西丽大学城创智工业园
电邮：jeky_zhao#qq.com
移动电话：139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap