ExamGuru IT/.Net Guru : Universal Data Access Components? on ASPFriends.com 'ngfx-patterns' list

.NETGURU

Universal Data Access Components?

Messages

Related Types

This message was discovered on ASPFriends.com 'ngfx-patterns' list.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.

agaisin@currentechonline.com

-- Moved from [aspngdata] to [ngfx-patterns] by Charles M. Carroll <Click here to reveal e-mail address> --Most examples I see don't use a seperate Data Access Layer, and even on sites like IBuySpy (as far as I saw) there's no real Universal Data Access Component where you just pass the object a connection string, type to return (dataset or datareader), type of provider to use, etc... and all the work is done in the component. In IBuySpy it looked like it was a very specialized data access layer.Is what I'm observing (not using a universal Data Access Layer) part of a new trend and a better programming practice in .Net? Is there a significant hit in performance when using a seperate and generic Data Access Layer?It seems silly to duplicate all the preparatory code required for creating DataReaders or DataSets. In asp3.0 on win2k I've seen numerous articles speaking about how much faster it is to open and close connections on the same page rather than through a data access component and with include files you could easily include the majority of the preparatory code for opening/closing connections at the top/bottom of each page that needed it. In .net, is there such a discrepancy in performance when using a seperate Data Access Layer? Is there object pooling like in MTS? Is that even necessary in .net? Is connection pooling automatically enabled or is there setup needed like in classic asp (i.e. changing registry, making sql server use tcp/ip)? Anyway, in .net there's no easy way around using a component by including code as in asp3.0 that I know of, and it's probably a better programming practice not to include code and instead to encapsulate it in components anyway.Can someone point me to some good sample code or components or articles for making such a generic .net data access component...Would love to hear others' thoughts and experiences on this topic.Thanks,Arthur Gaisin

Reply to this message...

Scott Watermasysk (VIP)

