C#操作數(shù)據(jù)庫總結(jié)(vs2005+sql2005)

更新時(shí)間：2012年09月24日 20:10:05 作者：

C#操作數(shù)據(jù)庫總結(jié),每次做項(xiàng)目都會(huì)用到數(shù)據(jù)庫，對數(shù)據(jù)庫的操作都是糊里糊涂從書里找代碼用。通過昨天晚上與今天早上的努力，把數(shù)據(jù)庫的操作整理了一下，下面把整理結(jié)果做個(gè)小結(jié)

開發(fā)工具：Microsoft Visual Studio 2005
數(shù)據(jù)庫:Microsoft SQL Server 2005
說明：這里建立的數(shù)據(jù)庫名為Demo,有一個(gè)學(xué)生表Student,為操作方便起見,我只添加兩個(gè)字段:studentnum和studentname.
一、SQL語句:

復(fù)制代碼代碼如下:

 
--create database Demo 
use Demo 

create table Student 
( 
studentnum char(14) primary key, 
studentname varchar(30) not null 
) 
insert into Student values('20041000010201','張揚(yáng)') 

二、代碼：
1.引入名稱空間:using System.Data.SqlClient;
2.定義連接字符串,連接對象，命令對象:
private String connectionstr;
private SqlConnection connection;
private SqlCommand command;
3.在構(gòu)造函數(shù)中初始化連接字符串，連接對象，命令對象

(1)初始化連接字符串：
方式① connectionstr="server=localhost;uid=sa;pwd=123456;database=Demo";
方式② connectionstr="server=127.0.0.1";Integrade Security=SSPI;database=Demo";
其中，SIMS是我要連接的數(shù)據(jù)庫名.(1)中的uid 和pwd是你登錄數(shù)據(jù)庫的登錄名和密碼
注：這種連接是連接本地的數(shù)據(jù)庫，若要連接局域網(wǎng)內(nèi)其它機(jī)子上的數(shù)據(jù)庫，可將方式①的"server=localhost;"改為"server=數(shù)據(jù)庫所在機(jī)子的IP;"

復(fù)制代碼代碼如下:

 
// 連接字符串：String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=product.mdb"; 
// 建立連接：OleDbConnection connection = new OleDbConnection(connectionString); 
// 使用OleDbCommand類來執(zhí)行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數(shù)據(jù)庫操作\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.操作數(shù)據(jù)庫中的數(shù)據(jù)
(1)查詢數(shù)據(jù)庫中的數(shù)據(jù)
方法一：

復(fù)制代碼代碼如下:

 
string snum=tBstudentnum .Text .Trim (); 
string str = "select * from Student where studentnum='" + snum + "'"; 
command .CommandText =str; 
connection.Open(); 
if (command.ExecuteScalar() == null) 
{ 
MessageBox.Show("您輸入的學(xué)號對應(yīng)的學(xué)生不存在！", "錯(cuò)誤", 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(); 

方法二：

復(fù)制代碼代碼如下:

 
string snum=tBstudentnum .Text .Trim (); 
string str = "select * from Student where studentnum='" + snum + "'"; 
command .CommandText =str; 
connection.Open(); 
if (command.ExecuteScalar() == null) 
{ 
MessageBox.Show("您輸入的學(xué)號對應(yīng)的學(xué)生不存在！", "錯(cuò)誤", 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)向數(shù)據(jù)庫中添加數(shù)據(jù)
方法一:

復(fù)制代碼代碼如下:

 
string snum = tBstudentnum.Text.Trim (); 
string sname = tBstudentname.Text.Trim(); 
if (snum == "" || sname == "") 
{ 
MessageBox.Show("學(xué)生學(xué)號或姓名不能為空！", "錯(cuò)誤", MessageBoxButtons.OK, 
MessageBoxIcon.Error); 
} 
else 
{ 
string insertstr="insert into Student values('"+snum +"','"+sname +"')"; 
command.CommandText = insertstr; 
connection.Open(); 
command.ExecuteNonQuery(); 
MessageBox.Show("學(xué)生添加成功！", "提示", MessageBoxButtons.OK, 
MessageBoxIcon.Information); 
connection.Close(); 
} 

方法二:

復(fù)制代碼代碼如下:

 
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("學(xué)生添加成功！", "提示", MessageBoxButtons.OK, 
MessageBoxIcon.Information); 

(3)修改數(shù)據(jù)庫中的數(shù)據(jù)
方法一:

復(fù)制代碼代碼如下:

 
string snum = tBstudentnum.Text.Trim(); 
string sname = tBstudentname.Text.Trim(); 
if (snum == "" || sname == "") 
{ 
MessageBox.Show("學(xué)生學(xué)號或姓名不能為空！", "錯(cuò)誤", MessageBoxButtons.OK, 
MessageBoxIcon.Error); 
} 
else 
{ 
string modifystr = "update Student set studentname='" + sname + 
"' where studentnum='" + snum + "'"; 
command.CommandText = modifystr; 
connection.Open(); 
command.ExecuteNonQuery(); 
MessageBox.Show("學(xué)生的姓名修改成功！", "提示", MessageBoxButtons.OK, 
MessageBoxIcon.Information ); 
connection.Close(); 

方法二:

復(fù)制代碼代碼如下:

 
string snum = tBstudentnum.Text.Trim(); 
string sname = tBstudentname.Text.Trim(); 
if (snum == "" || sname == "") 
{ 
MessageBox.Show("學(xué)生學(xué)號或姓名不能為空！", "錯(cuò)誤", 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("學(xué)生姓名修改成功！", "提示", MessageBoxButtons.OK, 
MessageBoxIcon.Information); 
} 

(4)刪除數(shù)據(jù)庫中的數(shù)據(jù)
方法一:

復(fù)制代碼代碼如下:

 
string snum = tBstudentnum.Text.Trim(); 
if (snum == "") 
{ 
MessageBox.Show("學(xué)生學(xué)號不能為空！", "錯(cuò)誤", 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("此學(xué)號對應(yīng)的學(xué)生不存在！", "錯(cuò)誤", MessageBoxButtons.OK, MessageBoxIcon.Error); 
} 
else 
{ 
command.CommandText = deletestr; 
command.ExecuteNonQuery(); 
MessageBox.Show("學(xué)生的信息刪除成功！", "提示", MessageBoxButtons.OK, 
MessageBoxIcon.Information); 
} 
connection.Close(); 

方二:

復(fù)制代碼代碼如下:

 
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("學(xué)生信息刪除成功！", "提示", MessageBoxButtons.OK, 
MessageBoxIcon.Information); 
} 
else 
{ 
MessageBox.Show("此學(xué)號對應(yīng)的學(xué)生不存在！", "錯(cuò)誤", MessageBoxButtons.OK, MessageBoxIcon.Error); 
} 