Use stored procedure in c# using LINQ to SQL

Total Post:46

Points:324
 2086  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. 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

      Modified On Apr-07-2018 05:03:35 AM
  1. 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

      Modified On Apr-07-2018 05:01:23 AM

Answer