-1

In my webpage image get from file upload control and it has to be store in sql server.how to write stored procedure for save image in sql server?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459

2 Answers2

5

Some sample code(not tested, could need some fixes):

CREATE PROCEDURE SaveFile(@id int, @file varbinary(max)) AS

INSERT INTO MyFiles VALUES(@id, @file)
GO

In the .NET code:

SqlCommand comm = new SqlCommand(@"SaveFile")
comm.CommandType = CommandType.StoredProcedure;

SqlParameter id = new SqlParameter("int", SqlDbType.Int);
id.value = 1; // some id

FileStream fileStream = ... your file;
byte[] buf = new byte[fileStream.length];
fileStream.Read(buf, 0, buf.length);
SqlParameter file = new SqlParameter("@file", SqlDbType.VarBinary, buf.Length);
file.Value = buf;

comm.Parameters.Add(id)    
comm.Parameters.Add(file)

comm.Execute();
Tisho
  • 8,320
  • 6
  • 44
  • 52
  • how to retrieve that image and display in a place? –  Jun 21 '12 at 10:08
  • how to show that pic?in panel r wat??pls guide me.. –  Jun 21 '12 at 10:10
  • The question is about storing the image in database. And I think it is already answered. How to show the pic? It is up to you to decide what UI element to use and how. In general to read the image - follow the procedure in reverse order. SELECT file FROM table; fileStream.Write(buf); However you might need to make a dynamic page to read the image and return it keeping in mind the Content-Type. – Tisho Jun 21 '12 at 11:08
1

Just use the usual stored procedure, just use a different datatype, save it as byte array, so you will have to convert first your image to byte array before passing it as parameter to your stored procedure, save the length and file type as well.

Here is a sample stored procedure. I'll leave the rest to you. If you know how to execute queries in your web app, you can call this procedure. just google for it.

USE [mydatabasename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[myprocedurename]

    @ImageFile image,
    @FileType nvarchar(10),
    @FileSize int
AS
-- INSERT a new row in the table.
INSERT [dbo].[mytablename]
(
    [ImageFile],
    [FileType ],
    [FileSize ]
)
VALUES
(
    @ImageFile,
    @FileType,
    @FileSize
)
-- Get the IDENTITY value for the row just inserted.
SELECT @ImageId=SCOPE_IDENTITY()

i guess the image file type will be obsolete so you should use a different file type for saving your bytes

Philip Badilla
  • 1,038
  • 1
  • 10
  • 21