Articles → ADO.NET → Writing generic data access code using DbProviderFactory class

Writing generic data access code

using DbProviderFactory class




































Software requirement





Prerequisite knowledge











What is a data source?





What is a data provider in .net?







What is generic data access code?











Creation of access data source





Click to Enlarge


Creation of SQL Server data source





Click to Enlarge




Adding provider and connection string in web.config file




<appSettings>
  <add key="ProviderName" value="System.Data.SqlClient"/>
  <!--<add key="ProviderName" value="System.Data.OleDb"/>-->
  </appSettings>
  <connectionStrings>
    <add name="connectionString" connectionString="Data Source=TestServer\SQLEXPRESS;Initial Catalog=Testing;Integrated Security=true;"/>
    <!--<add name="connectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;data source=d:\test.mdb"/>-->
</connectionStrings>





Writing generic code


string commandText = "insert into books(BookName) values('test')";

DbProviderFactory factory = DbProviderFactories.GetFactory(ConfigurationManager.AppSettings["ProviderName"]);

using (DbConnection con = factory.CreateConnection())
{
            using (DbCommand cmd = factory.CreateCommand())
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
                cmd.Connection = con;
                cmd.CommandText = commandText;
                con.Open();
                cmd.ExecuteNonQuery();
            }
}



Explanation of code











Output



Click to Enlarge


Change provider and connection string in web.config file




<appSettings>
  <!--<add key="ProviderName" value="System.Data.SqlClient"/>-->
  <add key="ProviderName" value="System.Data.OleDb"/>
</appSettings>
<connectionStrings>
  <!--<add name="connectionString" connectionString="Data Source=TestServer\SQLEXPRESS;Initial Catalog=Testing;Integrated Security=true;"/>-->
  <add name="connectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;data source=d:\test.mdb "/>
</connectionStrings>





Output



Click to Enlarge


Conclusion





Posted By  -  Karan Gupta
 
Posted On  -  Monday, February 13, 2012

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250