How to get a row from Sqldatareader in 3-tier?

Question

Hi everyone

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 V · Answer

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. public class Person { public string Name { get ; set ;} public string FirstName { get ; set ;} ... } With your approach you are breaking this idea because you're passing the DataReader to 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 the SqlDataReader . 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 above public class HomeDAL { public List Person > DefaultSearchFriends ( long userid ) { SqlConnection SocialConn = new SqlConnection ( connstr ); using ( SqlCommand comm = new SqlCommand ( proc_FriendsSearch  SocialConn )) { comm . CommandType = CommandType . StoredProcedure ; comm . Parameters . AddWithValue ( @userid  userid ); SocialConn . Open (); SqlDataReader dr = comm . ExecuteReader ( CommandBehavior . CloseConnection ); var persons = new List Person >(); while ( dr . Read ()) persons . Add ( new Person { Name = dr [ Name ] FirstName = dr [ FirstName ] }); dr . Close (); return persons ; } } } would be the much better approach.

Muralidharan Deenathayalan · Answer

public static DataTable Clerk_Search(string name string family string melliCode) { stirng clerkID = ; 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(); SqlDataAdapter adap = new SqlDataAdapter(); adap.selectCommand = comm; DataSet ds = new DataSet(); ds.Fill(adap); return ds.Tables[0]; } BL: static public SearchResult Clerk_Search(string name string family string meliCode) { DataTable table = DAL.Clerk_Search(.......); Convert your datatable into SearchResult or something similar to your business object/ } UI private void Clerk_Search_Click(object sender EventArgs e) { SearchResult result = Business.Clerk_Search(txtNameClerk.Text.Trim() txtFamilyClerk.Text.Trim() txtShomarehyeMeliClerk.Text.Trim()); txtClerkId.Text = result.ClerkId; } Note : The code is not tested.

Muralidharan Deenathayalan · Answer

Test mail sent to you.

elham deljooei · Answer

If i want to return a row from datare... Do i have to do it or use datatable? Could you ask you to give me your e-mail? My e-mail is Elhamdeljooei66@gmail.com.

Muralidharan Deenathayalan · Answer

If you want to return clerkid from datareader 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; }

Muralidharan Deenathayalan · Answer

Read the value from datareader and return the result to the BLL. What you're trying to return from datareader?

elham deljooei · Answer

Thanks for your help Your mean is that i have to put datareader into datatable and then return it? Could you tell me what's your mean exactly?

Muralidharan Deenathayalan · Answer

DAL : Read the database value and return in DataTable DataSetor string or int format. 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; }

Muralidharan Deenathayalan · Answer

Change the code Your Database object should be exposed to presentation layer. You need to convert the datareader into result into Business logic layer itself. public static DataTable 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 dataTable; //change here. } BL: static public SearchResult Clerk_Search(string name string family string meliCode) { Convert datatable into buinsess object here } UI private void Clerk_Search_Click(object sender EventArgs e) { //System.Data.SqlClient.SqlDataReader sqlDataReader; SearchResult result = Business.Clerk_Search(txtNameClerk.Text.Trim() txtFamilyClerk.Text.Trim() txtShomarehyeMeliClerk.Text.Trim()); txtClerkId.Text = result.ClerkId }