C# .NET - how to insert image in sqlserver colum

Asked By Muthu Kumar on 14-Jun-12 05:17 AM
i want to create one table  in sql server
the sql server fields (id,Name,image)
image column i want to insert image
how can i do it?


Thanks in advance..
TSN ... replied to Muthu Kumar on 14-Jun-12 05:50 AM

In oreder to insert the image in to the databse SqlServer the image datatype should be in the byte format.

Below is the code to insert the image in to the databse to select a image use the file upload control.

protected void btnSubmit_Click(object sender, EventArgs e)

{

SqlConnection conn = null;

try

{

FileUpload imag = (FileUpload)imagUpload;

Byte[] imagByte = null;

if (imag.HasFile && imag.PostedFile != null)

{

//To create a PostedFile

HttpPostedFile File = imagUpload.PostedFile;

//Create byte Array with file len

imagByte = new Byte[File.ContentLength];

//force the control to load data in array

File.InputStream.Read(imagByte, 0, File.ContentLength);

}

// Insert the employee name and image into db

string connstinr = ConfigurationManager.ConnectionStrings ["ConnString"].ConnectionString;

conn = new SqlConnection(connstring);

conn.Open();

string sql = "INSERT INTO Emp (empid,empimg) VALUES(@eid, @eimg)”;

SqlCommand cmd = new SqlCommand(sql, connection);

cmd.Parameters.AddWithValue("@eid", txtEId.Text.Trim());

cmd.Parameters.AddWithValue("@eimg", imgByte);

int id = Convert.ToInt32(cmd.ExecuteNonQuery());

}

catch

{

lblResult.Text = "There was an error";

}

finally

{

conn.Close();

}

}

 

Chintan Vaghela replied to Muthu Kumar on 14-Jun-12 06:21 AM

Hi Frndz,

 

Functionality:  Insert Image into DB Table

 

Store Image in Datatable in Binary Format

 

Take one FileUpload Control, One Upload Button

 

Create DB table with Primary kEy Auto Incremnt.

 

On Upload Button Click event Call Following logic

 

Logic:

 

Create Table

 

