Friday, October 21, 2011

access image data in sql server With C# and Vb

Atalasoft dotImage has streaming capabilities that can be used jointly with ADO.NET to read and write images directly to a database without saving to a temporary file. The following code snippets demonstrates this in C# and VB.NET.

Write to a Database

C#

private void SaveToSqlDatabase(AtalaImage image)
{
SqlConnection myConnection = null;
try
{
// Save image to byte array.
byte[] imagedata = image.ToByteArray(new Atalasoft.Imaging.Codec.JpegEncoder(75));



// Create the SQL statement to add the image data.
myConnection = new SqlConnection(CONNECTION_STRING);
SqlCommand myCommand = new SqlCommand
("INSERT INTO Atalasoft_Image_Database (Caption, ImageData) VALUES ('" + txtCaption.Text + "', @Image)", myConnection);
SqlParameter myParameter = new SqlParameter("@Image", SqlDbType.Image, imagedata.Length);
myParameter.Value = imagedata;
myCommand.Parameters.Add(myParameter);


// Open the connection and execture the statement.
myConnection.Open();
myCommand.ExecuteNonQuery();
}
finally
{
myConnection.Close();
}
}

Visual Basic.NET

Private Sub SaveToSqlDatabase(ByVal image As AtalaImage)
Dim myConnection As SqlConnection = Nothing
Try
' Save image to byte array.
Dim imagedata() As Byte = image.ToByteArray(New Atalasoft.Imaging.Codec.JpegEncoder(75))


' Create the SQL statement to add the image data.
myConnection = New SqlConnection(CONNECTION_STRING)
Dim myCommand As SqlCommand = New SqlCommand
("INSERT INTO Atalasoft_Image_Database (Caption, ImageData) VALUES ('" + txtCaption.Text + "', @Image)", myConnection)
Dim myParameter As SqlParameter = New SqlParameter("@Image", SqlDbType.Image, imagedata.Length)
myParameter.Value = imagedata
myCommand.Parameters.Add(myParameter)


' Open the connection and execture the statement.
myConnection.Open()
myCommand.ExecuteNonQuery()
Finally
myConnection.Close()
End Try
End Sub

Read from a Database

C#

private AtalaImage OpenFromSqlDatabase()
{
SqlConnection myConnection = null;
try
{
// Establish connection and SELECT statement.
myConnection = new SqlConnection(CONNECTION_STRING);
SqlCommand myCommand = new SqlCommand
("SELECT ImageData FROM Atalasoft_Image_Database WHERE Caption = '" + txtCaption.Text + "'", myConnection);
myConnection.Open();


// Get the image from the database.
byte[] imagedata = (byte[])myCommand.ExecuteScalar();
if (imagedata != null)
{
AtalaImage image = AtalaImage.FromByteArray(imagedata);
return image;
}
else
{
MessageBox.Show("Image does not exist in database.");
return null;
}
}
finally
{
myConnection.Close();
}
}

Visual Basic .NET

Private Function OpenFromSqlDatabase() As AtalaImage
Dim myConnection As SqlConnection = Nothing
Try
' Establish connection and SELECT statement.
myConnection = New SqlConnection(CONNECTION_STRING)
Dim myCommand As SqlCommand = New SqlCommand
("SELECT ImageData FROM Atalasoft_Image_Database WHERE Caption = '" + txtCaption.Text + "'", myConnection)
myConnection.Open()


' Get the image from the database.
Dim imagedata() As Byte = CType(myCommand.ExecuteScalar(), Byte())
If (Not imagedata Is Nothing) Then
Dim image As AtalaImage = AtalaImage.FromByteArray(imagedata)
Return image
Else
MessageBox.Show("Image does not exist in database.")
Return Nothing
End If
Finally
myConnection.Close()
End Try
End Function