1
Vote

execute reader

description

comments

jmarr wrote Jan 2, 2012 at 3:36 PM

That's a nice approach. I will add this now.

jmarr wrote Jan 2, 2012 at 5:24 PM

Vitidev,

I just added your method and checked in the changes. I did make one small change:
It automatically calls reader.Read() for you, so you will have to modify your existing code so that it does not call reader.Read().
Also, it returns an IEnumerable, so you can use it for one or many results.
(For single results, just add ".FirstOrDefault()". For multiple results, just use ".ToList()" or ".ToArray()".

vitidev wrote Jan 2, 2012 at 7:02 PM

It automatically calls reader.Read()
This is better, but in my use case I was need to load a dictionary. But I think, that is not very large overhead, when at the same time reader fill List. However I can always add the optional parameter "createList" for me

jmarr wrote Jan 2, 2012 at 9:23 PM

I'm not sure of your specific scenario, but maybe you can just use the ToDictionary extension method instead of ToList:
    [TestMethod]
    public void ShouldLoadEntityDictionary()
    {
        StubResultSet rs = new StubResultSet("ID", "Name", "Age");
        rs.AddRow(1, "Person1", 31);
        rs.AddRow(2, "Person2", 32);
        rs.AddRow(3, "Person3", 33);
        var db = CreateDB_ForQuery(rs);

        Dictionary people = db.ExecuteReader("SELECT PersonName FROM tbl WHERE ID=2", LoadPerson).ToDictionary(p => p.ID);

        Assert.AreEqual(3, people.Count);
        Assert.AreEqual(1, people[1].ID);
        Assert.AreEqual(2, people[2].ID);
        Assert.AreEqual(3, people[3].ID);
    }

vitidev wrote Jan 2, 2012 at 10:49 PM

I can't use ToDictionary.

I was tried your implementation of ExecuteReader;

my code was

Dictionary dic = db.ExecuteReader("SELECT id, hash FROM table",
                                    (reader) =>
                                        {
                                            var dict =new Dictionary();
                                            while (reader.Read())
                                                dict.Add(reader.GetString(1),reader.GetInt64(0));
                                            return dict;
                                        });
after your code I was forced to use so:

var dic = new Dictionary();
                db.ExecuteReader("SELECT id, hash FROM table",
                                    (reader) =>
                                        {
                                            dic.Add(reader.GetString(1),reader.GetInt64(0));
                                            return null; // I must return something
                                        });
but your idea is good, thats why I was added method
void ExecuteReader(string sql, Action action)

and as result my code so:
var dic = new Dictionary();
db.ExecuteReader("SELECT id, hash FROM table", (reader) => dic.Add(reader.GetString(1),reader.GetInt64(0)));

vitidev wrote Jan 2, 2012 at 11:07 PM

My scenario:

I import large list of elements and check for existing by hash stored in database, and resolving it's id. To reduce the load hard disk, I load hash list to memory (also I prevent lock database. Sqlite3 lock whole database). And I not need to load 3 millions entities (also the danger of using LOH). Thats why I started to look for ExecuteReader for scalar types. Of course I may use GetDataTable and convert it to Dictionary, but it is waste of memory

jmarr wrote Jan 2, 2012 at 11:49 PM

vitidev:

You should be able to use the current ExecuteReader function to create a dictionary in your scenario by using anonymous objects. See unit test below:
    [TestMethod]
    public void ShouldLoadEntityDictionaryUsingAnonymousObject()
    {
        StubResultSet rs = new StubResultSet("ID", "Hash");
        rs.AddRow(1, "Hash1");
        rs.AddRow(2, "Hash2");
        rs.AddRow(3, "Hash3");
        var db = CreateDB_ForQuery(rs);

        var people = db.ExecuteReader("SELECT ID, Hash FROM tbl",
            r => new { ID = r.GetValue("ID"), Hash = r.GetValue("Hash") })
            .ToDictionary(obj => obj.Hash);

        Assert.AreEqual(3, people.Count);
        Assert.AreEqual(1, people["Hash1"].ID);
        Assert.AreEqual(2, people["Hash2"].ID);
        Assert.AreEqual(3, people["Hash3"].ID);
    }

vitidev wrote Jan 3, 2012 at 9:45 AM

As far as I know, the list as whole object just will be placed in the Large Object Heap (LOH), but not dictionary. I think the better has another method than fill LOH

jmarr wrote Jan 3, 2012 at 2:12 PM

Ahh, that makes sense since you are trying to avoid allocating in the heap.
Here is another solution using the existing overload. This makes use of the KeyValuePair, and eliminates use of anonymous objects. Since this is a struct, it should allocate in the stack:
    [TestMethod]
    public void ShouldLoadEntityDictionary_StackAllocation()
    {
        StubResultSet rs = new StubResultSet("ID", "Hash");
        rs.AddRow(1, "Hash1");
        rs.AddRow(2, "Hash2");
        rs.AddRow(3, "Hash3");
        var db = CreateDB_ForQuery(rs);

        Dictionary people = db.ExecuteReader("SELECT PersonName FROM tbl",
            r => new KeyValuePair(r.GetValue("Hash"), r.GetValue("ID")))
            .ToDictionary(obj => obj.Key, pair => pair.Value);

        Assert.AreEqual(3, people.Count);
        Assert.AreEqual(1, people["Hash1"]);
        Assert.AreEqual(2, people["Hash2"]);
        Assert.AreEqual(3, people["Hash3"]);
    }

jmarr wrote Jan 3, 2012 at 2:26 PM

On second thought, that solution is pretty convoluted!
I think that your void ExecuteDataReader solution is probably the best. Or, using the existing Func based ExecuteDataReader, returning a meaningless value (as you were doing), would at least prevent you from having to modify the source:
        Dictionary people = new Dictionary();
        db.ExecuteReader("SELECT PersonName FROM tbl",
            r => { people.Add(r.GetValue("Hash"), r.GetValue("ID")); return true; });
Alternatively, I suppose it wouldn't hurt anything for me to add in your Action based overload to the code:

void ExecuteReader(string sql, Action action)

jmarr wrote Jan 3, 2012 at 2:43 PM

I added this method, but I renamed it to ExecuteReaderAction, because the overloads were so similar that it messed up the type inference of the original, which made it impossible to use some of the cleaner type inferred syntax.
So unless there is a better name, it is:

public void ExecuteReaderAction(string sql, Action action)
{ ... }

vitidev wrote Jan 3, 2012 at 3:45 PM

Mmm. KeyValuePair is sctructure, and thats why list of KeyValuePair will be allocated int the LOH completely, in contrast to referense types. So it is better to load a dictionary directly.
However in use cases, where not need list as return type, the void variant looks cleaner and slightly faster.
So unless there is a better name, it is:
public void ExecuteReaderAction(string sql, Action action)
This I do not understand.
Based on the principle of least astonishment if we have ExecuteReaderAction method, we then expect ExecuteReaderFunc
Also I used ExecuteReader overloads and I have not felt the problem.
It is normally that signatures different as parameters as return types.
Can be an example?

jmarr wrote Jan 3, 2012 at 4:38 PM

Regarding the method name, I agree that it would be better if both methods were simply called ExecuteReader. You will not noticed the problem at all if you fully qualify the method call by including the "" generic defintion. For example, you will not see a problem if you do this:

Person p = db.ExecuteReader
("SELECT PersonName FROM tbl WHERE ID=2", LoadPerson).FirstOrDefault();

However, I prefer using the inferred generic parameter syntax, which will cause a compile error if both overloads have the same name (note that the
is left out):

Person p = db.ExecuteReader("SELECT PersonName FROM tbl WHERE ID=2", LoadPerson).FirstOrDefault();

Since the inferred generic parameter Func snippet looks so similar to the Action snippet, the compiler doesn't know which method to resolve to.
Perhaps there is a different naming scheme for the two methods that would better differentiate them.

It makes a certain amount of sense to call them ExecuteReaderFunc and ExecuteReaderAction, although I think the Func / Action suffixes are not very clear.

jmarr wrote Jan 3, 2012 at 4:49 PM

What about:

IEnumerable ExecuteReaderResult(string sql, Func func);
void ExecuteReader(string sql, Action action);

jmarr wrote Jan 3, 2012 at 5:02 PM

After some more testing, I found something interesting.
When both overloads are named the same (ExecuteReader), this will cause a compiler error:
Person p = db.ExecuteReader("SELECT PersonName FROM tbl WHERE ID=2", LoadPerson).FirstOrDefault();

But this will not:
Person p = db.ExecuteReader("SELECT PersonName FROM tbl WHERE ID=2",
            r => new Person { ID = r.GetValue("ID"), Name = r.GetValue("Name"), Age = r.GetValue("Age") }).FirstOrDefault();
For some reason, it can not use the separate function to infer the return type of Person.
But given that I can either declare
or use the lambda syntax, I think that gives me enough options that I would be OK with letting them be overloaded methods.

So I think I will modify the offending unit tests and then switch both to "ExecuteReader" for simplicity sake.

vitidev wrote Jan 3, 2012 at 5:56 PM

Omg.
I used overloads in the test code http://pastebin.com/x9jwym7w
No compile error occurred. What is wrong?

jmarr wrote Jan 3, 2012 at 6:59 PM

No compile error is good, right? :)

This line of code should create the compile error I was previously referring to:

//func method
Person obj2 = db.ExecuteReader("SELECT *FROM table", LoadPerson).FirstOrDefault();

vitidev wrote Jan 3, 2012 at 7:18 PM

Person obj = db.ExecuteReader("SELECT *FROM table", (dbReader) =>{return new Person();}).FirstOrDefault();
The return type compiller resolve from lambda

Person obj2 = db.ExecuteReader
("SELECT *FROM table", LoadPerson).FirstOrDefault();
Compiller can't resolve return type from method LoadPerson (this is not Func) thats why we need set generic type in ExecuteReader
For example:
Person obj = db.ExecuteReader
("SELECT *FROM table", (dbReader) =>{return new Person();}).FirstOrDefault(); //
is optional
and
Person obj2 = db.ExecuteReader("SELECT *FROM table", (Func)LoadPerson).FirstOrDefault();

also work.

jmarr wrote Jan 3, 2012 at 9:32 PM

Very interesting.

FYI, the latest version with the two ExecuteReader overloads is uploaded to NuGet.