Functions and usage of sqlparameter-Abstract

Functions and usage of sqlparameter
From: rainsun blog I want to comment (0) font size: T | T

Because sometimes script injection is dangerous in the way of SQL statements, we do not recommend concatenating SQL statement strings in most cases. We hope to use sqlparameter to perform data operations, for the sqlparameter method, we can also encapsulate it into a reusable data parameter class, but a sqlparameter parameter is more than the SQL statement method.
AD:
Generally, if sqlparameter is not used when updating a able or dataset, if the entered SQL statement is ambiguous, for example, if the string contains single quotes,ProgramErrors may occur, and others can easily inject attacks by splicing SQL statements.

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
{< br> Conn. open ();
return (CMD. executenonquer Y ();
}< br> catch (exception)
{< br> return-1;
throw;
}< br> finally
{< br> Conn. close ();
}< br> the preceding Code does not use sqlparameter, except for security issues, this method cannot solve the problem of 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 ();
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
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 ();
}
}
}