USE STORED PROCEDURE IN C# USING LINQ TO SQL

sandy taylor

Total Post:46

Points:324
Posted by  sandy taylor
 1610  View(s)
Ratings:
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

  1. Maria Susan

    Post:29

    Points:107
    Re: Use stored procedure in c# using LINQ to SQL

    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

  1. Pawan Shukla

    Post:29

    Points:203
    Re: Use stored procedure in c# using LINQ to SQL

    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!