How to insert data to the database using a list (List

Question

Hi all I'm trying to insert some values to the database using mvc in asp.net. I heard that it would be good use objects when inserting data. So How can I achieve this using a List of objects through properties designed in a class.

Have a class called,

public class Customer
    {
        public string Name { get; set; }
        public string Company { get; set; }
        public int Telephone { get; set; }
        public string Email { get; set; }
        public int Id { get; set; }
    }

So I have a another class which simply do the sql commands

DBAccess.cs

public List<Customer> AddCustomers(string spName)
        {
            List<Customer> customers = new List<Customer>();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = spName;
        }

I know that using Idata Reader using can get the values in a database like this

IDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Customer cus = new Customer();

                cus.Name = reader["cus_name"].ToString();
                cus.Id = Convert.ToInt32(reader["cid"]);

                customers.Add(cus);
            }

How do I insert data back to the database using such a scenario? Help would be appreciated.

Is it update or insert? DataTable can be saved in database easily using SqlBulkCopy — Sami, Jul 29 '16 at 06:03
@AshaneAlvis. see this http://stackoverflow.com/a/12939934/4873601 — Awais Mahmood, Jul 29 '16 at 06:13
@AwaisMahmood I wanted to know that if we can achieve this using a List of objects Like `List ` — Ashane Alvis, Jul 29 '16 at 06:17
@Paarth I normally did like that. Wanted to try a new method. — Ashane Alvis, Jul 29 '16 at 06:17
@AshaneAlvis. so u wanted to pass the List to the method and you want that method to create the insert command dynamically?? — Awais Mahmood, Jul 29 '16 at 06:21
http://stackoverflow.com/questions/10405373/insert-entire-datatable-into-database-at-once-instead-of-row-by-row — Sami, Jul 29 '16 at 06:34
You should use Table-Valued Parameters ( TVP ). Please see my answer to [Pass Dictionary to Stored Procedure T-SQL](http://stackoverflow.com/a/25815939/577765) for an example. — Solomon Rutzky, Jul 29 '16 at 23:24

Awais Mahmood · Accepted Answer · 2016-07-29T07:09:32.067

At the base you have to make an SQL insert command to insert into DB. So, you could try the following. This method will get the list and table name as a parameter. Inside the loop reflection is used to insert the values in key value pair list.

public static bool Insert(List<YourModel> datas, string table)
{
    bool result = false;
    List<KeyValuePair<string, string>> values = new List<KeyValuePair<string, string>>();

    SqlConnection con = new SqlConnection("your connection string");
    con.Open();

    try
    {
        foreach (var data in datas)
        {

            values.Clear();
            foreach (var item in data.GetType().GetProperties())
            {          
                 values.Add(new KeyValuePair<string, string>(item.Name, item.GetValue(data).ToString()));
            }

            string xQry = getInsertCommand(table, values);
            SqlCommand cmdi = new SqlCommand(xQry, con);
            cmdi.ExecuteNonQuery();
        }
        result = true;
    }
    catch(Exception ex)
    { throw ex; }
    finally { con.Close(); }
    return result;
}

In the following method the key value pair list will be passed to another function to make the insert command.

private static string getInsertCommand(string table, List<KeyValuePair<string, string>> values)
{
    string query = null;
    query += "INSERT INTO " + table + " ( ";
    foreach (var item in values)
    {
        query += item.Key;
        query += ", ";
    }
    query = query.Remove(query.Length - 2, 2);
    query += ") VALUES ( ";
    foreach (var item in values)
    {
        if (item.Key.GetType().Name == "System.Int") // or any other numerics
        {
            query += item.Value;
        }
        else
        {
            query += "'";
            query += item.Value;
            query += "'";
        }
        query += ", ";
    }
    query = query.Remove(query.Length - 2, 2);
    query += ")";
    return query;
}

1 Answers1