I am following 3-tier architecture for building a website. For this I created a class named DB Manager.
Here is the class
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 && 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 && dt.Columns.Count == 1)
{
if (dt.Rows[0][0].ToString() == "true")
{
result = true;
}
else
{
result = false;
}
}
} catch (Exception)
{
result = false;
}
return result;
}
}
}
And when I query like this
query = "insert into client(account_id, name, receive_email) values(" + accountId + ", '" + clientBLL.name + "', " + clientBLL.receiveMail + ");";
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.
Like this
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;
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.
Obviously you need to add parameters to your DBmanager class. It is also missing some other important methods. Here is a complete class that you can use. I use the standard ConnectionStrings section in the web.config rather than the AppSettings section. You can either modify your web.config (recommended), or modify this class.
//Author: Racil Hilan
//You are free to modify and use this class in any project, personal or commercial,
//as long as you include this note. The author assumes no responsibility whatsoever
//for any damage that results from using this class, and does not guarantee in any way
//the suitability of this class for any purpose.
using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
namespace DataLayer {
/// <summary>Class that encapsulates a SQL Server database connection and CRUD operations.</summary>
public class SQLServerDb : IDisposable {
private DbConnection _con;
/// <summary>Default constructor which uses the "DefaultConnection" connectionString.</summary>
public SQLServerDb() : this("DefaultConnection") { }
/// <summary>Constructor which takes the connection string name.</summary>
/// <param name="connectionStringName"></param>
public SQLServerDb(string connectionStringName) {
string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
_con = new SqlConnection(connectionString);
}
/// <summary>Executes a non-query command.</summary>
/// <param name="command">The command to execute.</param>
/// <returns>The count of records affected by the command.</returns>
public int ExecuteNonQuery(DbCommand command) {
int result = 0;
if (command == null)
throw new ArgumentException("Command cannot be null.");
try {
_con.Open();
result = command.ExecuteNonQuery();
}
finally {
_con.Close();
}
return result;
}
/// <summary>Executes a command that returns a single scalar value.</summary>
/// <param name="command">The command to execute.</param>
/// <returns>The value returned by executing the command.</returns>
public object ExecuteScalar(DbCommand command) {
object result = null;
if (command == null)
throw new ArgumentException("Command cannot be null.");
try {
_con.Open();
result = command.ExecuteScalar();
}
finally {
_con.Close();
}
return result;
}
/// <summary>Executes a command that returns a DataSet.</summary>
/// <param name="command">The command to execute.</param>
/// <returns>The DataSet returned by executing the ecommand.</returns>
public DataSet ExecuteDataSet(DbCommand command) {
DataSet ds = new DataSet();
if (command == null)
throw new ArgumentException("Command cannot be null.");
try {
DbDataAdapter ad = new SqlDataAdapter((SqlCommand)command);
ad.Fill(ds);
}
finally {
_con.Close();
}
return ds;
}
/// <summary>Creates a command with the given parameters.</summary>
/// <param name="commandText">The SQL query to execute.</param>
/// <returns>The created command.</returns>
public DbCommand GetSqlStringCommand(string commandText) {
return GetCommand(commandText, CommandType.Text);
}
/// <summary>Creates a command with the given parameters.</summary>
/// <param name="commandText">The name of the stored procedure to execute.</param>
/// <returns>The created command.</returns>
public DbCommand GetStoredProcedureCommand(string commandText) {
return GetCommand(commandText, CommandType.StoredProcedure);
}
/// <summary>Creates a command with the given parameters.</summary>
/// <param name="commandText">The name of the stored procedure to execute.</param>
/// <returns>The created command.</returns>
private DbCommand GetCommand(string commandText, CommandType commandType) {
DbCommand command = _con.CreateCommand();
command.CommandType = commandType;
command.CommandText = commandText;
return command;
}
/// <summary>Adds an in parameter to a command.</summary>
/// <param name="command">The SQL query to execute</param>
/// <param name="name">The name of the parameter.</param>
/// <param name="dbType">The type of the parameter.</param>
/// <param name="value">The value of the parameter.</param>
public void AddInParameter(DbCommand command, string name, DbType dbType, object value) {
AddParameter(command, name, dbType, value, ParameterDirection.Input, 0);
}
/// <summary>Adds an out parameter to a command.</summary>
/// <param name="command">The SQL query to execute</param>
/// <param name="name">The name of the parameter.</param>
/// <param name="dbType">The type of the parameter.</param>
/// <param name="size">The maximum size, in bytes, of the data within the column.</param>
public void AddOutParameter(DbCommand command, string name, DbType dbType, int size) {
AddParameter(command, name, dbType, null, ParameterDirection.Output, size);
}
/// <summary>Adds a parameter to a command.</summary>
/// <param name="command">The SQL query to execute</param>
/// <param name="name">The name of the parameter.</param>
/// <param name="dbType">The type of the parameter.</param>
/// <param name="value">The value of the parameter.</param>
/// <param name="direction">The direction for the parameter.</param>
/// <param name="size">The maximum size, in bytes, of the data within the column.</param>
private void AddParameter(DbCommand command, string name, DbType dbType, object value, ParameterDirection direction, int size) {
var parameter = command.CreateParameter();
parameter.ParameterName = name;
parameter.DbType = dbType;
parameter.Value = value ?? DBNull.Value;
parameter.Direction = direction;
if (size > 0)
parameter.Size = size;
command.Parameters.Add(parameter);
}
public void Dispose() {
if (_con != null) {
_con.Dispose();
_con = null;
}
}
}
}
You can use it with your example like this:
var db = new SQLServerDb();
string sql = @"INSERT INTO accounts(email, password, user_type) VALUES(@email, @password, @userType);";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@email", DbType.String, bll.email);
db.AddInParameter(cmd, "@password", DbType.String, bll.password);
db.AddInParameter(cmd, "@userType", DbType.Boolean, bll.userType);
DataRow dr = db.ExecuteDataSet(cmd).Tables[0].Rows[0];
To use it with stored procedures instead of queries, use the GetStoredProcedureCommand() instead of the GetSqlStringCommand() one.