how to use SQL DATA READER in 3-tier Architecture [Resolved]

Posted by Gow.Net under ASP.NET on 9/17/2012 | Points: 10 | Views : 10937 | Status : [Member] | Replies : 4
how to use SQL DATA READER in 3-tier Architecture how get value from database and bind in Text box using sql data reader in 3-tier architecture

gowthaman8870226416


Responses

Posted by: Muhsinathk on: 9/17/2012 [Member] Bronze | Points: 50

Up
0
Down

Resolved
3 Tier architecture have 3 layers

1.Presentation Layer
2.Buisness Layer
3.Data access Layer

//Data Layer

static class DAL
{
const string ConnectionString = "server=.;initial catalog=Employee;user id=sa;password=sa123";

public static int ExecuteNonQuery(string commandText, SqlParameter[] parameters)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
foreach (SqlParameter param in parameters)
cmd.Parameters.Add(param);

int rowsAffected = cmd.ExecuteNonQuery();

return rowsAffected;
}
}
}
catch
{
throw;
}
}

public static SqlDataReader GetReader(string commandText, SqlParameter[] parameters)
{
try
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
foreach (SqlParameter param in parameters)
cmd.Parameters.Add(param);

SqlDataReader reader = cmd.ExecuteReader();

return reader;
}
}
}
catch
{
throw;
}
}
}

To resemble the table EmployeeDetails I have created the following entity class

class EmployeeEntity
{
public int Id { get; set; }
public string Name { get; set; }
public string Address { get; set; }

public override string ToString()
{
StringBuilder sb = new StringBuilder();
sb.Append("Employee Name = ").Append(this.Name);
sb.Append(" Have Id = ").Append(this.Id);
sb.Append(" Lives In = ").Append(this.Address);

return sb.ToString();
}
}

//Buisness Layer

static class Employee
{
public static int AddEmployee(EmployeeEntity e)
{
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@Id", e.Id);
param[1] = new SqlParameter("@Name", e.Name);
param[2] = new SqlParameter("@Address", e.Address);

return DAL.ExecuteNonQuery("INSERT INTO EmployeeDetails VALUES(@Id, @Name, @Address)", param);
}

public static EmployeeEntity GetEmployee(int id)
{
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@Id", id);

SqlDataReader reader = DAL.GetReader("SELECT * FROM EmployeeDetails WHERE Id = @Id", param);

if (reader.HasRows)
{
EmployeeEntity emp = new EmployeeEntity();
while (reader.Read())
{
int temp = 0;
int.TryParse(reader["Id"].ToString(), out temp);
emp.Id = temp;
emp.Name = reader["Name"].ToString();
emp.Address = reader["Address"].ToString();

break;
}
return emp;

}
else
return null;
}
}


//presentation Layer

class Program
{
static void Main(string[] args)
{
Employee.AddEmployee(new EmployeeEntity() { Id = 1, Name = "Amal", Address = "MyAddress" });
Employee.AddEmployee(new EmployeeEntity() { Id = 2, Name = "Hashim", Address = "Hashim Address" });
Employee.AddEmployee(new EmployeeEntity() { Id = 3, Name = "Rooney", Address = "Manchester" });

EmployeeEntity emp = Employee.GetEmployee(1);

Console.WriteLine(emp);
}
}

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hariinakoti on: 9/17/2012 [Member] Starter | Points: 25

Up
0
Down
ho Gow,
see this link:
http://www.dotnetspider.com/forum/292247-How-use-datareader-3-tier-architecture.aspx

Thanks & Regards
Hari

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gow.Net on: 9/17/2012 [Member] Starter | Points: 25

Up
0
Down
hi Hariinakoti i already see that but i cont under stand clearly

my sql text command =
select count(BK_ID) as code  from NewBook 

here i need the solution for code value bind in textbox using sql data reader in 3-tier architecture how?

gowthaman8870226416

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/17/2012 [Member] Bronze | Points: 25

Up
0
Down
Please mark as answer if it helps to you..That helps others who search the same..

Gow.Net, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response