使用数据库工厂的两个Db连接 [英] two Db Connection using database factory

问题描述

我需要知道，我有2个数据库，我需要知道如何在2个saperate类中使用数据库工厂和DataWorker dataWorker设置/定义2个数据库连接，我已经在web.config中定义了2个连接字符串文件，并且当我试图为另一个类中的第二个Db定义时，我已经使用它来与Db建立一个连接...我遇到错误

Hi I need to know, I have 2 databases and I need to know how i can set up/define 2 database connections using database factory and DataWorker dataWorker in a 2 saperate class, I already define 2 connections string in web.config file and I already using this for one connection to Db, when i am trying to define for second Db in another class... i am getting error

推荐答案

public class DataHelper
   {
       /// <summary>
       /// This method is created for the insert update table using  Stored procedure.
       /// </summary>
       /// <param name="strProcedureName"></param>
       /// <param name="objDbTransaction"></param>
       /// <param name="objDbParameterCollection"></param>
       public static void ExecuteNonQuery(string strProcedureName, DbTransaction objDbTransaction, DbParameter[] objDbParameter)
       {
           DbProviderFactory objDbProviderFactory = DbProviderFactories.GetFactory(Utility.ProviderName);

           DbCommand objDbCommand = objDbProviderFactory.CreateCommand();
           objDbCommand.CommandType = CommandType.StoredProcedure;
           objDbCommand.CommandText = strProcedureName;
           objDbCommand.Connection = objDbTransaction.Connection;
           objDbCommand.Transaction = objDbTransaction;

           foreach (DbParameter objDbParameterNew in objDbParameter)
           {
               objDbCommand.Parameters.Add(objDbParameterNew);
           }


           objDbCommand.ExecuteNonQuery();


       }

       /// <summary>
       /// This method  is created for executing reader using query(Select query/(View)).
       /// </summary>
       /// <param name="strQuery"></param>
       /// <returns></returns>
       public static DbDataReader ExecuteReader(string strQuery, DbParameter[] objDbParameter)
       {

           DbConnection objDbConnection = Utility.GetConnection;
           try
           {
               DbProviderFactory objDbProviderFactory = Utility.GetProviderFactory;

               objDbConnection.Open();

               DbCommand objDbCommand = objDbProviderFactory.CreateCommand();
               objDbCommand.CommandType = CommandType.Text;
               objDbCommand.Connection = objDbConnection;
               objDbCommand.CommandText = strQuery;

               if (objDbParameter != null)
               {
                   foreach (DbParameter objDbParameterNew in objDbParameter)
                   {
                       objDbCommand.Parameters.Add(objDbParameterNew);
                   }
               }
               return objDbCommand.ExecuteReader(CommandBehavior.CloseConnection);
           }
           finally
           {
               objDbConnection.Close();
           }
       }

       /// <summary>
       /// This method is created for select the single value
       /// </summary>
       /// <param name="strQuery"></param>
       /// <returns></returns>
       public static object ExecuteScalar(string strQuery, DbParameter[] objDbParameter)
       {
           DbConnection objDbConnection = Utility.GetConnection;
           try
           {
               DbProviderFactory objDbProviderFactory = Utility.GetProviderFactory;

               objDbConnection.Open();

               DbCommand objDbCommand = objDbProviderFactory.CreateCommand();
               objDbCommand.CommandType = CommandType.Text;
               objDbCommand.CommandText = BuildQuery(strQuery);
               objDbCommand.Connection = objDbConnection;

               if (objDbParameter != null)
               {
                   foreach (DbParameter objDbParameterNew in objDbParameter)
                   {
                       objDbCommand.Parameters.Add(objDbParameterNew);
                   }
               }

               return objDbCommand.ExecuteScalar();
           }

           finally
           {
               objDbConnection.Close();
           }
       }

       /// <summary>
       /// This method returns datatable.
       /// </summary>
       /// <param name="strQuery"></param>
       /// <returns></returns>
       public static DataTable ExecuteDataTable(string strQuery, DbParameter[] objDbParameter)
       {
           DbProviderFactory objDbProviderFactory = Utility.GetProviderFactory;
           DbConnection objDbConnection = Utility.GetConnection;
           objDbConnection.Open();

           DbCommand objDbCommand = objDbProviderFactory.CreateCommand();
           objDbCommand.CommandType = CommandType.Text;
           objDbCommand.CommandText = BuildQuery(strQuery);
           objDbCommand.Connection = objDbConnection;

           if (objDbParameter != null)
           {
               foreach (DbParameter objDbParameterNew in objDbParameter)
               {
                   objDbCommand.Parameters.Add(objDbParameterNew);
               }
           }

           DbDataAdapter objDbDataAdapter = objDbProviderFactory.CreateDataAdapter();
           objDbDataAdapter.SelectCommand = objDbCommand;
           DataTable objDataTable = new DataTable();
           objDbDataAdapter.Fill(objDataTable);
           objDbConnection.Close();
           return objDataTable;
       }

       /// <summary>
       /// This method returns dataset.
       /// </summary>
       /// <param name="strQuery"></param>
       /// <returns></returns>
       public static DataSet ExecuteDataSet(string strQuery)
       {
           DbProviderFactory objDbProviderFactory = Utility.GetProviderFactory;
           DbConnection objDbConnection = Utility.GetConnection;
           objDbConnection.Open();

           DbCommand objDbCommand = objDbProviderFactory.CreateCommand();
           objDbCommand.CommandType = CommandType.Text;
           objDbCommand.CommandText = BuildQuery(strQuery);
           objDbCommand.Connection = objDbConnection;

           DbDataAdapter objDbDataAdapter = objDbProviderFactory.CreateDataAdapter();
           objDbDataAdapter.SelectCommand = objDbCommand;

           DataSet objDataSet = new DataSet();
           objDbDataAdapter.Fill(objDataSet);
           objDbConnection.Close();
           return objDataSet;
       }

       private static string BuildQuery(string strQuery)
       {
           if (Utility.GetProviderFactory.GetType().ToString() == "System.Data.SqlClient.SqlClientFactory")
           {
               strQuery = strQuery.Replace("@", "@");
           }
           else if (Utility.GetProviderFactory.GetType().ToString() == "System.Data.OracleClient.OracleClientFactory")
           {
               strQuery = strQuery.Replace("@", ":");
           }
           else
           {
               strQuery = strQuery.Replace("@", "?");
           }
           return strQuery;
       }
   }

这篇关于使用数据库工厂的两个Db连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！