I have built upon a couple of different free resouces (AngryCoder source
code download and some of Steve Smith's ASPAlliance articles) to build the
following. I am not sure how good it is, but I am open to critism and
comments. If you like it, use it freely, but just remember it was built from
using some other free resouces (especially the two above).

Examples of use:
//Execute Scalar //has static and non static
String sql = "Select Top 1 ID From SomeTable";
int i = Convert.ToInt32(Engine.JustGetScalar(sql, Engine.ConnectionString)

//DataReader
String sql = "Select * From Sometable";
Engine engine = new Engine();
SqlDataReader results = engine.GetReader(sql, Engine.ConnectionString);

//Execute Non-Query
string sql = "pts_AddNewFileSet";
Engine engine = new Engine();
ParameterCollection pc = new ParameterCollection();
pc.Add("@JobNumber", SqlDbType.Int, 4, ParameterDirection.Input, JobNumber);
pc.Add("@FileSetFolder", SqlDbType.NVarChar, 20, ParameterDirection.Input,
FileSetFolder);
pc.Add("@Keywords", SqlDbType.NVarChar, 255, ParameterDirection.Input,
KeyWords);
pc.Add("@Description", SqlDbType.Text, 0, ParameterDirection.Input,
Description);
pc.Add("@UserName", SqlDbType.NVarChar, 100, ParameterDirection.Input,
UserName);
engine.Execute(sql, Engine.ConnectionString, pc);

-Scott

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Reflection;
using System.Collections;
using System.Runtime.CompilerServices;
using TAN.Portal.Framework.Utilities; //This namespace has a class for
writing the results of a SqlException to the windows log file. If needed, I
can supply this.

namespace TAN.Portal.Framework.SqlClient
{
    //Holds the info to build a SQLParameter
    public class Params
    {
        public String parameterName;
        public SqlDbType DbType;
        public Int32 Size;
        public ParameterDirection Direction;
        public Object Value;
    }

    public class ParameterCollection
    {
        //collection of params
        private ArrayList _paramscollection = new ArrayList();

        public void Add(String parameterName, SqlDbType DbType,
Int32 Size, ParameterDirection Direction, Object Value)
        {
            Params p = new Params();
            p.parameterName = parameterName;
            p.DbType = DbType;
            p.Size = Size;
            p.Direction = Direction;
            p.Value = Value;
            _paramscollection.Add(p);
        }

        public void Add(String parameterName, SqlDbType DbType,
Int32 Size, ParameterDirection Direction)
        {
            if(Direction == ParameterDirection.Output)
            {
                int i = 0;
                Params p = new Params();
                p.parameterName = parameterName;
                p.DbType = DbType;
                p.Size = Size;
                p.Direction = Direction;
                p.Value = i;
                _paramscollection.Add(p);
            }
            else
            {
                throw new Exception("Input parameters must
pass a value.");
            }
        }
        public ArrayList GetParameters
        {
            get
            {
                return _paramscollection;
            }
        }
    }

    public class Engine
    {
        static String _connectionstring;
        public static String ConnectionString
        {
            get
            {
                if (_connectionstring == null){
                try
                {
                    _connectionstring = (String)
ConfigurationSettings.AppSettings["DSN"];
                }
                catch{
                    throw new
Exception("ConnectionString value not set in web.config.");
                    }

                if (_connectionstring == null){
                    throw new
Exception("ConnectionString DSN value not set in web.config.");
                }
            }
            return _connectionstring;
            }
        }
        public static SqlParameter pMaker(String parameterName,
SqlDbType DbType, Int32 Size, ParameterDirection Direction, Object Value)
        {
        SqlParameter param;

        if(Size > 0)
        {
            param = new SqlParameter(parameterName, DbType,
Size);
        }
        else
        {
            param = new SqlParameter(parameterName, DbType);
        }
        param.Direction = Direction;
        if (!(Direction == ParameterDirection.Output && Value ==
null))
        {
            param.Value = Value;
        }
        return param;
        }

        public void Fill(ref DataSet ds,string DataTableName,string
Sql,string ConnectString)
        {
            SqlDataAdapter da = new
SqlDataAdapter(Sql,ConnectString);

            try
            {
                int rows = da.Fill(ds,DataTableName);
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();
                el.WriteEntry(e.ToString());
                throw;
            }
        }

        public void Fill(ref DataSet ds,string DataTableName,string
Sql,string ConnectString, ParameterCollection Parameters)
        {
            SqlCommand cmd = new SqlCommand(Sql, new
SqlConnection(ConnectString));
            cmd.CommandType = CommandType.StoredProcedure;
            foreach(Params p in Parameters.GetParameters)
            {

cmd.Parameters.Add(Engine.pMaker(p.parameterName,p.DbType, p.Size,
p.Direction, p.Value));
            }

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            try
            {
                int rows = da.Fill(ds,DataTableName);
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();
                el.WriteEntry(e.ToString());
                throw;
            }
        }

        public DataRow GetDataRow(string Sql, string ConnectString,
int Row)
        {
            DataTable dt = GetDataTable(Sql, ConnectString);
            DataRow dr;
            try
            {
                dr = dt.Rows[Row];
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();
                el.WriteEntry(e.ToString());
                throw;
            }
            return dr;
        }

        public DataRow GetDataRow(string Sql, string ConnectString)
        {
            return GetDataRow(Sql, ConnectString, 0);
        }

        public DataRow GetDataRow(string Sql, string ConnectString,
ParameterCollection Parameters)
        {
            return GetDataRow(Sql, ConnectString, Parameters,
0);
        }

        public DataRow GetDataRow(string Sql, string ConnectString,
ParameterCollection Parameters, int Row)
        {
            DataTable dt = GetDataTable(Sql, ConnectString,
Parameters);
            DataRow dr;
            try
            {
                dr = dt.Rows[Row];
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();
                el.WriteEntry(e.ToString());
                throw;
            }
            return dr;
        }

        public DataTable GetDataTable(string Sql,string
ConnectString)
        {
            SqlDataAdapter da = new
SqlDataAdapter(Sql,ConnectString);
            DataTable dt = new DataTable();

            try
            {
                int rows = da.Fill(dt);
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();

                el.WriteEntry(e.ToString());
                throw;
            }

            return dt;
        }

        public DataTable GetDataTable(string Sql,string
ConnectString, ParameterCollection Parameters)
        {
            SqlCommand cmd = new SqlCommand(Sql, new
SqlConnection(ConnectString));
            cmd.CommandType = CommandType.StoredProcedure;
            foreach(Params p in Parameters.GetParameters)
            {

cmd.Parameters.Add(Engine.pMaker(p.parameterName,p.DbType, p.Size,
p.Direction, p.Value));
            }

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();

            try
            {
                int rows = da.Fill(dt);
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();

                el.WriteEntry(e.ToString());
                throw;
            }

            return dt;
        }

        public bool Execute(string Sql, string ConnectString)
        {
            SqlCommand cmd = new SqlCommand(Sql, new
SqlConnection(ConnectString));

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();

                el.WriteEntry(e.ToString());
                throw;
            }
            finally
            {
                if(cmd.Connection.State ==
ConnectionState.Open)
                {
                    cmd.Connection.Close();
                }
            }
            return true;
        }

        public bool Execute(string Sql, string ConnectString,
ParameterCollection Parameters)
        {
            SqlCommand cmd = new SqlCommand(Sql, new
SqlConnection(ConnectString));
            cmd.CommandType = CommandType.StoredProcedure;
            foreach(Params p in Parameters.GetParameters)
            {

cmd.Parameters.Add(Engine.pMaker(p.parameterName,p.DbType, p.Size,
p.Direction, p.Value));
            }

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();

                el.WriteEntry(e.ToString());
                throw;
            }
            finally
            {
                if(cmd.Connection.State ==
ConnectionState.Open)
                {
                    cmd.Connection.Close();
                }
            }
            return true;
        }

        public object ExecuteScalar(string Sql, string
ConnectString, ParameterCollection Parameters)
        {
            object obj;
            SqlCommand cmd = new SqlCommand(Sql, new
SqlConnection(ConnectString));
            cmd.CommandType = CommandType.StoredProcedure;
            foreach(Params p in Parameters.GetParameters)
            {

cmd.Parameters.Add(Engine.pMaker(p.parameterName,p.DbType, p.Size,
p.Direction, p.Value));
            }

            try
            {
                cmd.Connection.Open();
                obj = cmd.ExecuteScalar();
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();

                el.WriteEntry(e.ToString());
                throw;
            }
            finally
            {
                if(cmd.Connection.State ==
ConnectionState.Open)
                {
                    cmd.Connection.Close();
                }
            }
            return obj;
        }

        public object ExecuteScalar(string Sql, string
ConnectString)
        {
            object obj;
            SqlCommand cmd = new SqlCommand(Sql, new
SqlConnection(ConnectString));

            try
            {
                cmd.Connection.Open();
                obj = cmd.ExecuteScalar();
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();

                el.WriteEntry(e.ToString());
                throw;
            }
            finally
            {
                if(cmd.Connection.State ==
ConnectionState.Open)
                {
                    cmd.Connection.Close();
                }
            }
            return obj;
        }

        public static object JustGetScalar(string Sql, string
ConnectString)
        {
            object obj;
            SqlCommand cmd = new SqlCommand(Sql, new
SqlConnection(ConnectString));

            try
            {
                cmd.Connection.Open();
                obj = cmd.ExecuteScalar();
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();

                el.WriteEntry(e.ToString());
                throw;
            }
            finally
            {
                if(cmd.Connection.State ==
ConnectionState.Open)
                {
                    cmd.Connection.Close();
                }
            }
            return obj;
        }

        public SqlDataReader GetReader(string Sql,string
ConnectString)
        {
            SqlCommand cmd = new SqlCommand(Sql, new
SqlConnection(ConnectString));

            try
            {
                cmd.Connection.Open();
                return
cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();

                el.WriteEntry(e.ToString());
                throw;
            }
        }

        public SqlDataReader GetReader(string Sql,string
ConnectString, ParameterCollection Parameters)
        {
            SqlCommand cmd = new SqlCommand(Sql, new
SqlConnection(ConnectString));
            cmd.CommandType = CommandType.StoredProcedure;
            foreach(Params p in Parameters.GetParameters)
            {

cmd.Parameters.Add(Engine.pMaker(p.parameterName,p.DbType, p.Size,
p.Direction, p.Value));
            }

            try
            {
                cmd.Connection.Open();
                return
cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();

                el.WriteEntry(e.ToString());
                throw;
            }
        }

        public SqlDataReader GetSingleRowReader(string Sql,string
ConnectString)
        {
            SqlCommand cmd = new SqlCommand(Sql, new
SqlConnection(ConnectString));

            try
            {
                cmd.Connection.Open();
                return
cmd.ExecuteReader(CommandBehavior.SingleRow);
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();

                el.WriteEntry(e.ToString());
                throw;
            }
        }

        public SqlDataReader GetSingleRowReader(string Sql,string
ConnectString, ParameterCollection Parameters)
        {
            SqlCommand cmd = new SqlCommand(Sql, new
SqlConnection(ConnectString));
            cmd.CommandType = CommandType.StoredProcedure;
            foreach(Params p in Parameters.GetParameters)
            {

cmd.Parameters.Add(Engine.pMaker(p.parameterName,p.DbType, p.Size,
p.Direction, p.Value));
            }

            try
            {
                cmd.Connection.Open();
                return
cmd.ExecuteReader(CommandBehavior.SingleRow);
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();

                el.WriteEntry(e.ToString());
                throw;
            }
        }

        public DataCollection GetDataCollection(string Sql, string
ConnectString, ParameterCollection Parameters)
        {
            SqlCommand cmd = new SqlCommand(Sql, new
SqlConnection(ConnectString));
            cmd.CommandType = CommandType.StoredProcedure;
            foreach(Params p in Parameters.GetParameters)
            {

cmd.Parameters.Add(Engine.pMaker(p.parameterName,p.DbType, p.Size,
p.Direction, p.Value));
            }

            try
            {
                cmd.Connection.Open();
                DataCollection dc = new DataCollection();
                dc.DataReader =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
                dc.Parameters = cmd.Parameters;
                return dc;
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();
                el.WriteEntry(e.ToString());
                throw;
            }
        }

        public SqlParameterCollection GetParameters(string Sql,
string ConnectString, ParameterCollection Parameters)
        {
            SqlCommand cmd = new SqlCommand(Sql, new
SqlConnection(ConnectString));
            cmd.CommandType = CommandType.StoredProcedure;
            foreach(Params p in Parameters.GetParameters)
            {

cmd.Parameters.Add(Engine.pMaker(p.parameterName,p.DbType, p.Size,
p.Direction, p.Value));
            }

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
                return cmd.Parameters;
            }
            catch (SqlException se)
            {
                EventLog el = new EventLog();
                el.WriteEntry(se, Sql);
                throw;
            }
            catch (Exception e)
            {
                EventLog el = new EventLog();
                el.WriteEntry(e.ToString());
                throw;
            }
        }

    }

    public class DataCollection
    {
        public SqlDataReader DataReader;
        public SqlParameterCollection Parameters;
        //Data Types as needed.
        //Might need a couple of different Collections
    }
}

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]
Sent: Tuesday, January 08, 2002 5:45 AM
To: ngfx-patterns
Subject: [ngfx-patterns] Universal Data Access Components?

