Execute Store Procedure In C#.net

Get Month Name In C#.net

2013-04-11T04:01:00.003-07:00

public string GetMonthName(int monthNo)
    {
        var formatInfoinfo = new DateTimeFormatInfo();
        string[] monthName = formatInfoinfo.MonthNames;
        return monthName[monthNo];
    }

Using Store Procedure How to Insert/store data into database table

2010-09-17T11:19:00.000-07:00

**Using Store Procedure How to Insert/store data into database table.
** In Database section create the following store procedure.

   -- =============================================
-- Author: Amin Uddin
-- Create date: 05/09/2010
-- Description: This store procedure used to save/store/ Insert all data of EmployeeInformation Table
-- =============================================
Create PROC InsertEmployeeInformation
        (
           @EmployeeId varchar(50),
           @EmployeeName varchar(50),
           @Salary decimal(18, 0),
           @JoiningDate datetime
       )
     asBegin
      if not exists(select employeeId from EmployeeInformation where employeeId = @EmployeeId)
          Begin
               Insert Into EmployeeInformation values(@EmployeeId,@EmployeeName,@Salary,@JoiningDate)
              return 1
         End
    else
      Begin
         return 0
        End
End
*********************************************************************************

** In C#.net Portion

public int InsetEmployeeInformation(Employee employee)
{
SqlConnection con = new SqlConnection();
SqlDataAdapter da = new SqlDataAdapter();
CommandType.StoredProcedure;
da.SelectCommand.CommandText = "InsertEmployeeInformation";
da.SelectCommand.CommandType = System.Data.
da.SelectCommand.Connection = con;
da.SelectCommand.Parameters.Add("@EmployeeId",System.Data.SqlDbType.VarChar, 50).Value = employee.EId;
da.SelectCommand.Parameters.Add("@EmployeeName", System.Data.SqlDbType.VarChar, 50).Value = employee.EName;
da.SelectCommand.Parameters.Add("@Salary", System.Data.SqlDbType.Decimal).Value = employee.Salary;
da.SelectCommand.Parameters.Add("@JoiningDate", System.Data.SqlDbType.Date).Value = employee.JoiningDate;
if (con.State == ConnectionState.Closed)
{
con.Open();
}

{

}if (da.SelectCommand.ExecuteNonQuery() == 1)
{
return 1;
}
else
{
return 0;
}
if (con.State == ConnectionState.Open)
{con.Close();

}
}

How To Fetch Data From Database Using Store Procedure

2010-09-04T13:35:00.000-07:00

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;
}
}