Introduction
Stored procedure helps to make your work easy. With the help of these you just have to supply some parameters only. In the following code, I tried to explain the working with Stored procedure.
namespace CSharp.AStepAhead.usingstoredprocedure
{
public class usingstoredprocedure
{
static string myConStr = "server=(local); integrated security=SSPI;database=HRnPAYROLL";
static SqlConnection myCon;
static SqlCommand myCmd;
static SqlDataReader myDr;
public static void Main()
{
int i = 0;
string empId, fnm, lnm, age;
for (; ; )
{
Console.Clear();
Console.Write("\n 1 Insert Record(s)");
Console.Write("\n 2 Update Record(s)");
Console.Write("\n 3 Delete Record(s)");
Console.Write("\n 4 Display Record(s)");
Console.Write("\n 5 Display Record(s) By Employee ID");
Console.Write("\n 6 Display Record(s) By Name Criteria");
Console.Write("\n 7 Exit\n");
Console.Write("\n Select your option [1-7]: ");
i = int.Parse(Console.ReadLine());
if (i <= 0)
{
Console.WriteLine("\a Invalid Selection!");
Console.ReadLine();
continue;
}
if (i < 7)
{
switch (i)
{
case 1: //Insert record
{
Console.Write(" Enter Employee Id:");
empId = Console.ReadLine();
Console.Write(" Enter First Name:");
fnm = Console.ReadLine();
Console.Write(" Enter Last Name:");
lnm = Console.ReadLine();
Console.Write(" Enter Age:");
age = Console.ReadLine();
Console.WriteLine("\n " + usingstoredprocedure.InsertEmployee(empId, fnm, lnm, age));
Console.ReadLine();
break;
}
case 2:
{
Console.Write(" Enter Employee Id:");
empId = Console.ReadLine();
Console.Write(" Enter First Name:");
fnm = Console.ReadLine();
Console.Write(" Enter Last Name:");
lnm = Console.ReadLine();
Console.Write(" Enter Age:");
age = Console.ReadLine();
Console.WriteLine("\n " + usingstoredprocedure.UpdateEmployee(empId, fnm, lnm, age));
Console.ReadLine();
break;
}
case 3:
{
Console.Write(" Enter Employee Id:");
empId = Console.ReadLine();
Console.WriteLine("\n " + usingstoredprocedure.DeleteEmployee(empId));
Console.ReadLine();
break;
}
case 4:
{
fnm = "";
empId = "";
usingstoredprocedure.dispData(fnm, empId);
break;
}
case 5:
{
Console.Write(" Enter Employee Id:");
empId = Console.ReadLine();
fnm = "";
usingstoredprocedure.dispData(fnm, empId);
break;
}
case 6:
{
Console.Write(" Enter Name:");
fnm = Console.ReadLine();
empId = "";
usingstoredprocedure.dispData(fnm, empId);
break;
}
}
}
if (i == 7)
{
Console.Write("\n You are existing!");
break;
}
}
Console.ReadLine();
Conclusion
Its easy to work with Stored Procedure.