用代码连接数据库实现增删改自己总结步骤

原文发布时间为：2008-07-24 —— 来源于本人的百度文章 [由搬家工具导入]

首先当然要写上using System.Data.SqlClient;//这句要写上

1、建立连接字符串如下：

SqlConnection testconn = new SqlConnection("Server=.\\SQLEXPRESS;database=test;uid=sa;pwd=123456");
//SQL服务器为.\\SQLEXPRESS,数据库为test,使用sql认证方式,用户名sa,密码123456

SqlConnection testconn = new SqlConnection("Server=.\\SQLEXPRESS;database=test;Integrated Security = True");

//SQL服务器为.\\SQLEXPRESS,数据库为test,使用windows认证方式

2、写SQL语句字符串

string idsql = "SELECT * FROM stu";
string countsql="SELECT COUNT(*) FROM stu";
string textsql = "SELECT * FROM stu WHERE id=" + DropDownList1.SelectedValue;
string addsql="INSERT INTO stu VALUES('"+TextBox1.Text+"','"+TextBox2.Text+"','"+TextBox3.Text+"')";
string deletsql = "DELETE FROM stu WHERE id=" + TextBox1.Text;
string upsql = "UPDATE stu SET name='" + TextBox2.Text + "'," + "class='" + TextBox3.Text + "' WHERE id=" + TextBox1.Text;

3、添加命令

SqlCommand sid = new SqlCommand(idsql,testconn);
SqlCommand count = new SqlCommand(countsql,testconn);
SqlCommand stext = new SqlCommand(textsql, testconn);
SqlCommand addcmd = new SqlCommand(addsql, testconn);
SqlCommand deletcmd = new SqlCommand(deletsql, testconn);
SqlCommand upcmd = new SqlCommand(upsql, testconn);

4、打开连接

testconn.Open();

5、执行命令

count.ExecuteScalar();//一般增删改的命令执行都用这个吧
addcmd.ExecuteScalar();
deletcmd.ExecuteScalar();
upcmd.ExecuteScalar();

查询读取数据比较麻烦如下：

SqlDataReader testdr = sid.ExecuteReader();//执行查询时需要个dr
testdr.Read();//执行读取

testdr["列名"];//读取一个值

TextBox1.Text = textdr["id"].ToString();
TextBox2.Text = textdr["name"].ToString();
TextBox3.Text = textdr["class"].ToString();

while(testdr.Read())//testdr.Read()读出某列所有数据
{
   DropDownList1.Items.Add(testdr["id"].ToString());//testdr["id"]读出id列的数据
}

textdr.Close();//最后关闭读取

6、关闭连接

testconn.Close();

---------------------------------------------------------------------------------------------------

以下为本人做的完整实例代码：

using System;
using System.Data;
using System.Configuration;
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 _Default : System.Web.UI.Page
{
    static int k;
    //SqlConnection testconn = new SqlConnection("Server=.\\SQLEXPRESS;database=test;uid=sa;pwd=123456");
    SqlConnection testconn = new SqlConnection("Server=.\\SQLEXPRESS;database=test;Integrated Security = True");
    protected void Page_Load(object sender, EventArgs e)
    {
      if (!IsPostBack)
        {
           
            //SqlCommand sid = testconn.CreateCommand();
            //SqlCommand count = testconn.CreateCommand();
            //sid.CommandText = "SELECT * FROM stu";
            //count.CommandText = "SELECT COUNT(*) FROM stu";

            string idsql = "SELECT * FROM stu";
            string countsql="SELECT COUNT(*) FROM stu";
            SqlCommand sid = new SqlCommand(idsql,testconn);
            SqlCommand count = new SqlCommand(countsql,testconn);

            try
            {

                testconn.Open();
                //k = (int)count.ExecuteNonQuery();
                k = (int)count.ExecuteScalar();
                SqlDataReader testdr = sid.ExecuteReader();
                while (testdr.Read())
                {
                    DropDownList1.Items.Add(testdr["id"].ToString());
                }
                testdr.Close();
            }
            catch (Exception ex)
            {
                Response.Write("<scirpt>alert('" + ex.Message.ToString() + "')</script>");
            }
            finally
            {
                testconn.Close();
            }
        }

       
    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
       
        string textsql = "SELECT * FROM stu WHERE id=" + DropDownList1.SelectedValue;
        SqlCommand stext = new SqlCommand(textsql, testconn);
        testconn.Open();
        SqlDataReader textdr = stext.ExecuteReader();
        textdr.Read();
        TextBox1.Text = textdr["id"].ToString();
        TextBox2.Text = textdr["name"].ToString();
        TextBox3.Text = textdr["class"].ToString();

        textdr.Close();
        testconn.Close();

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
      
        string addsql="INSERT INTO stu VALUES('"+TextBox1.Text+"','"+TextBox2.Text+"','"+TextBox3.Text+"')";
        SqlCommand addcmd = new SqlCommand(addsql, testconn);
        testconn.Open();
        addcmd.ExecuteScalar();
        testconn.Close();
        Response.Redirect(".//default.aspx");
       
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        string deletsql = "DELETE FROM stu WHERE id=" + TextBox1.Text;
        SqlCommand deletcmd = new SqlCommand(deletsql, testconn);
        testconn.Open();
        deletcmd.ExecuteScalar();
        testconn.Close();
        Response.Redirect(".//default.aspx");
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        string upsql = "UPDATE stu SET name='" + TextBox2.Text + "'," + "class='" + TextBox3.Text + "' WHERE id=" + TextBox1.Text;
        SqlCommand upcmd = new SqlCommand(upsql, testconn);
        testconn.Open();
        upcmd.ExecuteScalar();
        testconn.Close();
        Response.Redirect(".//default.aspx");
    }
}