I personally would not have searchbyname in the data access layer. I generally leave the stored procedure or sql statements in the business logic layer because thats a moving target and can change with business needs vs accessing data can be a nice reusable dll. Below is a sample SQL Server class, oracle is very similar. All it does is access the database in the way you tell it, and what database you tell it to and return or execute stuff. I use this across many projects without having to change a thing because with any new project the only thing that usually changes are the business requirements and the front end to work with the data. 1/3rd of my coding is done before I start any new project :)..sort of usually the business logic and saving users from themselves takes the longest.
Having System.data in the BAL is fine. DataTables are a fairly generic way of returning data without locking yourself into a certain database type.
Thats just my 2 cents.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DataAccess
{
public class SqlServer : IDisposable
{
bool disposed = false;
protected Dictionary<int, SqlParameter> _parameters { get; set; }
protected int _commandTimeout { get; set; }
public string ConnectionName { get; set; }
public string ConnectionString { get; set; }
public enum ReturnType
{
DataTable,
DataReader,
Scalar,
DataSet,
NoResult
}
public SqlServer()
:this("DefaultConnection")
{
}
public SqlServer(string connectionName)
{
ConnectionName = connectionName;
}
private SqlConnection getConnection(string connectionName)
{
SqlConnection sqlConn;
string connString;
try
{
connString = (ConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString != null) ? ConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString : ConnectionString;
sqlConn = new SqlConnection(connString);
sqlConn.Open();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
return sqlConn;
}
private static void AddParameters(SqlCommand command, Dictionary<string, object> parameters)
{
if (parameters == null)
{
return;
}
foreach (var param in parameters)
{
var parameter = command.CreateParameter();
parameter.ParameterName = param.Key;
parameter.Value = param.Value ?? DBNull.Value;
command.Parameters.Add(parameter);
}
}
public object executeSQL(string SQL, ReturnType returnDataType)
{
SqlCommand sqlComm = new SqlCommand();
SqlDataAdapter sqlDA = null;
try
{
sqlComm = new SqlCommand(SQL, getConnection(ConnectionName));
sqlComm.CommandType = CommandType.Text;
switch (returnDataType)
{
case ReturnType.DataReader:
return sqlComm.ExecuteReader();
case ReturnType.DataTable:
DataTable dtResult = new DataTable();
sqlDA = new SqlDataAdapter(sqlComm);
sqlDA.Fill(dtResult);
return dtResult;
case ReturnType.DataSet:
sqlDA = new SqlDataAdapter(sqlComm);
DataSet dsResult = new DataSet();
sqlDA.Fill(dsResult);
return dsResult;
case ReturnType.Scalar:
return sqlComm.ExecuteScalar();
case ReturnType.NoResult:
return sqlComm.ExecuteNonQuery();
default:
return sqlComm.ExecuteReader();
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlComm != null)
{
if (sqlComm.Connection.State == ConnectionState.Open & returnDataType != ReturnType.DataReader) { sqlComm.Connection.Close(); }
sqlComm.Dispose();
}
if (sqlDA != null) { sqlDA.Dispose(); }
}
}
public object executeSQL(string SQL, Dictionary<string,object> parameters, ReturnType returnDataType)
{
SqlCommand sqlComm = new SqlCommand();
SqlDataAdapter sqlDA = null;
try
{
sqlComm = new SqlCommand(SQL, getConnection(ConnectionName));
sqlComm.CommandType = CommandType.Text;
AddParameters(sqlComm, parameters);
switch (returnDataType)
{
case ReturnType.DataReader:
return sqlComm.ExecuteReader();
case ReturnType.DataTable:
DataTable dtResult = new DataTable();
sqlDA = new SqlDataAdapter(sqlComm);
sqlDA.Fill(dtResult);
return dtResult;
case ReturnType.DataSet:
sqlDA = new SqlDataAdapter(sqlComm);
DataSet dsResult = new DataSet();
sqlDA.Fill(dsResult);
return dsResult;
case ReturnType.Scalar:
return sqlComm.ExecuteScalar();
case ReturnType.NoResult:
return sqlComm.ExecuteNonQuery();
default:
return sqlComm.ExecuteReader();
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlComm != null)
{
if (sqlComm.Connection.State == ConnectionState.Open & returnDataType != ReturnType.DataReader) { sqlComm.Connection.Close(); }
sqlComm.Dispose();
}
if (sqlDA != null) { sqlDA.Dispose(); }
}
}
public object executeProcedure(string storedProcName, Dictionary<int, SqlParameter> parms, ReturnType returnDataType)
{
SqlCommand sqlComm = new SqlCommand();
SqlDataAdapter sqlDA = null;
try
{
sqlComm = new SqlCommand(storedProcName, getConnection(ConnectionName));
sqlComm.CommandType = CommandType.StoredProcedure;
for (int i = 1; i <= parms.Count; i++)
{
sqlComm.Parameters.Add(parms[i]);
}
switch (returnDataType)
{
case ReturnType.DataReader:
return sqlComm.ExecuteReader();
case ReturnType.DataTable:
DataTable dtResult = new DataTable();
sqlDA = new SqlDataAdapter(sqlComm);
sqlDA.Fill(dtResult);
return dtResult;
case ReturnType.DataSet:
sqlDA = new SqlDataAdapter(sqlComm);
DataSet dsResult = new DataSet();
sqlDA.Fill(dsResult);
return dsResult;
case ReturnType.Scalar:
return sqlComm.ExecuteScalar();
case ReturnType.NoResult:
return sqlComm.ExecuteNonQuery();
default:
return sqlComm.ExecuteReader();
}
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlComm != null)
{
if (sqlComm.Connection.State == ConnectionState.Open & returnDataType != ReturnType.DataReader) { sqlComm.Connection.Close(); }
sqlComm.Dispose();
}
if (sqlDA != null) { sqlDA.Dispose(); }
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (disposed)
return;
if (disposing)
{
}
disposed = true;
}
}
}