How to convert PDF binary string into MS SQL Server varBinary(max) datatype in C#

420 views Asked by At

In MS SQL query, I convert the binary string into varbinary(max) and insert the value. It is working fine. but how to convert binary string into varbinary(max) in c# using MS SQL stored procedure.

In MS SQL stored procedure, following are the steps which works fine and I can open the PDF in PDF viewer properly after insert.

Declare @ContentTest varbinary(max)
Declare @Doc varchar(max) 

// PDF Binary URL
Set @Doc = '0x255044462d312e340a25e2e3cfd30a332030206f626a0a3c3c2f54797065202f506167650a2.......';
SET @ContentTest = **CONVERT(VARBINARY(max), @Doc, 1)**;

EXEC dbo.SP_AddDocument
    @UserID = 2,
    @Subject = "User Details Report",
    @Details = "Report",
    @DocType = "PDF",
    @Compression = 0,
    @Content = @ContentTest,
    @CorroDate = "2022-11-25 10:05:47",
    @RequiresCheck = 0;

In C# when I try to convert the above MS SQL steps into code, the PDF does not open in PDF viever and I get a format error "we can't open this file something went wrong pdf". Following is the sample code which I tried and gets executed successfully but PDF format is not correct. Please suggest if I am missing anything.

public static async Task<string> syncDocumentToBPServer(JObject data)
{

    dbConnect();
    _sqlCon.Open();

    int lastInsertID = 0;
    int k = 0;

    if (_sqlCon.State == System.Data.ConnectionState.Open)
    {
        int bpPatientId = (int)data["bpPatientId"];
        **String content = data["content_binary"].ToString();**
        **byte[] theBytes = Encoding.UTF8.GetBytes(content);**

        string sql = @"DATABASE.dbo.[SP_AddDocument]";
        SqlCommand cmd = new SqlCommand(sql, _sqlCon);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@PatientID", bpPatientId));
        cmd.Parameters.Add(new SqlParameter("@Subject", "User Details Report"));
        cmd.Parameters.Add(new SqlParameter("@Details", "Report"));
        cmd.Parameters.Add(new SqlParameter("@DocType", "PDF"));
        cmd.Parameters.Add(new SqlParameter("@Compression", 0));
        **cmd.Parameters.Add("@Content", SqlDbType.VarBinary, 1).Value = theBytes;**
        cmd.Parameters.Add(new SqlParameter("@CorroDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
        cmd.Parameters.Add(new SqlParameter("@RequiresCheck", false));

        SqlParameter returnParameter = cmd.Parameters.Add("RecordID", SqlDbType.Int);
        returnParameter.Direction = ParameterDirection.ReturnValue;

        k = cmd.ExecuteNonQuery();
        if (k != 0)
        {
            lastInsertID = (int)returnParameter.Value;
        }
    }

    var result = new
    {
        success = k,
        recordId = lastInsertID,
    };
    _sqlCon.Close();

    return JsonConvert.SerializeObject(result);
}

Any help would be highly appreciated.

0

There are 0 answers