sql server 存储过程入参用in

转载

mob64ca140761a4 2024-10-25 09:15:29

文章标签 数据库 xhtml ui System 存储过程 文章分类 SQL Server 数据库

访问数据库：

View Code 

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
///DataBase 的摘要说明
/// </summary>
public class DataBase
{
    //私有变量，数据库连接
    protected SqlConnection Connection;
    protected string ConnectionString;
    //构造函数
    public DataBase()
    {
        ConnectionString = ConfigurationSettings.AppSettings["strCon"];
    }
    //保护方法,打开数据库连接
    private void Open()
    {
        //判断数据库是否连接
        if(Connection == null)
        {
            //不存在，新建并打开
            Connection = new SqlConnection(ConnectionString);
            Connection.Open();
        }
        else
        {
            //存在，判断是否处于关闭状态
            if(Connection.State.Equals(ConnectionState.Closed))
               Connection.Open();//连接处于关闭状态，重新打开
        }
    }
    //公有方法，关闭数据库连接
    public void Close()
    {
        if (Connection.State.Equals(ConnectionState.Open))
        {
            Connection.Close();//连接处于打开状态，关闭连接
        }
    }
    //公有方法，释放资源
    public void Dispose()
    {
        if (Connection != null)
        {
            Connection.Dispose();
            Connection = null;
        }
    }
    //私有方法，获得一个用来调用存储过程的SqlCommand
    //输入：
    //      ProcName - 存储过程名
    //      Params   - 用来调用存储过程的参数表
    public SqlCommand CreatCommand(string ProcName, SqlParameter[] Prams)
    {
        //打开数据库连接
        Open();
        //创建一个命令对象
        SqlCommand Cmd = new SqlCommand(ProcName, Connection);
        //指定命令对象的类型为存储过程
        Cmd.CommandType = CommandType.StoredProcedure;
        // 依次把参数传入命令文本
        if (Prams != null)
        {
            foreach (SqlParameter Parameter in Prams)
                Cmd.Parameters.Add(Parameter);
        }
        return Cmd;
    }
       /// <summary>
    /// 初始化参数值
    /// </summary>
    /// <param name="ParamName">存储过程名称或命令文本</param>
    /// <param name="DbType">参数类型</param>
    /// <param name="Size">参数大小</param>
    /// <param name="Direction">参数方向</param>
    /// <param name="Value">参数值</param>
    /// <returns>新的 parameter 对象</returns>
    public SqlParameter MakeParam(string ParamName,SqlDbType DbType, Int32 Size,ParameterDirection Direction,object Value)
    {
        SqlParameter Param;
        if(Size>0)
            Param = new SqlParameter(ParamName,DbType,Size);
        else 
            Param = new SqlParameter(ParamName,DbType);
        Param.Direction = Direction;
        if(!(Direction == ParameterDirection.Output && Value == null))
            Param.Value = Value;
        return Param;
    }
    //公有方法，实例化一个用于调用存储过程的输入参数
    //输入：
    //     ParamName - 参数名称
    //     DbType   -  参数类型
    //     Size     - 参数大小 
    //     Value     - 值
    public SqlParameter MakeInParam(string ParamName, SqlDbType DbTpye, int Size, object Value)
    {
        return MakeParam(ParamName, DbTpye, Size, ParameterDirection.Input, Value);
    }
    //公有方法，调用存储过程(带参数)
    //输入：
    //      ProcName - 存储过程名
    //      Params   - 用来调用存储过程的参数表
    //输出：
    //      对Update、Insert、Delete操作返回影响到的行数，其他情况为-1
    public int RunProc(string ProcName, SqlParameter[] Params)
    {
        int Count = -1;
        SqlCommand Cmd = CreatCommand(ProcName, Params);
        Count = Cmd.ExecuteNonQuery();
        Close();
        return Count;
    }
    //公有方法，调用存储过程(不带参数)
    //输入：
    //            ProcName存储过程名
    //输出：
    //            将执行结果以DataSet返回  
    public DataSet GetDataSet(string ProcName)
    {
        Open();
        SqlDataAdapter da = new SqlDataAdapter(ProcName, Connection);
        DataSet ds = new DataSet();
        da.Fill(ds);
        Close();
        return ds;
    }

    //公有方法，调用存储过程(带参数)
    //输入：
    //            ProcName - 存储过程名
    //      Params     - 存储过程需要的参数
    //输出：
    //            将执行结果以SqlDataReader返回
    //注意：使用后主意调用SqlDataReader.Close()方法
    public SqlDataReader RunProcGetReader(string ProcName, SqlParameter[] Params)
    {
        SqlCommand Cmd = CreatCommand(ProcName, Params);
        return Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
    }
}

实体类以及增删改查方法：

