CRUD Operations Using Web API 2 And Stored Procedure With A Sample Desktop Application

CRUD


Figure 1: Database

Sample Database

Let’s create a sample database named ‘SampleDB’ with SQL Management Studio. Using the ‘SampleDB’ now create a Table and name it ‘tblCustomer’.

Script

CREATETABLE [dbo].[tblCustomer]
(
    [CustID] [bigint] NOTNULL,
    [CustName] [nvarchar](50)NULL,
    [CustEmail] [nvarchar](50)NOTNULL,
    [CustAddress] [nvarchar](256)NULL,
    [CustContact] [nvarchar](50)NULL,
CONSTRAINT [PK_tblCustomer] PRIMARYKEYCLUSTERED
(
    [CustID] ASC,
    [CustEmail] ASC
)WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]
)ON [PRIMARY]

GO

Stored Procedure

Now in the following step we will perform CRUD operations with stored procedure:

1. First we will create a stored procedure (SP) to RETRIVE record from Customer table.
2. Now we will create a stored procedure( SP) to INSERT record into Customer table.
3. Now we will create another procedure (SP) to UPDATE existing data in our Customer table.
4. The last step we will create a stored procedure to DELETE existing record from customer table.

Stored Procedure to RETRIVE Record

ALTERPROCEDURE [dbo].[READ_CUSTOMER]
    -- Add the parameters for the stored procedure here
     @PageNo INT
    ,@RowCountPerPage INT
    ,@IsPaging INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    SETNOCOUNTON;
-- Select statements for procedure here

    IF(@IsPaging = 0)
    BEGIN
        SELECTtop(@RowCountPerPage)*FROM [dbo].[tblCustomer]
        ORDERBY CustID DESC
    END

    IF(@IsPaging = 1)
    BEGIN
        DECLARE @SkipRow INT
        SET @SkipRow =(@PageNo - 1)* @RowCountPerPage

        SELECT*FROM [dbo].[tblCustomer]
        ORDERBY CustID DESC

        OFFSET @SkipRow ROWSFETCHNEXT @RowCountPerPage ROWS ONLY
    END
END

Stored Procedure to INSERT Record

ALTERPROCEDURE [dbo].[CREATE_CUSTOMER]
    -- Add the parameters for the stored procedure here
(
     @CustName NVarchar(50)
    ,@CustEmail NVarchar(50)
    ,@CustAddress NVarchar(256)
    ,@CustContact  NVarchar(50)
)
AS
BEGIN
    ---- SET NOCOUNT ON added to prevent extra result sets from
    SETNOCOUNTON;

    ---- Try Catch--
    BEGINTRY
        BEGINTRANSACTION

        DECLARE @CustID Bigint
            SET @CustID =isnull(((SELECTmax(CustID)FROM [dbo].[tblCustomer])+1),'1')

        -- Insert statements for procedure here
        INSERTINTO [dbo].[tblCustomer]([CustID],[CustName],[CustEmail],[CustAddress],[CustContact])
        VALUES(@CustID,@CustName,@CustEmail,@CustAddress,@CustContact)
        SELECT 1
        COMMITTRANSACTION
    ENDTRY
    BEGINCATCH
            DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;
            SELECT @ErrorMessage =ERROR_MESSAGE(),@ErrorSeverity =ERROR_SEVERITY(),@ErrorState =ERROR_STATE();
            RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
        ROLLBACKTRANSACTION
    ENDCATCH

END

Stored Procedure to UPDATE Record

ALTERPROCEDURE [dbo].[UPDATE_CUSTOMER]
    -- Add the parameters for the stored procedure here
     @CustID BIGINT
    ,@CustName NVarchar(50)
    ,@CustEmail NVarchar(50)
    ,@CustAddress NVarchar(256)
    ,@CustContact  NVarchar(50)