| [ngfx-patterns] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-patterns.asp = JOIN/QUIT
-- Moved from [aspngdata] to [ngfx-patterns] by Charles M. Carroll
<Click here to reveal e-mail address> --

Most examples I see don't use a seperate Data Access Layer, and even on
sites like IBuySpy (as far as I saw) there's no real Universal Data Access
Component where you just pass the object a connection string, type to return
(dataset or datareader), type of provider to use, etc... and all the work is
done in the component. In IBuySpy it looked like it was a very specialized
data access layer.

Is what I'm observing (not using a universal Data Access Layer) part of a
new trend and a better programming practice in .Net? Is there a significant
hit in performance when using a seperate and generic Data Access Layer?

It seems silly to duplicate all the preparatory code required for creating
DataReaders or DataSets. In asp3.0 on win2k I've seen numerous articles
speaking about how much faster it is to open and close connections on the
same page rather than through a data access component and with include files
you could easily include the majority of the preparatory code for
opening/closing connections at the top/bottom of each page that needed it.

In .net, is there such a discrepancy in performance when using a seperate
Data Access Layer? Is there object pooling like in MTS? Is that even
necessary in .net? Is connection pooling automatically enabled or is there
setup needed like in classic asp (i.e. changing registry, making sql server
use tcp/ip)?

