Object Orienting -> proper methods

[detlion1643]

I'm still learning the proper ways of OOP, and making things easier for other users and myself. With that said, I'd like to run a few ideas/code through the forums here to see if what I am doing is not a memory leak, that the created variables are properly disposed of....

I made a generic base Sql.cs class inside of my framework. In this, I have a method that returns a SqlCommand. The reasoning is because it will pre-populate a couple of parameters and such:

public static SqlCommand NewCmd()
        {   
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Parameters.Add("@appYear", System.Data.SqlDbType.Int).Value = GetYear();
            if ((StudentInfo.AccountID != null) && (StudentInfo.AccountID != ""))
                cmd.Parameters.Add("@studentID", System.Data.SqlDbType.Int).Value = StudentInfo.AccountID;
            return cmd;
        }

As you can see, I create a new SqlCommand using that method and return it. Usage like this:

using(SqlCommand cmd = Sql.NewCmd())
{
    ...
}

As far as I can tell, the SqlCommand created in NewCmd is disposed after the using block that used the NewCmd method.

However, I'm a little stuck on the thinking behind doing this with DataReaders now...
For 1, I wanted to do all the DataReader part in my framework (handling nulls, exceptions, etc), and pass back the results as a Dictionary<int, List<string>>. Dictionary<int part = 1 entry per row. List<string> part = 1 entry per field/column. However, this seemed slower and unreliable when I tested it. I thought it would be easier than having users/myself constantly creating SqlDataReaders, checking for nulls, rows, and looping through. Users/myself could simply do:

Dictionary<int, List<string>> Results = Sql.ExecuteSql(cmd);
for(int i=0;i<Results.Count;i++)
{
    //Results.Keys.ElementAt(i) = keyname, or row number
    //Results[Results.Keys.ElementAt(i)][0] = value of row i, field/column 0
}

Am I thinking of trying to do too much in my framework, to help cut down coding everywhere else and for others? My framework can be used by anyone to make plugins, but 99.9% will be from myself. My clients are not coders or software literate at all... Any suggestions? Ideas?

[detlion1643]

Ah shoot.... I realized I posted this in VB.Net and not in C#. Could an admin/mod please move it for me? I can't seem to report my own thread for it, apologies...

[cicatrix]

If you're building a framework that is supposed to encapsulate all data reading issues in it, why you expose SqlCommands? Let the callers just invoke methods that will handle everything (opening a connection, performing a query, handle all nulls and return just the data). You can balanse the level of abstraction provided by your framework. Ideally, you should provide a 'low level' method that accepts a sql command string and its parameters like:

public DataTable QueryDb(string sqlcommand, Dictionary<string, object> parameters = null)

and also a number of 'high level' methods that wrap QueryDB around and get any specific information (i.e. GetUsers(), or GetProductInfo(), or whatever).

Of course you should also report any problems to the caller. You can do it either by providing a property (LastError or something) or add a parameter, (... , out object OpResult) to the method above.