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();
    }  }}

Related articles: