Generic Database handling class


C

Craig Lister

Hi guys,

I have created a very simple generic database class, which all my data
accessors make use of. I'm worried though, that I am leaving lost of
open connections, as my web service is becoming unavailble sometimes.
This is my database class:

public Database()
{
_conn = new SqlConnection(_myConString);
}

public SqlDataReader ExecuteStoredProcedure(SqlCommand cmd)
{
if (_conn.State == ConnectionState.Closed)
_conn.Open();
cmd.Connection = _conn;

try
{
SqlDataReader dr = cmd.ExecuteReader();

if (dr != null)
if (dr.HasRows)
{
return dr;
}
}
catch (Exception e)
{

LoggingService.WriteDebugLog("DB Error: " +
e.Message);
LoggingService.WriteDebugLog("Procedure call was: " +
cmd.CommandText);
foreach (SqlParameter sqlParameter in cmd.Parameters)
{
LoggingService.WriteDebugLog(string.Format(" -
Parameter name = [{0}], value = [{1}]", sqlParameter.ParameterName,
sqlParameter.Value));
}
throw;
}


return null;
}
public void Dispose()
{


}



I then use the class in all of my data accessors like this:



public static UserObject Get(int userId)
{
// Create the Database object, using default database (defined in
config file).
Database db = new Database();

const string sqlCommand = "up_user_SelectBy_PK_user_id";
SqlCommand cmd = new SqlCommand(sqlCommand);
cmd.CommandType = CommandType.StoredProcedure;

// Add parameters to the parameter cache.
cmd.Parameters.Add(new SqlParameter("@user_id", userId));
UserObject userObject = null;

using (IDataReader dataReader = db.ExecuteStoredProcedure(cmd))
{
if (dataReader.Read())
{
userObject = ReadResult(dataReader);
}
}
return userObject;
}


Does this seem OK?
 
Ad

Advertisements

A

Arne Vajhøj

Hi guys,

I have created a very simple generic database class, which all my data
accessors make use of. I'm worried though, that I am leaving lost of
open connections, as my web service is becoming unavailble sometimes.
This is my database class:

public Database()
{
_conn = new SqlConnection(_myConString);
}

public SqlDataReader ExecuteStoredProcedure(SqlCommand cmd)
{
if (_conn.State == ConnectionState.Closed)
_conn.Open();
cmd.Connection = _conn;

try
{
SqlDataReader dr = cmd.ExecuteReader();

if (dr != null)
if (dr.HasRows)
{
return dr;
}
}
catch (Exception e)
{

LoggingService.WriteDebugLog("DB Error: " +
e.Message);
LoggingService.WriteDebugLog("Procedure call was: " +
cmd.CommandText);
foreach (SqlParameter sqlParameter in cmd.Parameters)
{
LoggingService.WriteDebugLog(string.Format(" -
Parameter name = [{0}], value = [{1}]", sqlParameter.ParameterName,
sqlParameter.Value));
}
throw;
}


return null;
}
public void Dispose()
{


}



I then use the class in all of my data accessors like this:



public static UserObject Get(int userId)
{
// Create the Database object, using default database (defined in
config file).
Database db = new Database();

const string sqlCommand = "up_user_SelectBy_PK_user_id";
SqlCommand cmd = new SqlCommand(sqlCommand);
cmd.CommandType = CommandType.StoredProcedure;

// Add parameters to the parameter cache.
cmd.Parameters.Add(new SqlParameter("@user_id", userId));
UserObject userObject = null;

using (IDataReader dataReader = db.ExecuteStoredProcedure(cmd))
{
if (dataReader.Read())
{
userObject = ReadResult(dataReader);
}
}
return userObject;
}


Does this seem OK?

No.

You will be leaking connections.

You could try:

SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

But it would probably be even better to look at DAAB to replace
your code.

Arne
 
C

Craig Lister

Thanks guys...
So the quick fix might be the CommandBehavior.CloseConnection
solution?

But I'd like to look into Linq-2-SQL or ADO.NET Entity. I am using
MyGeneration to generate my data access and data objects... I'll need
to check if I can make scripts to do the Entity Framework style....
 
C

Craig Lister

Thanks for that! Going through the first tutorial now. :)

Can you confirm, this is fine for a web service?
 
C

Craig Lister

Thanks Steel & Big Steel (You guys related?! :) )

