CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
blue piranha
blue piranha

Reputation: 3876

Inserting PDF as byte array to SQL Server stored procedure using C#

I have a PDF which I need to insert into a SQL Server table's varbinary column.

I convert the PDF into a byte array using C#

byte[] bytes = File.ReadAllBytes(fileName);

I add a parameter to the SqlCommand:

 SqlParameter fileP = new SqlParameter("@file", SqlDbType.VarBinary);
 fileP.Value = bytes;
 myCommand.Parameters.Add(fileP);

The stored procedure basically takes a varbinary parameter and inserts into a table's varbinary column

 create procedure pdfInsert 
      @file varbinary(MAX)
 as
     insert ...

I get an error on execution of this procedure.

In the SQL Server profiler I found that the following procedure executes

exec pdfInsert @file = 0x2555... <-- byte array of pdf

The error is

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '40E3'.

In SSMS, when I execute this procedure with single quotes at both of the byte array.

Like this:

 exec pdfInsert @file = '0x2555.......'

I get the error:

Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Just curious to know what wrong am I doing? Any help is much appreciated. Thanks

Upvotes: 2

Views: 10515

Answers (2)

Y Neuhaus
Y Neuhaus

Reputation: 84

try to use Instead of "SqlParameter fileP = new SqlParameter("@file", SqlDbType.VarBinary);"

Use this cm.Parameters.AddWithValue("@file", bytes);

also make sure that column type is varbinary(max) and not any-other data type

Upvotes: 0

Decker97
Decker97

Reputation: 1653

This code works for me:

private void button1_Click(object sender, EventArgs e)
{
    byte[] bytes = File.ReadAllBytes(@"C:\pdf.pdf");
    SqlParameter fileP = new SqlParameter("@file", SqlDbType.VarBinary);
    fileP.Value = bytes;
    SqlCommand myCommand = new SqlCommand();
    myCommand.Parameters.Add(fileP);
    SqlConnection conn = new SqlConnection(@"Data Source=CoastAppsDev\SQL2008;Initial Catalog=CSharpWinForms;Integrated Security=True;");
    conn.Open();
    myCommand.Connection = conn;
    myCommand.CommandText = "spPdfInsert";
    myCommand.CommandType = CommandType.StoredProcedure;
    myCommand.ExecuteNonQuery();
    conn.Close();

}

with the stored proc:

Create Procedure [dbo].[spPdfInsert] (
    @file varbinary(max) = null
)
AS


Insert Into Pdfs
(   pdfData )
Values
(   @file )

What version of SQL Server are you on? 2008?

Upvotes: 4

Related Questions