First we need to create database in sql server .I already created database named it students .This table properties like StudentId , Name , Gender etc . I also created the required store procedure as well .Here is the script for create Students table and procedure.
CREATE TABLE [dbo].[Student](
[Student_Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Address] [nvarchar](100) NULL,
[EmailID] [nvarchar](50) NULL,
[Mobile] [varchar](10) NULL
)
CREATE PROCEDURE [dbo].[AddNewStudent]
(
@Name nvarchar(20),
@Address nvarchar(60),
@EmailId nvarchar(15),
@Mobile nvarchar(15)
)
AS
insert into Student
(Name, Address, EmailId, Mobile)
values
(@Name, @Address, @EmailId,@Mobile)
RETURN
GO
(
@Student_Id int
)
AS
delete from Student where Student_Id = @Student_Id
RETURN
GO
CREATE PROCEDURE [dbo].[GetStudentDetails]
(
@Student_Id int
)
AS
Select * from Student where Student_Id = @Student_Id
RETURN
GO
CREATE PROCEDURE [dbo].[GetStudentList]
AS
Select * from Student
RETURN
GO
CREATE PROCEDURE [dbo].[UpdateStudent]
(
@Student_Id int,
@Name nvarchar(20),
@Address nvarchar(60),
@EmailID nvarchar(15),
@Mobile nvarchar(15)
)
AS
update Student set
Name = @Name,
Address = @Address,
EmailID = @EmailID,
Mobile = @Mobile
where Student_Id = @Student_Id
RETURN
GO
We also need to create required class and method in our DLL Layer . Here is the class , methods and code for the methods .
public class Student
{
int _StudentID;
private string _Student_Name;
private string _Address;
private string _Email;
private string _Mobile;
public int StudentID
{
get
{
return _StudentID;
}
set
{
_StudentID = value;
}
}
public string Student_Name
{
get
{
return _Student_Name;
}
set
{
_Student_Name = value;
}
}
public string Address
{
get
{
return _Address;
}
set
{
_Address = value;
}
}
public string Email
{
get
{
return _Email;
}
set
{
_Email = value;
}
}
public string Mobile
{
get
{
return _Mobile;
}
set
{
_Mobile = value;
}
}
}
}
Here is the SQL Class with connection string .
public class Sql
{
const string CONNECTION_STRING = @"Data Source=.;Initial Catalog=3TierInWindowsApplication;Integrated Security=True";
// This function will be used to execute R(CRUD) operation of parameterless commands
internal static DataTable ExecuteSelectCommand(string CommandName, CommandType cmdType)
{
DataTable table = null;
using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
{
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
table = new DataTable();
da.Fill(table);
}
}
catch
{
throw;
}
}
}
return table;
}
// This function will be used to execute R(CRUD) operation of parameterized commands
internal static DataTable ExecuteParamerizedSelectCommand(string CommandName, CommandType cmdType, SqlParameter[] param)
{
DataTable table = new DataTable();
using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
{
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
cmd.Parameters.AddRange(param);
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(table);
}
}
catch
{
throw;
}
}
}
return table;
}
// This function will be used to execute CUD(CRUD) operation of parameterized commands
internal static bool ExecuteNonQuery(string CommandName, CommandType cmdType, SqlParameter[] pars)
{
int result = 0;
using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
{
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
cmd.Parameters.AddRange(pars);
try
{
if (con.State != ConnectionState.Open)
{
con.Open();
}
result = cmd.ExecuteNonQuery();
}
catch
{
throw;
}
}
}
return (result > 0);
}
}
}
Here is the student DataAccess class with store procedure..
public class StudentDBAccess
{public bool AddNewStudent(Student student)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Name", student.Student_Name),
new SqlParameter("@Address", student.Address),
new SqlParameter("@EmailID", student.Email),
new SqlParameter("@Mobile", student.Mobile)
};
return Sql.ExecuteNonQuery("AddNewStudent", CommandType.StoredProcedure, parameters);
}
public bool UpdateStudent(Student student)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Student_Id", student.StudentID),
new SqlParameter("@Name", student.Student_Name),
new SqlParameter("@Address", student.Address),
new SqlParameter("@EmailID", student.Email),
new SqlParameter("@Mobile", student.Mobile)
};
return Sql.ExecuteNonQuery("UpdateStudent", CommandType.StoredProcedure, parameters);
}
public bool DeleteStudent(int studentId)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Student_Id", studentId)
};
return Sql.ExecuteNonQuery("DeleteStudent", CommandType.StoredProcedure, parameters);
}
public Student GetStudentsDetails(int studentId)
{
Student student = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Student_Id", studentId)
};
using (DataTable table = Sql.ExecuteParamerizedSelectCommand("GetStudentDetails", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
student = new Student();
student.StudentID = Convert.ToInt32(row["Student_Id"]);
student.Student_Name = row["Name"].ToString();
student.Address = row["Address"].ToString();
student.Email = row["EmailID"].ToString();
student.Mobile = row["Mobile"].ToString();
}
}
return student;
}
public List<Student> GetStudentList()
{
List<Student> listStudents = null;
using (DataTable table = Sql.ExecuteSelectCommand("GetStudentList", CommandType.StoredProcedure))
{
if (table.Rows.Count > 0)
{
listStudents = new List<Student>();
foreach (DataRow row in table.Rows)
{
Student student = new Student();
student.StudentID = Convert.ToInt32(row["Student_Id"]);
student.Student_Name = row["Name"].ToString();
student.Address = row["Address"].ToString();
student.Email = row["EmailID"].ToString();
student.Mobile = row["Mobile"].ToString();
listStudents.Add(student);
}
}
}
return listStudents;
}
}
}
In our next session we will create Business Logic Layer and add reference from Data Access Layer .
No comments:
Post a Comment