Anyway, in .net there's no easy way around using a component by including
code as in asp3.0 that I know of, and it's probably a better programming
practice not to include code and instead to encapsulate it in components
anyway.

Can someone point me to some good sample code or components or articles for
making such a generic .net data access component...

Would love to hear others' thoughts and experiences on this topic.

Thanks,
Arthur Gaisin

Reply to this message...

Ron Mahon

Arthur
You ask some good questions and I wish I knew the answers.
However I do know that in the Ibuyspy Portal if you look in the webconfig
file under applications you will find the connection string defined.
I too miss the MTS server and are concerned what the effect would be it we
hade 100 or thousands of users hitting the same page at once.
Regards
Ron

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]
Sent: Tuesday, January 08, 2002 5:45 AM
To: ngfx-patterns
Subject: [ngfx-patterns] Universal Data Access Components?

| [ngfx-patterns] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-patterns.asp = JOIN/QUIT
-- Moved from [aspngdata] to [ngfx-patterns] by Charles M. Carroll
<Click here to reveal e-mail address> --

Most examples I see don't use a seperate Data Access Layer, and even on
sites like IBuySpy (as far as I saw) there's no real Universal Data Access
Component where you just pass the object a connection string, type to return
(dataset or datareader), type of provider to use, etc... and all the work is
done in the component. In IBuySpy it looked like it was a very specialized
data access layer.

