Hello Subin,
First of all hanks for reply. Please find inline answers.
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.
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());
}
}
}
}
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 ?
I checked in SQL query analyzer and it was return null value.
