Data base connector for sql server database

 I am going to write very basic functions to the connect your data base and retrieving/updating data from the database.
step 1:
you have database connection string which is placed your app.config file or web.config file.
there are many was to write connection string. here is simple example.

I am going to create a class and handle all database communication through this.

Step 2 :
create a class Name as "DbConnection.cs" and add static string attribute for keep connection string. and there is method to GetConnection to get db connection. also retrieving data , updating data there has separate method. follow the following example ;

using System.Configuration;
using System.Data.SqlClient;
using System.Data;



public class DbConnection
    {
        private static string ConnSQL;

// read sql connection from the configuration file.
        public DbConnection()
        {          
            ConnSQL = ConfigurationManager.ConnectionStrings["ConnStr"].ToString();
        }

// create sql connection
        protected static SqlConnection GetConnection()
        {
            SqlConnection sqlConnection = new SqlConnection(ConnSQL);
            return sqlConnection;
        }

// call this function and get dataTable return from the database
        public  DataTable getDataTablebyView(string viewName)
        {
            if (viewName == null || viewName == string.Empty)
            {
                return null;
            }
            DataTable dt = new DataTable();
            SqlConnection conn = GetConnection();
            SqlCommand cmd = new SqlCommand("select * from  " + viewName, conn);
            try
            {
                conn.Open();
                SqlDataAdapter Da = new SqlDataAdapter(cmd);
                Da.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                // error log
                return null;
            }
            finally
            {
                conn.Close();
            }
        }
//pass parameter for insert/ update/delete operation

pakageName - the name of sql pakage
list - this is the parameter list with vale

        public string SaveData(string pakageName, List list)
        {
            string result = "-1";
            SqlConnection conn = GetConnection();
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = pakageName;
                cmd.CommandType = CommandType.StoredProcedure;

                foreach(SqlParameter pram in list)
                {
                    cmd.Parameters.Add(pram);                
                }
             
                int val = cmd.ExecuteNonQuery();
                return val.ToString();          
            }
            catch (Exception e)
            {
               //log
                result=  "-1|" + e.Message;
            }
            finally
            {
                conn.Close();
            }

            return result;
        }

    }

Step 3
This step is show how to pass parameter to above method

insert new user
public string AddUser()
        {
            string pakaName = "TEST_UserInfo_Insert";
            List list = new List();

            SqlParameter sqlpmUserName = new SqlParameter("@UserName", SqlDbType.VarChar, 250);
            sqlpmUserName.Value = "dinuka";
            list.Add(sqlpmUserName);

            SqlParameter sqlpmAddress = new SqlParameter("@Address", SqlDbType.VarChar, 250);
            //sqlpmAddress.Value = "Sri Lanka";
            if (user.Address == null)
                sqlpmAddress.Value = DBNull.Value;
            else
                sqlpmAddress.Value = "Sri Lanka";
            list.Add(sqlpmAddress);

            SqlParameter sqlpmTpNumber = new SqlParameter("@TpNumber", SqlDbType.Int);
            sqlpmTpNumber.Value = 123123;
            list.Add(sqlpmTpNumber);

            DbConnection dbConn = new DbConnection();
            string result = dbConn.SaveData(pakaName, list);
            return result;
        }


Another method for  get data from the database


public  DataTable  getAllUserList()
        {
         
            DbConnection dbConn = new DbConnection();
            DataTable dtUser = dbConn.getDataTablebyView("VW_UserInfo");         
            return lstUser;
        }