Is what I'm observing (not using a universal Data Access Layer) part of a
new trend and a better programming practice in .Net? Is there a significant
hit in performance when using a seperate and generic Data Access Layer?

It seems silly to duplicate all the preparatory code required for creating
DataReaders or DataSets. In asp3.0 on win2k I've seen numerous articles
speaking about how much faster it is to open and close connections on the
same page rather than through a data access component and with include files
you could easily include the majority of the preparatory code for
opening/closing connections at the top/bottom of each page that needed it.

In .net, is there such a discrepancy in performance when using a seperate
Data Access Layer? Is there object pooling like in MTS? Is that even
necessary in .net? Is connection pooling automatically enabled or is there
setup needed like in classic asp (i.e. changing registry, making sql server
use tcp/ip)?

Anyway, in .net there's no easy way around using a component by including
code as in asp3.0 that I know of, and it's probably a better programming
practice not to include code and instead to encapsulate it in components
anyway.

Can someone point me to some good sample code or components or articles for
making such a generic .net data access component...

Would love to hear others' thoughts and experiences on this topic.

Thanks,
Arthur Gaisin

Reply to this message...

bryan costanich

Connection pooling is handled by win2k component services. It's just
like before.. to add a connection to a pool, simply close it. It's all
automagic.

---
from the .net framework sdk:

ms-help://MS.NETFrameworkSDK/cpguidenf/html/cpconconnectionpoolingforsql
servernetdataprovider.htm

ms-help://MS.NETFrameworkSDK/cpguidenf/html/cpconconnectionpoolingforsql
clientnetprovider.htm

ms-help://MS.NETFrameworkSDK/cpguidenf/html/cpconconnectionpoolingforole
dbnetdataprovider.htm

---

-b

-----Original Message-----
From: Ron Mahon [mailto:Click here to reveal e-mail address]=20
Sent: Tuesday, January 08, 2002 8:24 AM
To: ngfx-patterns
Subject: [ngfx-patterns] RE: Universal Data Access Components?

| [ngfx-patterns] member Click here to reveal e-mail address =3D YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-patterns.asp =3D JOIN/QUIT
Arthur
You ask some good questions and I wish I knew the answers.
However I do know that in the Ibuyspy Portal if you look in the
webconfig
file under applications you will find the connection string defined.
I too miss the MTS server and are concerned what the effect would be it
we
hade 100 or thousands of users hitting the same page at once.
Regards
Ron

