A union of curiosity and data science

Knowledgebase and brain dump of a database engineer

C# Grab image from web and put it in a SQL Server Table.

26 January 2012 Don-Rickman .Net, SQL Server (0)

Create a table in SQL to hold your images byte data. 

Your image column / field should look like this:

 

varbinary works well for saving image to SQL.

Code for grabbing the image from the web and store it in a byte array. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
public byte[] get_image_bytes(String url)
{
 
    Image image = null;
    Image thumb = null;
 
    WebClient webClient = new WebClient();
    byte[] current_data = null;
 
    MemoryStream ms = new MemoryStream();
 
    try
    {
        current_data = webClient.DownloadData(url);
        image = Image.FromStream(new MemoryStream(current_data));
        thumb = image.GetThumbnailImage(150, 150, null, IntPtr.Zero);
        image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
        return ms.ToArray();
    }
    catch (Exception ex)  // could not find the image or write it.
    {
        while (ex != null)
        {
            ex = ex.InnerException;
        }
        return null;
    }
  }

Store the byte array in SQL. 

1
2
3
4
5
6
7
8
9
10
11
//open my connection to stg SOLEBI
string connection_string = "Data Source=<server>;Initial Catalog=<db>;User=<user>;password=<pass>;";
conn = new SqlConnection(connection_string);
conn.Open();
 
cmd_list = new SqlCommand("update MyTable set image_thumb = @image_thumb where ID = 1");
cmd_list.Parameters.AddWithValue("@image_thumb", get_image_bytes("http://www.mma.tv/wp-content/uploads/2016/05/Hottie-with-the-Body2.jpg"));
cmd_list.Connection = conn;
cmd_list.ExecuteNonQuery();
conn.Close();
cmd_list = null;