View Code 

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
///Users 的摘要说明
/// </summary>
public class Users
{
    #region 私有成员
    private string _userID;
    private string _userPwd;
    private string _userName;
    #endregion 私有成员

    #region 属性
    public string UserID
    {
        set
        {
            this._userID = value;
        }
        get
        {
            return this._userID;
        }
    }
    public string UserPwd
    {
        set
        {
            this._userPwd = value;
        }
        get
        {
            return this._userPwd;
        }
    }
    public string UserName
    {
        set
        {
            this._userName = value;
        }
        get
        {
            return this._userName;
        }
    }
    #endregion 属性

    #region 方法
    //向Users表中添加用户信息(采用存储过程)
    //输出：
    //      插入成功：返回True；
    //      插入失败：返回False；
    public bool InsertByProc()
    {
        SqlParameter[] Params = new SqlParameter[3];
        DataBase mydb = new DataBase();
        //用户编号
        Params[0] = mydb.MakeInParam("@UserID", SqlDbType.VarChar, 50, UserID);
        //用户姓名
        Params[1] = mydb.MakeInParam("@UserName", SqlDbType.VarChar, 50, UserName);
        //用户密码
        Params[2] = mydb.MakeInParam("@UserPwd", SqlDbType.VarChar, 64, UserPwd); 
        int Count = -1;
        Count = mydb.RunProc("Proc_UsersAdd", Params);
        if (Count > 0)
            return true;
        else return false;
    }
    //更新用户
    //输入：
    //      XUserID - 用户编号；
    //输出：
    //      更新成功：返回True；
    //      更新失败：返回False；
    public bool UpdateByProc(string XUserID)
    {
        SqlParameter[] Params = new SqlParameter[2];
        DataBase mydb = new DataBase();
        Params[0] = mydb.MakeInParam("@UserID", SqlDbType.VarChar, 50, XUserID);               //用户编号           
        Params[1] = mydb.MakeInParam("@UserName", SqlDbType.VarChar, 50, UserName);
        int Count = -1;
        Count = mydb.RunProc("Proc_UsersModify", Params);
        if (Count > 0)
            return true;
        else return false;//用户姓名 
    }
    //删除用户
    //输入：
    //      XUserID - 用户编号；
    //输出：
    //      删除成功：返回True；
    //      删除失败：返回False；
    public bool DeleteByProc(string XUserID)
    {
        //定义参数类型
        SqlParameter[] Params = new SqlParameter[1];
        DataBase mydb = new DataBase();
        //用户编号
        Params[0] = mydb.MakeInParam("@UserID", SqlDbType.VarChar, 50, XUserID);                         
        int Count = -1;
        Count = mydb.RunProc("Proc_UsersDelete", Params);
        if (Count > 0)
            return true;
        else return false;
    }
    //查询用户
    //查询所用用户
    //不需要参数
    public DataSet QueryUsers()
    {
        DataBase mydb = new DataBase();
        return mydb.GetDataSet("Proc_UsersList");
    }

    //根据UserID判断该用户是否存在
    //输入：
    //      XUserID - 用户编号；        
    //输出：
    //      用户存在：返回True；
    //      用户不在：返回False；
    public bool CheckUser(string XUserID)
    {
        SqlParameter[] Params = new SqlParameter[1];
        DataBase DB = new DataBase();

        Params[0] = DB.MakeInParam("@UserID", SqlDbType.VarChar, 50, XUserID);                    

        SqlDataReader DR = DB.RunProcGetReader("Proc_UsersDetail", Params);
        if (!DR.Read())
        {
            return false;
        }
        else
        {
            return true;
        }
    }
    #endregion 方法
}

前台页面设计：

