Ton Smeets posted on Saturday, November 11, 2006

Hello,

Is it possible to use interfaces when retreiving from and updating data to the datasource.

In other words:

private void DataPortal_Fetch(Criteria criteria)

{

using (IDbConnection cn = Database.Conn)

// Where Database.Conn refers to public static SqlConnection conn = new SqlConnection (ConfigurationManager.ConnectionStrings["PTracker"].ConnectionString);

{

cn.Open();

using (IDbCommand cm = cn.CreateCommand())

{

cm.CommandType = CommandType.StoredProcedure;

cm.CommandText = "getProject";

cm.Parameters.AddWithValue("@id", criteria.Id);

using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))

{

dr.Read();

Else, when I want to change the datasource I need to rewrite all businessclasses to suite the new datasource.

Any Ideas???

RockfordLhotka replied on Sunday, November 12, 2006

If you get all the functionality you need from IDbConnection and IDbCommand, then there's no reason that wouldn't work. You'd need to create a factory object which can return an instance of the appropriate IDbConnection - thus abstracting that out of your objects and into a central location.

Ton Smeets replied on Sunday, November 12, 2006

Thank you for your response. I will dive in Visual Studio help to find any further answers. Just had to make sure i'm not jamming the dataportal while doing its job.

JoeFallon1 replied on Monday, November 13, 2006

I have used this style of coding for data access for 3 years - no problems. I support Oracle and SQL Server. The bottom line is that you need to be able to identify the correct concrete type to return. I use the appconfig section and set DatabaseType = "SQL Server" (or "Oracle"). I also read the connection string: mConnStr = ConfigurationManager.AppSettings("ConnectionString")

Then in my DAL code (similar to MS-DAAB) I branch on mDBType (which reads the config file).

e.g.

   Public Shared Function CreateConnection() As IDbConnection
      If mDBType = "SQL Server" Then
        Return New SqlConnection(mConnStr)
      ElseIf mDBType = "Oracle" Then
        Return New OracleConnection(mConnStr)
      Else
        'If mDBType = "OLEDB" Then
        Return New OleDbConnection(mConnStr)
      End If
    End Function

I have overloads for this method that take a string for mDBType (in case I need to change it on the fly for a specific reason) plus another that takes a live connection object to return a 2nd connection of the same type.

Joe

Jurjen replied on Monday, November 13, 2006

You may want to take a look at Petar's implementation of DAL in his ActiveObjects 2.0 wich abstracts it further to commands / parameters etc.

Jurjen.