It's a normal classic .Net web service... so .. I'll continue the
tutorial. Looks like it's just up my street though. I have my database
built and ready.. So, looks like th EF needs a database as an input.
Looks like all my (generated) stored procs are for nothing though,
right? They're generated INSERTS, UPDATES and SELECTS by PK and FKs.

But surely my custom procs will still be useful.
 
A

Arne Vajhøj

So the quick fix might be the CommandBehavior.CloseConnection
solution?
Yes.

But I'd like to look into Linq-2-SQL or ADO.NET Entity. I am using
MyGeneration to generate my data access and data objects...

If you work with data as individual objects, then using
an ORM framework may be a good idea.

LINQ to SQL seems to be a dead end for MS (it will probably
be in .NET forever and they will fix bugs, but no big
enhancements) so don't waste time on that.

The two most obvious ORM's to look at are:
* MS EF (Entity Framework)
* NHibernate

(there exists some enhancements build on top of NHibernate
that may be worth looking at as well)

Arne
 
Ad

Advertisements

A

Arne Vajhøj

I'll continue the
tutorial. Looks like it's just up my street though. I have my database
built and ready.. So, looks like th EF needs a database as an input.

EF is for database access so it requires a database.

:)
Looks like all my (generated) stored procs are for nothing though,
right? They're generated INSERTS, UPDATES and SELECTS by PK and FKs.

But surely my custom procs will still be useful.

Some ORM's can work with SP's.

I don't think EF can - at least I have never seen it.

You should be aware that ORM's including EF is only useful
for some types of usage - typical OLTP usage where you
are object centric. That is a huge portion of .NET apps,
so it is likely also a good choice for you.

You can mix ORM and non-ORM, but then you really need
to know what your are doing. Because a common feature
in ORM's is caching of data in memory. If you bypass
the ORM by calling SP's directly then you can end
up having invalid data in memory, that the ORM still
thinks is valid.

Arne
 
A

Arne Vajhøj

Can you confirm, this is fine for a web service?

EF does not care what type of app it is (console app, Windows GUI app,
Windows service, web app, web service etc.).

But you should not expose your EF objects via web services.

You web services should expose business logic not data.

(there are a few exceptions, but as a general rule)

Arne
 
C

Craig Lister

In my excitement, I stared a basic project based on my project, and
based on the first link thatw as posted - a tutorial. But already
smashed into an issue when a 'where' clause. I'm using my database..
so not following the exact example. But got my initial drop down
working, which took all of 2 minutes! Very nice. The example mentions
I can right click my data file in my soltution to see to XML - but I
am using VS2010, nd don;t seen to have that option when I right click.
Any idea how to see this XML?
 
C

Craig Lister

Actually, I have progressed. My issue was I was in SQL mode, when
doing the linq... and did a 'where myval = aval', and it was giving a
strange error. Using == has resolved that, and it's working! But now I
need to question the layering my app. At the moment, all my code is in
Form1. For example:


private DragonLabsEntities _dlEntities;
private version selectedVersion;


private void cbmDeviceType_SelectedIndexChanged(object sender,
EventArgs e)
{
z_device_type selectedDeviceType =
(z_device_type)cbmDeviceType.SelectedItem;
int selectedDeviceTypeId =
selectedDeviceType.device_type_id;

tsDb.Text = "Busy...";
IQueryable<version> versionQuery =
from p in _dlEntities.versions
where p.z_device_type.device_type_id ==
selectedDeviceTypeId
&& p.deleted == null
orderby p.version_major descending, p.version_minor
descending


select p;

tsDb.Text = "Sleeping";
List<version> selectedVersions = versionQuery.ToList();

selectedVersion = selectedVersions.FirstOrDefault();

PopulateFields();
}

private void PopulateFields()
{
lblVersion.Text = selectedVersion != null ?
selectedVersion.version_major + "." + selectedVersion.version_minor :
"<No Data>";
lblRelease.Text = selectedVersion != null ?
selectedVersion.release_date.ToShortDateString() : "<No Data>";
}

(Note: this is just quickly put together...)

As I build a more stable and managable app, I'd want my usual service
layer, data access etc. This can still be done, right? Can I make the
edmx file 'invisible' to the GUI level (As I'd have the DB invisible
to the GUI in my normal hand crafted apps), with only the data
accessors accesing it... and passing maybe <List> back to the service
layer, to then service my GUI?
 
C

Craig Lister

Thanks! That's some great info there.

It's quite a few new things being piled on at once, so what I can
maybe start off with is a WinForms Entity Framework application. So,
using the Entity Framewok, but remove the WebServic requirement for
now. Split it into a nice simple:

