SqlCommand详解以及SqlParameter的两种用法和DataTable基础

最新推荐文章于 2023-04-14 14:27:44 发布

weixin_40333655

最新推荐文章于 2023-04-14 14:27:44 发布

阅读量4k

点赞数 5

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_40333655/article/details/102899546

版权

数据库专栏收录该内容

14 篇文章

订阅专栏

一、SqlCommand介绍

SqlCommand对象允许你指定在数据库上执行的操作的类型。比如，你能够对数据库中的行数据执行select，insert，modify以及delete命令。
1.创建SqlCommand对象
SqlCommand cmd = new SqlCommand(“select email from userinfo”, conn);

2.增加记录
一般使用ExecuteNonQuery（）执行Insert into语句并返回受影响记录的条数。

SqlCommand comd = new SqlCommand();
comd.Connection = conn;
//CommandText允许同时执行多条SQL语句，多条SQL语句使用分号隔开。
comd.CommandText = "insert into userinfo(username,email) values('kate','kate@sample.com');";  //这也不是多条语句啊，难道跟后面结合一起吗？？带分号，不是被视作语句的一部分了吗，那不异常了吗？？
int i = comd.ExecuteNonQuery(); //如果i > 0,则表示SQL执行成功
Console.WriteLine("返回影响记录的条数:{0}",i.ToString());
comd.CommandText = "select IDENT_CURRENT('userinfo')";
//ExecuteScalar()返回执行结果集第一行第一列的值
//如果表定义了自增字段id
//可通过IDENT_CURRENT('表名')返回最后一个记录的id
Console.WriteLine("返回表当前会话userinfo最后一次自增字段的值:{0}",comd.ExecuteScalar().ToString());
comd.CommandText = "select @@IDENTITY";
Console.WriteLine("返回当前会话所有表最后一条记录自增字段id的值:{0}",comd.ExecuteScalar().ToString())

3.修改记录
ExecuteNonQuery方法同样用来更新数据，并返回受影响记录的条数。

SqlCommand comd = new SqlCommand();
comd.Connection = conn;
comd.CommandText = "update userinfo set email='kate@sample.com' where username = 'kate'";
//执行SQL语句并返回受影响记录的条数
//如果i>0,则表示SQL执行成功
int i = comd.ExecuteNonQuery();

4.删除记录
使用ExecuteNonQuery()执行delete语句并返回受影响记录的条数。SQL语句中可使用@符号定义采纳数，使用SqlParameter给参数赋值并加入SqlCommand.Parameters参数列表。

SqlCommand comd = new SqlCommand();
comd.Connection = conn;
//使用@符号定义参数，例如@username
comd.CommandText = "delete from userinfo where username = @username";
//定义一个参数，并给参数指定名称、类型、值
SqlParameter param = new SqlParameter();
param.ParameterName = "@username";
param.SqlDbType = SqlDbType.NVaraChar;
param.Value = "dai";
//添加参数
comd.Parameters.Add(param);
//执行Sql语句并返回受影响记录的条数
//如果i>0,则表示SQL执行成功
int i = comd.ExecuteNonQuery();

5.查询记录
（1）使用SqlDataReader
下面代码演示了使用SqlDataReader逐行读取记录的过程，使用Read()读取下一条记录。SqlDataReader使用完毕后必须及时关闭，否则在同一个SqlConnection上不能再次执行comd.ExecuteReader(),这点很重要。读取记录时一旦遇到异常错误，如果未在finally{}中关闭SqlDataReader,此时程序只能强制退出。使用reader[index].ToString()或reader[fieldName].ToString()获得值时，确保reader[index]或reader[fieldName]的值不是null。

SqlCommand comd = new SqlCommand();
comd.Connection = conn;
comd.CommandText = "select * from userinfo";
SqlDataReader reader = comd.ExecuteReader();
//逐行读取
while(reader.Read())
{
	for(int i = 0;i < reader.FieldCount;i++)
	{
		//按字段索引返回值
		Console.WriteLine(reader[i].ToString());
	}
	//按照字段名取值
	Console.WriteLine("username:{0}",reader["username"].ToString());
}
//必须关闭
reader.Close();