AS
BEGIN
    ---- SET NOCOUNT ON added to prevent extra result sets from
    SETNOCOUNTON;

    ---- Try Catch--
    BEGINTRY
        BEGINTRANSACTION

        -- Update statements for procedure here
        UPDATE [dbo].[tblCustomer]
        SET [CustName] = @CustName,
            [CustAddress] = @CustAddress,
            [CustContact] = @CustContact
        WHERE [CustID] = @CustID AND [CustEmail] = @CustEmail
        SELECT 1
        COMMITTRANSACTION
    ENDTRY
    BEGINCATCH
            DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;
            SELECT @ErrorMessage =ERROR_MESSAGE(),@ErrorSeverity =ERROR_SEVERITY(),@ErrorState =ERROR_STATE();
            RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
        ROLLBACKTRANSACTION
    ENDCATCH

END

Stored Procedure to DELETE Record

ALTERPROCEDURE [dbo].[DELETE_CUSTOMER]
    -- Add the parameters for the stored procedure here
      @CustID BIGINT
AS
BEGIN
    ---- SET NOCOUNT ON added to prevent extra result sets from
    SETNOCOUNTON;

    ---- Try Catch--
    BEGINTRY
        BEGINTRANSACTION

        -- Delete statements for procedure here
        DELETE [dbo].[tblCustomer]
        WHERE [CustID] = @CustID
        SELECT 1
        COMMITTRANSACTION
    ENDTRY
    BEGINCATCH
            DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;
            SELECT @ErrorMessage =ERROR_MESSAGE(),@ErrorSeverity =ERROR_SEVERITY(),@ErrorState =ERROR_STATE();
            RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
        ROLLBACKTRANSACTION
    ENDCATCH

END

Stored Procedure to VIEW Single Record Details

ALTERPROCEDURE [dbo].[VIEW_CUSTOMER]
    -- Add the parameters for the stored procedure here
    @CustID BIGINT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    SETNOCOUNTON;
-- Select statements for procedure here

    SELECT*FROM [dbo].[tblCustomer]
    WHERE [CustID] = @CustID
END

Let’s Start

Open Visual Studio 2015, Click File, New, then Project. In this window give a name to  the project and solution.



Figure 2: Open VS

Click OK and another window will appear with project template; choose Web API.



Figure 3: Click Another

Click OK and the visual studio will create and load a new ASP.NET application template.

In this app we are going to apply CRUD operation on a single table named Customer. To do first we need to create API Controller for the operations. To add a new Controller file we need to right click and an option menu will appear. After that click Add, then Controller.



Figure 4: Add

Let’s name it CustomerController. In the controller we will create action methods to perform CRUD operations:



Figure 5: Crud

API Controller for CRUD operations

namespace CRUD_APi.Controllers.apiController {
    [RoutePrefix("api/Customer")]
    publicclassCustomerController: ApiController {
        // GET: api/Customer?RowCount=5
        [HttpGet]
        publicIEnumerable < tblCustomer > GetCustomers(int pageSize) {
            try {
                int pageNumber = 0;
                int IsPaging = 0;
                CrudDataService objCrd = newCrudDataService();
                List < tblCustomer > modelCust = objCrd.GetCustomerList(pageNumber, pageSize, IsPaging);
                return modelCust;
            } catch {
                throw;
            }
        }

        // GET: api/Customer/InfinitScroll
        [HttpGet]
        publicIEnumerable < tblCustomer > GetCustomerScroll(int pageNumber, int pageSize) {
            try {
                int IsPaging = 1;
                CrudDataService objCrd = newCrudDataService();
                List < tblCustomer > modelCust = objCrd.GetCustomerList(pageNumber, pageSize, IsPaging);
                return modelCust;
            } catch (Exception ex) {
                throw ex;
            }
        }


        // GET: api/Customer/Create
        [HttpPost]
        [ResponseType(typeof(tblCustomer))]
        publicstring Create(tblCustomer objCust) {
            try {
                CrudDataService objCrd = newCrudDataService();
                Int32 message = 0;

                if ((objCust.CustName != null) && (objCust.CustEmail != null)) message = objCrd.InsertCustomer(objCust);
                else message = -1;
                return message.ToString();
            } catch {
                throw;
            }
        }

        // GET: api/Customer/Get
        [HttpGet]
        publictblCustomer GetCustomer(long ? id) {
            try {
                CrudDataService objCrd = newCrudDataService();
                tblCustomer modelCust = objCrd.GetCustomerDetails(id);
                return modelCust;
            } catch {
                throw;
            }
        }


        // GET: api/Customer/Edit
        [HttpPost]
        [ResponseType(typeof(tblCustomer))]
        publicstring Edit(tblCustomer objCust) {
            try {
                CrudDataService objCrd = newCrudDataService();
                Int32 message = 0;
                message = objCrd.UpdateCustomer(objCust);
                return message.ToString();

            } catch {
                throw;
            }
        }

        // GET: api/Customer/Delete
        [HttpDelete]
        publicstring Delete(long ? id) {
            try {
                CrudDataService objCrd = newCrudDataService();
                Int32 message = 0;
                message = objCrd.DeleteCustomer(id);
                return message.ToString();
            } catch {
                throw;
            }
        }
    }
}

