当前位置：首页 > 资讯 > 技术文档

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

时间：2020-09-18 05:11 编辑：来源：阅读：
扫一扫，手机访问

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

开发工具：Microsoft Visual Studio 2005 数据库:Microsoft SQL Server 2005 说明：这里建立的数据库名为Demo,有一个学生表Student,为操作方便起见,我只添加两个字段:studentnum和studentname. [b]一、SQL语句:[/b]

[u]复制代码[/u] 代码如下:

--create database Demo
use Demo

create table Student
(
studentnum char(14) primary key,
studentname varchar(30) not null
)
insert into Student values('20041000010201','张扬')

[b]二、代码： [/b]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;"

[u]复制代码[/u] 代码如下:

// 连接字符串：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)查询数据库中的数据方法一：

[u]复制代码[/u] 代码如下:

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

方法二：

[u]复制代码[/u] 代码如下:

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)向数据库中添加数据方法一:

[u]复制代码[/u] 代码如下:

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

方法二:

[u]复制代码[/u] 代码如下:

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)修改数据库中的数据方法一:

[u]复制代码[/u] 代码如下:

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

方法二:

[u]复制代码[/u] 代码如下:

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)删除数据库中的数据方法一:

[u]复制代码[/u] 代码如下:

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

方二:

[u]复制代码[/u] 代码如下:

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

全部评论(0)

上一篇：C#网络爬虫代码分享 C#简单的爬取工具
下一篇：C#中比较常用的DateTime结构的使用方法

资讯排行榜
更多>>