Explain the function and usage of SqlParameter in C in detail
- 2021-11-24 02:41:42
- OfStack
Generally speaking, if SqlParameter is not used when updating DataTable or DataSet, when the input Sql statement is ambiguous, such as single quotation marks in the string, the program will make errors, and others can easily splice Sql statements for injection attacks.
string sql
= "update
Table1 set name = 'Pudding' where ID = '1'";// Not adopted SqlParameter
SqlConnection
conn = new SqlConnection();
conn.ConnectionString
= "Data
Source=.\\SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|\\Database.mdf;User Instance=true";// The connection string is related to the database
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 use SqlParameter, except for security problems, this method can not solve the update of binary stream, such as picture file. The above problems can be solved by using SqlParameter. There are two common ways to use it, Add method and AddRange method.
1. Add method
SqlParameter
sp = new SqlParameter("@name","Pudding");
cmd.Parameters.Add(sp);
sp
= new SqlParameter("@ID","1");
cmd.Parameters.Add(sp);
This method can only add 1 SqlParameter at a time. The function of the above code is to update the field name where ID value equals 1 to Pudding (person name).
2. AddRange method
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, so the AddRange method can be adopted.
The following is the code for storing and reading pictures to the database through SqlParameter.
public int SavePhoto(string photourl)
{
FileStream
fs = new FileStream(photourl,
FileMode.Open, FileAccess.Read);// Create FileStream Object, which is used to the BinaryReader Write byte data stream
BinaryReader
br = new BinaryReader(fs);// Create BinaryReader Object for writing the following byte Array
byte[]
photo = br.ReadBytes((int)fs.Length);// New byte Array, write to br Data in
br.Close();// Remember to close it br
fs.Close();// And 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();// Adopt SqlDataReader To read the data
if (reader.Read())
{
byte[]
photo = reader[0] as byte[];// Will be 0 Data write to column byte Array
FileStream
fs = new FileStream(url,FileMode.CreateNew); Create FileStream Object for writing byte data streams
fs.Write(photo,0,photo.Length);// Will byte Write to the data in the array fs
fs.Close();// Shut down fs
}
reader.Close();// Shut down reader
}
catch (Exception
ex)
{
throw;
}
finally
{
conn.Close();
} }}