[EvLSnoopY]

Curtis Rutland, on 15 June 2011 - 12:21 PM, said:

Well, if you just want to take some of the concepts away, I'd suggest that instead of embedding the connection information in the object itself, I'd make a DB context object, that contains your connection string and SQL statements, and give it methods. One for selecting, one for inserting. The one for inserting should take a Class1 (or whatever) kind of object.

Curtis you suggested to the op to create a DB context object.
Is this the same as a context object in the Strategy Design Pattern?

[Curtis Rutland]

I honestly don't know. I didn't study many design patterns.

[EvLSnoopY]

Curtis Rutland, on 21 June 2011 - 12:07 PM, said:

I honestly don't know. I didn't study many design patterns.

Hmm...well if you don't mind, could you give an example of the type of context object you were talking about?

Sorry I don't mean to be a pain. I'm just curious

This post has been edited by EvLSnoopY: 21 June 2011 - 12:11 PM

[Curtis Rutland]

Hmm. Well, let's use my test database as an example.



First, we need an "entity" to map to the table:

public class TestObject {
    public int Id { get; set; }
    public DateTime Created { get; set; }
    public string Value { get; set; }
}

Pretty simple, just has properties that are the same as the table's columns.

Now, we create a context for selecting and inserting these values:

public class TestDbContext {
    private string connStr = "Data Source=HDQITS0V9D;Initial Catalog=Test;Integrated Security=SSPI;";
    private string selectSql = "SELECT Id, Created, Value FROM TestTable";
    private string insertSql = "INSERT INTO TestTable (Created, Value) OUTPUT INSERTED.Id VALUES (@Created, @Value)";

    private SqlConnection conn;
    private SqlCommand select, insert;

    public TestDbContext(){
        conn = new SqlConnection(connStr);
        select = new SqlCommand(selectSql, conn);
    }

    public List<TestObject> GetTestObjects() {
        List<TestObject> list = new List<TestObject>();
        try {
            if(conn.State != ConnectionState.Open)
                conn.Open();
            SqlDataReader reader = select.ExecuteReader();
            while (reader.Read()) {
                list.Add(new TestObject() {
                    Id = (int)reader["Id"],
                    Created = (DateTime)reader["Created"],
                    Value = (string)reader["Value"]
                });
            }
        }
        catch (SqlException exc) {
            //log exception, handle it. For dev purposes, throw it
            throw exc;
        }
        finally {
            if (conn.State != ConnectionState.Closed)
                conn.Close();
        }
        return list;
    }

    public bool InsertTestObject(TestObject obj) {
        insert = new SqlCommand(insertSql, conn);
        insert.Parameters.AddWithValue("@Created", obj.Created);
        insert.Parameters.AddWithValue("@Value", obj.Value);
        try {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            int id = (int)insert.ExecuteScalar();
            obj.Id = id;
        }
        catch (SqlException exc) {
            //log exception, handle it.
            return false;
        }
        finally {
            if (conn.State != ConnectionState.Closed)
                conn.Close();
        }
        return true;
    }
}

This takes a bit of explanation. We're creating the context object with the connection strings, and the sql statements built in, but you could make them constructor parameters.

In the constructor we instantiate our select command, since it'll never change, as well as our connection.

Then we make methods. The first is relatively simple. It executes the select command, retrieves the data, and creates a list of TestObjects populated with that data. It then returns the list.

The next method is to insert a TestObject into the DB. Create a new insert command, since it'll have different parameters each time. Add the parameters (with the values from the provided object we're inserting), then insert the object.

I actually learned a new thing here. In SQL 2005+, you can use the OUTPUT keyword in an INSERT statement to get the auto-id number of an inserted row, if you want. That's why I'm using ExecuteScalar instead of ExecuteNonQuery to perform the insert.

Once we have that value, we assign it to the object passed in (remember, classes are passed by reference, so if we make changes to it's properties, that's actually editing the original, not a copy).

Returning bool is just an easy way to say whether or not it worked.

Now, let's put that to actual use:

static void Main(string[] args) {
    TestDbContext context = new TestDbContext();
    var list = context.GetTestObjects();
    foreach (var o in list) {
        Console.WriteLine("{0}\t{1}", o.Created, o.Value);
    }
    TestObject obj = new TestObject() { Created = DateTime.Now, Value = "From Code" };
    context.InsertTestObject(obj);
    Console.WriteLine(obj.Id);
    list = context.GetTestObjects();
    foreach (var o in list) {
        Console.WriteLine("{0}\t{1}", o.Created, o.Value);
    }
    Console.ReadKey();
}

Pretty simple. Create a new context, use it to get all the objects in the DB. Add one, print it's new auto-id, then refresh the list to prove that the insert worked.

It's a little more work to set this up, but you can see that using it becomes much easier than trying to manage SQL statements each time.

[Curtis Rutland]

Also, I'd like to add while this is nice, at this point, we might as well just use LINQ to SQL, since we use it in a similar way, and it's actually easier to just drag tables onto a design surface than to hand-code entities and contexts.

[EvLSnoopY]

Thanks for the information Curtis, I really appreciate it! I voted you up

[T3hC13h]

Give PetaPoco a look, I think you may find it does exactly what your trying to do with very little fuss (and its also quite fast).