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.