CREATE TABLE [dbo].[tblImage](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [name] [varchar](50) NULL,

    [image] [image] NULL,

 CONSTRAINT [PK_tblImage] PRIMARY KEY CLUSTERED

(

    [id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

Upload Button Click Event

 

 

protected void btnUpload_Click(object sender, EventArgs e)

    {

      //Condition to check if the file uploaded or not

      if (fileuploadImage.HasFile)

      {

        //getting length of uploaded file

        int length = fileuploadImage.PostedFile.ContentLength;

        //create a byte array to store the binary image data

        byte[] imgbyte = new byte[length];

        //store the currently selected file in memeory

        HttpPostedFile img = fileuploadImage.PostedFile;

        //set the binary data

        img.InputStream.Read(imgbyte, 0, length);

        //Here call InsertImage Function for store ImageName and Image in Binary Foramt

        InsertImage(fileuploadImage.FileName.ToString(), imgbyte);

 

      }

    }

 

Function for Insert Image in DB Table

 

 

private void InsertImage(string imagename, byte[] imagebyte)

    {

      string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

      SqlConnection con = new SqlConnection(strConnection);

      SqlCommand cmd = new SqlCommand();

      cmd.Connection = con;

      cmd.CommandType = CommandType.Text;

      string strQuery = "Insert into tblImage(Name,Image) values(@Name,@Image) ";

      cmd.CommandText = strQuery;

      SqlParameter Name = new SqlParameter("@Name", SqlDbType.VarChar, 50);

      Name.Value = imagename;

      cmd.Parameters.Add(Name);

      SqlParameter Image = new SqlParameter("@Image", SqlDbType.Image);

      Image.Value = imagebyte;

      cmd.Parameters.Add(Image);

      con.Open();

      cmd.ExecuteNonQuery();

      con.Close();

    }

 

 Hope this helpful!

 

 

Thanks

 

 

 

Lalit M replied to Muthu Kumar on 14-Jun-12 07:33 AM
Hi..

use below code sample
----------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

public partial class ImageLoad : System.Web.UI.Page
{
   protected void Page_Load(object sender, EventArgs e)
    {
    }
  protected void btnUpload_Click1(object sender, EventArgs e)
    {
     string strImageName = txtImageName.Text.ToString();
    if (FileUpload1.PostedFile != null && FileUpload1.PostedFile.FileName != "")      {
        byte[] imageSize = new byte[FileUpload1.PostedFile.ContentLength];
        HttpPostedFile uploadedImage = FileUpload1.PostedFile;
        uploadedImage.InputStream.Read(imageSize, 0, (int)FileUpload1.PostedFile.ContentLength);
        // Create SQL Connection
        SqlConnection con = new SqlConnection("data Source=ADMIN;initial catalog=test; user id=Sa; password=123");
        // Create SQL Command
         SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "INSERT INTO Image(ImageName,Image)" + " VALUES (@ImageName,@Image)";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        SqlParameter ImageName = new SqlParameter("@ImageName", SqlDbType.VarChar, 50);
        ImageName.Value = strImageName.ToString();
        cmd.Parameters.Add(ImageName);
     
        SqlParameter UploadedImage = new SqlParameter("@Image", SqlDbType.Image, imageSize.Length);
        UploadedImage.Value = imageSize;
        cmd.Parameters.Add(UploadedImage);
       con.Open();
        int result = cmd.ExecuteNonQuery();
        con.Close();
        if (result > 0)
          lbmsg.Text = "File Uploaded";
      }
    }
}

Lalit M replied to Muthu Kumar on 14-Jun-12 07:35 AM
Try to another code sample
-----------
public bool PutImage(byte[] ImgIn)
{
    System.IO.MemoryStream ms =
     new System.IO.MemoryStream(ImgIn);
    System.Drawing.Bitmap b =
    (System.Drawing.Bitmap)Image.FromStream(ms);

    b.Save("C:\\your-file-to-be-saved.png",
       System.Drawing.Imaging.ImageFormat.Png);

    return true;
}

//Replace the below web reference with your own.
aby.MultiplexInfo I =
    new WindowsApplication2.aby.MultiplexInfo();

FileStream fs =
  new FileStream("C:\\your-file-to-be-sent.png",
    FileMode.OpenOrCreate, FileAccess.Read);
Byte[] img = new Byte[fs.Length];

fs.Read(img, 0, Convert.ToInt32(fs.Length));

I.PutImage(img);

dipa ahuja replied to Muthu Kumar on 14-Jun-12 08:17 AM
There are two ways to store image
 
1. Store just the name of the image(nvarchar DataType)
2. Store the actual image (Image dataType)
 
With nvarchar dataType to store just name of file
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
  try
  {
  string FolderPathToSave = Server.MapPath("~/images");
 
  //Save Image in Images Folder
 
  FileUpload1.SaveAs(FolderPathToSave + @"\" + FileUpload1.FileName);
  Status.Text = "File name: " +
    FileUpload1.PostedFile.FileName + "<br>";
  //Save ImageURL in Database
 
  string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
 
  SqlConnection connection = new SqlConnection(conn);
  connection.Open();
  SqlCommand comm = new SqlCommand("Insert into Photos (iname,url) values (@iname,@url)", connection);
  comm.Parameters.AddWithValue("iname", "photo1");
  comm.Parameters.AddWithValue("url", Path.GetFileName(FileUpload1.PostedFile.FileName));
  comm.ExecuteNonQuery();
  connection.Close();
 
  }
  catch (Exception ex)
  {
  Status.Text = "ERROR: " + ex.Message.ToString();
  }
}
Withe Image dataType:
 
 
protected void Button1_Click(object sender, EventArgs e)
{
  if (FileUpload1.HasFile)
  {
    //string productName = txtProductName.Text;
    byte[] productImage = FileUpload1.FileBytes;
 
    string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    SqlConnection connection = new SqlConnection(conn);
    connection.Open();
    SqlCommand comm = new SqlCommand("Insert Images (purl) values (@url)", connection);
 
    comm.Parameters.AddWithValue("url",productImage);
    comm.ExecuteNonQuery();
    connection.Close();
  }
}
 
Jitendra Faye replied to Muthu Kumar on 14-Jun-12 09:27 AM
For storing file into database first you have to convert into Byte Array Format, then you can store into DataBase.
Use this sample code for that-

protected void Button1_Click(object sender, EventArgs e)
{

byte[] imageSize = new byte[FileUpload1.PostedFile.ContentLength];
HttpPostedFile uploadedImage = FileUpload1.PostedFile;
uploadedImage.InputStream.Read(imageSize, 0, (int)FileUpload1.PostedFile.ContentLength);
SqlConnection cn = new SqlConnection("YOUR CONNECTION STRING");
cn.Open();
string strQuery = "insert into EmpTable(empimage) values(@ImageData)";
SqlCommand cmd = new SqlCommand(strQuery, cn);
cmd.Parameters.Add(new SqlParameter("@ImageData", (object)imageSize));
cmd.ExecuteNonQuery();
cn.Close();
}

USE THIS CODE AND LET ME KNOW.