I am trying to insert data using a datatable from Xamarin.Forms app into database using a Web API post method. I am using a user defined data type as follows:
CREATE TYPE [dbo].[StdDetailsTypeTable] AS TABLE
(
[id] [nchar](10) NOT NULL,
[section] [nchar](2) NULL,
[profilePic] [varbinary](max) NULL
)
I have a stored procedure that receives a datatable of type StdDetailsTypeTable and inserts the data into the students table as follows:
ALTER PROCEDURE [dbo].[getstddetails]
@stdDetailsTable AS StdDetailsTypeTable READONLY
AS
BEGIN
INSERT INTO [dbo].[students] ([id], [section], [profilePic])
SELECT
X.[id], X.section, X.[profilePic]
FROM
@stdDetailsTable X
END
This is the Students table:
CREATE TABLE [dbo].[students]
(
[id] [nchar](10) NOT NULL,
[Name] [nvarchar](50) NULL,
[EdLevel] [nvarchar](20) NULL,
[grade] [nchar](10) NULL,
[section] [nchar](2) NULL,
[profilePic] [varbinary](max) NULL,
CONSTRAINT [PK_students_primary]
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] TEXTIMAGE_ON [PRIMARY]
I also have a Web API post method that executes this stored procedure:
public void PostStdDetails([FromBody] DataTable stddetails)
{
SqlConnection conn = new SqlConnection(DBConnection);
try
{
SqlCommand cmd = new SqlCommand("getstddetails", conn);
cmd.CommandType = CommandType.StoredProcedure;
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
cmd.Parameters.Add("@stdDetailsTable", SqlDbType.Structured).Value = stddetails;
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}
}
and a button in xamarin.forms that is supposed to handle the data sending process like this:
async private void hf_Clicked(object sender, EventArgs e)
{
urlClass urldata = new urlClass();
string uri = urldata.url + "/PostStdDetails";
byte[] bytess = new byte[] { 72, 101, 108, 108, 111, 32, 87, 111, 114, 108, 100 };
DataTable dt = new DataTable();
dt.TableName = "djf";
dt.Columns.Add("id", typeof(string));
dt.Columns.Add("section", typeof(string));
dt.Columns.Add("profilePic", typeof(byte[]));
dt.Rows.Add("STDID0010", "A",bytess);
dt.Rows.Add("STDID0011", "B", bytess);
StringContent content = new StringContent(JsonConvert.SerializeObject(dt), Encoding.UTF8, "application/json");
try
{
HttpResponseMessage responsepost = await client.PostAsync(uri, content);
if (responsepost.IsSuccessStatusCode == true)
{
await DisplayAlert("Section Added", "Section per Grade is added successfully", "OK");
}
else
{
await DisplayAlert("Operation Failed", "Response Failed!", "Cancel");
}
}
catch (System.Net.WebException exp)
{
bool ans = await DisplayAlert("Connection Failed", "Please Check Your Internet Connection!", "Retry", "Cancel");
if (ans == true)
hf_Clicked(sender, e);
}
catch (Exception exp)
{
bool ans = await DisplayAlert("Connection Failed", "Lost Connection!", "Retry", "Cancel");
if (ans == true)
hf_Clicked(sender, e);
}
}
The column profilePic is supposed to be an image. When I remove this column, the procedure works well when executed by clicking the button.
But when I add it, I get operation failed. I tried sending the data using a Windows Forms app with the profilePic column existing, it worked!
I am confused. Note that I used the datatype varbinary(max) instead of image because I read somewhere that this is better. But anyway it didn't work with both types. What am I doing wrong?
Thanks in advance
Update: i changed my post method to catch the exception as suggested and got this:
i tried adding this line in my procedure Convert(varbinary(max),X.profilePic) but still got the same message
Update 2: this is the code of the windows forms app:
private void button1_Click(object sender, EventArgs e)
{
byte[] bytess = new byte[] { 72, 101, 108, 108, 111, 32, 87, 111, 114, 108, 100 };
DataTable dt = new DataTable();
dt.TableName = "djf";
dt.Columns.Add("id", typeof(string));
dt.Columns.Add("section", typeof(string));
dt.Columns.Add("profile",typeof(byte[]));
dt.Rows.Add("STDID009", "A",bytess);
dt.Rows.Add("STDID008", "B",bytess);
SqlConnection conn = new SqlConnection(DBConnection);
conn.Open();
SqlCommand cmd = new SqlCommand("getstddetails", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@stdDetailsTable", SqlDbType.Structured).Value = dt;
cmd.ExecuteNonQuery();
conn.Close();
}