A Practical way to access datasource

[snakay]

Hi All,

I often tend to fetch data from datasource using DataReader , these are usually a single data or single record of data cases. Within same method I reach to several datasource tables, each using datareader. This is awkward and cumbersome. Is it possible to create a class to which we send "select a,b,c from table1" , "where c=d", "group by a", "order by a,b desc" like;

private void toolStripButton1_Click(object sender, EventArgs e)
 {
     SqlImmediate(sSqlCommand, ifWhere , ifGroupby , ifOrderby, ifBinding)
}

public void SqlImmediate( string selectFrom, string sWhere , string sGroupby , string sOrderby, string listBindingstring )
{
// 1. Instantiate the connection
                OleDbConnection cn = null;
                cn = new OleDbConnection(strConn);
                cn.Open();
            // 2. Pass the connection to a command object
string selectCommand=selectFrom+sWhere+sGroupby+sOrderby
                OleDbCommand myAccessCommand = new OleDbCommand(selectCommand, cn);
               
            // 3. Call Execute reader to get query results
                try
                {
                    OleDbDataReader r = myAccessCommand.ExecuteReader();

                    if (r.Read())
                    {
// here somehow split listBindingstring into elements
                          string sm = r.GetString(2).ToString();
                          //as many as the number of elements parsed from listBindingstring
// here I like to be able to get all the fields binded to my textboxes/variables to be used anyplace in the form


                     }

}

I hope I explained what I mean. This reading with datareader many times for different datasource tables within the same procedure is really a pain on the neck, maybe I'm doing something very naive. Am I on the right track to attack this issue or is there much simpler approaches. I welcome any idea.

Thanks in advance
snky

[kvprajapati]

Take a look at this MSDN article - http://msdn.microsoft.com/en-us/library/ms971481.aspx

[snakay]

Thanks for the article, I've read it. It was a good recap of couple of hundred pages of very subject in question from different sources. I have no problem with the ways things are being done in .Net . I accept their way of approach as long as I try to use C#. No problem of using Dataset and Datareader where appropriate. My question is there is got to be a more practical way of handling the codes when you have to use number of Datareader operation in row, one after another, all fetching just one field or one line of record from varying data tables. All the codes that go with Datareader seems to be to much , multiplied with the number of Datareader you use. My question is, can we warp most of the codes in a class and just define the sql statements with the clauses needed and send it to the class(function) where they be processed, because after all they are routine codes that need not be repeated with every reader.

There seems to be too much code pollution with the way these things work, may be I am doing something wrong because I am not experienced in .Net, therefore I like to be clarified and advised if it is worth searching for a better way or accept the way it is described in these articles and resources.

[sknake]

I create a wrapped class for data access. I usually just load all of the results in DataTable since I don't bring back 1,000,000 records and i'm not concerned with memory management.

Here is a partial post but it shows how I go about it. This wraps all data access for SQL, OleDb, ODBC, and oracle.

private DbConnection GetDbConnection()
    {
      DbConnection result = null;

      switch (_connectionType)
      {
        case ConnectionWrapperType.ODBC:
          result = new OdbcConnection(_connectionString);
          break;
        case ConnectionWrapperType.OleDb:
          result = new OleDbConnection(_connectionString);
          break;
        case ConnectionWrapperType.Oracle:
          result = new OracleConnection(_connectionString);
          break;
        case ConnectionWrapperType.SQL:
          result = new SqlConnection(_connectionString);
          break;
        default:
          throw new InvalidEnumArgumentException("Invalid enumeration member", (int)_connectionType, _connectionType.GetType());
      }
      return result;
    }
    /* -------------------------------------------------------------------- */
    private DbCommand GetDbCommand(string CommandText, List<DataParameter> lst, DbConnection Connection, CommandType CmdType)
    {
      DbCommand result = null;
      switch (_connectionType)
      {
        case ConnectionWrapperType.ODBC:
          result = new OdbcCommand(CommandText, (OdbcConnection)Connection);
          result.CommandTimeout = _commandTimeout;
          result.CommandType = CmdType;
          break;
        case ConnectionWrapperType.OleDb:
          result = new OleDbCommand(CommandText, (OleDbConnection)Connection);
          result.CommandTimeout = _commandTimeout;
          result.CommandType = CmdType;
          break;
        case ConnectionWrapperType.Oracle:
          result = new OracleCommand(CommandText, (OracleConnection)Connection);
          result.CommandTimeout = _commandTimeout;
          result.CommandType = CmdType;
          break;
        case ConnectionWrapperType.SQL:
          result = new SqlCommand(CommandText, (SqlConnection)Connection);
          result.CommandTimeout = _commandTimeout;
          result.CommandType = CmdType;
          break;
        default:
          throw new InvalidEnumArgumentException("Invalid enumeration member", (int)_connectionType, _connectionType.GetType());
      }
      SetParameters(result, lst);
      return result;
    }
    public int Execute(string query, List<DataParameter> parms)
    {
      int result = 0;
      using (DbConnection conn = GetDbConnection())
      {
        conn.Open();
        using (DbCommand cmd = GetDbCommand(query, parms, conn))
        {
          LogQuery(query, parms);
          result = cmd.ExecuteNonQuery();
        }
        conn.Close();
      }
      return result;
    }
    /* -------------------------------------------------------------------- */
    public object QueryScalar(string query, List<DataParameter> parms)
    {
      object result = null;

      using (DbConnection conn = GetDbConnection())
      {
        conn.Open();
        using (DbCommand cmd = GetDbCommand(query, parms, conn))
        {
          LogQuery(query, parms);
          result = cmd.ExecuteScalar();
        }
        conn.Close();
      }
      return result;
    }
    /* -------------------------------------------------------------------- */
    public DataTable QueryDataTable(string query, List<DataParameter> parms)
    {
      DataTable result = null;
      using (DbConnection conn = GetDbConnection())
      {
        conn.Open();
        using (DbCommand cmd = GetDbCommand(query, parms, conn))
        {
          LogQuery(query, parms);
          using (DbDataReader dr = cmd.ExecuteReader())
          {
            result = new DataTable();
            result.Load(dr);
          }
        }
        conn.Close();
      }
      return result;
    }
    #region overloads
    /* -------------------------------------------------------------------- */
    //[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
    public int Execute(string query)
    {
      return Execute(query, new List<DataParameter>());
    }
    //[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
    public int Execute(string query, DataParameter param)
    {
      List<DataParameter> lst = new List<DataParameter>();
      lst.Add(param);
      return Execute(query, lst);
    }
    /* -------------------------------------------------------------------- */
    //[
    //global::System.Diagnostics.DebuggerNonUserCodeAttribute(),
    //global::System.Diagnostics.DebuggerStepThrough()
    //]
    public DataTable QueryDataTable(string query)
    {
      return QueryDataTable(query, new List<DataParameter>());
    }
    //[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
    public DataTable QueryDataTable(string query, DataParameter param)
    {
      List<DataParameter> lst = new List<DataParameter>();
      lst.Add(param);
      return QueryDataTable(query, lst);
    }
    /* -------------------------------------------------------------------- */
    public object QueryScalar(string query)
    {
      return QueryScalar(query, new List<DataParameter>());
    }
    public object QueryScalar(string query, DataParameter param)
    {
      List<DataParameter> lst = new List<DataParameter>();
      lst.Add(param);
      return QueryScalar(query, lst);
    }
    /* -------------------------------------------------------------------- */
    public string QueryStr(string query)
    {
      object result = QueryScalar(query);
      return ((result == null) || (result is System.DBNull)) ? string.Empty : (string)result;
    }
    public string QueryStr(string query, DataParameter param)
    {
      object result = QueryScalar(query, param);
      return ((result == null) || (result is System.DBNull)) ? string.Empty : (string)result;
    }
    public string QueryStr(string query, List<DataParameter> parms)
    {
      object result = QueryScalar(query, parms);
      return ((result == null) || (result is System.DBNull)) ? string.Empty : (string)result;
    }
    /* -------------------------------------------------------------------- */
    public DateTime QueryDateTime(string query)
    {
      object result = QueryScalar(query);
      return ((result == null) || (result is System.DBNull)) ? DateTime.MinValue : (DateTime)result;
    }
    public DateTime QueryDateTime(string query, DataParameter param)
    {
      object result = QueryScalar(query, param);
      return ((result == null) || (result is System.DBNull)) ? DateTime.MinValue : (DateTime)result;
    }
    public DateTime QueryDateTime(string query, List<DataParameter> parms)
    {
      object result = QueryScalar(query, parms);
      return ((result == null) || (result is System.DBNull)) ? DateTime.MinValue : (DateTime)result;
    }
    /* -------------------------------------------------------------------- */
    public DateTime? QueryDateTimeNullable(string query)
    {
      object result = QueryScalar(query);
      return ((result == null) || (result is System.DBNull)) ? null : (DateTime?)result;
    }
    public DateTime? QueryDateTimeNullable(string query, DataParameter param)
    {
      object result = QueryScalar(query, param);
      return ((result == null) || (result is System.DBNull)) ? null : (DateTime?)result;
    }
    public DateTime? QueryDateTimeNullable(string query, List<DataParameter> parms)
    {
      object result = QueryScalar(query, parms);
      return ((result == null) || (result is System.DBNull)) ? null : (DateTime?)result;
    }

Then calling it:

_dtAlarm = SQL.Main.QueryDataTable(query, lst);
      using (DataTable dt = SQL.Main.QueryDataTable(query))
      {
      SQL.Main.Execute(@"DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS");
      SQL.Main.Execute(@"DBCC CHECKDB WITH NO_INFOMSGS");

You can add another method & overloads for returning a DataReader and set the CommandBehavior to close the connection when the DataReader is closed.

[snakay]

Hi sknake,

That is a lot of good stuff you prepared. It sounds I might use them to accomplish what I desire to do. But let me try to understand and digest what you are doing. I will try to apply your work to my particular project and get back to you asap.

thanks

snky

[snakay]

Frankly speaking, I am having difficulties in relating and coordinating the sample code.To start with Dbconnection and Dbcommand, how do I associate them with the main program where I call those classes. My case is just OleDb, so relating those objects representing different functions (like connection,command and parameters) and automating data access. Can I have a simple sample showing interrelation of all the parts (just for OleDb to keep it less crowded).
Thanks
snky

[snakay]

This is how I solved it;

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace project1{
    public class DataAccess
    {
        OleDbConnection conn = new OleDbConnection(
            @"Provider=IBMDADB2;Database=pen2009;PROTOCOL=TCPIP;HOSTNAME=boss;PORT=50000;uid=db2admin;pwd=db2admin");
            

        public OleDbDataReader reader(string command)
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandText = command;
            cmd.Connection = conn;
            OleDbDataReader re = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return re;
        }
//here you can add other dataaccess methods like ExecuteScalar, ExecuteNonQuery,Datatable etc.
}
}

and in the main form;

private void dfTextBox1_Validated(object sender, EventArgs e)
        {

  string strCom = "select id, surname||\' \'||lastname from per015_ssk where code='" + dfCode.Text + "'";
               
                try
                { 
                    // 1. INİTİALİZE A VARİABLE OF TYPE DATAACCESS CLASS WE CREATED
                    DataAccess da_reader = new DataAccess();

                    OleDbDataReader r  = da_reader.reader(strCom);
                    if (r.Read())
                    {
                       
                        //Display in TextBoxes using Column Name or Ordinals
                        dfTextBox1.Text = r.GetString(0).ToString();
                     
                        // close the reader
                      r.Close();
                    }
}}

snky