Click here to Skip to main content
Rate this: bad
good
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 at 29-Dec-12 6:01am
   
Good example, my 5
Oleksandr Kulchytskyi at 29-Dec-12 6:06am
   
Thanks
Espen Harlinn at 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 at 29-Dec-12 6:01am
   
Also a good link.
ProgramFOX at 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 at 29-Dec-12 7:37am
   
5'ed!
Mika Wendelius at 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 at 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
0 Sergey Alexandrovich Kryukov 343
1 _Amy 290
2 Manfred R. Bihy 200
3 Maciej Los 185
4 CHill60 180
0 OriginalGriff 7,445
1 Sergey Alexandrovich Kryukov 6,347
2 Maciej Los 3,799
3 Peter Leow 3,558
4 CHill60 2,702


Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 25 Sep 2013
Copyright © CodeProject, 1999-2014
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