Click here to Skip to main content
15,886,137 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
What will be the Query to insert the image into the table for Sql Server 2008
Posted
Comments
StackQ 13-Dec-12 7:36am    
oh so u want directly query to,select image form system and store them into database.

So wait,I will also wait?
Perhaps it's possible?I don't know
[no name] 13-Dec-12 10:16am    
Its possible but the database will be over burdened.
OriginalGriff 13-Dec-12 7:39am    
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind.
SQL does not know about Images, so you nbeed to feed it a sequence of bytes - where they come form is a matter for the application calling SQL server, and exactly how you do it will depend on the language it is written in.
Use the "Improve question" widget to edit your question and provide better information.
MT_ 13-Dec-12 8:13am    
Why are you reposting question. http://www.codeproject.com/Questions/508376/InsertplusImageplusinplusSqlplusDatabase

Check this code sample
C#
protected void Button1_Click(object sender, EventArgs e)
    {
        //  'using System.IO' 
      
        FileStream fs = new FileStream("C:\\Files\\draft.jpg",FileMode.Open,FileAccess.Read);
        BinaryReader br = new BinaryReader(fs);
        byte[] photo = br.ReadBytes((int)fs.Length);

        br.Close();
        fs.Close();

        //  'using System.Data.SqlClient'
                     
        SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ContestConnection"].ConnectionString);

        SqlCommand addpic = new SqlCommand(  "INSERT INTO userTable ("+" img_data) "+" VALUES(@img)",myConnection);
            
        addpic.Parameters.Add("@img",SqlDbType.Image, photo.Length).Value = photo;
        
        myConnection.Open();
        
        addpic.ExecuteNonQuery();
        myConnection.Close();
         
        }


If you use FileUploadControl , then the code will be something like this

C#
protected void btnUpload_Click(object sender, EventArgs e)

{

//Create a new filestream object based on the file chosen in the FileUpload control

FileStream fs = new FileStream(ImageUploadToSQL.PostedFile.FileName, FileMode.Open, FileAccess.Read);

//Create a binary reader object to read the binary contents of the file to upload

BinaryReader br = new BinaryReader(fs);

//dump the bytes read into a new byte variable named image

byte[] image = br.ReadBytes((int)fs.Length);

//close the binary reader

br.Close();

//close the filestream

fs.Close();

//Now that we've read the chosen file into a byte variable we can work with,

//stick the contents of that variable into the SQL Server database

//Create a SQL Server connection variable

SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["BlobsConnectionString"].ConnectionString);

//Create a SQL Server command variable

SqlCommand addimage = new SqlCommand("INSERT INTO Images (" + " Image, ImageName)" + " VALUES(@Image, @ImageName)", myConnection);

//Assign our image variable and filename variable to the parameters of the SQL Command

addimage.Parameters.Add("@Image", SqlDbType.Image, image.Length).Value = image;

addimage.Parameters.Add("@ImageName", SqlDbType.NVarChar).Value = txtFileName.Text;

//open the SQL connection and run the command to insert image and image name to the database

myConnection.Open();

addimage.ExecuteNonQuery();

myConnection.Close();

}
 
Share this answer
 
v2
In Sql table take column of varbinary(max) type:
and after that while inserting, first convert the image to byte array using this code

C#
public byte[] ConvertImageToByteArray(FileUploadField fuImage)
{
    byte[] ImageByteArray;
    try
    {
        MemoryStream ms = new MemoryStream(fuImage.FileBytes);
        ImageByteArray = ms.ToArray();
        return ImageByteArray;
    }
    catch (Exception ex)
    {
        return null;
    }
}


Pass this byte array as parameter to your insert function as follow
C#
public static long insertImage(string ID, byte[] Image)
     {

         long Id = 0;
         SqlConnection conn = null;
         try
         {
             string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
             conn = new SqlConnection(strConn);
             SqlCommand cmd = new SqlCommand();
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Connection = conn;
             cmd.CommandText = "Your_Sp_Name_Here";
             cmd.Parameters.Add("@ID", SqlDbType.VarChar, 30).Value = ID;
             cmd.Parameters.Add("@Image", SqlDbType.Image, 0).Value = Image;
             conn.Open();

             Id = Convert.ToInt16(cmd.ExecuteScalar());
             conn.Close();



         }
         catch (Exception ex)
         {

         }

         finally
         {
             if (conn != null && conn.State != ConnectionState.Closed)
                 conn.Close();
         }


         return Id;
     }
 
Share this answer
 
v2
Check my post in this following link.Click here

Thanks
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900