ADO.NET

最新推荐文章于 2022-10-13 22:15:37 发布

原创最新推荐文章于 2022-10-13 22:15:37 发布 · 218 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了使用C#进行SQL操作的方法，包括插入、查询、获取自增字段值及使用参数化查询防止SQL注入等安全措施。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

//IDsposible
//using(类型 d = new)
//{ ................}

//SqlConnection为建立数据库连接的对象
//using (SqlConnection conn = new SqlConnection(
// "Data Source = . ;Initial Catalog=MyTest;User ID=sa;Password=*********"))
//{

// conn.Open(); // 打开链接
// using (SqlCommand cmd = conn.CreateCommand())
// {
// cmd.CommandText = "Insert into T_Student(Name,Age) values('元芳',25)"; //命令语句
//ExecuteNonQuery()一般用来执行Update，Delete，Insert语句，执行非查询
// cmd.ExecuteNonQuery(); //执行

// }
//}

using (SqlConnection conn = new SqlConnection(
"Data Source = . ;Initial Catalog=MyTest;User ID=sa;Password=lgb314285lgb"))
{

conn.Open(); // 打开链接
using (SqlCommand cmd = conn.CreateCommand())
{
// cmd.CommandText = "select count(*) from T_Student where Age<50";
// //ExecuteScalar 一般执行有且只有一行一列返回值的sql语句
//int i = (int)cmd.ExecuteScalar();
// MessageBox.Show(i + "条数据");

//获得自增字段的值
cmd.CommandText = " insert into T_Student (Name,Age) output inserted.IP values('aadd' , 25);";
//ExecuteScalar 一般执行有且只有一行一列返回值的sql语句
long i = (long)cmd.ExecuteScalar();
MessageBox.Show(i.ToString());

}
}

MessageBox.Show("执行完成");

////////////// ExecuteReader执行查询

using(SqlConnection conn = new SqlConnection ( " Data Source = . ;Initial Catalog=MyTest;User ID=sa;Password=lgb314285lgb") )
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from T_Student where Age > 50";
using ( SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read()) //有查询结果则reader.Read()为true

{
string name = reader.GetString(1); //获得第一列的值
int age = reader.GetInt32(2);
MessageBox.Show(name+","+age);
}
}
}

}

////防SQL注入漏洞参数化

using (SqlConnection conn = new SqlConnection( " Data Source = . ;Initial Catalog=MyTest;User ID=sa;Password=lgb314285lgb"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
//输入'1'or'1'=1 会造成SQL注入漏洞
// cmd.CommandText = "select Age from T_Student where Name = '"+txtName.Text+"'";
cmd.CommandText = "select Age from T_Student where Name =@Name";
//cmd.Parameters.AddWithValue("@Name", txtName.Text);
cmd.Parameters.Add(new SqlParameter("@Name", txtName.Text));

//也可以
//insert into ....values(@Name, @age)
//delete .....where ID = @hhhh
//表格不能参数化

//不能@表名‘，关键字等

using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int age = reader.GetInt32(0);
MessageBox.Show(age.ToString());

}
}

}

}