-----Original Message-----
From: Click here to reveal e-mail address [mailto:Click here to reveal e-mail address]
Sent: Tuesday, January 08, 2002 5:45 AM
To: ngfx-patterns
Subject: [ngfx-patterns] Universal Data Access Components?

| [ngfx-patterns] member Click here to reveal e-mail address =3D YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-patterns.asp =3D JOIN/QUIT
-- Moved from [aspngdata] to [ngfx-patterns] by Charles M. Carroll
<Click here to reveal e-mail address> --

Most examples I see don't use a seperate Data Access Layer, and even on
sites like IBuySpy (as far as I saw) there's no real Universal Data
Access
Component where you just pass the object a connection string, type to
return
(dataset or datareader), type of provider to use, etc... and all the
work is
done in the component. In IBuySpy it looked like it was a very
specialized
data access layer.

Is what I'm observing (not using a universal Data Access Layer) part of
a
new trend and a better programming practice in .Net? Is there a
significant
hit in performance when using a seperate and generic Data Access Layer?

It seems silly to duplicate all the preparatory code required for
creating
DataReaders or DataSets. In asp3.0 on win2k I've seen numerous articles
speaking about how much faster it is to open and close connections on
the
same page rather than through a data access component and with include
files
you could easily include the majority of the preparatory code for
opening/closing connections at the top/bottom of each page that needed
it.

In .net, is there such a discrepancy in performance when using a
seperate
Data Access Layer? Is there object pooling like in MTS? Is that even
necessary in .net? Is connection pooling automatically enabled or is
there
setup needed like in classic asp (i.e. changing registry, making sql
server
use tcp/ip)?

Anyway, in .net there's no easy way around using a component by
including
code as in asp3.0 that I know of, and it's probably a better programming
practice not to include code and instead to encapsulate it in components
anyway.

Can someone point me to some good sample code or components or articles
for
making such a generic .net data access component...

Would love to hear others' thoughts and experiences on this topic.

Thanks,
Arthur Gaisin

Reply to this message...

jimmy.nilsson@jnsk.se (Jimmy Nilsson)

Hi Arthur,

MS has been working on what they call the "Data Access Application Block for
.NET" and they released it yesterday at the following address (watch out for
linefeeds):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/
daab-rm.asp

I discuss what I think is a quite unusual data access pattern and helper in
my book (.NET Enterprise Design with Visual Basic .NET and SQL Server 2000).
One of its main goals is to reduce roundtrips between the business tier and
the data tier. Hopefully you'll find that pattern too interesting.

Best Regards,
Jimmy
###

Reply to this message...

Mike Amundsen

Jimmy:

hi there

that data access kit is pretty interesting.

just getting into it, but looks pretty good.

BTW - sorry i missed you the last time i was in Linkoping! we may have to
meet each other here in the U.S. again?

will you be at VSLive in SF in FEB?

MCA

-----Original Message-----
From: Jimmy Nilsson [mailto:Click here to reveal e-mail address]
Sent: Tuesday, January 08, 2002 1:31 PM
To: ngfx-patterns
Subject: [ngfx-patterns] Re: Universal Data Access Components?

| [ngfx-patterns] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-patterns.asp = JOIN/QUIT
Hi Arthur,

MS has been working on what they call the "Data Access Application Block for
.NET" and they released it yesterday at the following address (watch out for
linefeeds):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/
daab-rm.asp

I discuss what I think is a quite unusual data access pattern and helper in
my book (.NET Enterprise Design with Visual Basic .NET and SQL Server 2000).
One of its main goals is to reduce roundtrips between the business tier and
the data tier. Hopefully you'll find that pattern too interesting.

Best Regards,
Jimmy
###

Reply to this message...

jimmy.nilsson@jnsk.se (Jimmy Nilsson)

Hi Mike,

Hmm... I'm not sure if I'm allowed to answer this thread online. I'm feeling
a bit shy (or afraid or something like that) right now since I had a
"shameless plug" about my book in a recent posting. I send you a new email
directly instead.
:-)

Best Regard,s
Jimmy
###

[Original message clipped]

Reply to this message...

Aaron Fanetti

In the MS Application Block what is the impact of making all the methods
static? Someone a while back had stated that calls to static methods are
serialized, thus as load increases they can become a bottle neck. I didn't
think that this was true but I am not an expert on threading etc.

