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

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??

Answer

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;

Advertisement

Answer