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

  • 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.

    Monday, July 9, 2018 2:32 PM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 9, 2018 3:51 PM

All replies

  • User-492460945 posted

    Hi Dom,

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

    Thanks,

    RajeshV.

    Monday, July 9, 2018 2:43 PM
  • 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

    Monday, July 9, 2018 2:48 PM
  • 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.

    Monday, July 9, 2018 3:12 PM
  • 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!

    Monday, July 9, 2018 3:19 PM
  • 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..

    Monday, July 9, 2018 3:24 PM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 9, 2018 3:51 PM