sam kokane sam kokane - 1 month ago 8
ASP.NET (C#) Question

How to store column values in particular column in SQL server 2008?

Please find image

Basically I'm mapping the fields . As you can see in

GridView 2
I've selected specific column names .

Let's consider 1st record, i.e.
1 | id | Column0
,For
id
I have selected
Column0
, so In Database I want to store column values 1,2,3,4.. under id column. like below

id


1


2


3


So my question is how do I store column values in particular column where I'm passing column name, column values dynamically ?

Here's a code what I've done , I know its wrong.

protected void Button2_Click(object sender, EventArgs e)
{
DataTable dtMap = new DataTable();
dtMap.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)), new DataColumn("Source", typeof(string)), new DataColumn("Destination", typeof(string)) });

foreach (GridViewRow gr in GridView2.Rows)
{
int id = Convert.ToInt32(GridView2.Rows[gr.RowIndex].Cells[0].Text);
string source = GridView2.Rows[gr.RowIndex].Cells[1].Text;
string destination = ((DropDownList)gr.FindControl("DropDownList1")).SelectedItem.Value;


DataTable dt = new DataTable();
dt = (DataTable)Session["data"];
DataColumn selectedColumn = dt.Columns[destination];
List<object> allValues = dt.AsEnumerable().Select(r => r[selectedColumn]).ToList();

connection();
strsql = "Insert into F3_BC_Product_Mapping_Data (@source) values (@value) ";
cmd = new SqlCommand(strsql, con);
cmd.Parameters.AddWithValue("@source", destination);
cmd.Parameters.AddWithValue("@value", allValues);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

dtMap.Rows.Add(id, source, destination);
}

Answer

I tried something. Check this and let me know please.

            foreach (GridViewRow gr in GridView1.Rows)
            {
                int id = Convert.ToInt32(GridView1.Rows[gr.RowIndex].Cells[0].Text);
                string source = GridView1.Rows[gr.RowIndex].Cells[1].Text;
                string destination = ((DropDownList)gr.FindControl("DropDownList1")).SelectedItem.Value;


                InsertDynamicColumn(source, destination);
            }

here is my InsertDynamicColumn(string source, string destination) method. after i got my DataTable from Session i filtered Datatable based on destination parameter and put result to a new list. the rest is easy. insert value from list to source.

    private void InsertDynamicColumn(string source, string destination)
    {
        //dt from Session
        DataTable dt = new DataTable();
        dt = (DataTable)Session["myData"];

        //list filtered by Column name
        var list = from t in dt.AsEnumerable()
                       select new
                       {
                           colX = t.Field<string>(destination),// "ColX"
                       };

        //new filtered list.
        var listOfColx = list.ToList();


        //loop through list and insert values to source from parameters
        for (int i = 0; i < listOfColx.Count; i++)
        {
            connection();
            strsql = string.Format("Insert into F3_BC_Product_Mapping_Data ({0}) values (@value) ", source);
            cmd = new SqlCommand(strsql, con);
            cmd.Parameters.AddWithValue("@value", listOfColx[i]);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

        }
    }