locked
Insert ForEach data in View to database by using DBUtil in Controller RRS feed

  • Question

  • User852530961 posted

    Hi currently I am doing a shopping cart for my project

    I would like to ask how can I import the values in a ForEach to the database.

    For example, I have the following data in my view.

    @foreach (Cart_Has_Services c in Model)

            {

                <div class="cart-row">

                    <div class="cart-items">@c.Cart_Service</div>

                    <div class="cart-items">@c.Additional_Notes</div>

                    <div class="cart-items">@c.Unit_Price</div>

                    <div class="cart-items">

                        <form asp-controller="Cart" asp-action="UpdateCart" formaction="post">

                            <input type="number" class="item-quantity-input" value="@c.Quantity" />

                            <input type="submit" class="btn btn-secondary" value="Update" />

                        </form>

                    </div>

                    <div class="cart-items">

                        <a asp-controller="Cart"

                           asp-action="DeleteItem"

                           asp-route-id="@c.Cart_Id"

                           onclick="return confirm('Delete Serivce @c.Cart_Service')">

                            Delete

                        </a>

                    </div>

                </div>

            }

    As for now, I want to INSERT data (Cart Service, Additional Notes and Quantity) into my database (Order).

    In my controller:

    public IActionResult Checkout(Cart_Has_Services cart) 

            { 

            

                List<Cart_Has_Services> carts = DBUtl.GetList<Cart_Has_Services>("SELECT * FROM Cart");        

                string sql = @"INSERT INTO [Order](Order_Name,Order_Description,Order_Quantity) 

                             VALUES('{0}','{1}',{2})";

                int ord = DBUtl.ExecSQL(sql, cart.Cart_Service, cart.Additional_Notes, cart.Quantity);

                if (ord == 1)

                {

                    TempData["Message"] = "Perofrmance Successfully Created";

                    TempData["MsgType"] = "success";

                    return RedirectToAction("Success");

                }

                else

                {

                    ViewData["Message"] = DBUtl.DB_Message;

                    ViewData["MsgType"] = "danger";

                    return View("ShoppingCart");

                }

            }

    I tried the method that I have inserted but it created without inserting the data.

    How can I solve this problem

    Hope can some guidance.

    Thank you

    Sunday, January 17, 2021 4:17 AM

Answers

  • User1312693872 posted

    Hi, Kevinyong

    First, your offered view does not call the CheckOut action in your controller, do you want to post a list of data to controller?

    You can check my demo, get the list from Cart and post them to controller to add them to the table 'Order':

    View:

    @model IEnumerable<Cart_Has_Services>
    
    <div class="cart-items">
        <form asp-action="Checkout" method="post">
            @for(int i=0; i<Model.Count(); i++)  //if use foreach, it can't recognize multiple names
            {
            <div class="cart-row">
                <div class="cart-items">@Model.ToList()[i].Cart_Service</div> //for show the value
                <input asp-for="@Model.ToList()[i].Cart_Service" hidden />  //for passing the value
    
                <div class="cart-items">@Model.ToList()[i].Additional_Notes</div>
                <input asp-for="@Model.ToList()[i].Additional_Notes" hidden />
    
                <div class="cart-items">@Model.ToList()[i].Unit_Price</div>
                <input asp-for="@Model.ToList()[i].Unit_Price" hidden />
    
                <input asp-for="@Model.ToList()[i].Quantity" />
            </div>
            }
            <input type="submit" class="btn btn-secondary" value="Insert" />
        </form>
    </div> 

    Controller:

     [HttpPost]
            public IActionResult Checkout(List<Cart_Has_Services> cart)   //Get List
            {
                if (cart.Count()!= 0)
                {
                    string sql = @"INSERT INTO [Order](Order_Name,Order_Description,Order_Quantity) 
                             VALUES('{0}','{1}',{2})";
                    foreach (var item in cart)
                    {
                        int ord = DBUtl.ExecSQL(sql, item.Cart_Service, item.Additional_Notes, item.Quantity);
                        if (ord == -1)
                        {
                            return RedirectToAction("Error");
                        }
                    }
                    return RedirectToAction("Privacy"); //success page
                }
                else
                    return RedirectToAction("Error");
            }

    Result:

    Best Regards,

    Jerry Cai

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 19, 2021 7:34 AM