Thanks,
Aaron

-----Original Message-----
From: Jimmy Nilsson [mailto:Click here to reveal e-mail address]
Sent: Tuesday, January 08, 2002 12:31 PM
To: ngfx-patterns
Subject: [ngfx-patterns] Re: Universal Data Access Components?

| [ngfx-patterns] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-patterns.asp = JOIN/QUIT
Hi Arthur,

MS has been working on what they call the "Data Access Application Block for
.NET" and they released it yesterday at the following address (watch out for
linefeeds):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/
daab-rm.asp

I discuss what I think is a quite unusual data access pattern and helper in
my book (.NET Enterprise Design with Visual Basic .NET and SQL Server 2000).
One of its main goals is to reduce roundtrips between the business tier and
the data tier. Hopefully you'll find that pattern too interesting.

Best Regards,
Jimmy
###

Reply to this message...

jimmy.nilsson@jnsk.se (Jimmy Nilsson)

You don't *know* until you have explicit tested something. Even though I
haven't done so in in this case, I'm pretty sure that static methods are
faster than member methods. The calls aren't serialized, unless you aren't
having static memory that you have protected to get serialized access to.

Best Regards,
Jimmy
###
----- Original Message -----
From: "Aaron Fanetti" <Click here to reveal e-mail address>
To: "ngfx-patterns" <Click here to reveal e-mail address>
Sent: Wednesday, January 09, 2002 12:24 AM
Subject: [ngfx-patterns] Re: Universal Data Access Components?

[Original message clipped]

Reply to this message...

Francesco Sanfilippo

Hmm, I just tested the same stored procedure for 20 page_loads each running
a loop of 100 iterations, and my data class averages 2900ms compared to
4800ms for the Microsoft class.

Francesco

[Original message clipped]

_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com

Reply to this message...

Mike Amundsen

yep - sounds right me.

the MS class has a lot more overhead than the one you posted. this is
good/bad. the MS classes are less work to implement in many cases, but they
carry the burden of added baggage, too.

MCA

-----Original Message-----
From: Francesco Sanfilippo [mailto:Click here to reveal e-mail address]
Sent: Wednesday, January 09, 2002 1:36 AM
To: ngfx-patterns
Subject: [ngfx-patterns] Re: Universal Data Access Components?

Hmm, I just tested the same stored procedure for 20 page_loads each running
a loop of 100 iterations, and my data class averages 2900ms compared to
4800ms for the Microsoft class.

Francesco

[Original message clipped]

_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com

| [ngfx-patterns] member Click here to reveal e-mail address = YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-patterns.asp = JOIN/QUIT

Reply to this message...

jimmy.nilsson@jnsk.se (Jimmy Nilsson)

OK, but I just said that static methods are faster than member methods. Your
code is very different from that of the MS helper. And more well-tuned...
;-)

Best Regards,
Jimmy
###
----- Original Message -----
From: "Francesco Sanfilippo" <Click here to reveal e-mail address>
To: "ngfx-patterns" <Click here to reveal e-mail address>
Sent: Wednesday, January 09, 2002 7:35 AM
Subject: [ngfx-patterns] Re: Universal Data Access Components?

> Hmm, I just tested the same stored procedure for 20 page_loads each
running
[Original message clipped]

Reply to this message...

	System.Collections.ArrayList
	System.Configuration.ConfigurationSettings
	System.Convert
	System.Data.CommandBehavior
	System.Data.CommandType
	System.Data.ConnectionState
	System.Data.DataRow
	System.Data.DataSet
	System.Data.DataTable
	System.Data.DbType
	System.Data.ParameterDirection
	System.Data.SqlClient.SqlCommand
	System.Data.SqlClient.SqlConnection
	System.Data.SqlClient.SqlDataAdapter
	System.Data.SqlClient.SqlDataReader
	System.Data.SqlClient.SqlException
	System.Data.SqlClient.SqlParameter
	System.Data.SqlClient.SqlParameterCollection
	System.Data.SqlDbType
	System.Diagnostics.EventLog
	System.Exception

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification