一般来说,在更新DataTable或是DataSet时,如果不采用SqlParameter,那么当输入的Sql语句出现歧义时,如字符串中含有单引号,程序就会发生错误,并且他人可以轻易地通过拼接Sql语句来进行注入攻击。
sqlhelper里:
AddRange方法
public static string connstr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
//查询语句
public static DataTable ExcuteTable(string sql, params SqlParameter[] sqlParameters)
{
using (SqlDataAdapter sda = new SqlDataAdapter(sql, connstr))
{
if (sqlParameters != null && sqlParameters.Count() > 0)
sda.SelectCommand.Parameters.AddRange(sqlParameters);
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
}
//增删改语句
public static int ExcuteNonQuery(string sql, params SqlParameter[] sqlParameters)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
using (SqlCommand comm = new SqlCommand(sql, conn))
{
if (sqlParameters != null && sqlParameters.Count() > 0)
comm.Parameters.AddRange(sqlParameters);
conn.Open();
return comm.ExecuteNonQuery();
}
}
}
Add方法
public static Userinfo Select(string name, string pwd)
{
//构建查询语句 = 不能like
var sql = $"select * from UserInfo where name=@name and pwd=@pwd";
SqlParameter[] sqlParameters=
{
new SqlParameter("@name",name),
new SqlParameter("@pwd",pwd),
};
var table = DBhelper.ExcuteTable(sql,sqlParameters);
//如果没有数据 null
if (table == null || table.Rows.Count < 1)
{
return null;
}
var row = table.Rows[0];
Userinfo user = new Userinfo()
{
Id = Convert.ToInt32(row["Id"]),
Name = Convert.ToString(row["Name"]),
Role = Convert.ToInt32(row["Role"])
};
return user;
}