(2)使用SqlDataAdapter
下面代码演示了使用SqlDataAdapter的Fill（）方法向DataTable填充数据的过程，DataTable可直接赋值给DataGridView。相比SqlDataReader，这种方法更简单、更安全。

string sql = "select * from userinfo";
SqlDataAdapter adapter = new SqlDataAdapter(sql,conn);
DataTable dt = new DataTable();
adapter.Fill(dt);
this.dataGridView1.DataSource =dt;

(3)得到单一值
ExecuteScalar()返回查询结果第一行第一列的值。
某些时候你想从数据库中只取一个值，它可能是关于数据集的计数、和、平均值或者其他聚合数值。使用ExecuteReader方法并计算代码中的结果并不是做这些事情的有效方式。最好的选择就是让数据库能够执行并且只返回你所需要的单独的值。下面的示例说明了如何使用ExecuteScalar方法来实现：

SqlCommand cmd = new SqlCommand("select count(*) from userinfo", conn);
  int count = (int)cmd.ExecuteScalar();

总结
SqlCommand对象针对不同的命令而特定的方法。ExecuteReader方法返回SqlDataReader对象来现实查询的结果。对于insert，update以及delete这些SQL命令，使用ExecuteNonQuery方法。如果你只需要查询的单独聚集值，ExecuteScalar方法是最好的选择
二、SqlCommand操作数据库

================================================
下面穿插着将DataTable的基础知识回顾下
DataTable是ADO.NET中重要的对象，是一种内存关闭数据库，它可直接和DataGridView绑定。DataTable提供了强大的查询、计算功能。
1，构造DataTable
（1）可通过DataColumn、DataRow构造DataTable
下面代码演示了通过代码创建DataTable的过程。使用DataColumn为定义列，并设置其列名、数据类型，使用DataRow添加记录。

//创建DataTable
DataTable dt = new DataTable();
//创建列
DataColumn column = new DataColumn();
column.ColumnName = "username";
//设置列数据类型
column.DataType = System.Type.GetType("System.String");
//追加列
dt.Columns.Add(column);
column = new DataColumn();
column.ColumnName = "password";
column.DataType = System.Type.GetType("System.String");
dt.Columns.Add(column);

//按照dt创建新行
DataRow row = dt.NewRow();
//按照列设置单元格的值
row["username"] = "zhangsan";
row["password"] = "123456";
//将行追加至dt中
dt.Rows.Add(row);

row = dt.NewRow();
row["username"] = "lisi";
row["password"] = "123456";
dt.Rows.Add(row);

//绑定数据源，绑定后对dt或dataGridView1任何一个修改，它们的数据将自动同步
this.dataGridView1.DataSource = dt;

(2)通过SqlDataAdapter填充DataTable
通过DataColumn、DataRow构造DataTable非常烦琐，而使用SqlDataAdapter，只需要一条SQL查询语句就可快速构造一个和SQL Server数据表结构及数据一样的DataTable。见上面讲解的SqlDataAdapter使用。

2，获得DataTable中更新的行
DataTable和DataGridView绑定后，通过DataGridView修改记录时，DataTable中的值也会同步变化，那么如何获取DataTable中新增的行、修改的行、删除的行、未修改的行？可以使用DataRowState.Added、DataRowState.Modified、DataRowState.Deleted、DataRowState.Unchanged等行状态来识别，即遍历所有的行，逐行判断。使用foreach获得DataTable中新增的行、修改的行、删除的行、未修改的行等。
在这里插入图片描述

================================================

C# 中用 Sqlparameter 的两种用法
新建一个表：

