ASP.NET C# Data Access Layer function issue
I am using the 3-tier architecture in my .NET site. Currently in my DAL (Data Access Layer) I have Load, Update, Insert and Delete which is all working fine. However I now want to create a function to grab data by an email address.
Below is my load
, insert
and getByEmailAddress
that reside in my Data Access Layer
public DataTable Load()
{
SqlConnection conn = new SqlConnection(MyGlobals.conString);
SqlDataAdapter dAd = new SqlDataAdapter("administratorGetAll", conn);
dAd.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet dSet = new DataSet();
try
{
dAd.Fill(dSet, "AdministratorsTable");
return dSet.Tables["AdministratorsTable"];
}
catch
{
throw;
}
finally
{
dSet.Dispose();
dAd.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// Used to load records from database by email address
/// </summary>
public int GetByEmailAddress(AdministratorsBO administrator)
{
SqlConnection conn = new SqlConnection(MyGlobals.conString);
SqlCommand dCmd = new SqlCommand("administratorGetByEmailAddress", conn);
dCmd.CommandType = CommandType.StoredProcedure;
DataSet dSet = new DataSet();
try
{
dCmd.Parameters.AddWithValue("@emailAddress", administrator.EmailAddress);
SqlDataAdapter dAd = new SqlDataAdapter(dCmd);
dAd.Fill(dSet, "AdministratorsTable");
return Convert.ToInt32(dSet.Tables["AdministratorsTable"]);
}
catch
{
throw;
}
finally
{
dSet.Dispose();
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// Used to insert records into database
/// </summary>
public int Insert(AdministratorsBO administrator)
{
SqlConnection conn = new SqlConnection(MyGlobals.conString);
conn.Open();
SqlCommand dCmd = new SqlCommand("administratorInsert", conn);
dCmd.CommandType = CommandType.StoredProcedure;
try
{
dCmd.Parameters.AddWithValue("@userName", administrator.UserName);
dCmd.Parameters.AddWithValue("@emailAddress", administrator.EmailAddress);
开发者_JS百科 dCmd.Parameters.AddWithValue("@password", administrator.Password);
dCmd.Parameters.AddWithValue("@firstName", administrator.FirstName);
dCmd.Parameters.AddWithValue("@lastName", administrator.LastName);
dCmd.Parameters.AddWithValue("@isActive", administrator.IsActive);
return dCmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
dCmd.Dispose();
conn.Close();
conn.Dispose();
}
}
Next is my Business Access Layer:
public DataTable Load()
{
AdministratorsDAL aDAL = new AdministratorsDAL();
try
{
return aDAL.Load();
}
catch
{
throw;
}
finally
{
aDAL = null;
}
}
/// <summary>
/// Load records from database
/// </summary>
/// <returns></returns>
public DataTable GetByEmailAddress(AdministratorsBO administrator)
{
AdministratorsDAL aDAL = new AdministratorsDAL();
try
{
return aDAL.GetByEmailAddress(administrator);
}
catch
{
throw;
}
finally
{
aDAL = null;
}
}
/// <summary>
/// Insert records into database
/// </summary>
/// <param name="person"></param>
public int Insert(AdministratorsBO administrator)
{
AdministratorsDAL aDAL = new AdministratorsDAL();
try
{
return aDAL.Insert(administrator);
}
catch
{
throw;
}
finally
{
aDAL = null;
}
}
The Load and Insert are pretty standard and work. However the getByEmailAddress
is not configured properly. I tried mixing code from the load and insert but I was unsuccessful. Can anyone give me a hint, idea or something that would fix this so I can use getByEmailAddress
By the way, that function is supposed to hit a stored procedure that gathers all information by whatever emailAddress input is sent through.
Thanks in advance!
Your Business Layer is returning a DataTable for GetByEmailAddress but your Data Access Layer is returning an int for GetByEmailAddress. Don't you want in your DAL:
public DataTable GetByEmailAddress(AdministratorsBO administrator)
and the return changed to:
return dSet.Tables["AdministratorsTable"];
Referring from you business layer you should be returning a datatable not an integer
public DataTable GetByEmailAddress(AdministratorsBO administrator)
{
Using (SqlConnection conn = new SqlConnection(MyGlobals.conString))
{
SqlCommand dCmd = new SqlCommand("administratorGetByEmailAddress", conn);
dCmd.CommandType = CommandType.StoredProcedure;
DataSet dSet = new DataSet();
dCmd.Parameters.AddWithValue("@emailAddress", administrator.EmailAddress);
SqlDataAdapter dAd = new SqlDataAdapter(dCmd);
dAd.Fill(dSet);
return dSet.Tables[0];
}
}
精彩评论