Insert multiple values ​​into a table in C #

Question

I have 98 columns in a table named "molasses_analysis" and I need to insert records using my C # desktop application.
An example of my code is given below.

    string insert_sql = @"insert into molasses_analysis(mo_entry_date, mo_entry_time, mo_code, mo_brix, mo_pol, mo_purity, mo_crtd_by) " +
    " values(@entry_date, @entry_time, @mol_code, @brix, @pol, @purity, @crtd_by)";
    try
        {
         List<SqlParameter> param = new List<SqlParameter>();
         param.Add(new SqlParameter("@entry_date", entry_date));
         param.Add(new SqlParameter("@entry_time", entry_time));
         param.Add(new SqlParameter("@mol_code", mol_code));
         param.Add(new SqlParameter("@brix", brix));
         param.Add(new SqlParameter("@pol", pol));
         param.Add(new SqlParameter("@purity", purity));
         param.Add(new SqlParameter("@crtd_by", crtd_by));
        int inserted_rows = SqlHelper.ExecuteNonQuery(dbConn.sqlConn(),CommandType.Text, insert_sql, param.ToArray());
        }
catch (Exception ex)
      {
        MessageBox.Show("Data not saved!\nError message - "+ex.Message, "Error!!", MessageBoxButtons.OK, MessageBoxIcon.Error);
      }

Here I just used only seven fields / columns, but it would be very harsh and painful to write such code for 98 columns and assign an Sql Parameter for each column. My question is, is there any clean and good code to insert multiple columns using C # code?

Answer 1

The short answer is no; Not like you use local variables to populate each SqlParameter.

, Dictionary ( /), StringBuilder SQL. SqlParameter.

using System.Collections.Generic; // for dictionary
using System.Text; // for stringbuilder

// ...

// create a dictionary then use a literal to make it easier to populate
Dictionary<string, string> data = new Dictionary<string, string>
{
    { "entry_date", "SOMEVALUE1" }, 
    { "entry_time", "SOMEVALUE2" }
    // add more params and values here...
};

// start our query and params list
StringBuilder query = new StringBuilder("YOUR QUERY STARTS HERE");
List<SqlParameter> params = new List<SqlParameter>();

// iterate over each key/value pair, appending to the query and params list
foreach (KeyValuePair<string, string> pair in data) {
    query.Append("@" + pair.Key);
    params.Add(new SqlParameter(pair.Key, pair.Value));
}

. , ; , .

Answer 2

, . -, SQL,

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'molasses_analysis';
//this returns column names and column types

,

List<string> listColNames = new List<string>(); 

sqlInsert

foreach (string item in listColNames) {
    params.Add(new SqlParameter("@" + item, item));
}