Loop structure to perist data using addwithValue

Question

My LINQ knowledge is ok. I need a loop or another method to accomplish ADO.NET method (see foreach loop)

public bool AccessProcess(string sp, ListDictionary ld, CommandType cmdType)
{
    SqlConnection con = new SqlConnection(
        WebConfigurationManager.ConnectionStrings["conn"].ToString());
    SqlCommand cmd = new SqlCommand(sp, con);
    try
    {
        con.Open();
        cmd.CommandType = cmdType;
        foreach (string ky in ld.Keys)
        {
            cmd.Parameters.AddWithValue(ky, ld[ky]);
        }
        cmd.ExecuteNonQuery();
    }
    finally
    {
        con.Dispose();
        cmd.Dispose();
    }
    return true;
}

I need:

foreach (string ky in ld.Keys)
    yourObject.SetPropertyValue(ky, ld[ky]);

How can I do that? Can I use LINQ-to-SQL?

Can you give clear sample like

using (var contex = new DataclassContext())
foreach (string ky in ld.Keys)
{
    contex.Parameters.AddWithValue(ky, ld[ky]);
}
contex.SubmitChanges();

Answer 1

Linq to SQL maps data classes (often called an 'Entities') to a SQL database table.

So, first you need to use the Linq->SQL designer to generate these data classes for you, and a DataContext object that will map theses classes to and from the database.

You usually define this mapping in the Linq to SQL designer UI in Visual Studio. This UI let's you visually edit a .dbml file and essentially defines a mapping between your CLR entity objects and the backing SQL database table.

See this MSDN article for an example of how to add a new Linq to SQL class.

Once you have defined this class, you can create a new instance of this class and its property values. These property values are mapped to columns in the SQL database table. You then pass this object to the DataContext class generated by LINQ to SQL, to add a new row to the table in the database.

So, you would do something like this:

For a database table with columns "ColumnA", "ColumnB" and "ColumnC"

var myEntity = new EntityObject { ColumnA = "valueA", ColumnB = "valueB", "ColumnC" = "valueC" };
DataContext.InsertOnSubmit(myEntity);
DataContext.SubmitChanges();

This will insert a new row into the database with the column values specified.

Now if you don't want to manually write code to write to ColumnA, ColumnB, etc, then you could use reflection to write to the appropriate properties on the entity:

For example, with entity instance 'myEntity':

var properties = myEntity.GetType().GetProperties();

foreach (string ky in ld.Keys)
{
    var matchingProperty = properties.Where(p => p.Name.Equals(ky)).FirstOrDefault();
    if (matchingProperty != null)
    {
        matchingProperty.SetValue(myEntity, ld[ky], null);
    }
}

Using reflection doesn't give the best performance, so I would question why you even want to map an array of keys and values to a linq->sql entity, rather than using the entity object directly in the code that currently generates these key/value pairs.