How to create a datatable from an object and then execute stored procedure with a table-valued parameter?

Question

User-394943341 posted

Hello,

I'm trying to pass object to sql stored procedure but struggling to make any headway.

I've a simple page containing a gridview populated via a ShoppingCart class (this can obviously contain multiple records/rows):

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                BindData();

        }

        protected void BindData()
        {
            ShoppingCart cart = ShoppingCart.GetShoppingCart();
            gvCheckout.DataSource = cart.Items;
            gvCheckout.DataBind();

        }

Now, do I need to firstly convert this into a datatable or similar to then be able to add as parameters to my stored proc?

Example of stored proc fired OnClick (with hard-coded values adding only 1 row per execution):

        
private string connectionString;
protected void btnCheckout_Click(object sender, EventArgs e)
        {
            
            int a = 123;
            string b = "qwwert";
            string c = "poiiuyjh";

            connectionString = WebConfigurationManager.ConnectionStrings["xxxx"].ConnectionString;
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("InsertOrders", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@prodid", a);
            cmd.Parameters.AddWithValue("@cust_acc_prod", b);
            cmd.Parameters.AddWithValue("@descrip", c);

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }

            catch (SqlException err)
            {
                throw new ApplicationException("Data error.");
            }
            finally
            {
                con.Close();

            }

        }

I then assume I need to alter my stored proc to be able to accept table-valued parameters to thus allow multiple shopping cart records/rows to be inserted as per https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

Ideally, I could do with some pointers in the right direction please.

thanks in advance.

Answer 1

User-492460945 posted

Hi Dom,

You can also iterate through the ShoppingCart class using foreach loop. Did you try it?

Thanks,

RajeshV.

Answer 2

User-394943341 posted

Hi Rajesh,

I tried creating a datatable by iterating through the class but struggled.

Am I right in thinking that, for this to work, I do need to create a datatable? Or, can we use the shoppingcart class and importantly insert multiple rows using 1 execution of the stored proc?

Been trawling the web but keep going round in circles!

thanks,

Dom

Answer 3

User-492460945 posted

can we use the shoppingcart class and importantly insert multiple rows using 1 execution of the stored proc?

Yes you can simple iterate though your class and get property values. Please check below links.

https://stackoverflow.com/questions/4276566/how-can-you-loop-over-the-properties-of-a-class

https://www.codeproject.com/Articles/667438/How-to-iterate-through-all-properties-of-a-class

Thanks,

RajeshV.

Answer 4

User-394943341 posted

thanks, but my next question would be on how to pass this list of values to the stored proc? If I had a datatable then I believe I could pass that as a table valued parameter.

thanks for your input btw, much appreciated!

Answer 5

User-492460945 posted

Hi,

According to you r class, you cal do like this,

ShoppingCart cart = ShoppingCart.GetShoppingCart();
foreach(var item in cart.Items)
{
    connectionString = WebConfigurationManager.ConnectionStrings["xxxx"].ConnectionString;
    SqlConnection con = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("InsertOrders", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@prodid", item.col1);
    cmd.Parameters.AddWithValue("@cust_acc_prod", item.col2);
    cmd.Parameters.AddWithValue("@descrip", item.col3);
    cmd.ExecuteNonQuery();
}

Let me know if you need something different..

Answer 6

User-394943341 posted

Yes, but you're executing the stored proc for every row.

I think I've figured it out. Used this code to create a datatable from my SHoppingCart class:

 public static DataTable ToDataTable<T>(List<T> items)
        { //converts to datatable
          //https://stackoverflow.com/questions/18100783/how-to-convert-a-list-into-data-table
          //needs to be moved to 
            DataTable dataTable = new DataTable(typeof(T).Name);

            //Get all the properties
            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                //Defining type of data column gives proper data table 
                var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
                //Setting column names as Property names
                dataTable.Columns.Add(prop.Name, type);
            }
            foreach (T item in items)
            {
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {
                    //inserting property values to datatable rows
                    values[i] = Props[i].GetValue(item, null);
                }
                dataTable.Rows.Add(values);
            }
            //put a breakpoint here and check datatable
            return dataTable;
        }	

Then, pass the datatable to my Stored Proc which accepts a table valued parameter.

 protected void btnCheckout_Click(object sender, EventArgs e)
        {
            ShoppingCart cart = ShoppingCart.GetShoppingCart();
            DataTable dt = ToDataTable(cart.Items);

            connectionString = WebConfigurationManager.ConnectionStrings["xxxx"].ConnectionString;
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("InsertOrderDetails", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@OrderX", dt);


      

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
            }

            catch (SqlException err)
            {
                throw new ApplicationException("Data error.");
            }
            finally
            {
                con.Close();

            }

        }

Thanks for your help though. regards,Dom