How to insert pdf file into sql-server-CodePudding

I have an C# API which I use to read and insert pdf files into sql-server table. On the front-end side I have an React-application which consumes the API to do CRUD operations.

The react part works perfectly, I can do all the operations and I have no error/warning there.

I have already inserted some PDF documents into my table with this query:

INSERT INTO [DEFLEGOPINION] (content,extension,title)
SELECT BulkColumn , '.pdf' , 'Title123'
FROM OPENROWSET(BULK N'C:\temp\test.pdf', SINGLE_BLOB) AS BulkColumn
SELECT CAST('string' as varbinary(max)) FOR XML PATH(''), BINARY BASE64

So when I try to open and read these documents which are inserted this way, I can easily do that without any problem.

On the other hand, when I insert new files from API, the insertion works correctly but when I try to open the file it says that it's in a bad/corrupted format...

Here's how am I trying to insert files from c# api:

connection.Open();
string[] newContent = content.Split(',');
//here I removed the 'data:application/pdf;base64' part since I read somewhere that this makes a problem and should be remove

string encodedStr = Convert.ToBase64String(Encoding.UTF8.GetBytes(content));

String sql = "INSERT INTO myTable(Id,Name,Content) values('"   Guid.NewGuid()   "', '"
  name   "', CONVERT(varbinary(max), '"   newContent[1]   "', 0))";

using (SqlCommand command = new SqlCommand(sql, connection))
{
  command.ExecuteNonQuery();
  return true;
}
//Note:Name and Content are string parameters

Since the react part work as it should reading other documents, I don't think I have an issue there.

What am I missing? Should I change something on the method I use on the c# side or should I implement the first method but with the use of a file path (which it will be dynamic and I cannot know the user's file path's)

CodePudding user response：

Posting this here so it might help someone else in the future.

I just found out that it is a must to use parameters in order to insert a file into sql-server from c# api.

It goes like this:

using (SqlCommand command = new SqlCommand(sql, connection))                
{
command.Parameters.Add(new SqlParameter("@Ident", Guid.NewGuid()));
command.Parameters.Add(new SqlParameter("@Name", name));
command.Parameters.Add(new SqlParameter("@Content", file));
command.ExecuteNonQuery();
}