In Database Create the following sample Table -
CREATE TABLE EmployeeInformation(
EmployeeId varchar(50) NOT NULL primary key,
EmployeeName varchar(50) NULL,
Salary decimal(18, 0) NULL,
JoiningDate datetime NULL
)
**Now create a store procedure to fetch all data from EmployeeInformation Table
-- =============================================
-- Author: Amin Uddin
-- Create date: 05/09/2010
-- Description: This store procedure used to fetch all data from EmployeeInformation Table
-- =============================================
Create proc FetchEmployeeInformation
as
SELECT * FROM EmployeeInformation
//End Database section
*** In C#.net write the following code to execute the above SP and Return Data as DataSet
/// <summary>
/// This function is used to execute store Procedure
/// </summary>
/// <returns></returns>
private DataTable FetchData()
{
try
{
string strCon = ConfigurationManager.ConnectionStrings["HRMConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(strCon);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand();
da.SelectCommand.CommandText = "FetchEmployeeInformation";
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Connection = con;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
DataSet ds = new DataSet();
da.Fill(ds);
if (con.State == ConnectionState.Open)
{
con.Close();
}
return ds.Tables[0];
}
catch (Exception )
{
throw;
}
}
*** In C#.net write the following code to execute the above SP and Return Data as List
** Create The Following Class
public class Employee
{
private string eId = "";
public string EId
{
get { return eId; }
set { eId = value; }
}
private string eName = "";
public string EName
{
get { return eName; }
set { eName = value; }
}
private decimal salary = 0.00m;
public decimal Salary
{
get { return salary; }
set { salary = value; }
}
private DateTime joiningDate = DateTime.Now;
public DateTime JoiningDate
{
get { return joiningDate; }
set { joiningDate = value; }
}
}
** Write thefollowing function to execute the sp
/// <summary>
/// This function is used to execute store Procedure
/// </summary>
/// <returns></returns>
private List<Employee> FetchDataAsList()
{
try
{
string strCon = ConfigurationManager.ConnectionStrings["HRMConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(strCon);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand();
da.SelectCommand.CommandText = "FetchEmployeeInformation";
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Connection = con;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
DataSet ds = new DataSet();
da.Fill(ds);
if (con.State == ConnectionState.Open)
{
con.Close();
}
List<Employee> employeeList = new List<Employee>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
Employee employee = new Employee();
employee.EId = dr[0].ToString();
employee.EName = dr[1].ToString();
employee.Salary = Convert.ToDecimal(dr[2].ToString());
employee.JoiningDate = Convert.ToDateTime(dr[3].ToString());
employeeList.Add(employee);
}
return employeeList;
}
catch (Exception)
{
throw;
}
}