asp操作数据库总结

‘--------------------------------------------------------------------------------------------Conn.asp

Dim conn; set conn = server.createobject("adodb.connection")

conn.open = "provider=microsoft.jet.oledb.4.0;data source = " & server.mappath("data_\sys.mdb")

‘------------------------------------法1----查询、插入、更新、删除---------------------------------- set rs=conn.execute("Select * From articles where txtid='1'") ’无需创建rs是记录集(下同) set sav=conn.execute("insert into wen(title,body,sort)values('第三篇标题','内容暂无','科技')") set sav=conn.execute("update wen set body='内容' where title='第二篇标题'")

set sav=conn.execute("delete * from wen where title='第三篇标题' and body='内容暂无'") set sav=nothing

‘----------------------------------法2----查询------------------------------------

sql="select * from articles where txtid='1'”

set rs = server.createobject("ADODB.recordset")

rs.open sql,conn,1

‘----------------------------------------法2----插入-------------------------------

sql="Select * from articles"

set rs=server.CreateObject("adodb.recordSet")

rs.open sql,conn,1,3

rs.addnew

rs("class_name") = tclass

rs("txt_path") = tpath

rs.update

rs.close

‘------------------------------------法2—修改-------------------------------------

sql="Select * From articles where txtid=’2’"

set rs = server.createobject("ADODB.recordset")

rs.open sql,conn,3,3

rs("last_click")=now()

rs("clicks") = rs("clicks") + 1

rs.update

rs.close

‘--------------------------------------------法2—删除----------------------------

sql="select * from news where id="&request.form("id")

set rs=server.createobject("adodb.recordset")

rs.open sql,conn,1,3

rs.delete

rs.update

rs.close

‘---------------------------------------------------注释------------------------------------------------ RS.OPEN SQL,CONN,A,B

A: 0=只读,且当前数据记录只能向下移动 ；1=只读,当前数据记录可自由移动 ；2=可读写,当前数据记录可自由移动 ；3=可读写,当前数据记录可自由移动,可看到新增记录 B: 1=缺省锁定类型，记录集是只读的，不能修改记录 ；2=悲观锁定，当修改记录时，

数据提供者将尝试锁定记录以确保成功地编辑记录，只要编辑一开始，则立即锁住记录。 ；3=乐观锁定 ，直到用Update方法提交更新记录时才锁定记录 ；4=批量乐观锁定，允许修改多个记录，只有调用UpdateBatch方法后才锁定记录。 提示：当不需要改动任何记录时，应该使用只读的记录集，这样提供者不用做任何检测。对于一般的使用，乐观的锁定可能是最好的选择，因为记录只被锁定一小段时间，数据在这段时间被更新。这减少了资源的使用。

 

第二篇：C#操作数据库总结

发工具：Microsoft Visual Studio 20xx

数据库:Microsoft SQL Server 20xx

说明：这里建立的数据库名为Demo,有一个学生表Student,为操作方便起见,我只添加两个字

段:studentnum和studentname.

一、SQL语句:

--create database Demo

use Demo

create table Student

(

studentnum char(14) primary key,

studentname varchar(30) not null

)

insert into Student values('20xx1000010201','张扬')

二、代码：

1.引入名称空间:using System.Data.SqlClient;

2.定义连接字符串,连接对象，命令对象:

private String connectionstr;

private SqlConnection connection;

private SqlCommand command;

3.在构造函数中初始化连接字符串，连接对象，命令对象

(1)初始化连接字符串：

方式① connectionstr="server=localhost;uid=sa;pwd=123456;database=Demo";

方式② connectionstr="server=127.0.0.1";Integrade Security=SSPI;database=Demo";

其中，SIMS是我要连接的数据库名.(1)中的uid 和pwd是你登录数据库的登录名和密码

注：这种连接是连接本地的数据库，若要连接局域网内其它机子上的数据库，可将方式

①的"server=localhost;"改为"server=数据库所在机子的IP;"

(2)初始化连接对象

connection = new SqlConnection(connectionstr);

(3)初始化命令对象

command =new SqlCommand();

command .Connection =connection ;

4.操作数据库中的数据

(1)查询数据库中的数据

方法一：

string snum=tBstudentnum .Text .Trim ();

string str = "select * from Student where studentnum='" + snum + "'";

command .CommandText =str;

connection.Open();

if (command.ExecuteScalar() == null)

{

MessageBox.Show("您输入的学号对应的学生不存在！", "错误",

MessageBoxButtons.OK,MessageBoxIcon.Error);

}

else

{

SqlDataReader sdr = command.ExecuteReader();

while (sdr.Read())

{

tBstudentnum .Text = sdr["studentnum"].ToString();

tBstudentname.Text = sdr["studentname"].ToString();

}

sdr.Close();

}

connection.Close();

方法二：

string snum=tBstudentnum .Text .Trim ();

string str = "select * from Student where studentnum='" + snum + "'";

command .CommandText =str;

connection.Open();

if (command.ExecuteScalar() == null)

