Saturday, 3 May 2014

How to Store and Retrieve Image in SQL Server Database using C# .Net

Description:

In this article, I am going to write C# code to insert/save/store the image into Sql server database and then retrieve/read the image from Sql server database using Binary and Image datatype.

Note: It is recommended to use the sql datatype varbinary(max) to save/store image in sql server. because the Image data type will be removed in a future version of Microsoft SQL Server. So avoid the data types ntext, text, and image in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Summary:


Store/Insert Image into SQL Server Database as Binary datatype

Use the below C# function to store/save image file into into SQL Server table as Binary datatype.
public static void InsertImageintoSqlDatabaseAsBinary(string imageFilePath)
{
    using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
    {
        sqlconnection.Open();

        // create table if not exists 
        string createTableQuery = @"Create Table [UserTable](UserID int, [Photo] varbinary(max))";
        SqlCommand command = new SqlCommand(createTableQuery, sqlconnection);
        command.ExecuteNonQuery();

        // Converts image file into byte[]
        byte[] imgData = File.ReadAllBytes(imageFilePath);

        string insertXmlQuery = @"Insert Into [UserTable] (UserID,[Photo]) Values(1,@Photo)";

        // Insert Image Value into Sql Table by SqlParameter
        SqlCommand insertCommand = new SqlCommand(insertXmlQuery, sqlconnection);
        SqlParameter sqlParam = insertCommand.Parameters.AddWithValue("@Photo", imgData);
        sqlParam.DbType = DbType.Binary;
        insertCommand.ExecuteNonQuery();
    }
}

Retrieve/Read Image from SQL Server Database -Stored as Binary type

Use the below C# code to read/retrieve Image from SQL Server table that was stored as binary type and to save as new image file.
public static void ReadImageFromSqlDatabase_StoredAsBinary(int userID)
{
    using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
    {
        sqlconnection.Open();

        string selectQuery =string.Format(@"Select [Photo] From [UserTable] Where UserID={0}"
                            ,userID);

        // Read Image Value from Sql Table 
        SqlCommand selectCommand = new SqlCommand(selectQuery, sqlconnection);
        SqlDataReader reader = selectCommand.ExecuteReader();
        if (reader.Read())
        {
            byte[] imgData = (byte[])reader[0];
            using (MemoryStream ms = new MemoryStream(imgData))
            {
                System.Drawing.Image image = Image.FromStream(ms);
                image.Save(@"C:\Users\Administrator\Desktop\UserPhoto.jpg");
            }
        }
    }
}

Insert/Save Image file into SQL Server table as Image datatype

Use the below C# function to save/store Image file into into SQL Server table as Image type.
public static void InsertImageintoSqlDatabaseAsImageDatatype(string imageFilePath)
{
    using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
    {
        sqlconnection.Open();

        // create table if not exists 
        string createTableQuery = @"Create Table [UserTable](UserID int, [Photo] image)";
        SqlCommand command = new SqlCommand(createTableQuery, sqlconnection);
        command.ExecuteNonQuery();

        // Converts image file into byte[]
        byte[] imgData = File.ReadAllBytes(imageFilePath);

        string insertXmlQuery = @"Insert Into [UserTable] (UserID,[Photo]) Values(1,@Photo)";

        // Insert Image Value into Sql Table by SqlParameter
        SqlCommand insertCommand = new SqlCommand(insertXmlQuery, sqlconnection);
        SqlParameter sqlParam = insertCommand.Parameters.AddWithValue("@Photo", imgData);
        sqlParam.DbType = DbType.Binary;
        insertCommand.ExecuteNonQuery();
    }
}

Retrieve/Read Image from SQL Server Database -Stored as Image datatype

Use the below C# code to read/retrieve image from Sql server table that was stored as image datatype and to save as new image file.
public static void ReadImageFromSqlDatabase_StoredAsImage(int userID)
{
    using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
    {
        sqlconnection.Open();

        string selectQuery = string.Format(@"Select [Photo] From [UserTable] Where UserID={0}",
                             userID);

        //Read Image Value from Sql Table that was stored as Image Datatype
        SqlCommand selectCommand = new SqlCommand(selectQuery, sqlconnection);
        SqlDataReader reader = selectCommand.ExecuteReader();
        if (reader.Read())
        {
            byte[] imgData = (byte[])reader[0];
            using (MemoryStream ms = new MemoryStream(imgData))
            {
                System.Drawing.Image image = Image.FromStream(ms);
                image.Save(@"C:\Users\Administrator\Desktop\UserPhoto.jpg");
            }
        }
    }
}
The image file data will be stored in sql server database as binary structure in both the datatypes varbinary and image.

Insert/Save and Retrieve/Read Image in SQL Server table using C#


 But varbinary is recommended datatype to store image in sql server. check this link: http://msdn.microsoft.com/en-us/library/ms187993.aspx

Advertisements
Advertisements

2 comments:

  1. Thank you so much for this, it helped me save an image file as Image datatype. Have a good day sir!

    ReplyDelete