create table abc
(
id int IDENTITY(1,1) NOT NULL,
name nvarchar(100) ,
sex nvarchar(10)
)
insert into abc values(‘asf','男')
insert into abc values(‘ai','女')

创建表格完成。

新建一个存储过程：

create procedure selbyid
(
@id int,
@thename nvarchar(100) output
)
as
select @thename= name from abc where id=@id

第一种是调用插入操作：

public string connString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;//存储链接字符串，方便资源复用。
public SqlConnection getcon( )
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = connString;
return conn;
}
private void btnsqlparauseing_Click(object sender, EventArgs e)
{
SqlConnection con = getcon();
con.Open();
string sqlstr = "insert into abc values(@name,@sex)"; //免除sql注入攻击
SqlCommand cmd = new SqlCommand( );
cmd.Connection = con;
cmd.CommandText = sqlstr;
SqlParameter para = new SqlParameter(); //声明参数
para= new SqlParameter("@name", SqlDbType.NVarChar,100);//生成一个名字为@Id的参数,必须以@开头表示是添加的参数，并设置其类型长度，类型长度与数据库中对应字段相同，但是不能超出数据库字段大小的范围，否则报错。
para.Value = txtname.Text.ToString().Trim(); //这个是输入参数，所以可以赋值。
cmd.Parameters.Add(para);            //参数增加到cmd中。
para = new SqlParameter("@sex", SqlDbType.NVarChar, 10);
para.Value = txtsex.Text.ToString().Trim();
cmd.Parameters.Add(para);
int i =cmd.ExecuteNonQuery(); //执行sql语句，并且返回影响的行数。
MessageBox.Show(i.ToString() + "命令完成行受影响插入成功", "提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
con.Close();
}

2.使用sqlparameter几种方式来调用存储过程：
1，

private void btnshuchu_Click(object sender, EventArgs e)
{
SqlConnection con = getcon();
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "selbyid"; //存储过程的名称
cmd.CommandType = CommandType.StoredProcedure; //说明是存储过程
SqlParameter para = new SqlParameter();     //声明sqlparameter参数
para = new SqlParameter("@id", SqlDbType.Int); //这个参数是输入参数
para.Value = int.Parse(txtid.Text.ToString().Trim()); //因为是输入参数所以可以赋值
cmd.Parameters.Add(para); //加入cmd中
para=new SqlParameter("@thename",SqlDbType.NVarChar,100);//参数的大小可以小于数据库的参数规定值，但不能够大于数据库的参数大小。
cmd.Parameters.Add(para); //和下面一句不可掉乱，先增加再指明它是输出参数来的。
cmd.Parameters["@thename"].Direction = ParameterDirection.Output; //增加后，用output说明是输出参数。
int i=cmd.ExecuteNonQuery();
string name = cmd.Parameters["@thename"].Value.ToString(); //经过执行，存储过程返回了输出参数。
MessageBox.Show("命令完成 " + name + "是所查记录", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
con.Close();
}

套路就是：输出参数先声明，再赋值，再加入cmd的参数中，最后用cmd.ExecuteNonQuery()执行。
2.用AddWithValue：

private void btnothers_Click(object sender, EventArgs e)
{
SqlConnection con = getcon();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "selbyid";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter para = new SqlParameter();
cmd.Parameters.AddWithValue("@id", Convert.ToInt32(txtid.Text.Trim()));//输入参数可以用addWithValue来格式化参数，但输出参数只能用Add
cmd.Parameters.Add("@thename", SqlDbType.NVarChar,100).Direction = ParameterDirection.Output; //和下面一句不可顺序掉乱，否则会报错，先加入cmd中再指明它是输出参数来的。
con.Open();
int i = cmd.ExecuteNonQuery();
string name = cmd.Parameters["@thename"].Value.ToString(); //输出参数返回一个数值。
MessageBox.Show("命令完成 " + name + "是所查记录", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
con.Close();
}

3.用参数数组实现调用输入和输出参数的存储过程：

private void btnshuzu_Click(object sender, EventArgs e)
{
SqlConnection con = getcon();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "selbyid";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] para = { new SqlParameter("@id", SqlDbType.Int)};
para[0].Value = Convert.ToInt32(txtid.Text.ToString().Trim());
cmd.Parameters.AddRange(para); //输入参数和输出参数分别加入到cmd.Parameter中。
cmd.Parameters.Add("@thename",SqlDbType.NVarChar,100).Direction = ParameterDirection.Output; //和下面一句不可掉乱，先增加再指明它是输出参数来的。   
con.Open();
int i = cmd.ExecuteNonQuery();
string name = cmd.Parameters["@thename"].Value.ToString();
MessageBox.Show("命令完成 " + name + "是所查记录", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
con.Close();
}