C # operate the varbinary (max) Field

If you store the photos directly in the SQL Server database, we recommend that you use the varbinary (max) field. The following code demonstrates how to use C # To operate the varbinary (max) field.

1. Add a record

Private void btnbrowse_click (Object sender, eventargs e) // view the photo
{
Openfiledialog DLG = new openfiledialog ();
DLG. Filter = "*. jpg (JPG file) | *. jpg | *. gif | *. GIF ";
DLG. filterindex = 1;
If (DLG. showdialog () = dialogresult. OK)
{
Textbox3.text = DLG. filename;
Picturebox1.image = image. fromfile (DLG. filename );
}
}

// Add a record

Using (sqlconnection conn = new sqlconnection (configurationmanager. connectionstrings ["DB"]. connectionstring ))
{
String SQL = "insert into EMP (name, age, photo) values (@ name, @ age, @ photo )";
Sqlcommand cmd = new sqlcommand (SQL, Conn );
Cmd. Parameters. addwithvalue ("@ name", textbox1.text );
Cmd. Parameters. addwithvalue ("@ age", convert. toint32 (textbox2.text ));
// Byte [] B;
// Using (filestream FS = new filestream (textbox3.text, filemode. Open, fileaccess. Read ))
//{
// B = new byte [fs. Length];
// Fs. Read (B, 0, (INT) fs. Length );
//}
Byte [] B;
If (textbox3.text! = "")
{
B = file. readallbytes (textbox3.text );
Cmd. Parameters. addwithvalue ("@ photo", B );
}
Else
{
Cmd. Parameters. addwithvalue ("@ photo", system. Data. sqltypes. sqlbinary. null );
}
Conn. open ();
Try
{
Cmd. executenonquery ();
}
Catch (exception ex)
{

MessageBox. Show (ex. Message );
}

}

2. Display record information and provide the modification function

Private void formdetail_load (Object sender, eventargs e) // display record details
{
Using (sqlconnection conn = new sqlconnection (configurationmanager. connectionstrings ["DB"]. connectionstring ))
{
String SQL = "select * from EMP where id = @ ID ";
Sqlcommand cmd = new sqlcommand (SQL, Conn );
Cmd. Parameters. addwithvalue ("@ ID", ID );
Conn. open ();
Using (sqldatareader DR = cmd. executereader ())
{
If (dr. Read ())
{
Textbox1.text = Dr [1]. tostring ();
Textbox2.text = Dr [2]. tostring ();
If (! Dr. isdbnull (3) // prevent empty photo Fields
{
System. Data. sqltypes. sqlbytes bytes = dr. getsqlbytes (3 );
Picturebox1.image = image. fromstream (bytes. Stream); // display a photo
}
}
}
}
}

Private void btnsave_click (Object sender, eventargs e) // update record
{
Using (sqlconnection conn = new sqlconnection (configurationmanager. connectionstrings ["DB"]. connectionstring ))
{
Byte [] B;
If (textbox3.text! = "") // You need to update the photo.
{
String SQL = "Update EMP set name = @ name, age = @ age, photo = @ photo where id = @ ID ";
Sqlcommand cmd = new sqlcommand (SQL, Conn );
Cmd. Parameters. addwithvalue ("@ name", textbox1.text );
Cmd. Parameters. addwithvalue ("@ age", convert. toint32 (textbox2.text ));
Cmd. Parameters. addwithvalue ("@ ID", ID );
B = file. readallbytes (textbox3.text );
Cmd. Parameters. addwithvalue ("@ photo", B );
Conn. open ();
Try
{
Cmd. executenonquery ();
}
Catch (exception ex)
{

MessageBox. Show (ex. Message );
}
}
Else // do not need to update the photo
{
String SQL = "Update EMP set name = @ name, age = @ age where id = @ ID ";
Sqlcommand cmd = new sqlcommand (SQL, Conn );
Cmd. Parameters. addwithvalue ("@ name", textbox1.text );
Cmd. Parameters. addwithvalue ("@ age", convert. toint32 (textbox2.text ));
Cmd. Parameters. addwithvalue ("@ ID", ID );
Conn. open ();
Try
{
Cmd. executenonquery ();
}
Catch (exception ex)
{

MessageBox. Show (ex. Message );
}
}
}
}