Select, Insert and Delete in MVC using WCF Services

• Download source (RAR) - 13.7 MB

Introduction

In this piece of code, I have consumed a WCF service in MVC web application which comprises one view, one controller and one model. Here, view belongs to one action method but invokes other action method as well for different purposes, i.e. insert and delete.

Using the Code

1. I have taken a blank solution and added 2 different projects in that:
   1. WCF service library project
   2. MVC web application using razor engine
2. In WCF services, I simply used an interface for service operation and data contracts and two additional files for SQL connection and DML operations.
3. In MVC web application, I have used one model which does that part of calling functions from service using proxy class and manipulating them using containers like dataset or lists.
4. There is one controller namely MainController which has an action method "Index" that gets the data from SQL table, which has a view attached "Index.cshtml". Controller has 2 more action methods for insertion and delete functionality.
5. In view, I have used simple HTML controls and strongly typed controls, and JavaScript function to select multiple checkboxes and redirect to action methods.

(some codes in WCF are unused, one may use them to manipulate further.)

//Interface for contracts

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Net.Security;
using System.Data.Objects;
using System.ServiceModel.Description;
using System.ServiceModel.Channels;

namespace WcfServiceLibrary1
{
    // NOTE: You can use the "Rename" command on the "Refactor" 
    // menu to change the interface name 
    // "IService1" in both code and config file together.
    [ServiceContract]
    public interface IREGService
    {
        /// <summary>
        /// Operation contract to insert user details
        /// </summary>
        /// <param name="name">name</param>
        /// <param name="password">password</param>
        /// <param name="country">country</param>
        /// <param name="email">email</param>
        /// <returns>int result if 1 then succeeded else failed</returns>
        [OperationContract]
        int InsertUser(string name, string password, string country, string email);

        /// <summary>
        /// to get the details from the table
        /// </summary>
        /// <returns>DataSet</returns>
        [OperationContract]
        DataSet GetInfo();

        /// <summary>
        /// To Delete Existing Record
        /// </summary>
        /// <param name="ID">String which may contain multiple ids</param>
        /// <returns>int result if 1 then succeeded else failed</returns>
        [OperationContract]
        int DeleteUser(string ID);

        /// <summary>
        /// To update a certain Record
        /// </summary>
        /// <param name="ID">used a filter</param>
        /// <param name="name">name </param>
        /// <param name="password">password</param>
        /// <param name="country">country</param>
        /// <param name="email">email</param>
        /// <returns></returns>
        //[OperationContract]
        //int UpdateDetails(int ID, string name, string password, string country, string email);

        [OperationContract]
        DataSet GetCountry();
    }
    [DataContract]
    public class Users
    {
        private string _UserName;
        [DataMember]
        public string UserName
        {
            get { return _UserName; }
            set { _UserName = value; }
        }

        private string _Password;
        [DataMember]
        public string Password
        {
            get { return _Password; }
            set { _Password = value; }
        }

        private string _Country;
        [DataMember]
        public string Country
        {
            get { return _Country; }
            set { _Country = value; }
        }

