How to insert data to the database using a list (List<Object>) of objects and modals?

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
– Sami, Commented Jul 29, 2016 at 6:03
@AwaisMahmood I wanted to know that if we can achieve this using a List of objects Like List<Customer> — Ashane Alvis
– Ashane Alvis, Commented Jul 29, 2016 at 6:17

Awais Mahmood · Accepted Answer · 2016-07-29 07:09:32Z

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;
}

Collectives™ on Stack Overflow

How to insert data to the database using a list (List<Object>) of objects and modals?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related