Insert Data Into Binary(64) Column From Asp.Net API

Question

I am using a Asp.net web api to send data from an app to the API and insert into sql server.

This is DDL of my table, and my stored procedure that I am attempting to execute as well as the format of the data that is being sent. The issue is that I am attempting to assign a string value and I need to convert the string value to a binary(64) value before it hits the stored procedure. How do I do this?

--URL being sent to API
XXX.XXX.XX.XXX:XXX/api/user/adduser?firstparam="a"&secparam="b"&thirparam="c"&fourparam="d"&fiveparam="e"&sixparam=123

--SQL DDL
CREATE TABLE [dbo].[data1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[badgeIn] [varchar](100) NOT NULL,
[supIn] [varchar](100) NOT NULL,
[phone] [varchar](10) NULL,
[lsc] [varchar](100) NOT NULL,
[email] [varchar](100) NOT NULL,
[gona] [binary](64) NOT NULL
PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[data1] ON 
GO
INSERT [dbo].[data1] ([ID], [badgeIn], [supIn], [phone], [lsc], [email], [gona]) VALUES (1, N'R183', N'TX1384', N'5555555555', N'Test', N'test1234@test.com', 0x477265656E0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000)
GO
SET IDENTITY_INSERT [dbo].[data1] OFF
GO

--Stored Proc
ALTER PROCEDURE  [dbo].[InsertData] @badgeIn varchar(100), @supIn varchar(100), @Phone varchar(100), @lsc varchar(100), @Email varchar(100), @gona binary(64)

AS

INSERT INTO dbo.appinfo (badgeIn, supIn, phone, lsc, email, gona)   VALUES (@badgeIn, @supIn, @Phone, @lsc, @Email, @gona)\

EDIT
The error shown in my response variable is:

{StatusCode: 500, ReasonPhrase: 'Internal Server Error', Version: 1.1, Content: System.Net.Http.NSUrlSessionHandler+NSUrlSessionDataTaskStreamContent, Headers: { Pragma: no-cache X-Powered-By: ASP.NET Server: Microsoft-IIS/10.0 X-AspNet-Version: 4.0.30319 Date: Fri, 23 Nov 2018 13:32:43 GMT Cache-Control: no-cache Content-Type: application/json; charset=utf-8 X-Powered-By: ASP.NET X-AspNet-Version: 4.0.30319 Content-Length: 36 Expires: -1 }}

And this is how I send data to API

                var jsonData = new StringContent(JsonConvert.SerializeObject(data), Encoding.UTF8, "application/json");
            var response = await httpClient.PostAsync(url, jsonData);
            var result = await response.Content.ReadAsStringAsync();

[Convert the string to an array of bytes](https://stackoverflow.com/questions/241405/how-do-you-convert-a-string-to-a-byte-array-in-net) then you can [pass it as a parameter to your SqlCommand](https://stackoverflow.com/questions/1064121/how-do-i-insert-a-byte-into-an-sql-server-varbinary-column) — Alex K., Nov 23 '18 at 13:02
@AlexK. - but I just need the one field as a byte not the entire string — Doctor Ford, Nov 23 '18 at 13:32

score 0 · Answer 1 · answered Nov 23 '18 at 13:32

0

You could convert your string to a byte array and then convert that array to a base64 string which would be your target format to use in your insert operation

    public string convertToBinString(String gona)
    {
        byte[] gonaArray = System.Text.Encoding.UTF8.GetBytes(gona);
        return Convert.ToBase64String(gonaArray);
    }

answered Nov 23 '18 at 13:32

Wilson Crespo

1

Forgive my ignorance, but once I convert how would I then append it back to send to my API? – Doctor Ford Nov 23 '18 at 13:38
@DoctorFord may be I misunderstood the fact that you are trying to send the data from a javascript environment instead of processing them in a c# context, so, if you are trying to send data from javascript, then you could use the javascript funcion to convert a string to base64 sequence `btoa()`, once your normal string be enconded to a base64 string then you post it to your API endpoint – Wilson Crespo Nov 23 '18 at 14:12

Insert Data Into Binary(64) Column From Asp.Net API

1 Answers1