Currently I am working on the design of my degree project. Some days ago I began studying LINQ. I found it was interesting and planned to use it in my project but now I am getting confused at some point.
When I add the LINQ to SQL class it auto generates entities classes against each table in database.
Suppose I have two tables in database:
User
Projects
UserProjects (joint table)
and a joint table that represents which user is associated with which project.
LINQ to SQL class auto generates these three classes for me. Now shall I create separate (User and Project) classes as Business Object or use these auto generated entities?
Also, to use database functionality we are required to use 3 tier architecture. Can I directly call LINQ DAL method from my BLL or do I need to create separate DAL which will call a method of the LINQ DAL??
class UserBLL
{
public void saveUser(String username, String password)
{
// here I am calling LINQ DAL from by BLL
UserDataContext db = new UserDataContext();
User u =new User {Username = username, Password = password};
db.user.InsertOnSubmit(u);
db.SubmitChanges();
}
}
Is the above method calling sequence fine?
Linq To SQL is great for single tier design. Not so great for a disconnected model or multi tier environment.
The above code only inserts a single User into the database. If you fire up MSSQL SQL Server Profiler or connect up the log to the output in visual studio. You should see
//Hookup the log to the output in visual studio
using (var db = new UserDataContext()) {
db.Log = Console.Out;
}
INSERT INTO User VALUES (value1, value2, value3,...)
To update the the user your code should look at somthing like
public void UpdateUser(String username, String password, int userId)
{
using (var db = new UserDataContext()) {
//Get Row From Database Marching Id
var user = db.user.Single(p => p.Id = userId);
//Update Values
user.Username = username;
user.Password = password;
//Save To The Database
db.SubmitChanges();
}
}
//Get All Users From Database
public IEnumerable<User> GetAllUsers()
{
using (var db = new UserDataContext()) {
//Get Row From Database Matching Id
var users = from user in db.user
select user;
return users.ToList();
}
}
//To display the data just enumerate through the enumeration that is returned.
var users = BusinessInstance.GetAllUsers();
foreach (var user in users) {
//user is the current item
}
You should make your that you are using your database contract every time you do a unit of work. (because the database context using transaction by default, and this can get ugly, don't bother about performance with constructing the database context!)
Usually when you work with a multi tier environment, you would create a seperate POCO's when passing them over the wire(network).
NCommon is a great abstraction for Linq to Sql, should handle business validation and rules.
Note. Its good practice to hash password values in a database.