開(kāi)發(fā)工具：Microsoft Visual Studio 2005
數(shù)據(jù)庫(kù):Microsoft SQL Server 2005
說(shuō)明：這里建立的數(shù)據(jù)庫(kù)名為Demo,有一個(gè)學(xué)生表Student,為操作方便起見(jiàn),我只添加兩個(gè)字段:studentnum和studentname.
一、SQL語(yǔ)句:
--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.定義連接字符串,連接對(duì)象，命令對(duì)象:
private String connectionstr;
private SqlConnection connection;
private SqlCommand command;
3.在構(gòu)造函數(shù)中初始化連接字符串，連接對(duì)象，命令對(duì)象
(1)初始化連接字符串：
方式① connectionstr="server=localhost;uid=sa;pwd=123456;database=Demo";
方式② connectionstr="server=127.0.0.1";Integrade Security=SSPI;database=Demo";
其中，SIMS是我要連接的數(shù)據(jù)庫(kù)名.(1)中的uid 和pwd是你登錄數(shù)據(jù)庫(kù)的登錄名和密碼
注：這種連接是連接本地的數(shù)據(jù)庫(kù)，若要連接局域網(wǎng)內(nèi)其它機(jī)子上的數(shù)據(jù)庫(kù)，可將方式①的"server=localhost;"改為"server=數(shù)據(jù)庫(kù)所在機(jī)子的IP;"
(2)初始化連接對(duì)象
connection = new SqlConnection(connectionstr);
(3)初始化命令對(duì)象
command =new SqlCommand();
command .Connection =connection ;
4.操作數(shù)據(jù)庫(kù)中的數(shù)據(jù)
(1)查詢數(shù)據(jù)庫(kù)中的數(shù)據(jù)
方法一：
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é)號(hào)對(duì)應(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();
方法二：
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é)號(hào)對(duì)應(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ù)庫(kù)中添加數(shù)據(jù)
方法一:
string snum = tBstudentnum.Text.Trim ();
string sname = tBstudentname.Text.Trim();
if (snum == "" || sname == "")
{
MessageBox.Show("學(xué)生學(xué)號(hào)或姓名不能為空！", "錯(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();
}
方法二:
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ù)庫(kù)中的數(shù)據(jù)
方法一:
string snum = tBstudentnum.Text.Trim();
string sname = tBstudentname.Text.Trim();
if (snum == "" || sname == "")
{
MessageBox.Show("學(xué)生學(xué)號(hào)或姓名不能為空！", "錯(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();
方法二:
string snum = tBstudentnum.Text.Trim();
string sname = tBstudentname.Text.Trim();
if (snum == "" || sname == "")
{
MessageBox.Show("學(xué)生學(xué)號(hào)或姓名不能為空！", "錯(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ù)庫(kù)中的數(shù)據(jù)
方法一:
string snum = tBstudentnum.Text.Trim();
if (snum == "")
{
MessageBox.Show("學(xué)生學(xué)號(hào)不能為空！", "錯(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é)號(hào)對(duì)應(yīng)的學(xué)生不存在！", "錯(cuò)誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
command.CommandText = deletestr;
command.ExecuteNonQuery();
MessageBox.Show("學(xué)生的信息刪除成功！", "提示", 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("學(xué)生信息刪除成功！", "提示", MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
else
{
MessageBox.Show("此學(xué)號(hào)對(duì)應(yīng)的學(xué)生不存在！", "錯(cuò)誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
注：以上對(duì)數(shù)據(jù)庫(kù)的操作,大都從操作角度出發(fā)僅進(jìn)行單一的操作,有的地方并未進(jìn)行錯(cuò)誤處理,如修改學(xué)生信息時(shí),學(xué)號(hào)應(yīng)不可能編輯等.
