Inserting datatable into SQL Server database table using user defined table type doesn't work Xamarin.Forms

223 Views Asked by At

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:

enter image description here 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();
}
0

There are 0 best solutions below