Generally my solution is "Dont". A good ORM like NHibernate or EntityFramework enables a lot of complex behavior which you will need and would be very labor intensive to recode. NHibernate is nowhere near as scary to configure as anyone makes it out to
be.
I have an incredibly simplistic DAL which I use when my requirements are minimal and I'm shipping code into an environment where isolation is key. Simply automatically populating parameters and filling strongly typed results covers nearly 400 lines of code.
Making the system do lazy loading or caching or anything is simply too much code to be worth it where I want to use this.
I'm going to tell you to never use this but here it is:
@Vipindas
Thanks heaps - but that is exactly what I was not looking for - thanks for posting though
@Horizon_Net
Thanks heaps but that was a double link to vipindas post - thanks for post anyway
@JeffreyABecker
Thanks heaps - i will have a look at those - this is what i dont understand -
microsoft knows that people will need a DAL layer - why cant they make a specific one?
(yes i hear the cries of oledb...)
Microsoft does provide a higher level Data Access Layer, several in-fact. EntityFramework is the newest, most feature-ful and very similar to NHibernate. LinqToSql is an older, no-longer developing framework which is widely deployed. Technically typed
data sets achieve most of the ends people would need from a DAL as well. My personal opinion is that they all suck, lack features I need or just have suprising corner-cases which I run into really fast. ADO.Net was designed as a very rudimentry, highly abstract
data access library. It's job is to give you the tools to write a better component but people just wrote directly against it anyway.
NHibernate is an open source project which is mature (8 years old iirc), widely deployed, well supported, well documented and has features which are very important for the kinds of applications I tend to write. The biggest criticism is that it's got about
a billion features many of which overlap so the learning curve can be high.
If the answer I provided is useful or informative please check the "answer" button.
Warning: Code is often uncompiled and possibly started life written on the back of a napkin. Beware typos.
// use space seperator for @Name and their value like @name !! chaman
for (int i = 0; i <= parameter.GetUpperBound(0); i++)
{
if (parameter[i, 1] == null || parameter[i, 1] == "" || parameter[i, 1] == " ")
{
SPIName = parameter[i, 0].ToString().Replace("'", "''");
SPIValue = null;
}
else
{
// Spilt Name and value of SP
one to the biggest mistakes, my very biased opinion, that programmers make is to have simplistic database queries buried in their DAL.
imho, generally, the idea of the traditional DAL needs to be abandoned by the c# programmer world ... c# guru and LINQPad 4* creator Joe Albahari has this to say:
also, the Model in ASP.NET MVC is where imho your database connectivity belongs:
http://www.asp.net/mvc (some programmers put their DAL in the Model folder, so using MVC does not automatically mean that you will not be using a DAL).
read this:
http://wekeroad.com/2009/04/22/i-spose-ill-just-say-it-you-should-learn-mvc/ ...
testability should be a concern and imho a traditional DAL is less testable
by the c# programmer than LINQ. (disclaimer, i've not done thorough research on this
testability idea of LINQ vs. DAL, so i can not back it up with facts, yet).
please take a look at
JeffreyABecker's response to your question. ORM's like Entity Framework 4.2, NHibernate, Rob Conery's SubSonic, et cetera, are a far better way to access databases imho ... i'm an EF 4.2 so I can not say much about the other ORMs ... i do agree with
JeffreyABecker's general statement about lack of features ... so the big question is which product is the
right choice ... the answer appears to be TIMTOWTDI, i.e.,
there is more than one way to do it.
TOOLS ... get yourself a FREE copy of LINQPad 4 (http://linqpad.net) .. Joe Albahari's LINQPad 4 is an awesome programmer's scratch pad that enables you to quickly check out code snippets you find in books and on web sites
plus LINQPad also lets you rapidly experiment with c# ideas before committing them to vs2010.
LINQPad 4 supports c#, F#, SQL, vb.
B-) Please help me by completing my school survey about computer programmers on my website. Thank you!!! Gerry Lowry +1 705-429-7550 wasaga beach, ontario, canada
WPHC
Member
296 Points
506 Posts
C# and DAL concept
Feb 21, 2012 10:31 AM|LINK
To whom it may concern.
Thank you all for taking the time to read and possibly respond to this post.
Does anyone have any examples of hand written DAL or tutorials? in c#?
Thanks heaps
Vipindas
Contributor
5514 Points
810 Posts
Re: C# and DAL concept
Feb 21, 2012 10:52 AM|LINK
http://www.asp.net/web-forms/tutorials/data-access/introduction/creating-a-data-access-layer-cs
Horizon_Net
Star
8277 Points
1435 Posts
Re: C# and DAL concept
Feb 21, 2012 01:03 PM|LINK
Hi,
you can also refer to this MSDN article - http://msdn.microsoft.com/en-us/library/aa581776.aspx
If my post solves your problem, please mark as answer.
JeffreyABeck...
All-Star
16423 Points
3329 Posts
Re: C# and DAL concept
Feb 21, 2012 04:02 PM|LINK
Generally my solution is "Dont". A good ORM like NHibernate or EntityFramework enables a lot of complex behavior which you will need and would be very labor intensive to recode. NHibernate is nowhere near as scary to configure as anyone makes it out to be.
I have an incredibly simplistic DAL which I use when my requirements are minimal and I'm shipping code into an environment where isolation is key. Simply automatically populating parameters and filling strongly typed results covers nearly 400 lines of code. Making the system do lazy loading or caching or anything is simply too much code to be worth it where I want to use this.
I'm going to tell you to never use this but here it is:
http://pastebin.com/z7EHSGpS
Warning: Code is often uncompiled and possibly started life written on the back of a napkin. Beware typos.
WPHC
Member
296 Points
506 Posts
Re: C# and DAL concept
Feb 24, 2012 03:41 AM|LINK
@Vipindas
Thanks heaps - but that is exactly what I was not looking for - thanks for posting though
@Horizon_Net
Thanks heaps but that was a double link to vipindas post - thanks for post anyway
@JeffreyABecker
Thanks heaps - i will have a look at those - this is what i dont understand -
microsoft knows that people will need a DAL layer - why cant they make a specific one?
(yes i hear the cries of oledb...)
JeffreyABeck...
All-Star
16423 Points
3329 Posts
Re: C# and DAL concept
Feb 24, 2012 02:16 PM|LINK
Microsoft does provide a higher level Data Access Layer, several in-fact. EntityFramework is the newest, most feature-ful and very similar to NHibernate. LinqToSql is an older, no-longer developing framework which is widely deployed. Technically typed data sets achieve most of the ends people would need from a DAL as well. My personal opinion is that they all suck, lack features I need or just have suprising corner-cases which I run into really fast. ADO.Net was designed as a very rudimentry, highly abstract data access library. It's job is to give you the tools to write a better component but people just wrote directly against it anyway.
NHibernate is an open source project which is mature (8 years old iirc), widely deployed, well supported, well documented and has features which are very important for the kinds of applications I tend to write. The biggest criticism is that it's got about a billion features many of which overlap so the learning curve can be high.
Warning: Code is often uncompiled and possibly started life written on the back of a napkin. Beware typos.
tej narayan
Member
194 Points
69 Posts
Re: C# and DAL concept
Feb 25, 2012 01:50 AM|LINK
//for select queries
public DataTable Execute_DTSPQuery(string constr, int QueryType, string SPName, string[,] parameter)
{
SqlConnection Conn;
Conn = new SqlConnection(constr);
if (Conn.State != ConnectionState.Open)
{
Conn.Open();
}
DataTable dt = new DataTable();
SqlDataAdapter da;
string SPIName = "";
string SPIValue = "";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = SPName;
EnumQueryType QType = EnumQueryType.SP;
if (QueryType == Convert.ToInt32(QType))
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
// use space seperator for @Name and their value like @name !! chaman
for (int i = 0; i <= parameter.GetUpperBound(0); i++)
{
if (parameter[i, 1] == null || parameter[i, 1] == "" || parameter[i, 1] == " ")
{
SPIName = parameter[i, 0].ToString().Replace("'", "''");
SPIValue = null;
}
else
{
// Spilt Name and value of SP
SPIName = parameter[i, 0].ToString().Replace("'", "''");
SPIValue = parameter[i, 1].ToString().Replace("'", "''");
}
cmd.Parameters.AddWithValue(SPIName, SPIValue);
}
cmd.Connection = Conn;
da = new SqlDataAdapter(cmd);
da.SelectCommand.CommandTimeout = 0;
da.Fill(dt);
if (Conn.State != ConnectionState.Closed)
{
Conn.Close();
}
return dt;
}
//for insert or update
public static int operation(string storedprocedurename, params SqlParameter[] parameter)
{
cmd = new SqlCommand(spname, con);
cmd.CommandText = spname;
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
if (parameter != null)
{
foreach (SqlParameter item in parameter)
{
cmd.Parameters.Add(item);
}
}
SqlParameter p = new SqlParameter("@error", DbType.Int16);
p.Direction = ParameterDirection.Output;
cmd.Parameters.Add(p);
try
{
cmd.ExecuteNonQuery();
}
catch (Exception)
{
}
finally
{
cmd = null;
con.Close();
}
return (int)p.Value;
}
gerrylowry
All-Star
20515 Points
5713 Posts
Re: C# and DAL concept
Feb 25, 2012 03:26 PM|LINK
@ WPHC
one to the biggest mistakes, my very biased opinion, that programmers make is to have simplistic database queries buried in their DAL.
imho, generally, the idea of the traditional DAL needs to be abandoned by the c# programmer world ... c# guru and LINQPad 4* creator Joe Albahari has this to say:
http://www.linqpad.net/WhyLINQBeatsSQL.aspx "Why LINQ beats SQL"
also, the Model in ASP.NET MVC is where imho your database connectivity belongs: http://www.asp.net/mvc (some programmers put their DAL in the Model folder, so using MVC does not automatically mean that you will not be using a DAL).
read this: http://wekeroad.com/2009/04/22/i-spose-ill-just-say-it-you-should-learn-mvc/ ... testability should be a concern and imho a traditional DAL is less testable by the c# programmer than LINQ. (disclaimer, i've not done thorough research on this testability idea of LINQ vs. DAL, so i can not back it up with facts, yet).
please take a look at JeffreyABecker's response to your question. ORM's like Entity Framework 4.2, NHibernate, Rob Conery's SubSonic, et cetera, are a far better way to access databases imho ... i'm an EF 4.2 so I can not say much about the other ORMs ... i do agree with JeffreyABecker's general statement about lack of features ... so the big question is which product is the right choice ... the answer appears to be TIMTOWTDI, i.e., there is more than one way to do it.
see http://msdn.microsoft.com/en-us/data/aa937723 "The ADO.NET Entity Framework..." home page.
EF 4.1 added "Code First": http://blogs.msdn.com/b/adonet/archive/2011/09/28/ef-4-2-code-first-walkthrough.aspx
imho, worth your time:
2011: http://ayende.com/blog/137217/nhibernate-vs-entity-framework-usage
2010: http://ayende.com/blog/4351/nhibernate-vs-entity-framework-4-0
2010: http://ayende.com/blog/4379/what-is-the-story-behind-the-entity-framework-vs-nhibernate-posts
2010: http://ayende.com/blog/4387/what-happens-behind-the-scenes-nhibernate-linq-to-sql-entity-framework-scenario-analysis
regards ~~ gerry (lowry)
* if you do not have LINQPad 4:
TOOLS ... get yourself a FREE copy of LINQPad 4 (http://linqpad.net) .. Joe Albahari's LINQPad 4 is an awesome programmer's scratch pad that enables you to quickly check out code snippets you find in books and on web sites plus LINQPad also lets you rapidly experiment with c# ideas before committing them to vs2010.
LINQPad 4 supports c#, F#, SQL, vb.
srinanthuram
Contributor
6800 Points
1549 Posts
Re: C# and DAL concept
Feb 25, 2012 07:17 PM|LINK
hi
http://www.csharphelp.com/2007/03/data-access-layer-based-on-datasets/
thank u