View Code 

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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>无标题页</title>
    <script type="text/javascript"></script>
    <style type="text/css">

        .style1
        {
            font-size: small;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
     <table border="0" cellpadding="0" cellspacing="0" height="100%" width="100%">
      
    <tr>
      <td style="height:4px;" colspan="2">
         </td>
    </tr>


          <tr>
              <td  style="width: 4px;  background: url(../Images/line.gif) repeat-y;"> 
            </td>   
            <td  valign="top" align="left" width="960px">
           
                          <h4>>>用户管理</h4>
                          
                                        
                       <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                              AllowPaging="True" OnPageIndexChanging="GridView1_PageIndexChanging" 
                              PageSize="8" OnRowCancelingEdit="GridView1_RowCancelingEdit"  OnRowDeleting="GridView1_RowDeleting" 
                              OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating" 
                              DataKeyNames="UserID" Font-Size="13px" Width="58%" SkinID="gvSkin" 
                              CellPadding="4" ForeColor="#333333" GridLines="None">
                           <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                           <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
                    <Columns>    
                    <asp:TemplateField>
                           <ItemTemplate>
                              <asp:CheckBox ID="chkSelected" runat="server" Checked="False" Visible="True" /></ItemTemplate>
                         </asp:TemplateField>
                    <asp:TemplateField HeaderText="ID">
                            <ItemTemplate>
                                <%# Container.DataItemIndex+1 %>
                            </ItemTemplate>
                        </asp:TemplateField>                                        
                        <asp:TemplateField HeaderText="编号">                           
                            <ItemTemplate>
                                <asp:Label ID="Label1" runat="server" Text='<%# Eval("ID") %>'></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="姓名">
                            <EditItemTemplate>
                                <asp:TextBox ID="tbxName" Width="70px" runat="server" Text='<%# Eval("userName") %>'></asp:TextBox>
                            </EditItemTemplate>
                            <ItemTemplate>
                                <asp:Label ID="Label2" runat="server"><%# Eval("userName") %></asp:Label>
                            </ItemTemplate>
                        </asp:TemplateField>
                         
                        <asp:CommandField ShowEditButton="True" HeaderText="编辑" />
                        <asp:CommandField ShowDeleteButton="True" HeaderText="删除" />
                    </Columns>
                           <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
                           <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
                           <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                           <AlternatingRowStyle BackColor="White" />
                </asp:GridView>
                 <asp:Label ID="LabelPageInfo" runat="server" style="font-size: small"></asp:Label>
                <br /><asp:CheckBox ID="chkSelectAll" runat="server" AutoPostBack="true" Text="全选" 
                              Width="54px" OnCheckedChanged="chkSelectAll_CheckedChanged" 
                              style="font-size: small" />
                           <asp:ImageButton ID="ImageButtonDelete" runat="server" 
                              ImageUrl="~/Images/删除}.gif" OnClick="ImageButtonDelete_Click"></asp:ImageButton>
                                  
              
                          <br />
                                  
              
            </td>
        </tr>
       

          <tr>
              <td  style="width: 4px;  background: url(../Images/line.gif) repeat-y;"> 
                   </td>   
            <td  valign="top" align="left" width="960px">
           
          <table cellpadding="0" cellspacing="0" border="1"  
    bordercolor="#cccccc" style="border-collapse:collapse; width: 44%;" 
    frame="below">
              <tr>
                  <td bgcolor="#f6dda4" style="text-align:right;width:100%;" colspan="2">
                      <div class="title" align="left">
                          <h4>
                              添加用户</h4>
                      </div>
                  </td>
              </tr>
              <tr>
                  <td bgcolor="#f6dda4" style="text-align:right;">
                      <span class="style1">编号</span>：</td>
                  <td >
                      <div align="left">
                          <asp:TextBox id="txtUserID"  runat="server" MaxLength="20"></asp:TextBox>
                      </div>
                  </td>
              </tr>
              <tr>
                  <td bgcolor="#f6dda4" style="text-align:right;">
                      <span class="style1">姓名</span>：</td>
                  <td >
                      <div align="left">
                          <asp:TextBox id="txtUserName" runat="server" MaxLength="20"></asp:TextBox>
                      </div>
                  </td>
              </tr>
              <tr>
                  <td bgcolor="#f6dda4" style="text-align:right;">
                      <span class="style1">密码</span>：</td>
                  <td >
                      <div align="left">
                          <asp:TextBox id="txtUserPwd"  runat="server" MaxLength="20" TextMode="Password" 
                            Width="128px"></asp:TextBox>
                      </div>
                  </td>
              </tr>
              <tr>
                  <td>
                       </td>
                  <td valign="top" >
                       
                      <asp:ImageButton ID="imgBtnSave" runat="server" ImageUrl="~/Images/Save.GIF" 
                            OnClick="imgBtnSave_Click" />
                          
                      <asp:ImageButton ID="imgBtnReturn" runat="server" CausesValidation="false" 
                          ImageUrl="~/Images/Return.GIF" οnclick="imgBtnReturn_Click" />
                      <asp:Label ID="lblMessage" runat="server" 
                            ForeColor=red></asp:Label>
                           
                  </td>
              </tr>
          </table>
                                  
              
            </td>
        </tr>
       
    </table>  

    </div>
    </form>
</body>
</html>

后台代码：

View Code 

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GridViewBind();
        }
    }
    public void GridViewBind()
    {
        //创建Users类对象user
        Users user = new Users();
        //使用使用Users类QueryUsers方法查询所有用户信息
        DataSet myds = user.QueryUsers();
        //为GridView控件指定数据源
        GridView1.DataSource = myds;
        //设置控件的主键字段值
        GridView1.DataKeyNames = new string[] { "ID" };
        //从数据库中绑定数据
        GridView1.DataBind();
        LabelPageInfo.Text = "你所在的位置：当前（第" + (GridView1.PageIndex + 1).ToString() + "页 共" + GridView1.PageCount.ToString() + "页）";
    }
    //GridView控件RowDeleting事件
    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        //取出要删除记录的主键值
        string userID = GridView1.DataKeys[e.RowIndex].Values[0].ToString();
        //创建Users类的对象user
        Users user = new Users();
        //根据主键使用DeleteByProc方法删除用户
        if (user.DeleteByProc(userID))
        {
            Response.Write("<script>alert('删除成功！');location='Default.aspx'</script>");   
        }
        else
        {
            Response.Write("<script>alert('删除失败！');location='Default.aspx'</script>");
        }
        GridViewBind();//重新绑定数据
    }
    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        //GridView编辑项索引等于单击行的索引
        GridView1.EditIndex = e.NewEditIndex;
        GridViewBind();
    }
    //GridView控件RowUpdating事件
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        //取出记录的主键值
        string userID = GridView1.DataKeys[e.RowIndex].Values[0].ToString();
        //创建Users类对象user
        Users user = new Users();
        user.UserName = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("tbxName")).Text;
        //使用Users类UpdateByProc方法修改用户信息,该方法中主要是调用Proc_UsersAdd存储过程
        if (user.UpdateByProc(userID))
        {
            //绑定数据库中数据
            GridViewBind();
            //修改成功给予提示
            Response.Write("<script>alert('修改成功！');location='Default.aspx'</script>");
        }
        else
        {
            Response.Write("<script>alert('修改失败！');location='Default.aspx'</script>");
        }
        //取消编辑状态
        GridView1.EditIndex = -1;
        //重新绑定下数据
        GridViewBind();
    }
    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        GridViewBind();
    }
    protected void ImageButtonDelete_Click(object sender, ImageClickEventArgs e)
    {
        for (int i = 0; i < this.GridView1.Rows.Count-1; i++)
        {
            bool isChecked = ((CheckBox)GridView1.Rows[i].FindControl("chkSelected")).Checked;
            if (isChecked)
            {
                string userID = ((Label)GridView1.Rows[i].FindControl("Label1")).Text;
                //创建Users类对象user
                Users user = new Users();
                //根据主键使用DeleteByProc方法删除用户
                if (user.DeleteByProc(userID))
                {
                    Response.Write("<script language=javascript>alert('删除成功!');location='Default.aspx'</script>");
                }
                else
                {
                    Response.Write("<script language=javascript>alert('" + userID + "删除失败!');location='Default.aspx'</script>");
                }
            }
        }
        GridViewBind();
    }
    protected void chkSelectAll_CheckedChanged(object sender, EventArgs e)
    {
        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            ((CheckBox)GridView1.Rows[i].FindControl("chkSelected")).Checked = this.chkSelectAll.Checked;
        }
    }
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        GridViewBind();
    }
    protected void imgBtnSave_Click(object sender, ImageClickEventArgs e)
    {
        if (Page.IsValid)
        {
            Users user = new Users();
            user.UserID = txtUserID.Text.Trim();
            if (!user.CheckUser(user.UserID))
            {
                user.UserName = txtUserName.Text;
                string PwdMD5 = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(txtUserPwd.Text.Trim(), "MD5").ToString();
                user.UserPwd = PwdMD5;
                if (user.InsertByProc())
                {
                    lblMessage.Text = "成功插入该用户信息！";
                    Server.Transfer("Default.aspx");
                }
                else
                {
                    lblMessage.Text = "添加用户失败！";
                }
            }
            else
            {
                lblMessage.Text = "数据库中存在具有该编号的用户，请重新输入！";
            }
        }
    }
    protected void imgBtnReturn_Click(object sender, ImageClickEventArgs e)
    {
        Server.Transfer("Default.aspx");  
    }
}

存储过程：

View Code 

CREATE PROCEDURE [dbo].[Proc_UsersAdd]
    (@UserID         [varchar](50), 
     @UserName         [varchar](50),
     @UserPwd         [varchar](64)
    )
AS INSERT INTO [db2].[dbo].[tb_user] 
     (ID, 
     [userName],
     [userPwd]) 
VALUES 
    ( @UserID,     
     @UserName,
     @UserPwd)


CREATE PROCEDURE [dbo].[Proc_UsersDelete]
    (@UserID        [varchar](50))
AS DELETE [db2].[dbo].[tb_user] 
WHERE 
    ( [ID]     = @UserID)


CREATE PROCEDURE [dbo].[Proc_UsersList]
AS SELECT      [db2].[dbo].[tb_user].[ID],         
         [db2].[dbo].[tb_user].[userName]                            
                        
FROM          [db2].[dbo].[tb_user]


ALTER PROCEDURE [dbo].[Proc_UsersModify]
    (@UserID         [varchar](50),     
     @UserName                  [varchar](50))        
AS UPDATE [db2].[dbo].[tb_user] 

SET          [userName]    = @UserName

         
WHERE 

    ( [ID]     = @UserID)

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。