UI
MVP (??)
service layer
BLL
DAL
EF model

Do I need the MVP at the moment? I'm confused how that fits in right
now.Or is that what we're using to pass the entity objects up and down
the layers? With this architecture, would the UI ever directly access
the EF Model? Or any of it's objects? For exmple, would the UI have to
have a reference (using) for the EF Model? I'm thinking it must,
because when I need to set the value of a label, for example, I would
need to do something like:

myEditBox.Text = MyEntityObject.ValueToDisplay;
 
Ad

Advertisements

C

Craig Lister

Thanks again.

I've watched one of the videos where the chap does a basic MVC type
project, with a ProductId, and a Description. It looks very good, but
adds that extra heavy layer of complication (events, interfaces etc) -
however, it does seem pretty good. It fits what I am trying to achieve
pretty well, in that I am building a solution for a non-profit, which
at the moment, makes use of a central database (hosted at Godaddy),
and a WinForms app. So, the high level architecture is:

WinForms Application --> Classic Web Service (Hosted at GoDaddy) -->
Database.

That's the jist of my application.

This is now being expanded to allow users to do things via a web
interface (Web also hosted on same account at Godaddy). So, web
services is also pretty new to me, but I basically made a custom
class, with loads of parameters. as well as a RequestParameters
List<String>, and a ReplyParameters List<String>. This object is
shared within a classlib project shared between all my projects
(Forms, Web and WebService). The web service has a single method
exposed: Request, which accepts my custom object as a parameter, and
returns the same object. The web service then connects to the
database, and does what ever the request is. The Request method checks
a certain property of my object, which contaisn what the request is,
such as 'CHECK_LASTEST_VERSION', or 'AUTHENTICATE_USER', or
'DOWNLOAD_FIRMWARE_FILE'.

As I am doing binary uploads and downloads to the database via my web
service, I am using BASE64 encoding for the web service traffic - Is
that basically Serializing?

Sorry - going off topic.. but basically looking for how I can
impliment Entity Framework. As the WebService is the only 'component'
that interacts with the database, I'm thinking the EF Model is only
available within the WebService project. That would be the project
that has this model in it. The model serves my objects and lists of
objects. Those would have to be declared within a shared library? Same
as at the moment, I share my custom object which goes between the web
site and the web service, and the WinForms app and the web service. I
have a ClassLib project which contains the class definition, and all
my projects reference it

I'm just battling to get my head around the separation of the layers
(Physical and logical). It feels right to me to have the Data Access
Layer and Business Layer sitting within the web service project, on
the web server. Then the Services layer - I'd have thought it would be
in the WinForms application, but then, it's not shared. So, seems that
has to be in the web service too, with the methods being exposed.

Then the MVP would be in the WinForms app, as well as the Web App? Or
would that ALSO be in the web service?

The UI I get - that would be the WinForms front end, as well as the
Web front end .. as they would differ because they're serving
different purposes..

Yeah, my heads in a tangle. :)
 
A

Arne Vajhøj

If the Web service is on a backend Web server, on a N-Tier architecture
and on a LAN, then it's OK to bring those entities across to the
front-end server via business logic.

What do you think is going to happen to an entity setting on the
front-end that would not happen if using a DTO? A DTO can be changed and
so can an entity, but if the DTO had behavior like CRUD operations, the
DTO couldn't persist itself until it was sent back to the back-end.

The DTO can't persist itself from the front-end Web server, because the
database is setting behind the back-end Web server via a service. The
same holds true for an EF entity, which has behavior, it can't persist
any changes until the entity reaches the back end Web service. So in
that regard, I don't see the difference between a DTO or an EF entity in
that scenario.

I am not so concerned about the difference between DTO and entity. With
EF 4 POCO's there are not really any difference at all.

The point is that a service layer should not expose data access
layer (and that a business logic layer should not be a passthrough
to data access layer).

The service layer should expose real business services.

Doing it the other way is an anti-pattern.

(it is usually known as "CRUDy anti-pattern" but it can be
considered a specialization of the "chatty anti-pattern".

Arne
 
Ad

Advertisements

A

Arne Vajhøj

UI
MVP
BLL
service layer
Web service
DAL
EF model

OR

UI
MVP
service layer
Web service
BLL
DAL
EF model

And this is it.

The first is not good.

(there are exceptions, but in general)

The second is the way to design services.

Arne
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top