using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;

public static class DBTools
{
     // static constructor
     static DBTools()
     {
          // Do not really need Constructor code
     }

     // We build connectionstrings internally here to optimize performace and avoid trips to read the web config file with each DB hit.
     // This is the only section you will normally need to update if you are pushing the file from Dev to Live Environment.

     private static string DefaultDBServer = "MyDBServer";

     private static string GetConnectionString(string DBName)
     {
          switch (DBName)
          {
          case "Web_DB":
               return "Server=" + DefaultDBServer + ";Database=" + DBName + ";User ID=xxx;Password=yyy";
               break;
          default:
               return "Server=DBServerName;Database=Web_DB;User ID=uuu;Password=zzz";
               break;
          }
     }

     // Create a new sqlCommand object on a new connection. 3 Input parameters. The first is the database you want to hit, 2nd, your SQL string or SPROC name, and 3rd, your command Type "SPROC" or "Text".

     public static SqlCommand GetCommand(string MyDB, string MyCommandText, string MyCommandType)
     {
          string MyConnectionString = GetConnectionString(MyDB);

          SqlConnection MyConnection = new SqlConnection(MyConnectionString);

          SqlCommand MyCommand = new SqlCommand(MyCommandText, MyConnection);

          if (MyCommandType == "SPROC")
          {
               MyCommand.CommandType = CommandType.StoredProcedure;
          }
          else
          {
               MyCommand.CommandType = CommandType.Text;
          }

     return MyCommand;
     }

     // Although the methods above & below can be combined into one method with an additional input parameter (an array of SqlParameters[]), we avoid this because passing arrays between layers and methods has proven to be detrimental to performance and very expensive.
     // Therefore, we:
     // 1) Call the method above
     // 2) Add the necessary parameters to our command object
     // 3) Pass the command object back to the methods below and grab our data.

     // Execute a query/sproc command and return the results as a DataTable object

     public static DataTable ExecuteCommand(SqlCommand MyCommand)
     {
          DataTable MyTable;

          try
          {
               MyCommand.Connection.Open();
               SqlDataReader MyReader = MyCommand.ExecuteReader();
               MyTable = new DataTable();
               MyTable.Load(MyReader);
               MyReader.Close();
          }

          catch (Exception ex)
          {
               throw ex;
               }

          finally
          {
               MyCommand.Connection.Close();
               MyCommand.Dispose();
          }

          return MyTable;
     }

}

DataTable MyTable = new DataTable();
GridView MyGrid = new GridView();

SqlCommand MyCommand = DBTools.GetCommand("Web_DB", "GetProductDetails", "SPROC");
MyCommand.Parameters.AddWithValue("@CategoryID", "123"]);

DetailsTable = DBTools.ExecuteCommand(MyCommand);
MyGrid.DataSource = MyTable;
MyGrid.DataBind();