yet another Data Access Layer ( c# .net 2.0)

Question

Realised I am writing yet another code generator to do the sps/classes/interfaces etc for a simple ORM layer. This time most of the guts are in SQL. I have a general-purpose C# DAL for calling sps/getting results etc. (took me all of an hour or so and is tiny).

I thought for sure there'd be an easier way by now... is there?

I am confident/competent with SQL and use stored procs etc - I'm not looking to hide from SQL, just take the boring repetition out of coding for populating/persisting objects. I'm not into learning a templating language/complex app, or apps that produce mega-bloatware (or build on bloaty MS libraries). Also must be able to start with an existing database.

Is it still a case of roll-your-own? Really?

.Net 2.0 (Winforms)

EDIT: I am not entirely anti-template, if it's really simple/quick to pick up. Ideally, the solution would be small, free and unscary to other developers.

Answer 1

I like nHibernate, I am very proficient in SQL; however, I like not having to write a minimum of four procs for each table. For things which I can't figure out how to make nHibernate do.

You do have a lot of code generators out there but if you don't want to learn how to build a template, and aren't satisfied with the built / community templates then your only other option would be roll your own.

Answer 2

Take a look at SubSonic.

Answer 3

Not directly answering you question so.

Read this great post by Ayende: 25 Reasons Not To Write Your Own Object Relational Mapper

A recommend using NHibernate.

Answer 4

http://stackoverflow.com/questions/672020/simple-crud-generator-for-c

Answer 5

Take a look at BLToolkit:

[TestFixture]
public class ExecuteObject
{
    public abstract class PersonAccessor : DataAccessor<Person>
    {
        // Here we explicitly specify a stored procedure name.
        //
        [SprocName("Person_SelectByKey")]
        public abstract Person GetByID(int @id);

        // SQL query text.
        //
        [SqlQuery("SELECT * FROM Person WHERE PersonID = @id")]
        public abstract Person GetPersonByID(int @id);

        // Specify action name.
        // Stored procedure name is generated based on convention
        // defined by DataAccessor.GetDefaultSpName method.
        //
        [ActionName("SelectByName")]
        public abstract Person GetPersonByName(string @firstName, string @lastName);

        // By default method name defines an action name
        // which is converted to a stored procedure name.
        // Default conversion rule is ObjectName_MethodName.
        // This method calls the Person_SelectByName stored procedure.
        //
        public abstract Person SelectByName(string @firstName, string @lastName);
    }

    [Test]
    public void Test()
    {
        PersonAccessor pa = DataAccessor.CreateInstance<PersonAccessor>();

        // ExecuteObject.
        //
        Assert.IsNotNull(pa.GetByID        (1));
        Assert.IsNotNull(pa.GetPersonByID  (2));
        Assert.IsNotNull(pa.GetPersonByName("Tester", "Testerson"));
        Assert.IsNotNull(pa.SelectByName   ("Tester", "Testerson"));
    }
}

Answer 6

Thanks guys - I will look into the options you suggested.