public bool AddRecord(TraineeDL dl)
        {
            try
            {
                //establish the connection with sql server
                using (_sqlConnect = DbHandler.Connection)
                {
                    _sqlConnect.Open();
                    using (SqlCommand _insertCmd = new SqlCommand(InsertTrainee, _sqlConnect))
                    {
                        //Map model proprties with sql variables
                        _insertCmd.Parameters.AddWithValue("@name", dl.TraineeName);
                        _insertCmd.Parameters.AddWithValue("@batch", dl.BatchCode);
                        _insertCmd.Parameters.AddWithValue("@doj", dl.DateOfJoining);

                        int i = _insertCmd.ExecuteNonQuery();
                        if (i > 0) //insert is successful
                        {
                            return(true);
                        }
                        else
                        {
                            return(false);
                        }
                    }
                }
            }
            catch
            {
                return(false);
            }
        }
        public List <TraineeDL> DisplayTraineeByDepartment(string searchBatch)
        {
            try
            {
                List <TraineeDL> li = new List <TraineeDL>();
                using (_sqlConnect = DbHandler.Connection)
                {
                    _sqlConnect.Open();
                    using (SqlCommand _selectAllRecords = new SqlCommand(SearchByBatch, _sqlConnect))
                    {
                        _selectAllRecords.Parameters.AddWithValue("@batch", searchBatch);
                        //Use Data Reader to read data in read only forward only mode
                        using (SqlDataReader _reader = _selectAllRecords.ExecuteReader())
                        {
                            //Check if data reader has any row to read from table
                            if (_reader.HasRows)
                            {
                                //read one data at a time and continue the reading till last record
                                while (_reader.Read())
                                {
                                    TraineeDL dl = new TraineeDL();
                                    //under the properties of DL object save data info extracted
                                    //by data reader
                                    dl.TraineeId     = int.Parse(_reader["TraineeId"].ToString());
                                    dl.TraineeName   = _reader["TraineeName"].ToString();
                                    dl.BatchCode     = _reader["BatchCode"].ToString();
                                    dl.DateOfJoining = DateTime.Parse(_reader["DateOfJoining"].ToString());

                                    //Add the class object to list
                                    li.Add(dl);
                                }
                            }
                        }
                    }
                }
                return(li);
            }
            catch
            {
                return(null);
            }
        }
        public TraineeDL SearchById(int searchId)
        {
            TraineeDL dl = new TraineeDL();

            try
            {
                using (_sqlConnect = DbHandler.Connection)
                {
                    _sqlConnect.Open();
                    using (SqlCommand _selectById = new SqlCommand(SearchByID, _sqlConnect))
                    {
                        _selectById.Parameters.AddWithValue("@id", searchId);
                        //Use Data Reader to read data in read only forward only mode
                        using (SqlDataReader _reader = _selectById.ExecuteReader())
                        {
                            //Check if data reader has any row to read from table
                            if (_reader.HasRows)
                            {
                                //read one data at a time and continue the reading till last record
                                while (_reader.Read())
                                {
                                    //under the properties of DL object save data info extracted
                                    //by data reader
                                    dl.TraineeId     = int.Parse(_reader["TraineeId"].ToString());
                                    dl.TraineeName   = _reader["TraineeName"].ToString();
                                    dl.BatchCode     = _reader["BatchCode"].ToString();
                                    dl.DateOfJoining = DateTime.Parse(_reader["DateOfJoining"].ToString());
                                }
                            }
                        }
                    }
                }
                return(dl);
            }
            catch
            {
                return(null);
            }
        }
