It don't get any data from DataReader

Question

i work with N tiers tec in C# for ado, trying to make it easy to use and capable to change any database kind with out write all the cod all over again , my code here doesn't get any error but it doesn't get any values to my textbox
(i am trying to get data from table to many textboxs to update it later) and here how code works:{ at first i make some functions to take any set any kind of parameters or set any command and then i make other function to to execute what ever i set or get from database all that Function i build it in folder name (Data Access Layer) then i made other folder (Data Build layer)to take use all those function for what ever i want to do in any page (insert , update , delete , Select), the last think i do it to call the function i made at at (Data Build layer) to my page or control , i do all that because if i Change the database Type ,i change only one class and other classes still the same i hope i explain enough (sorry for my English not good enough)}

Code :

Class DataAccessLayer

public static void Setcommand (SqlCommand cmd,CommandType type,string commandtext)
{
    cmd.CommandType=type;
    cmd.CommandText=commandtext;

}



public static void AddSQLparameter(SqlCommand cmd, int size,SqlDbType type,object value,string paramName,ParameterDirection direction)
{
    if (cmd == null)
{
     throw (new ArgumentException("cmd"));
}

    if (paramName == null)
{
     throw (new ArgumentException("paramName"));
}
    SqlParameter param=new SqlParameter();
    param.ParameterName= paramName;
    param.SqlDbType=type;
    param.Size=size;
    param.Value=value;
    param.Direction=direction;
    cmd.Parameters.Add(param);
}



public static SqlDataReader ExecuteSelectCommand(SqlCommand cmd)
{
    if (cmd == null)
    {
        throw (new ArgumentNullException("cmd"));
    }
    SqlConnection con = new SqlConnection();
    cmd.Connection = con;
    con.Open();
    SqlDataReader dr = cmd.ExecuteReader();
    con.Close();        
    return dr ;

}

Class DatabuildLayer

SqlCommand com;
public DatabuildLayer()
{
  com = new SqlCommand();
  //
  // TODO: Add constructor logic here
  //
}
public SqlDataReader SelectCatalog(int catid)
{
   DataAccessLayer.Setcommand(com, CommandType.Text, "select    catname,catdescription,photo from category where catid=@catid" );
     DataAccessLayer.addSQLparameter(com,16,SqlDbType.Int,catid,"@catid",ParameterDirection.Input);

    return DataAccessLayer.ExecuteSelectCommand(com);;
}

and here my last code that retrieve my data to some textbox

in my Pageload :

 protected void Page_Load(object sender, EventArgs e)
    {
        DatabuildLayer= new DatabuildLayer();
        SqlDataReader dr ;
         dr = obj.SelectCatalog(catselectddl.SelectedIndex);
         if (dr.Read())
         {
             catnametxt.Text = dr["catname"].ToString();
             catdestxt.Text = dr["catdescription"].ToString();
         }
    }

Your code is terrible. I recommend you to start by utilizing The Data Access Application Block: http://msdn.microsoft.com/en-us/library/ff664408%28v=PandP.50%29.aspx — Registered User, Jun 02 '12 at 00:12
Wherever i read `DBHelper` or similar: http://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren/9707060#9707060 Btw, you should always close connections (use `using-statement`) — Tim Schmelter, Jun 02 '12 at 00:18
I think you're really complicating this, and I can help you get data from the data reader, but I have to ask. You mentioned you wanted to be able to swap in and out database kinds without writing any code - but what is the value of "com" and where is it set? I ask this because the select statement itself is only compatible across certain database engines and furthermore only if the statement is VERY simple. If we can simplify this based on my assumption we will make your life a lot easier. — Mike Perrenoud, Jun 02 '12 at 00:18
What is being returned by `dr.Read()`? Are you sure the query actually returns something? Maybe you just don't have records that match your query... — Ivo, Jun 02 '12 at 00:35
Sorry the Code and explanation Got fixed please get another look and tell me how my Work, this is my First Website i build (shopping Cart) — Marwan, Jun 02 '12 at 01:26

score 1 · Answer 1 · answered Jun 02 '12 at 00:45

Is it possible that the query is returning nothing, and dr.Read() is returning false? Assuming the code actually executes (it is hard to tell from here) that is probably the only thing that would stop it working - either that or empty columns.

For what it is worth I think that your code needs to be tidied up a bit from a structural and conventions point of view. You should probably look through your code and consider the naming guidelines for the .NET framework. When others read your code they will want it formatted and consistent with this documentation. http://msdn.microsoft.com/en-us/library/xzf533w0(v=vs.71).aspx

Further, most people doing ASP.NET these days try to look for some way to inject external dependencies (such as databases) into their code using a framework like WebFormsMVP available at http://webformsmvp.com/ in conjunction with an IoC container like autofac available at http://code.google.com/p/autofac/.

Using this approach you can push all external dependencies out of your application behind interfaces which would make it fairly trivial to plug in a different database engine.

score 1 · Answer 2 · answered Jun 02 '12 at 06:22

Your current wrapper code is not doing anything particularly useful (just subsituting the existing methods or your own tht do the same thing), and it is not closing the connections correctly. It is... a bit of a mess.

If you aren't already massively familiar with the raw ADO.NET interfaces, then maybe consider something like "dapper" which will do all this for you, with a sane API:

short catid = 16;
using(var conn = GetOpenConnection()) {
    var row = conn.Query(
        "select catname,catdescription,photo from category where catid=@catid",
        new { catid }).FirstOrDefault();
    if(row != null) {
        string name = row.catname, desc = row.catdescription;
        // ...
    }
}

Or if you have a class with CatName / CatDescription properties:

    var obj = conn.Query<Catalogue>(
        "select catname,catdescription,photo from category where catid=@catid",
        new { catid }).FirstOrDefault();

sorry i didn't understand your code well,in which layer i should right that code ? (i am really sorry if i make that really hard on you but i trying to get experience from my on ) — Marwan, Jun 02 '12 at 12:00
@Marwan any layer you want. Just note that it is using a library to provide the extension method (Query) — Marc Gravell, Jun 02 '12 at 14:41

score 0 · Answer 3 · answered Jun 02 '12 at 02:49

from my experience, when you close a connection associated with a DataReader, nothing can be retrieved from the reader anymore.

//You closed the connection before returning the dr in the your method below:
public static SqlDataReader ExecuteSelectCommand(SqlCommand cmd)
{
   if (cmd == null)
   {
       throw (new ArgumentNullException("cmd"));
   }
   SqlConnection con = new SqlConnection();
   cmd.Connection = con;
   con.Open();
   SqlDataReader dr = cmd.ExecuteReader();
   con.Close();  //here your connection was already closed
   return dr ;   //this dr is disconnected
}

Sorry i removed con.Close() and still not retrieve any data – Marwan Jun 02 '12 at 03:37 — Marwan, Jun 02 '12 at 03:37

It don't get any data from DataReader

3 Answers3