Functions and usage of SqlParameter

Generally, if you do not useSqlParameterWhen the entered SQL statement is ambiguous, for example, if the string contains single quotes, the program will encounter errors, and others can easily concatenate SQL statements to launch injection attacks.

 
 

  
  
String SQL = "update Table1 set name = 'pudding' where ID = '1'"; // SqlParameter is not used
  
  
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 preceding Code does not use SqlParameter. In addition to security issues, this method cannot solve binary stream updates and file fragments. SqlParameter can be used to solve the above problem. There are two common methods to use: Add method and AddRange method.

I. 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 one SqlParameter at a time. The function of the above Code is to update the name of the field with the ID value equal to 1 to Pudding ).

Ii. 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 multiple SqlParameter values are added. In this case, you can use the AddRange method.

The following code stores and reads images from the database using SqlParameter.

 
 

  
  
View sourceprint? 01 public int SavePhoto (string photourl)
  
  
{
  
  
FileStream fs = new FileStream (photourl, FileMode. Open, FileAccess. Read); // creates a FileStream object for writing a byte data stream to BinaryReader
  
  
BinaryReader br = new BinaryReader (fs); // creates a BinaryReader object to write the following byte array
  
  
Byte [] photo = br. ReadBytes (int) fs. Length); // creates a byte array and writes data in the br.
  
  
Br. Close (); // remember to Close the br
  
  
Fs. Close (); // There are also 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 (); // use SqlDataReader to read data
  
  
If (reader. Read ())
  
  
{
  
  
Byte [] photo = reader [0] as byte []; // write 0th columns of data to the byte array
  
  
FileStream fs = new FileStream (url, FileMode. CreateNew); creates a FileStream object for writing data to the byte data stream.
  
  
Fs. Write (photo, 0, photo. Length); // Write data in the byte array to fs
  
  
Fs. Close (); // Close fs
  
  
}
  
  
Reader. Close (); // Close reader
  
  
}
  
  
Catch (Exception ex)
  
  
{
  
  
Throw;
  
  
}
  
  
Finally
  
  
{
  
  
Conn. Close ();
  
  
}
  
  
}
  
  
}