Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NETSQL
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 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 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 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
Your Filters
Interested
Ignored
     
0 Shai Vashdi 1,518
1 Tadit Dash 350
2 Manas Bhardwaj 319
3 OriginalGriff 243
4 Sergey Alexandrovich Kryukov 205
0 Sergey Alexandrovich Kryukov 9,530
1 OriginalGriff 5,716
2 Peter Leow 4,320
3 Maciej Los 3,540
4 Abhinav S 3,373


Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 25 Sep 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid