Working with Stored Procedures using asp.net

G_arora
Posted by in ASP.NET category on for Beginner level | Views : 5148 red flag
Rating: 5 out of 5  
 1 vote(s)

Stored procedure helps to make your work easy. With the help of these you just have to supply some parameters only.
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.


Page copy protected against web site content infringement by Copyscape

About the Author

G_arora
Full Name: Gaurav Kumar Arora
Member Level: Starter
Member Status: Member,Microsoft_MVP
Member Since: 9/7/2009 2:53:47 AM
Country: India
Gaurav Kumar Arora
http://www.gaurav-arora.com
MCA, M.Phill(Comp.Sc.),MCP, MCTS, Certified Scrum Master. Life time member of CSI (Computer Society of India)

Login to vote for this post.

Comments or Responses

Posted by: Raja on: 9/9/2009
Hi Gaurav,

I liked your other article but this article is really tough to understand. Most of this article has code snippets and I couldn't understand how this will work for stored procedure like where usingstoredprocedure.InsertEmployee method?

Could you please explain this?

Thanks
Posted by: G_arora on: 9/10/2009
Hello Raja,

Thanks for your appreciation. Need to upload attachment and full code. I will update the article with description within couple of days.

Regards,
Gaurav
Posted by: Vinay13mar on: 10/24/2012 | Points: 25

Login to post response

Comment using Facebook(Author doesn't get notification)