Data Service

As we know earlier that we will use ADO.NET and Stored Procedure and to connect the database we need to modify our config file to add Connection String for database connection.

<connectionStrings>
<addnameaddname="dbConn"connectionString="Data source=DESKTOP-4L9DM2J; Initial Catalog=SampleDB;  User Id=sa; Password=sa@123"providerName="System.Data.SqlClient"/>
</connectionStrings>

Now we need to create another class to use connection string and open our database connection, let’s name it dbConnector.

// Database Connection
publicclassdbConnector
{
privateSqlConnection SqlConn = null;

publicSqlConnection GetConnection
        {
get { return SqlConn; }
set { SqlConn = value; }
        }

public dbConnector()
        {
string ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;
            SqlConn = newSqlConnection(ConnectionString);
        }
}

To perform CRUD operations we will create a separate class called CrudDataService. In this class we have five methods that will interact with the database to perform CRUD operations.

// Database Service

namespace CRUD_DataService {
    // Database Service
    publicclassCrudDataService {
        publicList < tblCustomer > GetCustomerList(int PageNo, int RowCountPerPage, int IsPaging) {
            dbConnector objConn = newdbConnector();
            SqlConnection Conn = objConn.GetConnection;
            Conn.Open();

            try {
                List < tblCustomer > _listCustomer = newList < tblCustomer > ();

                if (Conn.State != System.Data.ConnectionState.Open) Conn.Open();

                SqlCommand objCommand = newSqlCommand("READ_CUSTOMER", Conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.AddWithValue("@PageNo", PageNo);
                objCommand.Parameters.AddWithValue("@RowCountPerPage", RowCountPerPage);
                objCommand.Parameters.AddWithValue("@IsPaging", IsPaging);
                SqlDataReader _Reader = objCommand.ExecuteReader();

                while (_Reader.Read()) {
                    tblCustomer objCust = newtblCustomer();
                    objCust.CustID = Convert.ToInt32(_Reader["CustID"]);
                    objCust.CustName = _Reader["CustName"].ToString();
                    objCust.CustEmail = _Reader["CustEmail"].ToString();
                    objCust.CustAddress = _Reader["CustAddress"].ToString();
                    objCust.CustContact = _Reader["CustContact"].ToString();
                    _listCustomer.Add(objCust);
                }

                return _listCustomer;
            } catch {
                throw;
            } finally {
                if (Conn != null) {
                    if (Conn.State == ConnectionState.Open) {
                        Conn.Close();
                        Conn.Dispose();
                    }
                }
            }
        }

        publictblCustomer GetCustomerDetails(long ? id) {

            dbConnector objConn = newdbConnector();
            SqlConnection Conn = objConn.GetConnection;
            Conn.Open();

            try {
                tblCustomer objCust = newtblCustomer();

                if (Conn.State != System.Data.ConnectionState.Open) Conn.Open();

                SqlCommand objCommand = newSqlCommand("VIEW_CUSTOMER", Conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.AddWithValue("@CustID", id);
                SqlDataReader _Reader = objCommand.ExecuteReader();

                while (_Reader.Read()) {
                    objCust.CustID = Convert.ToInt32(_Reader["CustID"]);
                    objCust.CustName = _Reader["CustName"].ToString();
                    objCust.CustEmail = _Reader["CustEmail"].ToString();
                    objCust.CustAddress = _Reader["CustAddress"].ToString();
                    objCust.CustContact = _Reader["CustContact"].ToString();
                }

                return objCust;
            } catch {
                throw;
            } finally {
                if (Conn != null) {
                    if (Conn.State == ConnectionState.Open) {
                        Conn.Close();
                        Conn.Dispose();
                    }
                }
            }
        }

        publicInt32 InsertCustomer(tblCustomer objCust) {
            dbConnector objConn = newdbConnector();
            SqlConnection Conn = objConn.GetConnection;
            Conn.Open();

            int result = 0;

            try {
                if (Conn.State != System.Data.ConnectionState.Open) Conn.Open();

                SqlCommand objCommand = newSqlCommand("CREATE_CUSTOMER", Conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.AddWithValue("@CustName", objCust.CustName);
                objCommand.Parameters.AddWithValue("@CustEmail", objCust.CustEmail);
                objCommand.Parameters.AddWithValue("@CustAddress", objCust.CustAddress);
                objCommand.Parameters.AddWithValue("@CustContact", objCust.CustContact);

                result = Convert.ToInt32(objCommand.ExecuteScalar());

                if (result > 0) {
                    return result;
                } else {
                    return 0;
                }
            } catch {
                throw;
            } finally {
                if (Conn != null) {
                    if (Conn.State == ConnectionState.Open) {
                        Conn.Close();
                        Conn.Dispose();
                    }
                }
            }
        }

        publicInt32 UpdateCustomer(tblCustomer objCust) {
            dbConnector objConn = newdbConnector();
            SqlConnection Conn = objConn.GetConnection;
            Conn.Open();

            int result = 0;

            try {
                if (Conn.State != System.Data.ConnectionState.Open) Conn.Open();

                SqlCommand objCommand = newSqlCommand("UPDATE_CUSTOMER", Conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.AddWithValue("@CustID", objCust.CustID);
                objCommand.Parameters.AddWithValue("@CustName", objCust.CustName);
                objCommand.Parameters.AddWithValue("@CustEmail", objCust.CustEmail);
                objCommand.Parameters.AddWithValue("@CustAddress", objCust.CustAddress);
                objCommand.Parameters.AddWithValue("@CustContact", objCust.CustContact);

                result = Convert.ToInt32(objCommand.ExecuteScalar());

                if (result > 0) {
                    return result;
                } else {
                    return 0;
                }
            } catch {
                throw;
            } finally {
                if (Conn != null) {
                    if (Conn.State == ConnectionState.Open) {
                        Conn.Close();
                        Conn.Dispose();
                    }
                }
            }
        }

        publicInt32 DeleteCustomer(long ? id) {
            dbConnector objConn = newdbConnector();
            SqlConnection Conn = objConn.GetConnection;
            Conn.Open();

            int result = 0;

            try {
                if (Conn.State != System.Data.ConnectionState.Open) Conn.Open();

                SqlCommand objCommand = newSqlCommand("DELETE_CUSTOMER", Conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.AddWithValue("@CustID", id);
                result = Convert.ToInt32(objCommand.ExecuteScalar());

                if (result > 0) {
                    return result;
                } else {
                    return 0;
                }
            } catch {
                throw;
            } finally {
                if (Conn != null) {
                    if (Conn.State == ConnectionState.Open) {
                        Conn.Close();
                        Conn.Dispose();
                    }
                }
            }
        }
    }
}

Publish the Site in IIS



Figure 6: IIS

Let’s assign a port to access, In this case the site base url is: http://localhost:8081/.



Figure 7: Localhost

Let’s Create Windows Form Application

Open Visual Studio 2015, Click File, New, then Project. In this window give a name to the project and solution. This time we will create a Windows Form Application.



Figure 8: VS2015

In our new application let’s create a new Form and name it CRUDForm.cs



Figure 9: Crud Form

Our new form will look like the following screen:



Figure 10: new

In CRUD form we have a data grid which will load all data from the database through API controller.

Form Submission Code

namespace CRUD_WF
{
publicpartialclassCRUDForm : Form
    {
privateint pageNumber = 1;
privateint pageSize = 0;
privatestring baseUrl = string.Empty;
privatestring url = string.Empty;

public CRUDForm()
        {
            InitializeComponent();
            baseUrl = txtUrl.Text.ToString().Trim();
            pageSize = 5;
            url = baseUrl + "api/Customer?pageSize=" + pageSize;
        }

privatevoid CRUDForm_Load(object sender, EventArgs e)
        {
            GetCustomer_(url);
        }

privateasyncvoid GetCustomer_(string url)
        {
try
            {
using (var objClient = newHttpClient())
                {
using (var response = await objClient.GetAsync(url))
                    {
if (response.IsSuccessStatusCode)
                        {
var productJsonString = await response.Content.ReadAsStringAsync();
                            dgList.DataSource = JsonConvert.DeserializeObject<tblCustomer[]>(productJsonString).ToList();
                        }
                    }
                }
            }
catch
            {
                pageSize = 5; pageNumber = 1;
MessageBox.Show("Invalid URL!!");
            }
        }

privatevoid btnSubmit_Click(object sender, EventArgs e)
        {
if (btnSubmit.Text != "Update")
            {
                CreateCustomer();
            }
else
            {
if (lblCustID.Text == "")
                {
MessageBox.Show("Please Select a Customer to Edit");
                }
else
                {
                    EditCustomer();
                }
            }
        }

privateasyncvoid CreateCustomer()
        {
try
            {
string InsertUrl = baseUrl + "api/Customer/Create";
tblCustomer objCust = newtblCustomer();
                objCust.CustName = txtCustName.Text.ToString();
                objCust.CustEmail = txtCustEmail.Text.ToString();
                objCust.CustAddress = txtCustAddress.Text.ToString();
                objCust.CustContact = txtCustContact.Text.ToString();

if ((objCust != null) && (objCust.CustEmail != ""))
                {
using (var objClient = newHttpClient())
                    {
string contentType = "application/json";
var serializedCustomer = JsonConvert.SerializeObject(objCust);
var content = newStringContent(serializedCustomer, Encoding.UTF8, contentType);
var result = await objClient.PostAsync(InsertUrl, content);
                        GetCustomer_(url);
                        Clear();
                    }
                }
else
                {
MessageBox.Show("Email Id is Must!");
                }
            }
catch
            {
MessageBox.Show("Invalid Customer!!");
            }
        }

privateasyncvoid EditCustomer()
        {
try
            {
string EditUrl = baseUrl + "api/Customer/Edit";
tblCustomer objCust = newtblCustomer();

                objCust.CustID = Convert.ToInt32(lblCustID.Text);
                objCust.CustName = txtCustName.Text.ToString();
                objCust.CustEmail = txtCustEmail.Text.ToString();
                objCust.CustAddress = txtCustAddress.Text.ToString();
                objCust.CustContact = txtCustContact.Text.ToString();

if ((objCust != null) && (objCust.CustEmail != ""))
                {
using (var objClient = newHttpClient())
                    {
string contentType = "application/json";
var serializedCustomer = JsonConvert.SerializeObject(objCust);
var content = newStringContent(serializedCustomer, Encoding.UTF8, contentType);
var result = await objClient.PostAsync(EditUrl, content);
                        GetCustomer_(url);
                    }
                }
else
                {
MessageBox.Show("Email Id is Must!");
                }
            }
catch
            {
MessageBox.Show("Invalid Customer!!");
            }
        }

privatevoid btnDelete_Click(object sender, EventArgs e)
        {
try
            {
if (lblCustID.Text == "")
                {
MessageBox.Show("Please Select a Customer to Delete");
                }
else
                {
DialogResult result = MessageBox.Show("You are about to delete " + txtCustName.Text + " permanently. Are you sure you want to delete this record?", "Delete", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (result.Equals(DialogResult.OK))
                    {
long CustID = Convert.ToInt64(lblCustID.Text);
                        DeleteCustomer(CustID);
                    }
                }
            }
catch
            {
MessageBox.Show("Invalid Customer!!");
            }
        }

privateasyncvoid DeleteCustomer(long? id)
        {
try
            {
string DeleteUrl = baseUrl + "api/Customer/Delete";
using (var objClient = newHttpClient())
                {
var result = await objClient.DeleteAsync(String.Format("{0}/{1}", DeleteUrl, id));
                }

                GetCustomer_(url);
            }
catch
            {
MessageBox.Show("Invalid Customer!!");
            }
        }

privatevoid btnNew_Click(object sender, EventArgs e)
        {
            Clear();
        }

privatevoid btnReset_Click(object sender, EventArgs e)
        {
            Clear();
        }

privatevoid Clear()
        {
            lblCustID.Text = "";
            txtCustName.Text = "";
            txtCustEmail.Text = "";
            txtCustAddress.Text = "";
            txtCustContact.Text = "";
            btnSubmit.Text = "Submit";
            txtCustEmail.ReadOnly = false;
        }

privatevoid txtUrl_TextChanged(object sender, EventArgs e)
        {
try
            {
                baseUrl = txtUrl.Text.ToString().Trim();
            }
catch
            {
MessageBox.Show("Invalid Approach!!");
            }
        }

privatevoid btnNext_Click(object sender, EventArgs e)
        {
try
            {
if (pageNumber == 0)
                    pageNumber = 1;

                pageSize = 5; pageNumber++;

string url = baseUrl + "api/Customer?pageNumber=" + pageNumber + "&pageSize=" + pageSize;
                GetCustomer_(url);
                btnReload.Text = "Page View: " + pageNumber.ToString() + "/Reload..";
            }
catch
            {
MessageBox.Show("Invalid Approach!!");
            }
        }

privatevoid btnPrev_Click(object sender, EventArgs e)
        {
try
            {
                pageSize = 5; pageNumber--;
if (pageNumber == 0)
                    pageNumber = pageNumber + 1;

string url = baseUrl + "api/Customer?pageNumber=" + pageNumber + "&pageSize=" + pageSize;
                GetCustomer_(url);
                btnReload.Text = "Page View: " + pageNumber.ToString() + "/Reload..";
            }
catch
            {
MessageBox.Show("Invalid Approach!!");
            }
        }

privatevoid btnReload_Click(object sender, EventArgs e)
        {
            pageSize = 5;
            pageNumber = 1;
            GetCustomer_(url);
            btnReload.Text = "Reload..";
        }

privatevoid dgList_SelectionChanged(object sender, EventArgs e)
        {
try
            {
if (dgList.SelectedCells.Count > 0)
                {
int selectedrowindex = dgList.SelectedCells[0].RowIndex;
DataGridViewRow selectedRow = dgList.Rows[selectedrowindex];

                    lblCustID.Text = Convert.ToString(selectedRow.Cells[0].Value);
                    txtCustName.Text = Convert.ToString(selectedRow.Cells[1].Value);
                    txtCustEmail.Text = Convert.ToString(selectedRow.Cells[2].Value);
                    txtCustAddress.Text = Convert.ToString(selectedRow.Cells[3].Value);
                    txtCustContact.Text = Convert.ToString(selectedRow.Cells[4].Value);
                    btnSubmit.Text = "Update";
                    txtCustEmail.ReadOnly = true;
                }
            }
catch
            {
MessageBox.Show("Invalid Customer!!");
            }

        }
    }
}

Desktop Application

In this stage we need to input the HTTP URL to perform CRUD Operation through API controller.



Figure 11: Web App

Web Application



Figure 12: Output 

OUTPUT

Finally displaying data in both Web & Desktop Application at the same time using Web API.
Hope this will help someone. Thanks!