In general, when updating a DataTable or DataSet, if you do not use SqlParameter, when the input SQL statement is ambiguous, such as the string contains single quotation marks, the program will have an error, and others can easily splicing SQL statements to inject attacks.
?
123456789101112131415161718 |
string
sql =
"update Table1 set name = ‘Pudding‘ where ID = ‘1‘"
;
//未采用SqlParameter
SqlConnection conn =
new
SqlConnection();
conn.ConnectionString =
"Data Source=.\\SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|\\Database.mdf;User Instance=true"
;
//连接字符串与数据库有关
SqlCommand cmd =
new
SqlCommand(sql, conn);
try
{
conn.Open();
return
(cmd.ExecuteNonQuery());
}
catch
(Exception)
{
return
-1;
throw
;
}
finally
{
conn.Close();
}
|
The above code does not adopt SqlParameter, in addition to the existence of security issues, the method is not able to resolve the binary stream update, slice file. The above problems can be solved by using SqlParameter, and there are two common ways to use them, the Add method and the AddRange method.
First, the Add method
?
1234 |
SqlParameter sp = new SqlParameter( "@name" , "Pudding" ); cmd.Parameters.Add(sp); sp = new SqlParameter( "@ID" , "1" ); cmd.Parameters.Add(sp); |
This method can only add one SqlParameter at a time. The function of the above code is to update the field name with the ID value equal to 1 to pudding (person name).
Second, AddRange method
?
12 |
SqlParameter[] paras = new SqlParameter[] { new SqlParameter( "@name" , "Pudding" ), new SqlParameter( "@ID" , "1" ) }; cmd.Parameters.AddRange(paras); |
Obviously, the Add method is inconvenient when adding multiple SqlParameter, at which point the AddRange method can be used. Here is the code to store and read the picture through SqlParameter to the database.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758 |
public
int
SavePhoto(
string
photourl)
{
FileStream fs =
new
FileStream(photourl, FileMode.Open, FileAccess.Read);
//创建FileStream对象,用于向BinaryReader写入字节数据流
BinaryReader br =
new
BinaryReader(fs);
//创建BinaryReader对象,用于写入下面的byte数组
byte
[] photo = br.ReadBytes((
int
)fs.Length);
//新建byte数组,写入br中的数据
br.Close();
//记得要关闭br
fs.Close();
//还有fs
string
sql =
"update Table1 set photo = @photo where ID = ‘0‘"
;
SqlConnection conn =
new
SqlConnection();
conn.ConnectionString =
"Data Source=.\\SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|\\Database.mdf;User Instance=true"
;
SqlCommand cmd =
new
SqlCommand(sql, conn);
SqlParameter sp =
new
SqlParameter(
"@photo"
, photo);
cmd.Parameters.Add(sp);
try
{
conn.Open();
return
(cmd.ExecuteNonQuery());
}
catch
(Exception)
{
return
-1;
throw
;
}
finally
{
conn.Close();
}
}
public
void
ReadPhoto(
string
url)
{
string
sql =
"select photo from Table1 where ID = ‘0‘"
;
SqlConnection conn =
new
SqlConnection();
conn.ConnectionString =
"Data Source=.\\SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|\\Database.mdf;User Instance=true"
;
SqlCommand cmd =
new SqlCommand(sql, conn);
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
//采用SqlDataReader的方法来读取数据
if (reader.Read())
{
byte
[] photo = reader[0]
as
byte
[];
//将第0列的数据写入byte数组
FileStream fs =
new
FileStream(url,FileMode.CreateNew);创建FileStream对象,用于写入字节数据流
fs.Write(photo,0,photo.Length);
//将byte数组中的数据写入fs
fs.Close();
//关闭fs
}
reader.Close();
//关闭reader
}
catch
(Exception ex)
{
throw
;
}
finally
{
conn.Close();
}
} } |
. NET writes non-text types to SQL