Answer to Question #200540 in C# for sana

Question

Using disconnected architecture in ADO.NET perform crud operations on the Student Registration form

Answer 1

Create the databse table using the following SCRIPT:

CREATE TABLE [dbo].[Student] (
    [Id]        INT          NOT NULL,
    [FirstName] VARCHAR (20) NULL,
    [LastName]  VARCHAR (20) NULL,
    [Semester]  INT          NULL,
    [GPA]       FLOAT (53)   NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (1, N'Mike', N'Clark', 5, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (2, N'Wilson', N'Mccubbin', 3, 4)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (3, N'John', N'Petrillo', 5, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (4, N'Peter', N'Hummer', 6, 2)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (5, N'Gloria', N'Fenderson', 6, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (6, N'Margaret', N'Willams', 4, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (7, N'Ella', N'Ostrowski', 2, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (8, N'Clifford', N'Nilsen', 1, 4)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (9, N'Dennis', N'Niemeyer', 6, 5)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (10, N'Emil', N'Lingenfelter', 6, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (11, N'Harold', N'Lewallen', 6, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (12, N'Winfield', N'Laurence', 6, 4)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (13, N'Levi', N'Langan', 6, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (14, N'Adolph', N'Guyette', 5, 5)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (15, N'Maurice', N'Fluellen', 3, 2)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (16, N'Cornelius', N'Coelho', 4, 2)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (17, N'Felix', N'Bopp', 2, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (18, N'Reuben', N'Beesley', 3, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (19, N'Wallace', N'Agustin', 5, 2)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (20, N'Claud', N'Vale', 4, 3)
INSERT INTO [dbo].[Student] ([Id], [FirstName], [LastName], [Semester], [GPA]) VALUES (21, N'Phillip', NULL, 6, 3)

C Sharp code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;


namespace Q200540
{
    class Program
    {
        /// <summary>
        /// Main method
        /// </summary>
        /// <param name="args"></param>
        static void Main(string[] args)
        {
            string databaseConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\StudentsDatabase.mdf;Integrated Security=True;Connect Timeout=30";
            string tableName = "Students";
            //get all studetns from the database
            DataSet dataSetStudents = new DataSet();
            var connection = new SqlConnection(databaseConnectionString);
            var adapterStudents = new SqlDataAdapter("SELECT * from Student", connection);
            //fill in insert, update, and delete commands
            SqlCommandBuilder cmdBldr = new SqlCommandBuilder(adapterStudents);
            // fill the dataset
            adapterStudents.FillSchema(dataSetStudents, SchemaType.Source, tableName);
            adapterStudents.Fill(dataSetStudents, tableName);


            int Id;
            string firstName;
            string lastName;
            int semester;
            float GPA;


            int userChoice = -1;


            while (userChoice != 5)
            {
                //Main menu
                Console.WriteLine("1. Add a new student.");
                Console.WriteLine("2. Display all students.");
                Console.WriteLine("3. Update a student.");
                Console.WriteLine("4. Remove a student.");
                Console.WriteLine("5. Exit");
                Console.Write("Your choice: ");
                userChoice = int.Parse(Console.ReadLine());


                if (userChoice == 1)//Add a new student
                {
                    Console.Write("Enter the student id: ");
                    int.TryParse(Console.ReadLine(), out Id);
                    DataRow selectedRecord = dataSetStudents.Tables[tableName].Rows.Find(Id.ToString());
                    if (selectedRecord == null)
                    {
                        Console.Write("Enter the student first name: ");
                        firstName = Console.ReadLine();
                        Console.Write("Enter the student last name: ");
                        lastName = Console.ReadLine();
                        Console.Write("Enter the student semester: ");
                        int.TryParse(Console.ReadLine(), out semester);
                        Console.Write("Enter the student GPA: ");
                        float.TryParse(Console.ReadLine(), out GPA);


                        DataRow dataRow = dataSetStudents.Tables[tableName].NewRow(); //Creates a new data row
                        dataRow["Id"] = Id;
                        dataRow["FirstName"] = firstName;
                        dataRow["LastName"] = lastName;
                        dataRow["Semester"] = semester;
                        dataRow["GPA"] = GPA;


                        dataSetStudents.Tables[tableName].Rows.Add(dataRow); //Add the data row
                        adapterStudents.Update(dataSetStudents, tableName);
                        Console.WriteLine("\nA new student has been added.\n");
                    }
                    else {
                        Console.WriteLine("\nSelect other ID.\n");
                    }
                    
                }
                else if (userChoice == 2)//Display all students
                {
                    Console.WriteLine("{0,-10}{1,-20}{2,-20}{3,-20}{4,-20}", "Id", "First Name", "Last Name", "Semester", "GPA");
                    for (int i = 0; i < dataSetStudents.Tables[0].Rows.Count; i++)
                    {
                        Console.WriteLine("{0,-10}{1,-20}{2,-20}{3,-20}{4,-20}", dataSetStudents.Tables[tableName].Rows[i][0], dataSetStudents.Tables[tableName].Rows[i][1],
                            dataSetStudents.Tables[tableName].Rows[i][2], dataSetStudents.Tables[tableName].Rows[i][3], dataSetStudents.Tables[tableName].Rows[i][4]);
                    }
                }
                else if (userChoice == 3)// Update a student
                {
                    Console.Write("Enter the student id you want to edit: ");
                    int.TryParse(Console.ReadLine(), out Id);
                    DataRow selectedRecord = dataSetStudents.Tables[tableName].Rows.Find(Id.ToString());
                    if (selectedRecord != null)
                    {


                        Console.Write("Enter a new student first name: ");
                        firstName = Console.ReadLine();
                        Console.Write("Enter a new student last name: ");
                        lastName = Console.ReadLine();
                        Console.Write("Enter a new student semester: ");
                        int.TryParse(Console.ReadLine(), out semester);
                        Console.Write("Enter a new student GPA: ");
                        float.TryParse(Console.ReadLine(), out GPA);


                        selectedRecord["FirstName"] = firstName;
                        selectedRecord["LastName"] = lastName;
                        selectedRecord["Semester"] = semester;
                        selectedRecord["GPA"] = GPA;
                        adapterStudents.Update(dataSetStudents, tableName);
                        Console.WriteLine("\nThe student's record has been updated.\n");
                    }
                    else
                    {
                        Console.WriteLine("\nThe student ID {0} does not exist.\n", Id);
                    }
                }
                else if (userChoice == 4)//Remove a student
                {
                    Console.Write("Enter the student id you want to remove: ");
                    int.TryParse(Console.ReadLine(), out Id);
                    DataRow selectedRecord = dataSetStudents.Tables[tableName].Rows.Find(Id.ToString());
                    if (selectedRecord != null)
                    {
                        selectedRecord.Delete();
                        adapterStudents.Update(dataSetStudents, tableName);
                        Console.WriteLine("\nThe student's record has been deleted.\n");
                    }
                    else {
                        Console.WriteLine("\nThe student ID {0} does not exist.\n", Id);
                    }
                    
                }
                else if (userChoice == 5)
                {
                    //exit
                }
                else
                {
                    Console.WriteLine("Wrong menu item.");
                }
            }
        }
    }
}