IDataReader Read() method question

[Gelios]

Hello All!
I am going to crazy and feeling myself so stupid but I don't understand such behavior.
I have code:
public int getNextAgentId()
{
Int32 agent_id = 0;
IDataReader dr = dbap.DBDataReader("SELECT MAX(agent_id) FROM Agents");
if(dr.Read())
{
agent_id = dr.GetInt32(0);
}
dr.Close();
return agent_id + 1;
}

where dbap is an instance of DB abstraction class, hence using IDataReader interface instead of
concrete DB DataReader such as SqlDataReader.

SELECT returns null due to database table is empty and I have
error message "Data is Null. This method or property cannot be called on Null values."
on line agent_id = dr.GetInt32(0);
As I understand in the beginning datareader positioned before first record and when I call Read method
it should point to first record but why it returns true in conditions when now rows returned from DB?

Any ideas?
Thanks in advance to all.

Below my code of abstraction class (dbap is instance of DBAbstractionProcessor class):

public class DBAbstractionProcessor
{
// implemented DB provider types
public enum DbType
{
SQLServer,
OLEDb,
ODBC
};

private DbType dbtype;
private AbstractDBFactory dbf;


public DBAbstractionProcessor(DbType db_type)
{
this.dbtype = db_type;
}

private void selectDbType()
{
switch(this.dbtype)
{
case DbType.SQLServer:
this.dbf = new SQLServerDb();
break;
case DbType.ODBC:
this.dbf = new OdbcDb();
break;
case DbType.OLEDb:
this.dbf = new OleDb();
break;
}
}

public void init(string dburl)
{
this.selectDbType();
this.dbf.init(dburl);
}

public void init(string dburl, string user, string passwd)
{
this.selectDbType();
this.dbf.init(dburl,user, passwd);
}

public void connect()
{
dbf.connect();
}

public void disconnect()
{
dbf.disconnect();
}

public DataSet DBAPSelectQuery(string cmd)
{
return this.dbf.DBSelectQuery(cmd);
}

public void DBAPInsertQuery(string cmd)
{
this.dbf.DBInsertQuery(cmd);
}

public IDataReader DBDataReader(string query)
{
return this.dbf.DBDataReader(query);
}

}

Factory class which implements factory design pattern:

/// <summary>
/// Factory Design Patterns implementation.
/// At the present time abstraction if MS SQL Server, ODBC and OLEDB
/// interfaces implemented. In case of neccessety, any other interfaces
/// can be easily implemented.
/// </summary>
public abstract class AbstractDBFactory
{
// database url string
protected string url = null;
// database username
protected string username = null;
// database password
protected string password = null;

// simple initialize by url
public void init(string dburl)
{
if(dburl == null)
{
throw(new ArgumentException("Illigal agruments"));
}
this.url = dburl;
}

// full initialize
public void init(string dburl, string user, string pass)
{
this.init(dburl);
this.username = user;
this.password = pass;
}

// clear all settings
public void destroy()
{
this.url = null;
this.username = null;
this.password = null;
}

// abstract methods which implemented by SQLServerDb, OdbcDb and OleDb classes
abstract public void connect();
abstract public void disconnect();
abstract public DataSet DBSelectQuery(string cmd);
abstract public void DBInsertQuery(string cmd);
abstract public IDataReader DBDataReader(string cmd);

}


And SQL Server implementation:


/// <summary>
/// MS SQL Server interface implementation.
/// </summary>
public class SQLServerDb : AbstractDBFactory
{
// connector
private SqlConnection sqlCon;

public override void connect()
{
// if base class which stores url, username and passwords not initilized
// throw exception
if(base.url == null)
{
throw(new ApplicationException("Class is not initialized. Please run init() first."));
}
else
{
//Disable pooling for sql server
if(base.url.IndexOf("Pooling") == -1)
{
base.url += ";Pooling=false";
}
// if username and password properties exists initilize by them otherwise by url
if(base.username != null && base.password != null)
{
this.sqlCon = new SqlConnection(base.url + ";uid=" + base.username + ";pwd=" + base.password);
LoggingProcessor.Info("url uid pwd init");
}
else
{
this.sqlCon = new SqlConnection(base.url);
LoggingProcessor.Info("url - " + base.url);
}
try
{
sqlCon.Open();
}
catch (Exception e)
{
throw new Exception("SQL Server error: " + e.ToString());
}
LoggingProcessor.Info("sql server connection established");
}
}

// simple select query
// returns DataSet
public override DataSet DBSelectQuery(string cmd)
{
DataSet ds = new DataSet();
try
{
if(this.sqlCon.State == ConnectionState.Closed)
{
this.connect();
}
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = new SqlCommand(cmd, sqlCon);
sqlDA.Fill(ds);
}
catch (Exception e)
{
throw new Exception("SQL Server error: " + e.ToString());
}
finally
{
sqlCon.Close();
}

return ds;
}

// simple insert query
public override void DBInsertQuery(string cmd)
{
try
{
if(this.sqlCon.State == ConnectionState.Closed)
{
this.connect();
}
SqlCommand sqlCmd = new SqlCommand(cmd, this.sqlCon);
sqlCmd.Prepare();
sqlCmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception("SQL Server error: " + e.ToString());
}
}

// DBDataReader - provides selection and returns SqlDataReader
public override IDataReader DBDataReader(string cmd)
{
try
{
if(this.sqlCon.State == ConnectionState.Closed)
{
this.connect();
LoggingProcessor.Info("connection is not initialized. initializing...");
}
SqlCommand sqlCmd = new SqlCommand(cmd, this.sqlCon);
LoggingProcessor.Info(cmd);
//sqlCmd.Prepare();
return sqlCmd.ExecuteReader();
}
catch (Exception e)
{
throw new Exception("SQL Server error: " + e.ToString());
}
}

// disconnect
public override void disconnect()
{
if(sqlCon.State != ConnectionState.Closed)
{
try
{
this.sqlCon.Close();
}
catch (Exception e)
{
throw new Exception("SQL Server error: " + e.ToString());
}
}
}

}


Best regards,
/Gelios

 

[Dumitru Sbenghe]

Your reader have one row with one column with a null value.
Calling Get<Type>(indexColumn) on a null value will return your exception:
"Data is Null. This method or property cannot be called on Null values."

Before calling GetInt32(0) check for null value with dr.IsDBNull(0)

Dumitru

 

[Gelios]

Your reader have one row with one column with a null value.
Calling Get<Type>(indexColumn) on a null value will return your exception:
"Data is Null. This method or property cannot be called on Null values."

Before calling GetInt32(0) check for null value with dr.IsDBNull(0)

Dumitru

First of all thanks for your reply.
But IsDbNull is not declared in IDataReader interface. It is extension in derived concrete DataReaders. ((

 

[Dumitru Sbenghe]

Also try to look in IDataRecord

public interface IDataReader : IDisposable, IDataRecord

 

[Gelios]

Also try to look in IDataRecord

public interface IDataReader : IDisposable, IDataRecord

Thanks. Everything is working now.