I've had a problem. I want to know how to get a row from sqldatareader in 3-tier?
My code don't run. Could you tell me Why it isn't correct ?
this is my code:
DAL:
public static SqlDataReader Clerk_Search(string name, string family, string melliCode)
{
SqlDataReader sqlDataReader;
SqlCommand comm = new SqlCommand("Clerk_Search", Data_Access.conn);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@clk_Name", name);
comm.Parameters.AddWithValue("@clk_Family", family);
comm.Parameters.AddWithValue("@clk_MeliCod", melliCode);
conn.Open();
sqlDataReader = comm.ExecuteReader();
conn.Close();
return sqlDataReader;
}
BL:
static public System.Data.SqlClient.SqlDataReader Clerk_Search(string name, string family, string meliCode)
{
System.Data.SqlClient.SqlDataReader sqlDataReader;
sqlDataReader = Data_Access.Clerk_Search(name, family, meliCode);
return sqlDataReader;
}
UI
private void Clerk_Search_Click(object sender, EventArgs e)
{
//System.Data.SqlClient.SqlDataReader sqlDataReader;
System.Data.SqlClient.SqlDataReader red;
red = Business.Clerk_Search(txtNameClerk.Text.Trim(), txtFamilyClerk.Text.Trim(), txtShomarehyeMeliClerk.Text.Trim());
txtClerkId.Text = red["clk_Id"].ToString();
}
Anu VPosted Jan 7, 2016, 4:43 AM
Well, the basic idea of a layered architecture is to decouple the different components for several reasons. Some reasons are testability, maintainability, extensibility but there are many more.
To pass the data between these layers - well it depends a bit on the kind of data - but usually you would use some simple classes as data transfer objects (DTO), which would be filled with data in the DAL. E.g.
With your approach you are breaking this idea, because you're passing the
DataReaderto presentation layer which implies, that you cannot switch the DAL technology without touching the other layers. E.g. if you want to use Entity Framework you would have to modify every part in the code, where you're currently using theSqlDataReader.You can also see, if you'd stick to the idea behind the layered approach, you don't have to think about your second question.
I hope this helps a little bit.
EDIT
Ok I'm a bit curious, that you don't find a proper solution. But anyways, the first and simplest approach could be, don't publish the
SqlDataReader. Handle its life cycle in the DAL. Means, assuming that you're using my DTO abovewould be the much better approach.
Posted Jul 31, 2013, 5:55 AM
Note : The code is not tested.
Posted Jul 31, 2013, 5:50 AM
elham deljooeiPosted Jul 31, 2013, 5:48 AM
Could you ask you to give me your e-mail?
My e-mail is [email protected].
Posted Jul 31, 2013, 5:42 AM
public static string Clerk_Search(string name, string family, string melliCode)
{
stirng clerkID = "";
SqlDataReader sqlDataReader;
SqlCommand comm = new SqlCommand("Clerk_Search", Data_Access.conn);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@clk_Name", name);
comm.Parameters.AddWithValue("@clk_Family", family);
comm.Parameters.AddWithValue("@clk_MeliCod", melliCode);
conn.Open();
sqlDataReader = comm.ExecuteReader();
if (sqlDataReader.HasRows)
{
sqlDataReader.Read();
clerkID = Conver.ToString(sqlDataReader["clerkID"]);
}
conn.Close();
return clerkID;
}
Posted Jul 31, 2013, 5:40 AM
What you're trying to return from datareader?
elham deljooeiPosted Jul 31, 2013, 5:39 AM
Your mean is that i have to put datareader into datatable and then return it?
Could you tell me what's your mean exactly?
Posted Jul 31, 2013, 5:35 AM
BLL : Receive the DAL value and convert into Business object, may be your custom class, or string, int etc.
UI: Receive the value from BLL and assign into UI components like textbox, grid etc.
public static string Clerk_Search(string name, string family, string melliCode)
{
stirng clerkID = "";
SqlDataReader sqlDataReader;
SqlCommand comm = new SqlCommand("Clerk_Search", Data_Access.conn);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@clk_Name", name);
comm.Parameters.AddWithValue("@clk_Family", family);
comm.Parameters.AddWithValue("@clk_MeliCod", melliCode);
conn.Open();
sqlDataReader = comm.ExecuteReader();
if (sqlDataReader.HasRows)
{
sqlDataReader.Read();
clerkID = Conver.ToString(sqlDataReader["clerkID"]);
}
conn.Close();
return clerkID;
}
BL:
static public string Clerk_Search(string name, string family, string meliCode)
{
return DAL.Clerk_Search(.......);
}
UI
private void Clerk_Search_Click(object sender, EventArgs e)
{
//System.Data.SqlClient.SqlDataReader sqlDataReader;
string ClerkId = Business.Clerk_Search(txtNameClerk.Text.Trim(), txtFamilyClerk.Text.Trim(), txtShomarehyeMeliClerk.Text.Trim());
txtClerkId.Text = ClerkId;
}
Posted Jul 31, 2013, 5:30 AM
Your Database object should be exposed to presentation layer. You need to convert the datareader into result into Business logic layer itself.