Displaying data in console window from database

Question

I created this simple 'Employee' program to train up my sql. And I decided to show all the data from the database if the user want to.

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

namespace MySQLQueryTest
{
class Program
{
    static void Main(string[] args)
    {
        string response = "";
        while (response != "exit")
        {
            Console.Write(">>");
            response = Console.ReadLine();
            switch (response.ToLower())
            {
                case "emp":
                    employeeMenu();
                    break;
                case "-h":
                    Console.WriteLine("emp\tDisplays the Employee database menu");
                    Console.WriteLine("exit\tWill exit the program");
                    Console.WriteLine("clear\tWill clear the console window");
                    break;
                case "clear":
                    Console.Clear();
                    break;
                default:
                    Console.WriteLine("Invalid Command; for help type -h");
                    break;

            }
        }
    }
    static void employeeMenu()
    {
        Console.WriteLine("-------------Menu-------------");
        Console.WriteLine("Type 'list' to list all employees.");
        Console.WriteLine("Type 'add' to add an employee.");
        Console.WriteLine("------------------------------");
        string response = "";
        while (response != "exit")
        {
            Console.Write("EmployeeMenu>");
            response = Console.ReadLine();
            switch (response.ToLower())
            {
                case "add":
                    getInfo();
                    break;
                case "exit":
                    Console.WriteLine("Returning to Main Menu...");
                    break;
                case "list":
                    Console.WriteLine("Here I will display all the SQL data");
                    break;
                default:
                    Console.WriteLine("Invalid Command\n");

                    break;
            }
        }

    }
    static void getInfo()
    {
        Console.Write("First Name: ");
        string fname = Console.ReadLine();
        Console.Write("Last Name: ");
        string lname = Console.ReadLine();
        Console.Write("Job: ");
        string job = Console.ReadLine();
        addEmployee(fname, lname, job);
    }
    static void addEmployee(string the_fname, string the_lname, string the_job)
    {
        var connection = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename='c:\users\omri\documents\visual studio 2012\Projects\Map\Map\db.mdf';Integrated Security=True");
        SqlCommand cmd;
        connection.Open();

        try
        {
            cmd = connection.CreateCommand();
            cmd.CommandText = "INSERT INTO Employees(fname, lname, job) values('" + the_fname + "', '" + the_lname + "', '" + the_job + "');";
            cmd.ExecuteNonQuery();

            Console.WriteLine("Added " + the_fname + " " + the_lname + " to employee database.");
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (connection.State == System.Data.ConnectionState.Open)
            {
                connection.Close();
            }
        }
    }
}

}

I was searching the internet for some answers, but no success, the closest thing I found was this: Displaying result of query .

Not sure this is what i'm looking for. So, my question is how can I show all the Data from Employees? Thank you in advance!

Answer 1

Homework assignment?

You already have code that runs SQL queries when you are adding employees into your database. To show all the employees in your database you want to do something similar to your addEmployee method, but instead of running an INSERT sql command you want to SELECT

        using (SqlConnection connection = new SqlConnection(YOURCONNECTIONSTRING))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand("SELECT * FROM Employees", connection))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            Console.WriteLine(reader.GetValue(i));
                        }
                        Console.WriteLine();
                    }
                }
            }
        }