Click here to Skip to main content
13,252,815 members (51,366 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
How to store images in sql & How to retrive images from sql?
Posted 29-Dec-12 0:45am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

CREATE TABLE [dbo].[ImageData]
 (
    [ImageID] [int] IDENTITY(1,1) NOT NULL,
    [ImageData] [image] NULL,
 CONSTRAINT [PK_ImageData] PRIMARY KEY CLUSTERED
 (
    [ImageID] 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]



In this example I am going to use Four(4) Stored Procedures call ReadAllImage, ReadAllImageIDs, ReadImage, SaveImage and use below SQL scripts to create those Procedures.

CREATE proc [dbo].[ReadAllImage] as
SELECT * FROM ImageData
GO


CREATE proc [dbo].[ReadAllImageIDs] as
SELECT ImageID FROM ImageData
GO



CREATE proc [dbo].[ReadImage] @imgId int as
SELECT ImageData FROM ImageData
WHERE ImageID=@imgId
GO



CREATE proc [dbo].[SaveImage] @img image as
INSERT INTO ImageData(ImageData)
VALUES (@img)
GO



 FileStream FS = new FileStream(@fop.FileName, FileMode.Open, FileAccess.Read);
     byte[] img = new byte[FS.Length];
     FS.Read(img, 0, Convert.ToInt32(FS.Length));
 
     if (con.State == ConnectionState.Closed)
       con.Open();
     SqlCommand cmd = new SqlCommand("SaveImage", con);
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.Parameters.Add("@img", SqlDbType.Image).Value = img;
     cmd.ExecuteNonQuery();
     loadImageIDs();


SqlConnection con = new SqlConnection(DBHandler.GetConnectionString());
    SqlCommand cmd = new SqlCommand("ReadImage", con);
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.Parameters.Add("@imgId", SqlDbType.Int).Value = 
              Convert.ToInt32(cmbImageID.SelectedValue.ToString());
    SqlDataAdapter adp = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    try
    {
        if (con.State == ConnectionState.Closed)
            con.Open();
        adp.Fill(dt);
        if (dt.Rows.Count > 0)
        {
            MemoryStream ms = new MemoryStream((byte[])dt.Rows[0]["ImageData"]);
            picImage.Image = Image.FromStream(ms);
            picImage.SizeMode = PictureBoxSizeMode.StretchImage;
            picImage.Refresh();
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", 
              MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        if (con.State == ConnectionState.Open)
            con.Close();
    }
  Permalink  
v2
Comments
Mika Wendelius 29-Dec-12 6:01am
   
Good example, my 5
Oleksandr Kulchytskyi 29-Dec-12 6:06am
   
Thanks
Espen Harlinn 29-Dec-12 7:37am
   
Well done :-D
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

  Permalink  
Comments
Mika Wendelius 29-Dec-12 6:01am
   
Also a good link.
ProgramFOX 29-Dec-12 6:02am
   
Thank you!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

And to add one more link, if you want to use FileStream in SQL Server, here's an example: How to store and fetch binary data into a file stream column[^]
  Permalink  
Comments
Espen Harlinn 29-Dec-12 7:37am
   
5'ed!
Mika Wendelius 29-Dec-12 7:54am
   
Thank you :)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 6

This is exactly what you want :
Storing binary data in SQL Server using EF[^]
  Permalink  
Comments
edjeit 25-Sep-13 10:28am
   
I don't understand why this is down voted...

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web01 | 2.8.171114.1 | Last Updated 25 Sep 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100