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

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:\UsersAdministratorDesktopUserPhoto.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:\UsersAdministratorDesktopUserPhoto.jpg");
            }
        }
    }
}

The image file data will be stored in sql server database as binary structure in both the datatypes varbinary and image.

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