SQL output parameter of varbinary to use as image on ASPX web form

Question

I am trying to retrieve the logo stored into my table and display the image from the database to my aspx web page. I have output parameters in a SQL stored procedure which output the name and a varbinary data type. In the SQL table the first column is name which is NVARCHAR(100) and the second column is an image logo. using SQL SERVER for DBMS.

E.g. Client1 is name and Logo looks like this: 0x89504E470D0A1A0A0000000D49484452000000C8000000C80802000000223A39C9000000097048597300002E2300002E230178A53F76000000206348524D00007A25000080830000F9FF000080E9000075300000EA6000003A980000176F925FC546000012954944415478DAEC9D79741455BE80BFEA4EBA3B0B09092124806149D80504C20E0282FA044591F500E2809C0047715F8E8EC3E0E8F846F1313A7014C4055704065...

Example SQL code to insert into table:

INSERT INTO dbo.client
SELECT 'Client1', BulkColumn 
FROM Openrowset( Bulk 'F:\logo.png', Single_Blob) as img

Stored proc called in C# code:

 CREATE PROCEDURE [dbo].[sp_ui_getClientNameLogo]
    -- Add the parameters for the stored procedure here
    @ClientName NVARCHAR(100) OUTPUT,
    @Logo varbinary(max) OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SET @ClientName = (SELECT Client_Name FROM dbo.client)

    SET @Logo = (SELECT Logo FROM dbo.client)

END

C# - Index.aspx.cs:

        public static void getClientInfo()
    {
        string connectionstring = ConfigurationManager.ConnectionStrings["SQLConString"].ConnectionString;
        SqlConnection cn = new SqlConnection(connectionstring);
        cn.Open();
        SqlCommand cmd = new SqlCommand("dbo.sp_ui_getClientNameLogo", cn);
        cmd.Parameters.Add(new SqlParameter("@ClientName", SqlDbType.NVarChar, 100, ParameterDirection.Output, false,0, 10, "ClientName", DataRowVersion.Default, null));
        cmd.Parameters.Add(new SqlParameter("@Logo", SqlDbType.VarBinary, 1000, ParameterDirection.Output, false, 0, 10, "Logo", DataRowVersion.Default, null));
        var da = new SqlDataAdapter(cmd);
        cmd.CommandType = CommandType.StoredProcedure;
        var dt = new DataTable();
        da.Fill(dt);

        cn.Close();

        clientName = cmd.Parameters["@ClientName"].Value.ToString();
        **logoBinary = cmd.Parameters["@Logo"].Value. //THIS IS WHERE I AM STUCK**

    }

How do I get the output variable of the logo and store it as an image to use on my web page??

Which dbms are you using? (That code is product specific. Perhaps <sql-server>?) — jarlh
– jarlh, Commented Jan 31, 2020 at 10:30

Atk · Accepted Answer · 2020-01-31 11:43:51Z

2

Try this code

On aspx page

     <img id='yourID' runat='server'/>

On CS page

      File.WriteAllBytes(yourfilename, (byte[])(cmd.Parameters["@Logo"].Value));
      byte[] logoBinary = (byte[])(cmd.Parameters["@Logo"].Value);
      string base64string= Convert.ToBase64String(logoBinary, 0, logoBinary.Length);
      yourID.Src = "data:image/jpg;base64," + base64String;

edited Jan 31, 2020 at 11:43

answered Jan 31, 2020 at 10:33

Atk

7521 gold badge4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

roa765 Over a year ago

Where do i put this? and what is 'yourfilename' ?

Atk Over a year ago

Lets say you want to save your image on C drive then "C:\\Folder\\yourfilename.jpg" if your image is jpg.

roa765 Over a year ago

Sorry i dont want to save my image anywhere. I have stored the image in the database in the table. I want to retrieve the image from the database and show on my ASPX web page? The logo has already been imported into the sql table from its location.

roa765 Over a year ago

I havent got any code yet as i dont know how to get it from the database to the C# end. Please see above question - I have made a comment where i am stuck logoBinary = cmd.Parameters["@Logo"].Value. //THIS IS WHERE I AM STUCK

Atk Over a year ago

Give you img tag ID and attribute runat='server' <img id='img' runat='server' src="./assets/img/users/u1.jpg" /> </div>

|

Collectives™ on Stack Overflow

SQL output parameter of varbinary to use as image on ASPX web form

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related