Using C# Interfaces to Make Applications Resilient to Changes

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

namespace DataLayer
{
    public class UserRepository: IUserRepository
    {
        //Change connection string as per your sql server
        //Store it in App.Config 
        private const string strConection = "Data Source=localhost;Initial Catalog=test;User Id=testUser;Password=welcome;";
        #region IUserRepository Members
        /// <summary>
        /// Returns Total Number of Users using Stored Procedure "SampleDb_TotalUsers"
        /// </summary>
        /// <returns>int</returns>
        public int GetTotalUsers()
        {
            try
            {
                int totalUsers = -1;
                SqlConnection myCon = new SqlConnection(strConection);
                myCon.Open();
                SqlCommand com = new SqlCommand();
                com.Connection = myCon;
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "SampleDb_TotalUsers";
                SqlParameter paramOut = new SqlParameter("@TotalUsers", DbType.Int32);
                paramOut.Direction = ParameterDirection.Output;
                com.Parameters.Add(paramOut);
                com.ExecuteNonQuery();
                if (com.Parameters["@TotalUsers"] != null)
                {
                    totalUsers = Convert.ToInt32(com.Parameters["@TotalUsers"].Value);
                }
                myCon.Close();
                return totalUsers;
            }
            catch (SqlException sExp)
            {
                throw new Exception(sExp.ToString());
            }
            catch (Exception exp)
            {
                throw new Exception(exp.ToString());
            }
        }
        /// <summary>
        /// Inserts new user 
        /// </summary>
        /// <param name="fName">string</param>
        /// <param name="lName">string</param>
        public void AddNewUser(string fName, string lName)
        {
            try
            {
                SqlConnection myCon = new SqlConnection(strConection);
                myCon.Open();
                SqlCommand com = new SqlCommand();
                com.Connection = myCon;
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "SampleDb_AddUser";

                SqlParameter paramFirstName = new SqlParameter("@FirstName", SqlDbType.NVarChar, 50);
                paramFirstName.Value = fName;
                paramFirstName.Direction = ParameterDirection.Input;
                com.Parameters.Add(paramFirstName);

                SqlParameter paramLastName = new SqlParameter("@LastName", SqlDbType.NVarChar, 50);
                paramLastName.Value = lName;
                paramLastName.Direction = ParameterDirection.Input;
                com.Parameters.Add(paramLastName);

                com.ExecuteNonQuery();
                myCon.Close();
            }
            catch (SqlException sExp)
            {
                throw new Exception(sExp.ToString());
            }
            catch (Exception exp)
            {
                throw new Exception(exp.ToString());
            }
        }
        /// <summary>
        /// Deletes user based on ID
        /// </summary>
        /// <param name="userID"></param>
        public  void DeleteUser(int userID)
        {
            try
            {
                SqlConnection myCon = new SqlConnection(strConection);
                myCon.Open();
                SqlCommand com = new SqlCommand();
                com.Connection = myCon;
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "SampleDb_DeleteUser";

                SqlParameter paramUserID = new SqlParameter("@UserID", SqlDbType.Int,32);
                paramUserID.Value = userID;
                paramUserID.Direction = ParameterDirection.Input;
                com.Parameters.Add(paramUserID);

                com.ExecuteNonQuery();
                myCon.Close();
            }
            catch (SqlException sExp)
            {
                throw new Exception(sExp.ToString());
            }
            catch (Exception exp)
            {
                throw new Exception(exp.ToString());
            }
        }
        #endregion
    }
}