        private string _Email;
        [DataMember]
        public string Email
        {
            get { return _Email; }
            set { _Email = value; }
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
namespace WcfServiceLibrary1
{
    // NOTE: You can use the "Rename" command on the "Refactor" 
    // menu to change the class name "Service1" in both code and config file together.
    public class REGService : IREGService
    {
public int InsertUser(string UserName, string Password, string Country, string Email)
        {
            RegistraionDAO rdoa = new RegistraionDAO();
            int Result = 0;
            int OUT_ERR_NO;
            string OUT_ERR_IDEN;
            string OUT_ERR_MSG;
            string strparams = string.Format
            ("Parameters : {0},{1},{2},{3}", UserName, Password, Country, Email);
            try
            {
                Result = rdoa.InsertUser
                (UserName, Password, Country, 
                Email, out OUT_ERR_NO, out OUT_ERR_MSG, out OUT_ERR_IDEN);
            } catch
            {
                Result = -1;
            }
            finally{
                    }  
           return Result;
        }
      public DataSet GetInfo()
        {
            DataSet ods = new DataSet();
            RegistraionDAO rdoa = new RegistraionDAO();
            int Result = -1;
            int OUT_ERR_NO;
            string OUT_ERR_IDEN;
            string OUT_ERR_MSG;
            string strparams = string.Format("Parameters : ");
           try
           {
          Result = rdoa.GetUser
          (out OUT_ERR_NO, out OUT_ERR_MSG, out OUT_ERR_IDEN, out ods);
           }
           catch
            {
            }
            finally
             {

             }
            return ods;         
        }
        public DataSet GetCountry()
        {
            DataSet countrylist = new DataSet();
            RegistraionDAO rdoa = new RegistraionDAO();

            int Result = -1;
            int OUT_ERR_NO;
            string OUT_ERR_IDEN;
            string OUT_ERR_MSG;
            string strparams = string.Format("Parameters : ");
            try
            {
          Result = rdoa.GetCountry
          (out OUT_ERR_NO, out OUT_ERR_MSG, out OUT_ERR_IDEN, out countrylist);
            }
            catch
             {
             }
            finally
             {
             }
             return countrylist;
           }
        }
      }

//SQL Chelper class which contains the functions for SQL connection and manipulations

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

namespace WcfServiceLibrary1
{
    class SqlHelper
    {
        //Database connection strings
        // public static readonly string ConnString = 
        // ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;

        //public static readonly string ConnectionStringInventoryDistributedTransaction = 
        //ConfigurationManager.ConnectionStrings["SQLConnString2"].ConnectionString;
        //public static readonly string ConnectionStringOrderDistributedTransaction = 
        //ConfigurationManager.ConnectionStrings["SQLConnString3"].ConnectionString;
        //public static readonly string ConnectionStringProfile = 
        //ConfigurationManager.ConnectionStrings["SQLProfileConnString"].ConnectionString;

        // Hashtable to store cached parameters
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset) 
        /// against the database specified in the connection string
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, 
        /// "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">
        /// a valid connection string for a SqlConnection</param>
        /// <param name="commandType">the 
        /// CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">
        /// the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">
        /// an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows 
        /// affected by the command</returns>
        public static int ExecuteNonQuery(string connectionString, 
            CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset) 
        /// against an existing database connection
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, 
        /// "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">
        /// the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">
        /// the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">
        /// an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows 
        /// affected by the command</returns>
        public static int ExecuteNonQuery(SqlConnection connection, 
            CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, 
        /// "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">an existing sql transaction</param>
        /// <param name="commandType">
        /// the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">
        /// the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">
        /// an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows 
        /// affected by the command</returns>
        public static int ExecuteNonQuery(SqlTransaction trans, 
            CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// Execute a SqlCommand that returns a resultset 
        /// against the database specified in the connection string
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:
        ///  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, 
        /// "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">
        /// a valid connection string for a SqlConnection</param>
        /// <param name="commandType">
        /// the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">
        /// the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">
        /// an array of SqlParamters used to execute the command</param>
        /// <returns>A SqlDataReader containing the results</returns>
        public static SqlDataReader ExecuteReader(string connectionString, 
            CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);

            // we use a try/catch here because if the method throws an exception, we want to
            // close the connection throw code, because no datareader will exist, hence the
            // commandBehaviour.CloseConnection will not work
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        /// Execute a SqlCommand that returns the first column of the 
        /// first record against the database specified in the connection string
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, 
        /// "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">a valid connection string for a SqlConnection
        /// </param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)
        /// </param>
        /// <param name="commandText">the stored procedure name or T-SQL command
        /// </param>
        /// <param name="commandParameters">
        /// an array of SqlParamters used to execute the command
        /// </param>
        /// <returns>An object that should be 
        /// converted to the expected type using Convert.To{Type}
        /// </returns>
        public static object ExecuteScalar(string connectionString, 
            CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// Execute a SqlCommand that returns the first column 
        /// of the first record against an existing database connection
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, 
        /// "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)
        /// </param>
        /// <param name="commandText">
        /// the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">
        /// an array of SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted 
        /// to the expected type using Convert.To{Type}
        /// </returns>
        public static object ExecuteScalar(SqlConnection connection, 
            CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// add parameter array to the cache
        /// </summary>
        /// <param name="cacheKey">Key to the parameter cache</param>
        /// <param name="cmdParms">
        /// an array of SqlParamters to be cached</param>
        public static void CacheParameters
        (string cacheKey, params SqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>
        /// Retrieve cached parameters
        /// </summary>
        /// <param name="cacheKey">key used to lookup parameters</param>
        /// <returns>Cached SqlParamters array</returns>
        public static SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        /// <summary>
        /// Prepare a command for execution
        /// </summary>
        /// <param name="cmd">SqlCommand object</param>
        /// <param name="conn">SqlConnection object</param>
        /// <param name="trans">SqlTransaction object</param>
        /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
        /// <param name="cmdText">
        /// Command text, e.g. Select * from Products</param>
        /// <param name="cmdParms">SqlParameters to use in the command</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, 
            SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        /// <summary>
        /// Execute a SqlCommand (that returns resultset) against an existing database connection
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:
        ///  DataSet ds = ExecuteDataSet(connString, CommandType.StoredProcedure, 
        /// "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType 
        /// (stored procedure, text, etc.)</param>
        /// <param name="commandText">
        /// the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">
        /// an array of SqlParamters used to execute the command</param>
        /// <returns>an int representing the number of rows affected by the command
        /// </returns>
        public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType,
            string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                cmd.CommandTimeout = Convert.ToInt32
                    (ConfigurationManager.AppSettings.Get("COMMAND_TIME_OUT"));
                PrepareCommand(cmd, con, null, cmdType, cmdText, commandParameters);
                SqlDataAdapter sqlData = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                sqlData.Fill(ds);
                return ds;
            }
        }
    }
}
-------------------------------------------/////--------------------------------------------
//Data Access Class where data is actually manipulated using SQL objects

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

namespace WcfServiceLibrary1
{
    class RegistraionDAO
    {
        private string GetConnectionString()
        {
            // To avoid storing the connection string in your code,
            // you can retrieve it from a configuration file.
            // When the encrypted connection string is retrieved at run time, the .NET Framework
            // uses the specified provider to decrypt the CipherValue and make it
            // available to your application. You do not need to write any additional code to
            // manage the decryption process.
            string strconn;
            strconn = ConfigurationManager.ConnectionStrings
                ["SQLConnString"].ConnectionString;
            return strconn;
        }
        public int InsertUser(string username, string password, 
            string country, string email, out int OUT_ERR_NO,
                                     out string OUT_ERR_MSG,
                                    out string OUT_ERR_IDEN)
        {
            int result = 0;
            string connectionstring = GetConnectionString();
            SqlTransaction objtrans = null;
            SqlConnection objcon = null;
            try
            {
                objcon = new SqlConnection(connectionstring);
                objcon.Open();
                objtrans = objcon.BeginTransaction();
                SqlParameter[] paramsInfo = new SqlParameter[7];
                paramsInfo[0] = new SqlParameter("@IN_USER_NAME", SqlDbType.NVarChar, 100);
                paramsInfo[0].Value = username;
                paramsInfo[1] = new SqlParameter("@IN_PASSWORD", SqlDbType.NVarChar, 100);
                paramsInfo[1].Value = password;
                paramsInfo[2] = new SqlParameter("@IN_COUNTRY", SqlDbType.NVarChar, 100);
                paramsInfo[2].Value = country;
                paramsInfo[3] = new SqlParameter("@IN_EMAIL", SqlDbType.NVarChar, 100);
                paramsInfo[3].Value = email;
                //output parameters

                paramsInfo[4] = new SqlParameter("@OUT_ERR_NO", SqlDbType.Int);
                paramsInfo[4].Direction = ParameterDirection.Output;
                paramsInfo[5] = new SqlParameter("@OUT_ERR_MSG", SqlDbType.VarChar, 500);
                paramsInfo[5].Direction = ParameterDirection.Output;
                paramsInfo[6] = new SqlParameter("@OUT_ERR_IDEN", SqlDbType.VarChar, 500);
                paramsInfo[6].Direction = ParameterDirection.Output;
                SqlHelper.ExecuteNonQuery
                (objtrans, CommandType.StoredProcedure, "PKG_INSERT_USER", paramsInfo);
                OUT_ERR_NO = Convert.ToInt32(paramsInfo[4].Value.ToString());
                OUT_ERR_MSG = paramsInfo[5].Value.ToString();
                OUT_ERR_IDEN = paramsInfo[6].Value.ToString();
                if (OUT_ERR_NO == 0)
                {
                    objtrans.Commit();
                    result = 1;
                }
                else
                {
                    objtrans.Rollback();
                    result = -1;
                }

            }
            catch (Exception excp)
            {
                if (objtrans != null)
                    objtrans.Rollback();
                OUT_ERR_NO = -1;
                OUT_ERR_MSG = excp.Message.ToString();
                OUT_ERR_IDEN = excp.Source.ToString();
            }
            finally
            {
                objtrans = null;
                if (objcon != null)
                {
                    if (objcon.State == ConnectionState.Open)
                        objcon.Close();
                    objcon = null;
                }
            }
            return result;
        }

        public int GetUser(out int OUT_ERR_NO,
                                     out string OUT_ERR_MSG,
                                    out string OUT_ERR_IDEN,
                                           out DataSet ods)
        {
            int _intResult = -1;
            string connectionString = GetConnectionString();

             ods = new DataSet();
            try
            {
                SqlParameter[] paramsInfo = new SqlParameter[3];

                paramsInfo[0] = new SqlParameter("@OUT_ERR_NO", SqlDbType.Int);
                paramsInfo[0].Direction = ParameterDirection.Output;
                paramsInfo[1] = new SqlParameter("@OUT_ERR_MSG", SqlDbType.VarChar, 500);
                paramsInfo[1].Direction = ParameterDirection.Output;
                paramsInfo[2] = new SqlParameter("@OUT_ERR_IDEN", SqlDbType.VarChar, 500);
                paramsInfo[2].Direction = ParameterDirection.Output;

                ods = SqlHelper.ExecuteDataSet(connectionString, CommandType.StoredProcedure,
                            "PKG_GET_USER", paramsInfo);

                OUT_ERR_NO = Convert.ToInt32(paramsInfo[0].Value.ToString());
                OUT_ERR_MSG = paramsInfo[1].Value.ToString();
                OUT_ERR_IDEN = paramsInfo[2].Value.ToString();

                _intResult = 0;
            }
            catch (Exception excp)
            {
                OUT_ERR_NO = -1;
                OUT_ERR_MSG = excp.Message.ToString();
                OUT_ERR_IDEN = excp.Source.ToString();
            }
            finally
            {

            }
            return _intResult;
        }

        public int DeleteUser(string id, out int OUT_ERR_NO,
                                     out string OUT_ERR_MSG,
                                    out string OUT_ERR_IDEN)
        {
            int _intResult = -1;
            string connectionString = GetConnectionString();

            try
            {
                SqlParameter[] paramsInfo = new SqlParameter[4];

                paramsInfo[0] = new SqlParameter("@IN_ID", SqlDbType.VarChar);
                paramsInfo[0].Value = id;

                paramsInfo[1] = new SqlParameter("@OUT_ERR_NO", SqlDbType.Int);
                paramsInfo[1].Direction = ParameterDirection.Output;
                paramsInfo[2] = new SqlParameter("@OUT_ERR_MSG", SqlDbType.VarChar, 500);
                paramsInfo[2].Direction = ParameterDirection.Output;
                paramsInfo[3] = new SqlParameter("@OUT_ERR_IDEN", SqlDbType.VarChar, 500);
                paramsInfo[3].Direction = ParameterDirection.Output;

                SqlHelper.ExecuteDataSet(connectionString, CommandType.StoredProcedure,
                            "PKG_DELETE_USER", paramsInfo);

                OUT_ERR_NO = Convert.ToInt32(paramsInfo[0].Value.ToString());
                OUT_ERR_MSG = paramsInfo[1].Value.ToString();
                OUT_ERR_IDEN = paramsInfo[2].Value.ToString();

                _intResult = 0;
            }
            catch (Exception excp)
            {
                OUT_ERR_NO = -1;
                OUT_ERR_MSG = excp.Message.ToString();
                OUT_ERR_IDEN = excp.Source.ToString();
            }
            finally
            {

            }
            return _intResult;
        }

        public int GetCountry(out int OUT_ERR_NO,
                                     out string OUT_ERR_MSG,
                                    out string OUT_ERR_IDEN,
                                           out DataSet Countrylist)
        {
            int _intResult = -1;
            string connectionString = GetConnectionString();

            Countrylist = new DataSet();
            try
            {
                SqlParameter[] paramsInfo = new SqlParameter[3];
              

                paramsInfo[0] = new SqlParameter("@OUT_ERR_NO", SqlDbType.Int);
                paramsInfo[0].Direction = ParameterDirection.Output;
                paramsInfo[1] = new SqlParameter("@OUT_ERR_MSG", SqlDbType.VarChar, 500);
                paramsInfo[1].Direction = ParameterDirection.Output;
                paramsInfo[2] = new SqlParameter("@OUT_ERR_IDEN", SqlDbType.VarChar, 500);
                paramsInfo[2].Direction = ParameterDirection.Output;

                Countrylist = SqlHelper.ExecuteDataSet(connectionString, CommandType.StoredProcedure,
                            "PKG_GET_COUNTRY", paramsInfo);

                OUT_ERR_NO = Convert.ToInt32(paramsInfo[0].Value.ToString());
                OUT_ERR_MSG = paramsInfo[1].Value.ToString();
                OUT_ERR_IDEN = paramsInfo[2].Value.ToString();

                _intResult = 0;
            }
            catch (Exception excp)
            {
                OUT_ERR_NO = -1;
                OUT_ERR_MSG = excp.Message.ToString();
                OUT_ERR_IDEN = excp.Source.ToString();
            }
            finally
            {

            }
            return _intResult;
        }
       }
   }
///
------------------------------Now add MVC project to the solution--------------------------------
MVC Code:

//Model Class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.ComponentModel.DataAnnotations;
//proxy object
using proxy = MvcRegApp.ServRef.REGServiceClient;
using System.Web.Mvc;
public class UserModel
    {
        proxy Objserv = new proxy();

        private string _UserName;

        public string UserName
        {
            get { return _UserName; }
            set { _UserName = value; }
        }

        private string _Password;

        public string Password
        {
            get { return _Password; }
            set { _Password = value; }
        }

        private string _Country;

        public string Country
        {
            get { return _Country; }
            set { _Country = value; }
        }

        private string _Email;

        public string Email
        {
            get { return _Email; }
            set { _Email = value; }
        }

        public DataSet ODS = new DataSet();

        public DataSet CpuntrySet = new DataSet();

        public List<SelectListItem> SelectCountrylist = new List<SelectListItem>();

        //function to insert data which takes object of the class as parameter
        public void InsertData(UserModel objmodel)
        {
            Objserv.InsertUser(objmodel._UserName, 
                objmodel._Password, objmodel._Country, objmodel._Email);
        }

      //to select data

        public void GetData()
        {
            ODS = Objserv.GetInfo();
        }

//to get country list for dropdownlist

        public void GetCountry()
        {

            CpuntrySet = Objserv.GetCountry();
            foreach (DataRow dr in CpuntrySet.Tables[0].Rows)
            {
                SelectCountrylist.Add(new SelectListItem 
                { Text = dr["NAME"].ToString(), 
                    Value = dr["NAME"].ToString() });
            }
        }

//to delete data

        public void DeleteData(string id)
        {
            Objserv.DeleteUser(id);
        }
    }

// Controller class
//Apart Index method no action method has their own view 
//but they redirect to the default Action after invoking the respective function.
   public class MainController : Controller
    {
        //
        // GET: /Main/
        UserModel ObjModel = new UserModel();

//Default Action method which invokes the get data and country list
        public ActionResult Index()
        {
            //get country list fro dropdown
            ObjModel.GetCountry();

          //to sget selected data
            ObjModel.GetData();
            return View(ObjModel);
        }

//Action Method for Insert which takes model class object as parameter
        public ActionResult InsertUser(UserModel UMDL)
        {
            //function taking model class object as parameter for insert
            UMDL.InsertData(UMDL);
            return RedirectToAction("Index");
        }

//Action Method which takes string for id as parameter
        public ActionResult DeleteUser(string id)
        {

           //to call delete function
            ObjModel.DeleteData(id);
            return RedirectToAction("Index");
        }
    }

//View
@model MvcRegApp.Models.UserModel
@using System.Data;
@{
    ViewBag.Title = "Index";
}

<h2 align="center">Registration Form</h2>
@*SELECT ALL SCRIPT- to check all the check boxes in table in one go*@
<script type="text/javascript">
    function toggle(source) {
        checkboxes = document.getElementsByName('SELECTALL');
        for (var i = 0, n = checkboxes.length; i < n; i++) {
            checkboxes[i].checked = source.checked;
        }
    }

</script>

<script src="~/Scripts/jquery-2.1.1.js" 
    type="text/javascript"></script>
  <script src="~/Scripts/jquery.tablesorter.js" 
      type="text/javascript"></script>
  <script src="~/Scripts/jquery.tablesorter.pager.js" 
      type="text/javascript"></script> 

@* PAGING SCRIPT-- to break the table into fixed length page*@
  <script type="text/javascript">
      $(document).ready(function () {
          $("#dt_basic")
            .tablesorter({ widthFixed: true, widgets: ['zebra'] })
            .tablesorterPager({ container: $("#pager") });
      });
</script>
@*Redirect To Delete-- script passing ids to the delete method in controller*@
<script type="text/javascript">
    function getval(source) {
        var id = "";
        checkboxes = document.getElementsByName('SELECTALL');
        for (var i = 0, n = checkboxes.length; i < n; i++) {
            if (checkboxes[i].checked) {
                id += "," + checkboxes[i].value;
            }
        }
        window.location = "Main/DeleteUser/" + id;

    }
</script>
@* styling for the table *@
<style type="text/css">
               table  {
    border-collapse: collapse;
}

 table, td, th {
    border: 1px solid black;

     }
     th
     {
        background-color:#C1D6DE;
         }
         td
         {
             width:auto;
           background-color:#FFE9D4;
             }
             .text
             {
                 background-color:transparent;
                 border-style:none;
                 }
                 #un
                 {
                     background-color:transparent;
                     border:none;
                     color:Black;
                     }
</style>
@* thml form containing the strongly typed html controls *@
@using (Html.BeginForm("InsertUser", "Main"))
{
   <table align="center">
   <tr><td>User Name</td><td>
       @Html.TextBoxFor(x => x.UserName)</td></tr>
   <tr><td>Password</td><td>
       @Html.TextBoxFor(x => x.Password)</td></tr>
   <tr><td>Country </td><td>@Html.DropDownListFor
   (x => x.Country, Model.SelectCountrylist, 
       "--Select Country--")</td></tr>
   <tr><td>Email </td><td>
       @Html.TextBoxFor(x => x.Email)</td></tr>
   <tr><td></td><td><input type="submit" 
   value="Save" />&nbsp;&nbsp;<button id="cancel" 
   value="Cancel" type="reset" 
   style="width:60px">Cancel</button></td></tr>
   </table> 
}        
    <div align="center" id="pager" 
        class="pager" style="text-align: center;">
                  <img src="../../Images/first.png" 
                      class="first" alt="" />
                  <img src="../../Images/prev.png" 
                      class="prev" alt=""/>
                  <span class="pagedisplay"></span>
                  <!-- this can be any element, including an input -->
                  <img src="../../Images/next.png" 
                      class="next" alt=""/>
                  <img src="../../Images/last.png" 
                      class="last" alt=""/>
                 <select class="pagesize">
                   <option selected="selected" value="10">10</option>
                   <option value="20">20</option>
                   <option value="30">30</option>
                   <option value="40">40</option>
                   </select>
</div>

<table align="center" id="dt_basic">
  <thead>
  <tr>
  <th>NAME</th><th>PASSWORD</th>
  <th>COUNTRY</th><th>EMAIL</th>
  <th><input type="checkbox" name="SELECTALL" 
  value="" onclick="toggle(this)" /></th></tr>
  </thead>
  <tbody>
  @foreach (DataRow dr in Model.ODS.Tables[0].Rows)
  {
  <tr>

  <td>@dr["USERNAME"].ToString()</td>
  <td>@dr["PASSWORD"].ToString()</td>
  <td>@dr["COUNTRY"].ToString()</td>
  <td>@dr["EMAIL"].ToString()</td>
   <td><input type="checkbox" name="SELECTALL" 
   value="@dr["ID"]" /></td>
  </tr>
  }
   </tbody>

  </table>
  <table align="center">
  <tr><td><input type="button" id="delete"  
  value="Delete" name="Delete" 
      onclick="getval()"/></td></tr>
  </table>

Points of Interest

• Insert, delete and select in MVC using WCF WER services