title: Db Manager class for third tier (Data Access Layer) in asp.net
body: <p>I am following 3-tier architecture for building a website. For this I created a class named DB Manager. </p>
  
  <p>Here is the class</p>
  
  <pre><code>namespace DAL
  {
      class DBManager
      {
          private static DataTable dt = new DataTable();
          private static string ConnectionString = System.Configuration.ConfigurationManager.AppSettings["SQLSERVER"];
  
          public static int ExecuteNonQuery(string query)
          {
              int result;
              SqlConnection con = new SqlConnection(ConnectionString);
              SqlCommand command = new SqlCommand(query, con);
  
              try
              {
                  con.Open();
                  result = command.ExecuteNonQuery();
                  con.Close();
              }
              catch
              {
                  result = -1;
              }
              finally
              {
                  con.Close();
              }
  
              return result;
          }
  
          public static DataTable ExecuteDataTable(string query)
          {
              SqlConnection con = new SqlConnection(ConnectionString);
              SqlDataAdapter da = new SqlDataAdapter();
              dt = new DataTable();
  
              try
              {
                  con.Open();
                  da.SelectCommand = new SqlCommand(query, con);
                  con.Close();
                  da.Fill(dt);
              }
              catch
              {
                  dt.Rows.Clear();
              }
  
              return dt;
          }
  
          public static string ExecuteScaler(string query)
          {
              SqlConnection con = new SqlConnection(ConnectionString);
              SqlDataAdapter da = new SqlDataAdapter();
              string result = string.Empty;
  
              try
              {
                  con.Open();
                  da.SelectCommand = new SqlCommand(query, con);
                  con.Close();
                  dt = new DataTable();
                  da.Fill(dt);
  
                  if (dt.Rows.Count == 1 &amp;&amp; dt.Columns.Count == 1)
                  {
                      result = dt.Rows[0][0].ToString();
                  }
  
              }
              catch
              {
                  result = string.Empty;
              }
  
              return result;
          }
  
          public static bool ExecuteReader(string query)
          {
              bool result = false;
              SqlConnection con = new SqlConnection(ConnectionString);
              SqlDataAdapter da = new SqlDataAdapter();
  
              try
              {
                  con.Open();
                  da.SelectCommand = new SqlCommand(query, con);
                  con.Close();
                  dt = new DataTable();
                  da.Fill(dt);
  
                  if (dt.Rows.Count == 1 &amp;&amp; dt.Columns.Count == 1)
                  {
                      if (dt.Rows[0][0].ToString() == "true")
                      {
                          result = true;
                      }
                      else
                      {
                          result = false;
                      }
                  }
              } catch (Exception)
              {
                  result = false;
              }
  
              return result;
          }
      }
  }
  </code></pre>
  
  <p>And when I query like this</p>
  
  <pre><code>query = "insert into client(account_id, name, receive_email) values(" + accountId + ", '" + clientBLL.name + "', " + clientBLL.receiveMail + ");";
  </code></pre>
  
  <p>But this method of querying is supposed to be very bad idea. The good method is to give parameters into the SqlCommand which data you want to insert or retrieve. </p>
  
  <p>Like this </p>
  
  <pre><code>query = @"insert into accounts(email, password, user_type) output inserted.id values(@email, @password, @userType);";
  cmd.Parameters.Add("email", SqlDbType.NVarChar).Value = bll.email;
  cmd.Parameters.Add("password", SqlDbType.VarBinary).Value = bll.password;
  cmd.Parameters.Add("userType", SqlDbType.Bit).Value = 0;
  </code></pre>
  
  <p>But my DB Manager class does not support this method. I want a Db Manager class which support this Sql Command querying method not the old one (which I expressed earlier). How can I do this.</p>