Populate c# datastructure from table or view [modified]

[Michael Pauli]

Hi! There must be a smarter way to do this! This is about if I can bind a simple datastructure to a table. Problem: I want to hold an instance of a (simple) datastructure - say a List or a Collection or similar - I don't care - representing the contents of a view or table - say view. How to impl. that the most natural/generic way? My solution: // Fill DataTable. DataTable dt = new DataTable(); string strConn = "Data Source=MyPC\\SQLExpress; Initial Catalog=MyDBInst; Integrated Security=True; User ID=; Password=;"; SqlConnection con = new SqlConnection(strConn); SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM v_SomeViewOfMine", con); da.Fill(dt); // Populate List. List my_list = new List(); foreach(DataRow dr in dt.Rows) my_list.Add(new TestClass( (string)dr.ItemArray[0], (int)dr.ItemArray[1], (int)dr.ItemArray[2], (int)dr.ItemArray[3])); con.Close(); Where constructor TestClass is: public TestClass(string s, int a, int b, int c) { Name = s; Category = a; Conference = b; SubCategory = c; } This approch works - "all is fine" and list is fine! Or is it? I feel that I miss some binding michanism-thingy like (pseudo): List my_list = new List(); my_list.BindToDataTable(dt); my_list.SetBindingColumn("Name", "Name", Type.string); my_list.SetBindingColumn("Category", "Category", Type.int); my_list.SetBindingColumn("Conference", "Conference", Type.int); my_list.SetBindingColumn("Sub category", "SubCategory", Type.int); Inspired from how one would setup a DataGrid in a xaml (< DataGridTextColumn Header="Name" Binding="{Binding Path=Name}" />). So in other words: Do I have to iterate the DataTable myself (dealing with dr.ItemArray[] typed object) or can I bind it to a datastructure in some way? Thank's in advance.

Michael Mogensen

modified on Saturday, October 30, 2010 8:56 PM

[Karthik A]

You can do something like this instead of looping throught the dataset/SqlDataAdapter. First you can create a class that inherits DataContext. And then you can use methods of DataContext like to ExecuteQuery<> to convert your results in to objects. With this method you can directly use your queries you wrote earlier. Consider the following example. First you need to define a class for interacting with your database

public class DBManagerDataContext : DataContext
{
private static string connectionString = ""; // Your connection string

public static DBManagerDataContext CreateInstance()
{
    return new DBManagerDataContext(connectionString);
}

protected DBManagerDataContext(string connectionString)
    : base(connectionString, new AttributeMappingSource())
{

}

}

Then you can use this context to execute queries and convert them in to objects as shown below:

public class Report
{
public int ReportID;
public string ReportTitle;
public DateTime ReportDate;

private static string query = "select ReportID, ReportTitle, ReportDate from dbo.Reports"; // Your query

public static List<Report> GetReportList()
{
    DBManagerDataContext context = DBManagerDataContext.CreateInstance();
    return context.ExecuteQuery<Report>(query).ToList();
}

}

You can use the method "GetReportList()" given above like this for example:

List<Report> reports = Report.GetReportList();

Please note that the query column names should match the definition in the object This is just an example. You could do something like this, that would serve your purpose. I guess this is what you are asking about...

Cheers, Karthik