locked
Accessing a different class depending on the database type RRS feed

  • Question

  • User437181934 posted

    Hi there everyone


    I need to make a webservice in .net (I know how this works) but then I need to get data out of a database. But my company wants to make it in a way that you can use the system with multiple databases. 

    So my option was make multiple abstract classes which contains the folowing code (depending on the database type, this would be a SQL database)


    public abstract class AbstractDAO<T, Tid> 
        {
            private string connectionString;
            public AbstractDAO()
            {
                connectionString = WebConfigurationManager.ConnectionStrings["bieren"].ConnectionString;
            }
            public T ExecuteReaderSingleRow(string sqlString, List<SqlParameter> parameters)
            {
                using (SqlConnection oConn = new SqlConnection(connectionString))
                {
                    SqlCommand oCmd = new SqlCommand(sqlString, oConn);
                    foreach (SqlParameter p in parameters)
                        oCmd.Parameters.Add(p);
                    oConn.Open();
                    using (SqlDataReader oReader = oCmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (oReader.Read())
                        {
                            return GetObjectFromReader(oReader);
                        }
                    }
                    return default(T);
                }
            }

    public abstract class AbstractDAO<T, Tid> 

        {
            private string connectionString;

            public AbstractDAO()
            {
                connectionString = WebConfigurationManager.ConnectionStrings["bieren"].ConnectionString;
            }


            public T ExecuteReaderSingleRow(string sqlString, List<SqlParameter> parameters)

            {
                using (SqlConnection oConn = new SqlConnection(connectionString))
                {

                    SqlCommand oCmd = new SqlCommand(sqlString, oConn);

                    foreach (SqlParameter p in parameters)

                        oCmd.Parameters.Add(p);

                    oConn.Open();

                    using (SqlDataReader oReader = oCmd.ExecuteReader(CommandBehavior.SingleRow))

                    {
                        if (oReader.Read()) {

                            return GetObjectFromReader(oReader);

                        }

                    }

                    return default(T);

                }

            }


    So there would be 3-4 abstract classes like this depending on the amount of databases they want to use. 

    Normally I extends my class with  ": AbstractDAO<Beer, int>"(as example) 

    But since I got a AbstractDAOsql and for example AbstractDAOmysql and so on. How can I let the class figure out which one to use?

    Or is there a better way to do this ? 


    Greets, Robbert 



    ps.: didn't knew where to put this topic, sorry 

    Thursday, March 25, 2010 9:59 AM

Answers

All replies

  • User-952121411 posted

    If I understand your question, you need to be able to dynamically switch the database connection or type (SQL, Oracle, etc.).  There are several ways to do this to make your code flexible; most solution revolve around making a configuration change in the .config file to use the appropriate type of database connection.  This eliviates any hardcoding in each app or recompiling.  Take a look to the following:

    Implementing a Database Factory Pattern in C# ASP .NET: 

    http://primaryobjects.com/CMS/Article81.aspx

    Achieve database independence by developing a pluggable data layer:

    http://www.simple-talk.com/dotnet/.net-framework/ado.net-2.0-factory-classes/

    ...ran across this the other day; a bit older but the principal looked cool:

    DI using Unity Application Blocks - C#, ASP.Net, VB.Net:

    http://www.dotnetspark.com/kb/267-di-using-unity-application-blocks.aspx

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 25, 2010 1:46 PM
  • User-525215917 posted

    Using .NET Framework you need some mechanism to create some database objects and DI stuff mentioned before is good start.

    My suggestion is: check out database objects interfaces and base classes that are defined under System.Data namespace and use them as much as you can. This way you are able to make your code maximally independent of database and at same time your code is not full of calls to DI/IoC containers.

    Thursday, March 25, 2010 2:54 PM
  • User437181934 posted

    I used the Database Factory pattern and it's working !!

    It is a good way to do this, and easy for people to just change the connectionstring in the web.config


    Thanks !! 

    Friday, March 26, 2010 10:47 AM
  • User437181934 posted

    I heard there was also a good way of doing this with the Enterprise Library. 

    And that there was also a build in solution for the differend kinds of SQL statements (in the Enterprise Lib)

    Does anyone knows if there is a tutorial for this? Because I never worked with that library before.


    Greets

    Monday, March 29, 2010 5:29 AM
  • User-952121411 posted

    Yes there are an abundant of resources; take a look to the following:

    Online Tutorials about Enterprise Library: 

    http://channel9.msdn.com/wiki/wiki/EnterpriseLibraryTutorials/

    patterns & practices – Enterprise Library Home (Look at items under 'Learn' section):

    http://entlib.codeplex.com/Wikipage

    patterns & practices – Enterprise Library Forum:

    http://entlib.codeplex.com/Thread/List.aspx

    Microsoft Enterprise Library:
    <!---->
     
     
    Monday, March 29, 2010 10:56 AM