CODEWITHSUNDEEP
c#sql
vir
vir

Reputation: 1091

List(T) from sql db table

I want to create List(T) from sql db table

let's say i have one table

ID    Name  
1     xyz   
2     efd
3     abc
4     pqr

i want to some code in C# who will read this db table data and write following lines in my c# class or / notepad or whatever...

List<ItemViewModel> Items= new List<ItemViewModel>();
   Items.Add(new ItemViewModel() { id= 1, name= "xyz"}
   Items.Add(new ItemViewModel() { id= 2, name= "efd"}
   Items.Add(new ItemViewModel() { id= 3, name= "abc"}
   Items.Add(new ItemViewModel() { id= 4, name= "pqr"}

thanks in advance

Upvotes: 0

Views: 2997

Answers (4)

Marc Gravell
Marc Gravell

Reputation: 1062745

Add "dapper" to your project (available on NuGet), then:

var list = connection.Query<YourType>("select * from TableName").ToList();

Or for a parameterless query:

var region = "South";
var list = connection.Query<YourType>(
    "select * from TableName where Region=@region", new { region });

Upvotes: 2

Anders Abel
Anders Abel

Reputation: 69260

If you just want a list populated with whatever data is currently in the database table, you can just do a simple query. You don't have to involve code generation.

Using linq-to-sql to read the contents of the table and create an ItemViewModel for each entry:

using(var context = new MyLinqDbContext())
{
    var items = (from i in context.MyTable
                 select new ItemViewModel { id = ID, name = Name })
                .ToList();
}

Upvotes: 1

Amen Ayach
Amen Ayach

Reputation: 4348

Here one of best code that you can got, the following method can deal with any data classes and system defined types :

public List<T> ExecuteQuery<T>(string s, SqlConnection condb, params SqlParameter[] Params)
{
    List<T> res = new List<T>();
    string er = "";
    SqlDataReader r = null;
    try {
        if (condb == null)
            throw new Exception("Connection is NULL");
        if (string.IsNullOrEmpty(s))
            throw new Exception("The query string is empty");
        using (SqlCommand cm = new SqlCommand(s, condb)) {
            if (Params.Length > 0) {
                cm.Parameters.AddRange(Params);
            }
            if (cm.Connection.State != ConnectionState.Open)
                cm.Connection.Open();
            r = cm.ExecuteReader;

            object prps = typeof(T).GetProperties;
            object prpNames = prps.Select((System.Object f) => f.Name).ToList;

            if (r.HasRows) {
                while (r.Read) {
                    if (typeof(T).FullName.Contains("System.")) {
                        res.Add(r(0));
                    // Classes
                    } else {
                        object c = Activator.CreateInstance(typeof(T));
                        for (j = 0; j <= r.FieldCount - 1; j++) {
                            object jj = j;
                            //er = dt.Rows(jj)("ColumnName").ToLower
                            object fname = r.GetName(j).ToString;
                            er = fname;
                            object fType = r.GetProviderSpecificFieldType(j).ToString.ToLower;
                            object p = prps.Where((System.Object f) => f.Name.Trim.ToLower == fname.ToLower).ToList;
                            if (p.Count > 0) {
                                //Date or DateTime
                                if (fType.Contains("date")) {
                                    if (!p(0).PropertyType.FullName.ToLower.Contains("system.nullable") && (r(fname) == null || r(fname).Equals(System.DBNull.Value))) {
                                        p(0).SetValue(c, Now, null);
                                    } else {
                                        if (!(p(0).PropertyType.FullName.ToLower.Contains("system.nullable") && (r(fname) == null || r(fname).Equals(System.DBNull.Value)))) {
                                            p(0).SetValue(c, r(fname), null);
                                        }
                                    }
                                //String
                                } else if (fType.Contains("string")) {
                                    if (r(fname) == null || r(fname).Equals(System.DBNull.Value)) {
                                        p(0).SetValue(c, "", null);
                                    } else {
                                        p(0).SetValue(c, r(fname), null);
                                    }
                                } else {
                                    if (!(p(0).PropertyType.FullName.ToLower.Contains("system.nullable") && (r(fname) == null || r(fname).Equals(System.DBNull.Value)))) {
                                        p(0).SetValue(c, r(fname), null);
                                    }
                                }
                            }
                        }
                        res.Add(c);
                    }
                }
            }
            r.Close();

        }
    //If cm IsNot Nothing Then
    //    'cm.Connection.Close()
    //    cm.Dispose()
    //End If

    } catch (Exception ex) {
        if (r != null && r.IsClosed == false)
            r.Close();
        throw ex;
    }
    return res;
        }

Usage :

var data = ExecuteQuery<ItemViewModel>("SELECT [ID], [Name] FROM [ItemViewTable]",
                                       new SqlConnection("SomeConnectionString"));

Upvotes: 1

Olli
Olli

Reputation: 2135

If you want C# code generated which is being created from database values and compiled into your solution, you want to use Microsofts text templating engine (T4). To get a hold of this technique, you can read up on it in detail in this blog entry.

If you understand the basics of T4, you can read up this blog, there's an example of how to dynamically create Enum classes for static lookup tables which are stored in a database. Starting from this code, you can write your own code generation template which creates the classes you need.

Upvotes: -1

Related Questions