All replies

  • User852530961 posted

    Hi, I understand the duplication of the post but I really need to solve this problem. The previous thread I posted at the wrong section.

    This is why i post this again.

    I hope you'll understand
    Do you know how to solve this problem?
    Thank You
    Sunday, January 17, 2021 11:00 AM
  • User-1545767719 posted

    I understand the duplication of the post but I really need to solve this problem.

    Although I understand that you need to solve the issue it cannot justify the multi-posting. I and probably most participants do not really appreciate to see duplicated posts. I ask you to take action to make them single.

    Monday, January 18, 2021 12:44 AM
  • User852530961 posted

    Hi, I understand the concern of the multi-posting. I checked my thread and it seems like the duplicate thread that is similar to this issue has been removed. As for another thread which is the "Update" has been solved.

    In this case, Is this thread still available and valid ?

    Thank you

    Monday, January 18, 2021 12:54 AM
  • User1312693872 posted

    Hi,Kevinyong

      int ord = DBUtl.ExecSQL(sql, cart.Cart_Service, cart.Additional_Notes, cart.Quantity);

    Could you please show me the DBUtl class of yours? So that I can reproduce the problem.

    Best Regards,

    Jerry Cai

    Tuesday, January 19, 2021 3:17 AM
  • User852530961 posted

    Hi Jerry,

    The following is the codes for the DBUtl:

    using Microsoft.Extensions.Configuration;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.Dynamic;
    using System.IO;
    using System.Reflection;
    
    public static class DBUtl
    {
       public static string DB_CONNECTION;
       public static string DB_SQL;        // Added From L09
       public static string DB_Message;
    
       static DBUtl()
       {
          IConfiguration config =
             new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json")
                .Build();
          string env = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT");
          if (env.Equals("Development"))
             DB_CONNECTION = config.GetConnectionString("DefaultConnection");
          else if (env.Equals("Production"))
             DB_CONNECTION = config.GetConnectionString("ProductionConnection");
       }
    
       public static List<dynamic> GetList(string sql, params object[] list)
       {
          return GetTable(sql, list).ToDynamic();
       }
    
       public static List<ModelClass> GetList<ModelClass>(string sql, params object[] list)
       {
          return GetTable(sql, list).ToStatic<ModelClass>();
       }
    
       private static List<DTO> ToStatic<DTO>(this DataTable dt)
       {
          var list = new List<DTO>();
          foreach (DataRow row in dt.Rows)
          {
             DTO obj = (DTO)Activator.CreateInstance(typeof(DTO));
             foreach (DataColumn column in dt.Columns)
             {
                PropertyInfo Prop = obj.GetType().GetProperty(column.ColumnName, BindingFlags.Public | BindingFlags.Instance);
                if (row[column] == DBNull.Value)
                   Prop?.SetValue(obj, null);
                else
                {
                   //Debug.WriteLine(row[column].GetType() + " " + Prop?.PropertyType); 
                   if (row[column].GetType() == Prop?.PropertyType)
                      Prop?.SetValue(obj, row[column]);
                }
             }
             list.Add(obj);
          }
          return list;
       }
    
       private static List<dynamic> ToDynamic(this DataTable dt)
       {
          var dynamicDt = new List<dynamic>();
          foreach (DataRow row in dt.Rows)
          {
             dynamic dyn = new ExpandoObject();
             foreach (DataColumn column in dt.Columns)
             {
                var dic = (IDictionary<string, object>)dyn;
                dic[column.ColumnName] = row[column];
             }
             dynamicDt.Add(dyn);
          }
          return dynamicDt;
       }
    
       public static DataTable GetTable(string sql, params object[] list)
       {
          for (int i = 0; i < list.Length; i++)
             if (list[i] is string)
                list[i] = list[i].ToString().EscQuote();
    
          DB_SQL = String.Format(sql, list);
    
          DataTable dt = new DataTable();
          using (SqlConnection dbConn = new SqlConnection(DB_CONNECTION))
          using (SqlDataAdapter dAdptr = new SqlDataAdapter(DB_SQL, dbConn))
          {
             try
             {
                dAdptr.Fill(dt);
                return dt;
             }
    
             catch (System.Exception ex)
             {
                DB_Message = ex.Message;
                return null;
             }
          }
       }
    
       public static int ExecSQL(string sql, params object[] list)
       {
            for (int i = 0; i < list.Length; i++)
                if (list[i] is string)
                    list[i] = list[i].ToString().EscQuote();
    
          DB_SQL = String.Format(sql, list);
    
          int rowsAffected = 0;
          using (SqlConnection dbConn = new SqlConnection(DB_CONNECTION))
          using (SqlCommand dbCmd = dbConn.CreateCommand())
          {
             try
             {
                dbConn.Open();
                dbCmd.CommandText = DB_SQL;
                rowsAffected = dbCmd.ExecuteNonQuery();
             }
    
             catch (System.Exception ex)
             {
                DB_Message = ex.Message;
                rowsAffected = -1;
             }
          }
          return rowsAffected;
       }
    
       public static string EscQuote(this string line)
       {
          return line?.Replace("'", "''");
       }
    
    
    }
    

    As the DBUtl, I used to perform some of the basic SQL statement (INSERT, SELECT, UPDATE etc.),  

    Tuesday, January 19, 2021 3:25 AM
  • User1312693872 posted

    Hi,Kevinyong

    You lost the '' in sql, when I add the '' , I can store the data to the database.

    string sql = @"INSERT INTO [Order](Order_Name,Order_Description,Order_Quantity) 
    
                             VALUES('{0}','{1}','{2}')";

    Best Regards,

    Jerry Cai

    Tuesday, January 19, 2021 5:57 AM
  • User852530961 posted

    Hi Jerry,

    Thank you for your reply

    For "{2}", I didn't put the quote because is the datatype for that column is an integer.

    I tried it still show the same result which is unable to bring the data.

    Thank you

    Tuesday, January 19, 2021 6:01 AM
  • User1312693872 posted

    Hi, Kevinyong

    First, your offered view does not call the CheckOut action in your controller, do you want to post a list of data to controller?

    You can check my demo, get the list from Cart and post them to controller to add them to the table 'Order':

    View:

    @model IEnumerable<Cart_Has_Services>
    
    <div class="cart-items">
        <form asp-action="Checkout" method="post">
            @for(int i=0; i<Model.Count(); i++)  //if use foreach, it can't recognize multiple names
            {
            <div class="cart-row">
                <div class="cart-items">@Model.ToList()[i].Cart_Service</div> //for show the value
                <input asp-for="@Model.ToList()[i].Cart_Service" hidden />  //for passing the value
    
                <div class="cart-items">@Model.ToList()[i].Additional_Notes</div>
                <input asp-for="@Model.ToList()[i].Additional_Notes" hidden />
    
                <div class="cart-items">@Model.ToList()[i].Unit_Price</div>
                <input asp-for="@Model.ToList()[i].Unit_Price" hidden />
    
                <input asp-for="@Model.ToList()[i].Quantity" />
            </div>
            }
            <input type="submit" class="btn btn-secondary" value="Insert" />
        </form>
    </div> 

    Controller:

     [HttpPost]
            public IActionResult Checkout(List<Cart_Has_Services> cart)   //Get List
            {
                if (cart.Count()!= 0)
                {
                    string sql = @"INSERT INTO [Order](Order_Name,Order_Description,Order_Quantity) 
                             VALUES('{0}','{1}',{2})";
                    foreach (var item in cart)
                    {
                        int ord = DBUtl.ExecSQL(sql, item.Cart_Service, item.Additional_Notes, item.Quantity);
                        if (ord == -1)
                        {
                            return RedirectToAction("Error");
                        }
                    }
                    return RedirectToAction("Privacy"); //success page
                }
                else
                    return RedirectToAction("Error");
            }

    Result:

    Best Regards,

    Jerry Cai

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 19, 2021 7:34 AM
  • User852530961 posted

    Hi Jerry,

    Sorry for the late reply as I just tested the method that you provided and is working well. 

    Now, I face another problem as I have a total variable which is something like this:

    @{
        var total = @Model.Sum(i => i.Unit_Price * i.Quantity);
    }

    As you can see, I use the model (unit price and quantity) to get the total.

    Is it possible to get the value by using the input tag and asp-for = "@total" to get the value and pass into the controller for the insert?

    Thank you 

    Friday, January 22, 2021 7:30 AM
  • User1312693872 posted

    Hi,Kevinyong

    You want to calculate the total price and pass it to controller? The following is an example:

    @model IEnumerable<Cart_Has_Services>
    
    <div class="cart-items">
    
        <form asp-action="Checkout" method="post">
            @for (int i = 0; i < Model.Count(); i++)
            {
                <div class="cart-row">
                    <div class="cart-items">@Model.ToList()[i].Cart_Service</div>
                    <input asp-for="@Model.ToList()[i].Cart_Service" hidden />
    
                    <div class="cart-items">@Model.ToList()[i].Additional_Notes</div>
                    <input asp-for="@Model.ToList()[i].Additional_Notes" hidden />
    
                    <div class="cart-items">@Model.ToList()[i].Unit_Price</div>
                    <input asp-for="@Model.ToList()[i].Unit_Price" hidden />
    
                    <input asp-for="@Model.ToList()[i].Quantity" class="getquantity" data-id1="@Model.ToList()[i].Unit_Price" />
                </div>
            }
            <br />
            <input asp-for="@total" hidden />   //pass the total to controller
            <input type="submit" class="btn btn-secondary" value="Insert" />
        </form>
    </div>
    <div id="showtotal" />  //show total
    
    @section Scripts
    {
        <script>
            $(document).ready(function () {   // update the total dynamically
                var total = 0;
                $('.getquantity').on("change", function () {
                    $('.cart-row').each(function () {
                        var i = $('.getquantity', this);
                        var price = $(i).data("id1");
                        var num = $(i).val();
                        total = total + (price * num);
                    });
                    $("#total").val(total);   //offer the value to asp-for=@total
                    $("#showtotal").text(total);  //pass the value to show in view
                });
            });
        </script>
    }

    Controller:

    [HttpPost]
            public IActionResult Checkout(List<Cart_Has_Services> cart, double total)
            {
                //....
            }

    Result:

    Best Regards,

    Jerry Cai

    Friday, January 22, 2021 9:50 AM
  • User852530961 posted

    Hi Jerry, 

    Thank you for your reply, the method is working but when I try to get the inserted record id (payment_id etc.), it keeps giving me 0

    For example in my controller:

    string payment = @"INSERT INTO Payment(Payment_Method,Currency_Type,Total_Amount) 
                                 VALUES('{0}','{1}',{2})";
                int pay = DBUtl.ExecSQL(payment, "Cash", currency,total);
              
                    string paymentid = @"SELECT Payment_Id FROM Payment WHERE Paid_date = GETDATE()";
                    int pid = Convert.ToInt32(paymentid);
    

    As for now, I can do the first INSERT statement but till when I try to get the paymentid, it keeps giving null. 

    As the payment_id, I need to retrieve to insert into Order table which has the foreign key (Payment_Id)

    How can I get the payment_id that was after the INSERT statement into the payment table?

    Thank you

    Friday, January 22, 2021 12:49 PM
  • User1312693872 posted

    Hi,Kevinyong

    The paymentid gives null because there is some error in your database, GetDATE() will get always get the current time including hours, minutes

    and seconds, check the Paid_date, whether it can match the time generated by GETDATE().

    And do you have problems with the former questions? I suggest you ask one question per thread, so others can easily search the related posts.

    Best Regards,

    Jerry Cai

    Monday, January 25, 2021 9:44 AM
  • User852530961 posted

    Hi Jerry,

    I am using the GETDATE() cause the action will be performing 3 INSERT statement and the first is the Payment table

    INSERT INTO Payment (Payment_Method, Currency_Type, Total_Amount) 
    VALUES ('Cash','SGD',30.00)
    

    Now, my 2nd step is I want to get the lastest ID from the record that I just inserted into the Payment table.

    INSERT INTO [Order] (Order_Name, Order_Description, Order_Quantity, Payment_Id)
    VALUES ('IT Repair Services', '',2,(SELECT SCOPE_IDENTITY())),
    VALUES ('Repair Services', '',1,(SELECT SCOPE_IDENTITY())),

    At last, I have to insert the Order ID into a resolving table (ServiceHasOrder) where it contains the Order_Id and Service_Id and this is the INSERT statement that I made:

    INSERT INTO ServiceHasOrder (Order_Id,Services_Id)
    VALUES ((SELECT Order_Id FROM [Order] WHERE Ordered_date = GETDATE()), (SELECT Services_Id FROM Service s, [Order] o WHERE s.Service_name=o.Order_Name))
    

    For inserting into the resolving table, I will have to get the Order_Id from the Order table where I just inserted into and insert into the ServiceHasOrder table. Next, I also have a Services Table and I want to get the Services_Id into the ServiceHasOrde table so I use WHERE by comparing the order name and service name to get the Service_Id

    This is the logic that I want to achieve for this action in the controller.

    Till now, I can only achieve Step 1 and I stuck and on getting the payment_id at the controller.

    As for now, I want to get the Id with the condition that I want for my inserting

    Please help

    Thank you.
     

    Monday, January 25, 2021 5:08 PM
  • User1312693872 posted

    Hi,Kevinyong

    Set a breakpoint to check whether the insert has been correctly executed, if you can find the records, then the problem lies in the paymentid:

    string paymentid = @"SELECT Payment_Id FROM Payment WHERE Paid_date = GETDATE()";
    int pid = Convert.ToInt32(paymentid);

    The paymentid only got the sql string, not the value, can you show us the way you get the id value?

    Best Regards,

    Jerry Cai

    Thursday, January 28, 2021 9:34 AM
  • User852530961 posted

    Hi Jerry,

    I tried and it showed me 0, it doesn't show me the payment_id value

    I changed to SCOPE IDENTITY (), it also showed me the same thing

    Thank you

    Thursday, January 28, 2021 9:45 AM
  • User1312693872 posted

    Hi,Kevinyong

    How did you run the 

    string paymentid = @"SELECT Payment_Id FROM Payment WHERE Paid_date = GETDATE()";

    and get the string paymentid=0? Without truly running this sql, the string will always be the string with it's value is the "..sql sentence..". You

    should share us the code to execute this sql  like the executesql in dbutil or elsewhere so that we can check why the value=0.

    Best Regards,

    Jerry Cai

    Tuesday, February 2, 2021 9:48 AM