示例#4
0
        static void Main(string[] args)
        {
            TraineeDL            tr       = new TraineeDL();
            TraineeDataOperation operaion = new TraineeDataOperation();
            string loopInput = string.Empty;
            string loopEntry = string.Empty;

            do
            {
                Console.WriteLine("Enter Menu:\n1. Insert Record\n2. Display Record" +
                                  "\n3. Display By Search Trainee Id\n4. Display By Searched Department Code");
                int menu = int.Parse(Console.ReadLine());
                switch (menu)
                {
                case 1:
                    do
                    {
                        try
                        {
                            Console.WriteLine("Enter Employee Name:");
                            string name = Console.ReadLine();
                            //Batch Code should be in below format:
                            //XXXXYYZZPPP where X is alphabets in capital,Y is number
                            //Z is alphabet in capital,P is number
                            //example:SLPP20DN005 -> correct; slppDN005 or SLPP005DN20- >wrong
                            string pattern      = @"^[A-Z]{4}[0-9]{2}[A-Z]{2}[0-9]{3}$";
                            string batchCode    = string.Empty;
                            bool   patternCheck = false;
                            do
                            {
                                Console.WriteLine("Enter Batch Code:");
                                batchCode    = Console.ReadLine();
                                patternCheck = Regex.IsMatch(batchCode, pattern);
                                if (patternCheck == false)
                                {
                                    Console.WriteLine("Batch Code Wrong..Try Again");
                                }
                                else
                                {
                                    break;
                                }
                            }while (patternCheck == false);
                            Console.WriteLine("Enter Date of Joining (in dd/MM/yyyy):");
                            DateTime doj = DateTime.ParseExact(Console.ReadLine(), "dd/MM/yyyy", null);

                            tr = new TraineeDL(name, batchCode, doj);
                            bool result = operaion.AddRecord(tr);
                            if (result == true)
                            {
                                Console.WriteLine("Data Successfully Inserted");
                            }
                            else
                            {
                                throw new Exception("Data Insertion failed");
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                    }while (loopEntry.Equals("yes", StringComparison.InvariantCultureIgnoreCase));
                    break;

                case 2:
                    List <TraineeDL> storageList = operaion.DisplayAllTraineesInfo();
                    if (storageList.Count > 0)  //data is present list
                    {
                        Console.WriteLine("{0,-20}{1,-20}{2,-20}{3}", "Trainee Id",
                                          "Trainee Name", "Batch Code", "Date of Joining");
                        foreach (var data in storageList)
                        {
                            Console.WriteLine("{0,-20}{1,-20}{2,-20}{3}",
                                              data.TraineeId, data.TraineeName, data.BatchCode,
                                              data.DateOfJoining.ToString("dd-MM-yyyy"));
                        }
                    }
                    else
                    {
                        Console.WriteLine("There is no data under the table for display");
                    }
                    break;

                case 3:
                    Console.WriteLine("Enter Trainee Id to search: ");
                    int       searchId        = int.Parse(Console.ReadLine());
                    TraineeDL searchedTrainee = operaion.SearchById(searchId);
                    if (searchedTrainee != null)  //ie data is found
                    {
                        Console.WriteLine("Trainee Name: " + searchedTrainee.TraineeName);
                        Console.WriteLine("Batch Code: " + searchedTrainee.BatchCode);
                    }
                    else
                    {
                        Console.WriteLine("No Trainee with searched id found");
                    }
                    break;

                case 4:
                    Console.WriteLine("Enter Batch Code to search:");
                    string           searchCode         = Console.ReadLine();
                    List <TraineeDL> dislayByDepartment = operaion.DisplayTraineeByDepartment(searchCode);
                    if (dislayByDepartment.Count > 0)     //data is present list
                    {
                        Console.WriteLine("{0,-20}{1,-20}{2,-20}{3}", "Trainee Id",
                                          "Trainee Name", "Batch Code", "Date of Joining");
                        foreach (var data in dislayByDepartment)
                        {
                            Console.WriteLine("{0,-20}{1,-20}{2,-20}{3}",
                                              data.TraineeId, data.TraineeName, data.BatchCode,
                                              data.DateOfJoining.ToString("dd-MM-yyyy"));
                        }
                    }
                    else
                    {
                        Console.WriteLine("There is no data under the table for searched batch code");
                    }
                    break;
                }
                Console.WriteLine("Press yes to continue..Any other key to stop:");
                loopInput = Console.ReadLine();
            }while (loopInput.Equals("yes", StringComparison.InvariantCultureIgnoreCase));
        }