自己做的一道机试题

最新推荐文章于 2020-09-20 08:44:20 发布

gdjlc

最新推荐文章于 2020-09-20 08:44:20 发布

阅读量903

点赞数

分类专栏： ASP.NET 文章标签： dataset database string textbox asp server

本文链接：https://blog.csdn.net/gdjlc/article/details/2516781

版权

ASP.NET 专栏收录该内容

54 篇文章 0 订阅

订阅专栏

两个关联的表的增删查改
主要用了参数 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

    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

    <%@ 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 id="form1" runat="server">  
            姓名：<asp:TextBox ID="txtName" runat="server"></asp:TextBox>  
             <asp:HiddenField runat="server" ID="hfIDEdit" />  
            <br />  
            电话：<asp:TextBox ID="txtPhone" runat="server"></asp:TextBox><br />  
            地址：<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox><br />  
            <asp:Button ID="btnAdd" runat="server" Text="添加" OnClick="btnAdd_Click" />  
            <asp:Label ID="lblMsg" runat="server" Text=""></asp:Label><br />  
            ==================================================================<br />  
            输入姓名：<asp:TextBox ID="txtSearchName" runat="server"></asp:TextBox>  
            <asp:Button ID="btnSearch" runat="server" Text="搜索" OnClick="btnSearch_Click" />         
            <asp:Repeater runat="server" ID="rptUsers" OnItemCommand="rptUsers_ItemCommand" >  
                <HeaderTemplate>  
                    <table width="500" id="mytable" cellspacing="0">  
                        <tr>  
                            <td class="alt"></td>  
                            <td class="alt"> ID</td>  
                            <td class="alt">姓名</td>  
                            <td class="alt"> 电话</td>  
                            <td class="alt">地址</td>  
                            <td class="alt"></td>  
                        </tr>  
                </HeaderTemplate>  
                <ItemTemplate>  
                    <tr>  
                    <td> <asp:CheckBox ID="chkDel" runat="server" /> </td>  
                        <td>  
                            <%#Eval("ID") %>  
                            <asp:HiddenField runat="server" ID="hfID" Value='<%#Eval("ID") %>' />  
                            <asp:HiddenField runat="server" ID="hfName" Value='<%#Eval("Name") %>' />  
                            <asp:HiddenField runat="server" ID="hfPhone" Value='<%#Eval("Phone") %>' />  
                            <asp:HiddenField runat="server" ID="hfAddress" Value='<%#Eval("Address") %>' />  
                        </td>  
                        <td>  
                            <%#Eval("Name") %>  
                        </td>  
                        <td>  
                            <%#Eval("Phone") %>  
                        </td>  
                        <td>  
                            <%#Eval("Address") %>  
                        </td>  
                        <td>  
                          <asp:LinkButton ID="BtnEdit" CommandName="btnEdit" runat="server">编辑</asp:LinkButton>  
                        </td>  
                    </tr>  
                </ItemTemplate>  
                <FooterTemplate>  
                    </table>                                 
                 </FooterTemplate>  
            </asp:Repeater>  
            <input type="checkbox" id="chkall" name="chkall" value="on" οnclick="DoCheck();">全选  
                   <asp:Button runat="server" ID="btnDel" Text="删除" OnClick="btnDel_Click" />    
              
        </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;
        }
    }
}

gdjlc

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
自己做的一道机试题

两个关联的表的增删查改主要用了参数 SqlParameter，事务执行多条sql 表UsersID int Name varchar 表UsersDetailID intUserId int (对应表Users的ID)Phone varcharAddress varcharWeb.Conf
复制链接

扫一扫