Click here to Skip to main content
12,357,130 members (68,648 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: ASP.NET SQL
How to store images in sql & How to retrive images from sql?
Posted 28-Dec-12 23:45pm
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 | Mobile
Web02 | 2.8.160621.1 | Last Updated 25 Sep 2013
Copyright © CodeProject, 1999-2016
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