Question

asp.net中Delete语句应该怎么写？前台和后台的？怎么连上数据库?

我要做一个留言板网页，不会连接数据库，问下前台和后台的代码分别怎么写。知道的帮帮忙啊！！！SQL数据库主键是id，数据库名是[留言板].[dbo].[Table_1]

Answer 1

好多问题。。。
！- -

不要牵涉到前台。。也可以直接在后台中编写。。。

首先当然要写上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");
}
}

Answer 2

http://blog.csdn.net/xqf003