Seminar Ticket Booking C# ADO.NET Cognizant Handson Solution

SEMINAR TICKET BOOKING USING ADO DOT NET

Scenario:

Seminar ticket booking capture the name and seat number.  We need to implement New booking, View all the booking and update booking using ado .net with SQLSERVER database.

Table Details:

Table Name  : Booking

Field Name	Datatype
Id – Primary key – Auto Increment	int
Name	varchar(50)
Seatno	varchar(20)

Implement following steps:

1)      Use below methods

Method Name	Argument	Return Type	Access Specifier	Responsibilities
NewBooking	string name, string seatno	void	public	New booking Insert a record into table. ‘Id’ not need to pass, is auto increment by one.
GetAllBooking		void	public	Retrieve all the booking from the booking table.Note : Use query to retrieve only name and seatno column from the table. Display ONLY these 2 column from all the booking details.

UpdateBooking

string name, string seatno,

void

public

Here we having update option for only the name.Update the record name against the seat number

2)      For the main logic,

Method Name	Argument	Return type	Access Specifier	Responsibilities
MainMenu	N/A	bool	private static	Here we are,Call the ”GetAllBooking” method to display the data from database.Then we have 3 menu options, Seminar Ticket Booking1. New Booking2. View All Booking3. Update Booking 4. Exit If ‘1’ then, need to be capture name and seat number insert to table. If ‘2’ then, retrieve all the data from the table and display the name and seat number If ‘3’ then, capture both name and seat number, name only wee can update here against the seat number.

 3) Use ‘ConnectionString‘ as the connection string name to connect to the database. This is given in App.config.

YOU NEED NOT MAKE ANY CHANGES TO App.config

Sample Input/Output:

S.No	Sample Input & Output
1	Seminar Ticket Booking1. New Booking2. View All Booking3. Update Booking 4. Exit1Booking Name :ShyamSeat No. :A20—New Booking Saved—1   Shyam   A20
2	Seminar Ticket Booking1. New Booking2. View All Booking3. Update Booking 4. Exit2 Shyam  A20

3	Seminar Ticket Booking1. New Booking2. View All Booking3. Update Booking 4. Exit3 Booking Name :Shyam SaravanSeat No. :A20—Booking Updated—1   Shyam Saravan    A20

Program.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SeminarTicketBooking  //DO NOT change the namespace name
{
    public class Program   //DO NOT change the class name
    {
        public static string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        public static SqlConnection con = new SqlConnection(ConnectionString);
        public static void Main(string[] args)
        {
            bool showMenu = true;
            while (showMenu)
            {
                showMenu = MainMenu();
            }
        }

        private static bool MainMenu()
        {
            int input = 0;
            string name; string seatno;

            Program pgm = new Program();

            Console.WriteLine("Seminar Ticket Booking");
            Console.WriteLine("1. New Booking");
            Console.WriteLine("2. View All Booking");
            Console.WriteLine("3. Update Booking");
            Console.WriteLine("4. Exit");
            input = Convert.ToInt32(Console.ReadLine());

            if (input == 4)
            {
                return false;
            }

            if (input == 1)
            {
                Console.WriteLine("Booking Name : ");
                name = Console.ReadLine();

                Console.WriteLine("Seat No. : ");
                seatno = Console.ReadLine();

                pgm.NewBooking(name, seatno);
            }
            if (input == 2)
            {
                pgm.GetAllBooking();
            }
            else if (input == 3)
            {
                Console.WriteLine("Booking Name : ");
                name = Console.ReadLine();

                Console.WriteLine("Seat No. : ");
                seatno = Console.ReadLine();

                pgm.UpdateBooking(name, seatno);
            }


            Console.WriteLine();
            return true;
        }

        public void GetAllBooking()  //DO NOT change method signature
        {
            //Fill your code here
            string query = "select Name, Seatno from Booking";
            con.Open();
            SqlCommand cmd = new SqlCommand(query, con);
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine(reader["Name"] + "" + reader["Seatno"]);
                }
            }
            con.Close();

        }
        public void NewBooking(string name, string seatno)  //DO NOT change method signature
        {
            //Fill Code here
            string query = "insert into Booking(Name,Seatno) values ('" + name + "','" + seatno + "')";
            con.Open();
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.ExecuteNonQuery();
            con.Close();

        }
        public void UpdateBooking(string name, string seatno)   //DO NOT change method signature
        {
            //Fill Code here
            string query = "update Booking set Name = '" + name + "' where Seatno = '" + seatno + "'";
            con.Open();
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}