利用带参数的命令完成对Users表操作-优快云博客

本文介绍了一个基于ASP.NET的应用程序，实现用户信息的增删改查操作，并通过配置文件和执行代码完成数据库连接和数据操作。

界面：

<%@ 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>
    <style type="text/css">
        .style1
        {
            width: 100%;
        }
        .style2
        {
            text-align: right;
            width: 592px;
        }
        .style3
        {
            color:Red;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        <table class="style1">
            <tr>
                <td class="style2">
                    编号：</td>
                <td>
                    <asp:TextBox ID="txtno" runat="server"></asp:TextBox>
                 <span class="style3">* 查询用户信息时使用</span> </td>
            </tr>
            <tr>
                <td class="style2">
                    姓名：</td>
                <td>
                    <asp:TextBox ID="txtname" runat="server"></asp:TextBox>
                 <span class="style3">*</span> <asp:RequiredFieldValidator
                        ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtname"
                        ErrorMessage="必须填写！" ForeColor="Red" ValidationGroup="1"></asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td class="style2">
                    密码：</td>
                <td>
                    <asp:TextBox ID="password" runat="server" TextMode="Password"></asp:TextBox>
                 <span class="style3">*</span> <asp:RequiredFieldValidator
                        ID="RequiredFieldValidator2" runat="server" ControlToValidate="password"
                        ErrorMessage="必须填写！" ForeColor="Red" ValidationGroup="1"></asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td class="style2">
                    确认密码：</td>
                <td>
                    <asp:TextBox ID="qrpassword" runat="server" TextMode="Password"></asp:TextBox>
                     <span class="style3">*</span> <asp:CompareValidator
                        ID="CompareValidator1" runat="server" ControlToCompare="qrpassword"
                        ControlToValidate="password" ErrorMessage="再次输入密码，确认密码一致！" ForeColor="Red"
                        ValidationGroup="1"></asp:CompareValidator>
                </td>
            </tr>
            <tr>
                <td class="style2">
                     </td>
                <td>
                    <asp:Button ID="btn_add" runat="server" Text="添加" ValidationGroup="1"
                        onclick="btn_add_Click" />
  
                    <asp:Button ID="btn_chaxun" runat="server" Text="查询"
                        onclick="btn_chaxun_Click" />
                </td>
            </tr>
            <tr>
                <td class="style2">
                     </td>
                <td>
                    <asp:Button ID="btn_gengxin" runat="server" Text="更新"
                        onclick="btn_gengxin_Click" />
  
                    <asp:Button ID="btn_delete" runat="server" Text="删除"
                        onclick="btn_delete_Click" style="height: 21px" />
                </td>
            </tr>
        </table>

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

配置文件：

<?xml version="1.0"?>

<configuration>
<connectionStrings>
  <add name="sql" connectionString="data source=.\SQLEXPRESS;initial catalog=bydticket;integrated security=true"/>
  <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
  <compilation debug="true" targetFramework="4.0"/>
  <authentication mode="Forms">
   <forms loginUrl="~/Account/Login.aspx" timeout="2880"/>
  </authentication>
  <membership>
   <providers>
    <clear/>
    <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/"/>
   </providers>
  </membership>
  <profile>
   <providers>
    <clear/>
    <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/"/>
   </providers>
  </profile>
  <roleManager enabled="false">
   <providers>
    <clear/>
    <add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="ApplicationServices" applicationName="/"/>
    <add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/"/>
   </providers>
  </roleManager>
</system.web>
<system.webServer>
  <modules runAllManagedModulesForAllRequests="true"/>
</system.webServer>
</configuration>

执行代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

    }
    private static SqlCommand GetCommand()
    {
        string strCnn = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
        SqlConnection sqlcnn;
        sqlcnn = new SqlConnection(strCnn);
        return sqlcnn.CreateCommand();
    }
    protected void btn_add_Click(object sender, EventArgs e)
    {
        SqlCommand sqlcmm = GetCommand();
        sqlcmm.CommandText = "insert into Users(uid,name,password,power,lock) values(@id,@name,@password,100,1)";
        sqlcmm.Parameters.Add("@id", SqlDbType.VarChar, 6).Value = txtno.Text;
        sqlcmm.Parameters.Add("@name", SqlDbType.NVarChar, 8).Value = txtname.Text;
        sqlcmm.Parameters.Add("@password", SqlDbType.VarChar, 16).Value = Convert.ToInt32(password.Text);

        sqlcmm.Connection.Open();
        int i = sqlcmm.ExecuteNonQuery();
        sqlcmm.Connection.Close();
        Response.Write(i);
    }
    protected void btn_chaxun_Click(object sender, EventArgs e)
    {
        SqlCommand sqlcmm = GetCommand();
        sqlcmm.CommandText = "select * from Users where uid=@uid";
        sqlcmm.Parameters.Add("@uid", SqlDbType.VarChar, 6).Value = txtno.Text;

        sqlcmm.Connection.Open();
        SqlDataReader reader = sqlcmm.ExecuteReader();
        if (reader.Read())
        {
            txtname.Text = reader.GetString(reader.GetOrdinal("name"));
            password.Text = reader["password"].ToString();
        }
        reader.Close();
        sqlcmm.Connection.Close();
        //DataTable dt = new DataTable();
        //SqlDataAdapter adapter = new SqlDataAdapter(sqlcmm);
        //adapter.Fill(dt);
        //sqlcmm.Connection.Close();
        //Response.Write(dt.Rows[0]["uid"].ToString());
        //Response.Write(dt.Rows[0]["password"].ToString());
    }
    protected void btn_gengxin_Click(object sender, EventArgs e)
    {
        SqlCommand sqlcmm = GetCommand();
        sqlcmm.CommandText = "update users set name=@name,password=@pass where uid=@uid";
        SqlParameter param = new SqlParameter();
        param.ParameterName = "@name";
        param.SqlDbType = System.Data.SqlDbType.NVarChar;
        param.Size = 8;
        param.Value = this.txtname.Text;
        sqlcmm.Parameters.Add(param);

        param = new SqlParameter("@pass", System.Data.SqlDbType.VarChar, 16);
        param.Value = password.Text;
        sqlcmm.Parameters.Add(param);

        param = new SqlParameter("@uid", System.Data.SqlDbType.VarChar, 6);
        param.Value = this.txtno.Text;
        sqlcmm.Parameters.Add(param);

        sqlcmm.Connection.Open();
        int i = sqlcmm.ExecuteNonQuery();
        sqlcmm.Connection.Close();
        Response.Write(i);
    }
    protected void btn_delete_Click(object sender, EventArgs e)
    {
        SqlCommand sqlcmm = GetCommand();
        sqlcmm.CommandText = "delete users where uid=@uid";
        sqlcmm.Parameters.Add("@uid", SqlDbType.VarChar, 6).Value = txtno.Text;
        sqlcmm.Connection.Open();
        int i = sqlcmm.ExecuteNonQuery();
        sqlcmm.Connection.Close();
        Response.Write(i);
    }
}