C# – Read, Insert, Update, Delete From SQL Database

Code snippets for reading, inserting, updating and deleting from SQL database.

static void Read()
{
    try
    {
        string connectionString =
            "server=.;" +
            "initial catalog=employee;" +
            "user id=sa;" +
            "password=sa123";
        using (SqlConnection conn =
            new SqlConnection(connectionString))
        {
            conn.Open();
            using (SqlCommand cmd =
                new SqlCommand("SELECT * FROM EmployeeDetails", conn))
            {
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("Id = ", reader["Id"]);
                        Console.WriteLine("Name = ", reader["Name"]);
                        Console.WriteLine("Address = ", reader["Address"]);
                    }
                }

                reader.Close();
            }
        }
    }
    catch (SqlException ex)
    {
        //Log exception
        //Display Error message
    }
}

static void Insert()
{
    try
    {
        string connectionString =
            "server=.;" +
            "initial catalog=employee;" +
            "user id=sa;" +
            "password=sa123";
        using (SqlConnection conn =
            new SqlConnection(connectionString))
        {
            conn.Open();
            using (SqlCommand cmd =
                new SqlCommand("INSERT INTO EmployeeDetails VALUES(" +
                    "@Id, @Name, @Address)", conn))
            {
                cmd.Parameters.AddWithValue("@Id", 1);
                cmd.Parameters.AddWithValue("@Name", "Amal Hashim");
                cmd.Parameters.AddWithValue("@Address", "Bangalore");

                int rows = cmd.ExecuteNonQuery();

                //rows number of record got inserted
            }
        }
    }
    catch (SqlException ex)
    {
        //Log exception
        //Display Error message
    }
}

static void Update()
{
    try
    {
        string connectionString =
            "server=.;" +
            "initial catalog=employee;" +
            "user id=sa;" +
            "password=sa123";
        using (SqlConnection conn =
            new SqlConnection(connectionString))
        {
            conn.Open();
            using (SqlCommand cmd =
                new SqlCommand("UPDATE EmployeeDetails SET Name=@NewName, Address=@NewAddress" +
                    " WHERE Id=@Id", conn))
            {
                cmd.Parameters.AddWithValue("@Id", 1);
                cmd.Parameters.AddWithValue("@Name", "Munna Hussain");
                cmd.Parameters.AddWithValue("@Address", "Kerala");

                int rows = cmd.ExecuteNonQuery();

                //rows number of record got updated
            }
        }
    }
    catch (SqlException ex)
    {
        //Log exception
        //Display Error message
    }
}

static void Delete()
{
    try
    {
        string connectionString =
            "server=.;" +
            "initial catalog=employee;" +
            "user id=sa;" +
            "password=sa123";
        using (SqlConnection conn =
            new SqlConnection(connectionString))
        {
            conn.Open();
            using (SqlCommand cmd =
                new SqlCommand("DELETE FROM EmployeeDetails " +
                    "WHERE Id=@Id", conn))
            {
                cmd.Parameters.AddWithValue("@Id", 1);
                
                int rows = cmd.ExecuteNonQuery();

                //rows number of record got deleted
            }
        }
    }
    catch (SqlException ex)
    {
        //Log exception
        //Display Error message
    }
}

C# | 3 Tier Architecture

In this article I am going to explain how easily we can build up a 3 layered application using .net framework and c#. I have an Employee database. For demonstration I have cut the scope by talking only to one table, EmployeeDetails.

In 3 tier, we are dealing with

1. Data Access Layer

2. Business Layer

3. Presentation Layer

Here is the code of my Data Access Layer

static class DAL
{
    const string ConnectionString = "server=.;initial catalog=Employee;user id=sa;password=sa123";

    public static int ExecuteNonQuery(string commandText, SqlParameter[] parameters)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {
                    foreach (SqlParameter param in parameters)
                        cmd.Parameters.Add(param);

                    int rowsAffected = cmd.ExecuteNonQuery();

                    return rowsAffected;
                }
            }
        }
        catch
        {
            throw;
        }
    }

    public static SqlDataReader GetReader(string commandText, SqlParameter[] parameters)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {
                    foreach (SqlParameter param in parameters)
                        cmd.Parameters.Add(param);

                    SqlDataReader reader = cmd.ExecuteReader();

                    return reader;
                }
            }
        }
        catch
        {
            throw;
        }
    }
}

To resemble the table EmployeeDetails I have created the following entity class

class EmployeeEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }

    public override string ToString()
    {
        StringBuilder sb = new StringBuilder();
        sb.Append("Employee Name = ").Append(this.Name);
        sb.Append(" Have Id = ").Append(this.Id);
        sb.Append(" Lives In = ").Append(this.Address);

        return sb.ToString();
    }
}

Now comes the business layer

static class Employee
{
    public static int AddEmployee(EmployeeEntity e)
    {
        SqlParameter[] param = new SqlParameter[3];
        param[0] = new SqlParameter("@Id", e.Id);
        param[1] = new SqlParameter("@Name", e.Name);
        param[2] = new SqlParameter("@Address", e.Address);

        return DAL.ExecuteNonQuery("INSERT INTO EmployeeDetails VALUES(@Id, @Name, @Address)", param);
    }

    public static EmployeeEntity GetEmployee(int id)
    {
        SqlParameter[] param = new SqlParameter[1];
        param[0] = new SqlParameter("@Id", id);

        SqlDataReader reader = DAL.GetReader("SELECT * FROM EmployeeDetails WHERE Id = @Id", param);

        if (reader.HasRows)
        {
            EmployeeEntity emp = new EmployeeEntity();
            while (reader.Read())
            {
                int temp = 0;
                int.TryParse(reader["Id"].ToString(), out temp);
                emp.Id = temp;
                emp.Name = reader["Name"].ToString();
                emp.Address = reader["Address"].ToString();

                break;
            }
            return emp;

        }
        else
            return null;
    }
}

Finally my presentation

class Program
{
    static void Main(string[] args)
    {
        Employee.AddEmployee(new EmployeeEntity() { Id = 1, Name = "Amal", Address = "MyAddress" });
        Employee.AddEmployee(new EmployeeEntity() { Id = 2, Name = "Hashim", Address = "Hashim Address" });
        Employee.AddEmployee(new EmployeeEntity() { Id = 3, Name = "Rooney", Address = "Manchester" });

        EmployeeEntity emp = Employee.GetEmployee(1);

        Console.WriteLine(emp);
    }
}

For better demonstration, you can think the presentation layer as a windows form application. With a 3 textboxes and one button. You can enter Id, Name and Address and use the button event for adding the data.

SQL Server | Resetting Identity Column

Identity column’s are used when you want to have a serial number for each row that get inserted into the table. In other word, it will create a numeric sequence. We can make a column identity while creating the table. Check this simple query which will create an Employee table.

CREATE TABLE EMPLOYEE(EmpID int identity(1, 1), EmpName varchar(20))

The above statement will create a table named EMPLOYEE with two columns, EmpID and EmpName. EmpID is an identity column, the 1st record will have the EmpID as 1 and all subsequent records will have a value incremented by 1. So if you give identity(1,2), each record will have the EmpID as an increment of 2

At some instant we might require the identity value of the insert statement.

SELECT @@IDENTITY

If you want to insert a value into an identity column you can use the SET IDENTITY_INSERT statement.

Once you delete all the data from the table, the identity column won’t get reset to the initial value. For doing this we must use the following statement.

DBCC CHECKIDENT('Employee', RESEED, 0)

Another approach is to Truncate. This will delete all data as well as reset the Identity column.

TRUNCATE TABLE Employee