The function and usage of SqlParameter

Sometimes the diagram is convenient, will be directly related to the operation of the SqlHelper file, the following classes will appear:

public static Object ExecuteScalar (String sqlstr, params sqlparameter[] parameters)        {            using (SqlConnection conn = new SqlConnection (connstr))            {                Conn. Open ();                using (SqlCommand cmd = conn. CreateCommand ())                {                    cmd.commandtext = sqlstr;                    Cmd. Parameters.addrange (Parameters);                    return CMD. ExecuteScalar ();}}}        

Previously, the params sqlparameter[] Parameters modified operation, found sometimes inconvenient. It is also the original good, more flexible.

There are generally two methods of calling:

First, the Add method

New SqlParameter ("@name""pudding"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

New New SqlParameter ("@name""pudding"new SqlParameter ( " @ID " " 1 " )};cmd. Parameters.addrange (paras);

I do not have the actual operationcmd. Parameters.addrange (paras) because inthe parameters have been added to the ExecuteScalar method. 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.

 Public intSavephoto (stringPhotourl) {FileStream FS=NewFileStream (Photourl, FileMode.Open, FileAccess.Read);//Create a FileStream object for writing byte data flow to BinaryReaderBinaryReader br =NewBinaryReader (FS);//creates a BinaryReader object for writing to the following byte array    byte[] photo = Br. Readbytes ((int) fs. Length);//Create a new byte array to write data in BRBr. Close ();//Remember to close BRFs. Close ();//and FS .    stringsql ="update Table1 Set photo = @photo where ID = ' 0 '"; SqlConnection Conn=NewSqlConnection (); Conn. ConnectionString="Data source=.\\sqlexpress;integrated security=true; attachdbfilename=| Datadirectory|\\database.mdf; User instance=true"; SqlCommand cmd=NewSqlCommand (SQL, conn); SqlParameter SP=NewSqlParameter ("@photo", photo); Cmd.    Parameters.Add (SP); Try{Conn.        Open (); return(cmd.    ExecuteNonQuery ()); }    Catch(Exception) {return-1; Throw; }    finally{Conn.    Close (); }}  Public voidReadphoto (stringURL) {        stringsql ="Select photo from Table1 where ID = ' 0 '"; SqlConnection Conn=NewSqlConnection (); Conn. ConnectionString="Data source=.\\sqlexpress;integrated security=true; attachdbfilename=| Datadirectory|\\database.mdf; User instance=true"; SqlCommand cmd=NewSqlCommand (SQL, conn); Try{Conn.            Open (); SqlDataReader Reader= cmd. ExecuteReader ();//use the SqlDataReader method to read the data            if(reader. Read ()) {byte[] photo = reader[0] as byte[];//writes data from column No. 0 to a byte arrayFileStream fs =NewFileStream (url,filemode.createnew); Creates a FileStream object that is used to write byte data flow fs. Write (Photo,0, photo. Length);//writes data from a byte array to FSFs. Close ();//Turn off FS} reader. Close ();//Close Reader        }        Catch(Exception ex) {Throw; }        finally{Conn.        Close (); }    }}

Note: Refer to a friend's blog in the Garden.

The function and usage of SqlParameter