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

当前位置:

首页 > 编程开发 > 汇编语言 >

C#教程之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);

	}

栏目列表