IDataReader Read() methid question

Hello All!
I am going to crazy and feeling myself so stupid but I don't understand such behaviour.
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 dpab 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 begining datareader positioned before first reacord 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.

Regards,
Nodir Gulyamov

re: IDataReader Read() methid question

I had thought and decide to manually catch exception, but exception is System.Data.SqlTypes.SqlNullValueException.
But this type of exception is generated only in case of using SQL Server, but i need some abstract exception to catch
from all database types. Does somebody has any ideas how can i do it?


Gelios wrote:[color=blue]
> Hello All!
> I am going to crazy and feeling myself so stupid but I don't
> understand such behaviour.
> 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 dpab 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 begining datareader positioned before first
> reacord 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.
>
> Regards,
> Nodir Gulyamov[/color]

re: IDataReader Read() methid question

Hi Gelios,
Here the developer has IDBDataReader, which is an abstract class instead of
specific SqlDataReader or OdbcDataReader as one will not need to modify the
code if the database end is changed which will directly change the .NET
database client.
Regarding the second question, I am not quite sure, however, selecting max
value from an empty table still should have return some value. Have you
tried running that query through SQL query analyzer ?
best,
Subin Kushle,
GAPS

"Gelios" <gelios@rbcmail.ru> wrote in message
news:d4up63$29et$1@gavrilo.mtu.ru...[color=blue]
> Hello All!
> I am going to crazy and feeling myself so stupid but I don't understand[/color]
such behaviour.[color=blue]
> 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 dpab is an instance of DB abstraction class, hence using IDataReader[/color]
interface instead of[color=blue]
> 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[/color]
Null values."[color=blue]
> on line agent_id = dr.GetInt32(0);
> As I understand in the begining datareader positioned before first reacord[/color]
and when I call Read method[color=blue]
> it should point to first record but why it returns true in conditions when[/color]
now rows returned from DB?[color=blue]
>
> Any ideas?
> Thanks in advance to all.
>
> Regards,
> Nodir Gulyamov[/color]

re: IDataReader Read() methid question

Hello Subin,
First of all hanks for reply. Please find inline answers.
[color=blue]
> Hi Gelios,
> Here the developer has IDBDataReader, which is an abstract class instead of
> specific SqlDataReader or OdbcDataReader as one will not need to modify the
> code if the database end is changed which will directly change the .NET
> database client.[/color]
I am not sure what you meant. Who is developer of IDBDataReader?
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());
}
}
}

}

[color=blue]
> Regarding the second question, I am not quite sure, however, selecting max
> value from an empty table still should have return some value. Have you
> tried running that query through SQL query analyzer ?[/color]

I checked in SQL query analyzer and it was return null value.
[color=blue]
> best,
> Subin Kushle,
> GAPS
>
> "Gelios" <gelios@rbcmail.ru> wrote in message
> news:d4up63$29et$1@gavrilo.mtu.ru...
>[color=green]
>>Hello All!
>> I am going to crazy and feeling myself so stupid but I don't understand[/color]
>
> such behaviour.
>[color=green]
>>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 dpab is an instance of DB abstraction class, hence using IDataReader[/color]
>
> interface instead of
>[color=green]
>>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[/color]
>
> Null values."
>[color=green]
>>on line agent_id = dr.GetInt32(0);
>>As I understand in the begining datareader positioned before first reacord[/color]
>
> and when I call Read method
>[color=green]
>>it should point to first record but why it returns true in conditions when[/color]
>
> now rows returned from DB?
>[color=green]
>>Any ideas?
>>Thanks in advance to all.
>>
>>Regards,
>>Nodir Gulyamov[/color]
>
>
>[/color]

re: IDataReader Read() methid question

> agent_id = dr.GetInt32(0);

Change to:

if (!dr.IsDBNull(0))
agent_id = dr.GetInt32(0);
else
agent_id = -1;


--
Dave Sexton
dave@www..jwaonline..com
-----------------------------------------------------------------------
"Gelios" <gelios@rbcmail.ru> wrote in message news:d4urr2$2b3c$1@gavrilo.mtu.ru...[color=blue]
>I had thought and decide to manually catch exception, but exception is System.Data.SqlTypes.SqlNullValueException.
> But this type of exception is generated only in case of using SQL Server, but i need some abstract exception to catch
> from all database types. Does somebody has any ideas how can i do it?
>
>
> Gelios wrote:[color=green]
>> Hello All!
>> I am going to crazy and feeling myself so stupid but I don't understand such behaviour.
>> 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 dpab 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 begining datareader positioned before first reacord 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.
>>
>> Regards,
>> Nodir Gulyamov[/color][/color]

re: IDataReader Read() methid question

Dave wrote:[color=blue][color=green]
>>agent_id = dr.GetInt32(0);[/color]
>
>
> Change to:
>
> if (!dr.IsDBNull(0))
> agent_id = dr.GetInt32(0);
> else
> agent_id = -1;
>
>[/color]
Thanks Dave!
I understood that IDataReader also implements IDataRecord interface which define IsDbNull method.
Thanks again.