{

MessageBox.Show("您输入的学号对应的学生不存在！", "错误", MessageBoxButtons.OK,MessageBoxIcon.Error);

}

else

{

SqlDataAdapter sda = new SqlDataAdapter(str,connection );

DataSet ds = new DataSet();

sda.Fill(ds, "Student");

DataTable dt = ds.Tables["Student"];

tBstudentnum.Text = dt.Rows[0]["studentnum"].ToString();

tBstudentname.Text = dt.Rows[0]["studentname"].ToString();

}

connection.Close();

(2)向数据库中添加数据

方法一:

string snum = tBstudentnum.Text.Trim ();

string sname = tBstudentname.Text.Trim();

if (snum == "" || sname == "")

{

MessageBox.Show("学生学号或姓名不能为空！", "错误",

MessageBoxButtons.OK,

MessageBoxIcon.Error);

}

else

{

string insertstr="insert into Student values('"+snum +"','"+sname +"')";

command.CommandText = insertstr;

connection.Open();

command.ExecuteNonQuery();

MessageBox.Show("学生添加成功！", "提示", MessageBoxButtons.OK,

MessageBoxIcon.Information);

connection.Close();

}

方法二:

string str = "select * from Student";

string insertstr = "insert into Student values('" + snum + "','" + sname + "')";

SqlDataAdapter sda = new SqlDataAdapter(str, connection);

DataSet ds = new DataSet();

sda.Fill(ds, "Student");

DataTable dt = ds.Tables["Student"];

DataRow dr = dt.NewRow();

dr["studentnum"] = snum;

dr["studentname"] = sname;

dt.Rows.Add(dr);

sda.InsertCommand = new SqlCommand(insertstr, connection);

sda.Update(ds, "Student");

MessageBox.Show("学生添加成功！", "提示", MessageBoxButtons.OK,

MessageBoxIcon.Information);

(3)修改数据库中的数据

方法一:

string snum = tBstudentnum.Text.Trim();

string sname = tBstudentname.Text.Trim();

if (snum == "" || sname == "")

{

MessageBox.Show("学生学号或姓名不能为空！", "错误",

MessageBoxButtons.OK,

MessageBoxIcon.Error);

}

else

{

string modifystr = "update Student set studentname='" + sname +

"' where studentnum='" + snum + "'";

command.CommandText = modifystr;

connection.Open();

command.ExecuteNonQuery();

MessageBox.Show("学生的姓名修改成功！", "提示",

MessageBoxButtons.OK,

MessageBoxIcon.Information );

connection.Close();

方法二:

string snum = tBstudentnum.Text.Trim();

string sname = tBstudentname.Text.Trim();

if (snum == "" || sname == "")

{

MessageBox.Show("学生学号或姓名不能为空！", "错误",

MessageBoxButtons.OK,

MessageBoxIcon.Error);

}

else

{

string str = "select * from Student where studentnum='" + snum + "'"; ;

string updatestr = "update Student set studentname='" + sname +

"' where studentnum='" + snum + "'";

SqlDataAdapter sda = new SqlDataAdapter(str, connection);

DataSet ds = new DataSet();

sda.Fill(ds, "Student");

DataTable dt = ds.Tables["Student"];

dt.Rows[0]["studentname"] = sname;

sda.UpdateCommand = new SqlCommand(updatestr , connection);

sda.Update(ds, "Student");

MessageBox.Show("学生姓名修改成功！", "提示", MessageBoxButtons.OK,

MessageBoxIcon.Information);

}

(4)删除数据库中的数据

方法一:

string snum = tBstudentnum.Text.Trim();

if (snum == "")

{

MessageBox.Show("学生学号不能为空！", "错误", MessageBoxButtons.OK,

MessageBoxIcon.Error);

}

else

{

string str = "select * from Student where studentnum='" + snum + "'";

string deletestr = "delete from Student where studentnum='" + snum + "'";

command.CommandText =str ;

connection.Open();

if (command.ExecuteScalar() == null)

{

MessageBox.Show("此学号对应的学生不存在！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

else

{

command.CommandText = deletestr;

command.ExecuteNonQuery();

MessageBox.Show("学生的信息删除成功！", "提示",

MessageBoxButtons.OK,

MessageBoxIcon.Information);

}

connection.Close();

方二:

string str = "select * from Student where studentnum='" + snum + "'";

string deletestr = "delete from Student where studentnum='" + snum + "'";

SqlDataAdapter sda = new SqlDataAdapter(str, connection);

DataSet ds = new DataSet();

sda.Fill(ds, "Student");

DataTable dt = ds.Tables["Student"];

if (dt.Rows.Count > 0)

{

dt.Rows[0].Delete();

sda.DeleteCommand = new SqlCommand(deletestr, connection);

sda.Update(ds, "Student");

MessageBox.Show("学生信息删除成功！", "提示",

MessageBoxButtons.OK,

MessageBoxIcon.Information);

}

else

{

MessageBox.Show("此学号对应的学生不存在！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);

}