score:2

Accepted answer

If you are working with SQL Server, ADO.NET + LINQ-to-SQL is definitely the way to go.

Reasons for LINQ:

  1. It fits naturally with your .NET code, IDE, and debugger
  2. You don't have to maintain your code in one place and your queries in another
  3. You aren't hard-coding SQL queries and you don't have to go to the trouble of creating paramterized SQL (which is really a pain in my opinion)
  4. It is super fast to write and surprisingly flexible
  5. Your code will be cleaner and smaller because you don't have to have ADO.NET and database plumbing everywhere - LINQ takes care of all the hard stuff
  6. You get a nice entity designer that allows you to drag-n-drop from a SQL connection

Reasons Not to Use LINQ:

  1. If you have to squeeze every last ounce of performance out of a query, LINQ may not be the most natural choice because SQL can sometimes be more flexible. But you should really consider your query carefully to see if there is an easier way to do it.
  2. Some places don't like the security implications because the tables have to be public to your connected user. Some companies only allow access to tables via SPROCs.

For your example, you'll want to create classes (POCO's) that represent your database entities. Then you'll use LINQ to query your tables and load data into the new entity objects. I prefer to have a constructor on my domain/entity objects that transform the persistent database objects generated by LINQ into domain-layer objects to be used by the client.

public class Person
{
    public int ID { get; set; }
    public string Surname { get; set; }
    public string Name { get; set; }
    public short Age { get; set; }

    public Person()
    {
    }

    public Person( Persistence.Person src )
    {
      this.ID = src.ID;
      this.Surname = src.surname;
      this.Name = src.name;
      this.Age = src.age;
    }
}

...
public List<Domain.Person> LoadPeople()
{
    using( var context = this.CreateContext() )
    {
        var personQuery = from p in context.Persons
                          select new Domain.Person( p );

        return personQuery.ToList();
    }
}

public Person LoadPerson( int personID )
{
    using( var context = this.CreateContext() )
    {
        var personQuery = from p in context.Persons
                          where p.id == personID
                          select new Domain.Person( p );

        return personQuery.SingleOrDefault();
    }
}

score:1

Assuming you have the following tables:

tblUsers
UserID LName FName GenderID
1      Joe   Chan  1
2      Koh   Wang  2
3      John  Chen  1


tblGenders
GenderID Gender
1        Male
2        Female

You can use this code to get a dataset that would contain the combined information from the two tables above.

        SqlConnection con = new SqlConnection(Connection String for you database);
        con.Open();
        SqlCommand comm = con.CreateCommand();
        comm.CommandText = "SELECT u.UserID, u.Fname, u.Lname, u.GenderID, g.Gender 
                            FROM tblUsers u, tblGenders g 
                            WHERE u.GenderID = g.GenderID";
        SqlDataAdapter da = new SqlDataAdapter(comm);
        DataSet ds = new DataSet();
        da.Fill(ds);

you can access the rows under the table by using this code da.Table[0].Rows;

if you are already knowledgeable with stored procedures you can also use this code:

    internal static List<RoomType> FetchRoomTypeList()
    {
        List<RoomType> roomTypes = new List<RoomType>();
        SqlCommand commRoomTypeSelector = ConnectionManager.MainConnection.CreateCommand();
        commRoomTypeSelector.CommandType = CommandType.StoredProcedure;
        commRoomTypeSelector.CommandText = "Rooms.asp_RMS_RoomTypeList_Select";
        SqlDataAdapter da = new SqlDataAdapter(commRoomTypeSelector);
        DataSet ds = new DataSet();
        da.Fill(ds);
        roomTypes = (from rt in ds.Tables[0].AsEnumerable()
                     select new RoomType
                     {
                         RoomTypeID = rt.Field<int>("RoomTypeID"),
                         RoomTypeName = rt.Field<string>("RoomType"),
                         LastEditDate = rt.Field<DateTime>("LastEditDate"),
                         LastEditUser = rt.Field<string>("LastEditUser"),
                         IsActive = (rt.Field<string>("IsActive") == "Active")
                     }
                    ).ToList();

        return roomTypes;
    }

for more information visit:

http://www.homeandlearn.co.uk/csharp/csharp.html


Related Articles