Home > DeveloperSection > Forums > Use stored procedure in c# using LINQ to SQL
sandy taylor
sandy taylor

Total Post:46

Points:324
Posted on    December-11-2014 12:32 AM

 ASP.Net LINQ  SQL Server  Linq To SQL 
Ratings:


 2 Reply(s)
 1218  View(s)
Rate this:

I'm looking for how to use a stored procedure using Linq-to-SQL in C#.

 

This is my stored procedure:

 

ALTER PROCEDURE [dbo].[uploadImage] @ppr INT,

    @imagename VARCHAR,

    @imagecontent VARCHAR,

    @imagebinary IMAGE

AS

BEGIN TRANSACTION

IF EXISTS (

        SELECT ImageID

        FROM [ImageStorage]

        WHERE ImageID = (

                SELECT codeimg

                FROM Agent

                WHERE PPR = @ppr

                )

        )

BEGIN

    --select ImageBinary from [ImageStorage] where ImageID = ( select codeimg from Agent where PPR=@ppr)

    UPDATE ImageStorage

    SET ImageName = @imagename,

        ImageContentType = @imagecontent,

        ImageBinary = @imagecontent

    WHERE imageID = (

            SELECT codeimg

            FROM Agent

            WHERE PPR = @ppr

            )

END

ELSE

    INSERT INTO ImageStorage (

        ImageName,

        ImageContentType,

        ImageBinary

        )

    VALUES (

        @imagename,

        @imagecontent,

        @imagebinary

        )

COMMIT

Thank you



Maria Susan

Total Post:29

Points:107
Posted on    December-11-2014 6:35 AM

consider changing your proc to this:

ALTER PROCEDURE [dbo].[uploadImage] @ppr INT,

    @imagename VARCHAR,

    @imagecontent VARCHAR,

    @imagebinary IMAGE

AS

BEGIN

    UPDATE ImageStorage

    SET ImageName = @imagename,

        ImageContentType = @imagecontent,

        ImageBinary = @imagecontent

    WHERE @ppr IS NOT NULL

        AND imageID = (

            SELECT codeimg

            FROM Agent

            WHERE PPR = @ppr

            )

 

    IF @@ROWCOUNT = 0

        INSERT INTO ImageStorage (

            ImageName,

            ImageContentType,

            ImageBinary

            )

        VALUES (

            @imagename,

            @imagecontent,

            @imagebinary

            )

END


Pawan Shukla
Pawan Shukla

Total Post:29

Points:203
Posted on    December-11-2014 7:04 AM

Use this to convert the image:

 

using (MemoryStream ms = new MemoryStream())

{

     image.Save(ms, System.Drawing.Imaging.ImageFormat.Png);

     var binary = new System.Data.Linq.Binary(ms.GetBuffer());

}

pass "binary" to your method as "imagebinary" parameter


Don't want to miss updates? Please click the below button!

Follow MindStick