使用command对象从数据库中对数据:增删改查

web.config:

 <connectionStrings>
   <!-- <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS; 
         AttachDbFilename=|DataDirectory|\mydatabase.mdf;Intefrated Security=True;User Instance=True"/>-->
 <add name="ConnectionString" connectionString="Data Source=LAPTOP-AHBSRNDO;Initial Catalog=stu;Integrated Security=True"/>
  </connectionStrings>

command_query.aspx.cs：

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;
public partial class command_query : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection sqlconn = new SqlConnection(sqlconnstr);
        //建立Commend对象
        SqlCommand sqlcommand = new SqlCommand();
        sqlcommand.Connection = sqlconn;
        sqlconn.Open();
        sqlcommand.CommandText = "select * from student";
        SqlDataReader sqldatareader = sqlcommand.ExecuteReader();
        while (sqldatareader.Read()) {
            Label1.Text += sqldatareader.GetString(0) + "&nbsp;&nbsp";
            Label1.Text += sqldatareader.GetString(1) + "&nbsp;&nbsp";
            Label1.Text += sqldatareader.GetString(2) + "&nbsp;&nbsp";
            Label1.Text += sqldatareader.GetDateTime(3) + "&nbsp;&nbsp";
            Label1.Text += sqldatareader.GetString(4) + "&nbsp;&nbsp";
            Label1.Text += sqldatareader.GetString(5) + "&nbsp;&nbsp";
        };
        sqlcommand = null;
        sqlconn.Close();
        sqlconn = null;

    }
}

command_insert.aspx.cs:

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 command_insert : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection sqlconn = new SqlConnection(sqlconnstr);
        SqlCommand sqlcommand= new SqlCommand();
        sqlcommand.Connection = sqlconn;
        sqlcommand.CommandText = "insert into student(No,Name,Sex,birthday,Address,Photo)values(@No,@Name,@Sex,@birthday,@Address,@Photo)";
        sqlcommand.Parameters.AddWithValue("@No", TextBox1.Text);
        sqlcommand.Parameters.AddWithValue("@Name", TextBox2.Text);
        sqlcommand.Parameters.AddWithValue("@Sex", DropDownList1.Text);
        sqlcommand.Parameters.AddWithValue("@birthday", TextBox3.Text);
        sqlcommand.Parameters.AddWithValue("@Address", TextBox4.Text);
        sqlcommand.Parameters.AddWithValue("@Photo", FileUpload1.FileName);
        try
        {
            sqlconn.Open();
            sqlcommand.ExecuteNonQuery();
            if (FileUpload1.HasFile == true)
            {
                FileUpload1.SaveAs(Server.MapPath(("~/images/") + FileUpload1.FileName));

            }
            Label7.Text = "成功增加记录";
        }
        catch (Exception ex)
        {
            Label7.Text = "错误原因：" + ex.Message;
        }
        finally
        {
            sqlcommand = null;
            sqlconn.Close();
            sqlconn = null;
        }
        }
          }

command_update.aspx.cs:

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.Data;
using System.Configuration;
public partial class command_update : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        int intUpdateCount;
        string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection sqlconn = new SqlConnection(sqlconnstr);
        SqlCommand sqlcommand = new SqlCommand();
        sqlcommand.Connection = sqlconn;
        sqlcommand.CommandText = "update_student";
        sqlcommand.CommandType = CommandType.StoredProcedure;
        sqlcommand.Parameters.AddWithValue("@No", TextBox1.Text);
        sqlcommand.Parameters.AddWithValue("@Name", TextBox2.Text);
        sqlcommand.Parameters.AddWithValue("@Sex", DropDownList1.Text);
        sqlcommand.Parameters.AddWithValue("@birthday", TextBox3.Text);
        sqlcommand.Parameters.AddWithValue("@Address", TextBox4.Text);
        sqlcommand.Parameters.AddWithValue("@Photo", FileUpload1.FileName);

        try
        {
            sqlconn.Open();
            intUpdateCount = sqlcommand.ExecuteNonQuery();
            if (FileUpload1.HasFile == true)
            {
                FileUpload1.SaveAs(Server.MapPath(("~/images/") + FileUpload1.FileName));
            }
            if (intUpdateCount > 0)
                Label7.Text = "成功修改记录";
            else
                Label7.Text = "该记录不存在";
        }
        catch (Exception ex)
        {
            Label1.Text = "错误原因：" + ex.Message;
        }
        finally {
            sqlcommand = null;
            sqlconn.Close();
            sqlconn = null;
        }
        }
    }

command_delete.aspx.cs:

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 command_delete : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        int intDeleteCount;
        string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection sqlconn = new SqlConnection(sqlconnstr);
        SqlCommand sqlcommand = new SqlCommand();
        sqlcommand.Connection = sqlconn;
        sqlcommand.CommandText = "delete from student where no=@no";
        sqlcommand.Parameters.AddWithValue("@no", TextBox1.Text);
        try
        {
            sqlconn.Open();
            intDeleteCount = sqlcommand.ExecuteNonQuery();
            if (intDeleteCount > 0)
                Label2.Text = "成功删除记录";
            else
                Label2.Text = "该记录不存在";
        }
        catch (Exception ex)
        {
            Label2.Text = "错误原因:" + ex.Message;
        }
        finally {
            sqlcommand = null;
            sqlconn.Close();
            sqlconn = null;
        }
    }
}

数据库事务处理：transaction.aspx:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class transction : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        string sqlconnstr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        SqlConnection sqlconn = new SqlConnection(sqlconnstr);
        sqlconn.Open();
        SqlTransaction tran = sqlconn.BeginTransaction();
        SqlCommand sqlcommand = new SqlCommand();
        sqlcommand.Connection = sqlconn;
        sqlcommand.Transaction = tran;
        try
        {
            sqlcommand.CommandText = "update student set Address='beijing' where No=1";
            sqlcommand.ExecuteNonQuery();
            //sqlcommand.CommandText = "update student set Address='zhengzhou' where No=2";
            sqlcommand.ExecuteNonQuery();
            tran.Commit();
            Label1.Text = "事务提交成功";
        }
        catch (Exception ex)
        {
            tran.Rollback();
            Label1.Text = "事务提交失败:" + ex.Message;

        }
        finally {
            sqlcommand = null;
            sqlconn.Close();
            sqlconn = null;
        }
    }
}

---