Articles → ADO.NET → Writing Generic Data Access Code Using Dbproviderfactory Class

Writing Generic Data Access Code Using Dbproviderfactory Class





  1. Software requirement
  2. Prerequisite knowledge
  3. What is a data source?
  4. What is a data provider in .net?
  5. What is generic data access code?
  6. Creation of access data source.
  7. Creation of SQL Server data source.
  8. Adding provider and connection string in web.config file.
  9. Writing generic code.
  10. Explanation of code.
  11. Output.
  12. Change provider and connection string in web.config file.
  13. Output.
  14. Conclusion.



Software Requirement





Prerequisite Knowledge




  1. How to create projects (or websites) in Visual Studio?
  2. What is ADO.NET?
  3. How to use ADO.NET to connect to different data sources?

What Is A Data Source?





What Is A Data Provider In .Net?







What Is Generic Data Access Code?











Creation Of Access Data Source




Picture showing the table schema of the access database

Click to Enlarge


Creation Of SQL Server Data Source




Picture showing the table schema of the SQL server database

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


Picture showing the data inserted into the sql server table

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


Picture showing the data inserted into the access table

Click to Enlarge


Conclusion





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

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250