C#操作数据库总结(vs2005+sql2005)

开发工具：microsoft visual studio 2005
数据库:microsoft sql server 2005
说明：这里建立的数据库名为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('20041000010201','张扬')

二、代码：
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;"

// 连接字符串：string connectionstring = "provider=microsoft.jet.oledb.4.0;data source=product.mdb";
// 建立连接：oledbconnection connection = new oledbconnection(connectionstring);
// 使用oledbcommand类来执行sql语句：
// oledbcommand cmd = new oledbcommand(sql, connection);
// connection.open();
// cmd.executenonquery();
#endregion

#region 连接字符串
//string strcon = @"provider=microsoft.jet.oledb.4.0;data source=d:\程序书籍软件\c#程序代码\access数据库操作\addresslist.mdb"; //绝对路径
// string strcon = @"provider=microsoft.jet.oledb.4.0;data source="+environment.currentdirectory+"\\addresslist.mdb"; //相对路径

(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);
}