ADO.NET增删改查

 

今天学习了ADO.NET对数据库的基本操作，让我一直纠结的难题，在老师的讲解下都一一明白了，感觉老师讲的很好，通过今天我做作业，让我的收获很大，下面我来分享一下我的练习题吧

<connectionStrings>
  <add name="sqlcnn" connectionString="data source=.;initial catalog=ShuJuKuCaoZuo;integrated security=true;"/>
  
 </connectionStrings>

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

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void Button1_Click(object sender, EventArgs e)//注册
    {
        SqlCommand com=GetCommand();
        com.CommandText = "insert into TLbook(name,password,sex,telephone) values('"+this.TextBox1.Text+"','"+this.TextBox2.Text+"','"+this.TextBox4.Text+"','"+this.TextBox5.Text+"')";
        com.Connection.Open();
        int a = com.ExecuteNonQuery();
        if (a!=0)
        {
            Response.Write("注册成功");
            this.TextBox1.Text = null;
            this.TextBox4.Text = null;
            this.TextBox5.Text = null;
        }
        else
        {
            Response.Write("注册失败");
        }
        com.Connection.Close();
    }
    private static SqlCommand GetCommand( )//封装方法
    {
        string sqlCnn = ConfigurationManager.ConnectionStrings["sqlcnn"].ConnectionString;
        SqlCommand com;
        SqlConnection con = new SqlConnection(sqlCnn);
        com = con.CreateCommand();
        return con.CreateCommand();
    }
    protected void Button4_Click(object sender, EventArgs e)//查询
    {
        SqlCommand com = GetCommand();
        com.CommandText = "select sex,telephone from TLbook where name='"+this.TextBox1.Text+"'";
        com.Connection.Open();
        SqlDataReader reader = com.ExecuteReader();
        if (reader.Read())
        {
            //this.TextBox1.Text = reader.GetString(reader.GetOrdinal("name"));
            this.TextBox4.Text = reader.GetString(reader.GetOrdinal("sex"));
            this.TextBox5.Text = reader["telephone"].ToString();
        }
        else
        {
            this.Response.Write("用户不存在！");
        }
        reader.Close();
        com.Connection.Close();
    }
    protected void Button2_Click(object sender, EventArgs e)//删除
    {
        SqlCommand com = GetCommand();
        com.CommandText = "delete from TLbook where name='"+this.TextBox1.Text+"'";
        com.Connection.Open();
        int a = com.ExecuteNonQuery();
        if (a!=0)
        {
            Response.Write("删除失败");
        }
        else
        {
            Response.Write("删除成功");
        }
        com.Connection.Close();
    }

    protected void Button3_Click(object sender, EventArgs e)//修改
    {
        SqlCommand com = GetCommand();
        com.CommandText = "update TLbook set password=@password,sex=@sex,telephone=@telephone where name=@name";
        SqlParameter param = com.CreateParameter();
        param.ParameterName = "@name";
        param.SqlDbType = System.Data.SqlDbType.NChar;
        param.Size = 10;
        param.Value = this.TextBox1.Text;
        com.Parameters.Add(param);
      
        param = new SqlParameter("@password",System.Data.SqlDbType.NChar,10);
        param.Value = this.TextBox2.Text;
        com.Parameters.Add(param);

        param = new SqlParameter("@sex",System.Data.SqlDbType.Char,2);
        param.Value = this.TextBox4.Text;
        com.Parameters.Add(param);

        param = new SqlParameter("@telephone",System.Data.SqlDbType.Int);
        param.Value = this.TextBox5.Text;
        com.Parameters.Add(param);
        com.Connection.Open();
       
        int a=com.ExecuteNonQuery();
        this.Response.Write("成功");
        if (a != 0)
        {
            this.Response.Write("修改成功");
        }
        else
        {
            this.Response.Write("修改失败");
        }

        com.Connection.Close();
    }
}