How do you store a picture in an image column?

Question

I have a User table:

Name varchar(20)
Picture image

I want to store a image into the Picture column -- how can I achieve this using SQL Scripts?

Answer 1

Here is a sample code for storing image to sql server :

SqlConnection conn = new SqlConnection(connectionString);

try
{
    int imageLength = uploadInput.PostedFile.ContentLength;
    byte[] picbyte = new byte[imageLength];
    uploadInput.PostedFile.InputStream.Read (picbyte, 0, imageLength);

    SqlCommand command = new SqlCommand("INSERT INTO ImageTable (ImageFile) VALUES (@Image)", conn);
    command.Parameters.Add("@Image", SqlDbType.Image);
    command.Parameters[0].Value = picbyte;

    conn.Open();
    command.ExecuteNonQuery();
    conn.Close();
}
finally
{
    if (conn.State != ConnectionState.Closed)
    {
        conn.Close();
    }
}

NOTE : uploadInput is a file input control, to upload image file to server. The code taken from an ASP.NET application.

EDIT : Here is the insert script to an image typed column :

INSERT INTO ImageTable (ImageColumn)

SELECT ImageColumn FROM 
OPENROWSET(BULK N'C:\SampleImage.jpg', SINGLE_BLOB) 
AS ImageSource(ImageColumn);

Answer 2

For purely scripted access, look up the BULK command in Books Online. SQL Server 2005 allows you to pull binary data directly from disk. This will not work with previous versions of SQL server however.