MVC 4 - Insert list/array from Controller to SQL Server

Question

I am using MVC4 C# Razor view and MS SQL Server. I need to insert a list/array value from controller to sql server. I am passing values from view to controller and getting the values in controller.

My data structures are -

{sid: "101", m1Qty: "1", m2Qty: "3", m3Qty: ""}
{sid: "102", m1Qty: "5", m2Qty: "6", m3Qty: ""}
{sid: "103", m1Qty: "8", m2Qty: "0", m3Qty: ""}

Above data needed to insert my table (tbl_monthqty) in the below order. ID auto generated -

ID  SID     MonthID   mQty
1   101        1       1 
2   102        1       5
3   103        1       8
4   101        2       3
5   102        2       6

If any value null or 0, need to ignore

MonthID is for example - m1Qty = 1, m2Qty = 2, m3Qty = 3

My controller (C#) is -

[HttpPost]
public JsonResult SaveQty(IList<AllQty> model)
{
    var list = new [] { model };
    var count = list.Count();

    DataTable dt = new DataTable();
    dt.Columns.Add("SID");
    dt.Columns.Add("MonthID");
    dt.Columns.Add("mQty");

    for(int i=0; i<count; i++)
    {
        //dt.Rows.Add();
        // Not sure what I will do here
    }

    return Json(new { success = true });
}

My class is -

public class AllQty
{
    public int SID { get; set; }
    public int MonthID { get; set; }
    public int mQty { get; set; }
}

I am getting the list value in controller but not sure how I will insert those list/array values in my table. I have tried few asked questions like this but did not work.

Possible duplicate of [How to fill a datatable with List](https://stackoverflow.com/questions/19076034/) — adiga, Oct 17 '17 at 09:44
Possible duplicate of [How to fill a datatable with List](https://stackoverflow.com/questions/19076034/how-to-fill-a-datatable-with-listt) — Tetsuya Yamamoto, Oct 17 '17 at 09:47
@adiga, my data structure is different from the above question — Arif, Oct 17 '17 at 09:47
Possible duplicate of [Sql Bulk Copy/Insert in C#](https://stackoverflow.com/questions/18841000/sql-bulk-copy-insert-in-c-sharp) — Aryan Firouzian, Oct 17 '17 at 09:48
if you use entity framework, it creates the model or data structure. It doesn't matter if you have different data structure. You can insert values the same way. — Aryan Firouzian, Oct 17 '17 at 09:50
@AryanFirouzyan, I don't want to use entity framework, could you please help me how I will add data in my table using the data I am getting — Arif, Oct 17 '17 at 09:51
Do you have any class to represent the data structure. So we can convert json data to list of objects? — Aryan Firouzian, Oct 17 '17 at 09:58
According to the question, your intention is to convert a list to `datatable`. Of course, the classes are different. SO is not a code writing service. Please read [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/a/261593/3082296) — adiga, Oct 17 '17 at 10:00
@AryanFirouzyan, I have updated my question, where I added the class — Arif, Oct 17 '17 at 10:03
What did not work out? You need to change the properties from [this answer](https://stackoverflow.com/a/19076205/3082296) to that of your class. — adiga, Oct 17 '17 at 10:10
@adiga, how I will add m1Qty, m2Qty, m3Qty value to mQty column because all in one row, also I am not much experienced — Arif, Oct 17 '17 at 10:12

score 2 · Accepted Answer · answered Oct 17 '17 at 11:09

First create data model that represent json data structure:

public class FirstModel
  {
    public int SID;
    public string m1Qty;
    public string m2Qty;
    public string m3Qty;
  }

Then data model that you want to store the data:

public class AllQty
  {
    public int SID { get; set; }
    public int MonthID { get; set; }
    public int mQty { get; set; }
  }

Then convert the json to list of FirstModel objects (I assume you already did it), and finally convert data in List to List :

        List<FirstModel> qtYs = new List<FirstModel>();
        List<AllQty> allQties = new List<AllQty>();
        foreach (FirstModel item in qtYs)
        {
            if (string.IsNullOrEmpty(item.m1Qty))
            {
                AllQty allQty = new AllQty
                {
                    MonthID = 1,
                    mQty = int.Parse(item.m1Qty),
                    SID = item.SID
                };
                allQties.Add(allQty);
            }

            if (string.IsNullOrEmpty(item.m2Qty))
            {
                AllQty allQty = new AllQty
                {
                    MonthID = 2,
                    mQty = int.Parse(item.m1Qty),
                    SID = item.SID
                };
                allQties.Add(allQty);
            }

            if (string.IsNullOrEmpty(item.m3Qty))
            {
                AllQty allQty = new AllQty
                {
                    MonthID = 3,
                    mQty = int.Parse(item.m1Qty),
                    SID = item.SID
                };
                allQties.Add(allQty);
            }
        }

        DataTable dt = new DataTable();
        dt.Columns.Add("SID");
        dt.Columns.Add("MonthID");
        dt.Columns.Add("mQty");

        foreach (AllQty allQty in allQties)
        {
            var row = dt.NewRow();

            row["SID"] = allQty.SID;
            row["MonthID"] = allQty.MonthID;
            row["mQty"] = allQty.mQty;

            dt.Rows.Add(row);
        }

MVC 4 - Insert list/array from Controller to SQL Server

1 Answers1