Which Data Acces Layer model???

[ozgur develioglu]

Hi everyone,

I'm trying to develop a data acces layer model for ASP.NET applications.
For every table in the database, I have a class corresponding a table. These
classes have static methods like load(...), save(...), update(...),
delete()... as classic in data acces layers.

public static SqlDataReader GetCompanies() {
SqlConnection myConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

SqlCommand myCommand = new SqlCommand("BSG_INTRANET_COMPANY_GET",
myConnection);

// Mark the Command as a SPROC

myCommand.CommandType = CommandType.StoredProcedure;


// Execute the command

myConnection.Open();

SqlDataReader result =
myCommand.ExecuteReader(CommandBehavior.CloseConnection);

// Return the datareader result

return result; }

public static DataView GetCompaniesView(){

SqlDataReader rea=CompanyDB.GetCompanies();


DataTable dt=new DataTable();

dt.Columns.Add("ID");

dt.Columns.Add("NAME");


DataRow dr;

while(rea.Read()){

dr=dt.NewRow();

dr["ID"]=rea.GetValue(0);

dr["NAME"]=rea.GetValue(1);


dt.Rows.Add(dr);

}

DataView dv=new DataView(dt);

rea.Close();

return dv;

}

So I can make bindings like:
DataView dv=CompanyDB.GetCompaniesView(); dgCompanies.DataSource=dv;
or
ddlCompany.DataSource=CompanyDB.GetCompanies();

I'm using always Stored Procedures.

Microsoft has lanced a ApplicationDataBlok called SQLHelper. This class
lets you to call SPs with less amount of code. But it is really discussable
that SQLHelper make your round trips slower. ( It's maybe faster to call a
SP directly). So I decided not to use SQLHelper classes.

I don't know using static methods make the class work slower but it
reduces the code listing as you mention.

Today I'm trying to cache some of my tables in order not to go to DB
everytime a query is made. If there is a SELECT query to Companies table, we
will look at the cache and if we find it, we will use it without going to
DB; if no, we will go to DB and put the table in the cache. if there is a
INSERT,UPDATE,SELETE query we will dispose the table in the cache.

I'm trying to make my classes work with both MS-Sql Server, ODBC, OLE
and ORACLE but I haven't find a solution for this.

I make also connection pooling with 10 minimum connections.

If you are coding such things in order to make your application working
faster, please send your comments.

Ozgur.
BSG Web Application Development

 

[Kevin Swarts]

[AD]
Check out nGen, http://www.nAlliance.com/Products/nGen.

It creates the classes for each table.

KS