In this article we will learn basic CRUD operation using Web Api-2, and Stored Procedure with a sample Desktop Application.
Sample Database:
Let’s Create a Sample database named ‘SampleDB’ with SQL Management Studio. Using the ‘SampleDB’ now create a Table name ‘tblCustomer’.
Script:
CREATE TABLE [dbo].[tblCustomer](
[CustID] [bigint] NOT NULL,
[CustName] [nvarchar](50) NULL,
[CustEmail] [nvarchar](50) NOT NULL,
[CustAddress] [nvarchar](256) NULL,
[CustContact] [nvarchar](50) NULL,
CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED
(
[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 the step we will go through to perform CRUD operations with stored procedure:
- First we will create a stored procedure (SP) to RETRIVE record from Customer table
- Now we will create a stored procedure( SP) to INSERT record into Customer table
- Now we will create another procedure (SP) to UPDATE existing data in our Customer table.
- The last step we will create a stored procedure to DELETE existing record from customer table.
Stored Procedure to RETRIVE Record:
-- ============================================= -- Author:-- Create date: <05/10/2015,,> -- Description: -- ============================================= ALTER PROCEDURE [dbo].[READ_CUSTOMER] -- Add the parameters for the stored procedure here INT , INT , INT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from SET NOCOUNT ON; -- Select statements for procedure here IF( = 0) BEGIN SELECT top* FROM [dbo].[tblCustomer] ORDER BY CustID DESC END IF( = 1) BEGIN DECLARE INT SET = ( - 1) * SELECT * FROM [dbo].[tblCustomer] ORDER BY CustID DESC OFFSET ROWS FETCH NEXT ROWS ONLY END END
Stored Procedure to INSERT Record:
-- ============================================= -- Author:-- Create date: <05/10/2015,,> -- Description: -- ============================================= ALTER PROCEDURE [dbo].[CREATE_CUSTOMER] -- Add the parameters for the stored procedure here ( NVarchar(50) , NVarchar(50) , NVarchar(256) , NVarchar(50) ) AS BEGIN ---- SET NOCOUNT ON added to prevent extra result sets from SET NOCOUNT ON; ---- Try Catch-- BEGIN TRY BEGIN TRANSACTION DECLARE Bigint SET = isnull(((SELECT max(CustID) FROM [dbo].[tblCustomer])+1),'1') -- Insert statements for procedure here INSERT INTO [dbo].[tblCustomer] ([CustID],[CustName],[CustEmail],[CustAddress],[CustContact]) VALUES(,,,,) SELECT 1 COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE NVARCHAR(4000), INT, INT; SELECT = ERROR_MESSAGE(), = ERROR_SEVERITY(), = ERROR_STATE(); RAISERROR (,,); ROLLBACK TRANSACTION END CATCH END
Stored Procedure to UPDATE Record:
-- ============================================= -- Author:-- Create date: <05/10/2015,,> -- Description: -- ============================================= ALTER PROCEDURE [dbo].[UPDATE_CUSTOMER] -- Add the parameters for the stored procedure here BIGINT , NVarchar(50) , NVarchar(50) , NVarchar(256) , NVarchar(50) AS BEGIN ---- SET NOCOUNT ON added to prevent extra result sets from SET NOCOUNT ON; ---- Try Catch-- BEGIN TRY BEGIN TRANSACTION -- Update statements for procedure here UPDATE [dbo].[tblCustomer] SET [CustName] = , [CustAddress] = , [CustContact] = WHERE [CustID] = AND [CustEmail] = SELECT 1 COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE NVARCHAR(4000), INT, INT; SELECT = ERROR_MESSAGE(), = ERROR_SEVERITY(), = ERROR_STATE(); RAISERROR (,,); ROLLBACK TRANSACTION END CATCH END
Stored Procedure to DELETE Record:
-- ============================================= -- Author:-- Create date: <05/10/2015,,> -- Description: -- ============================================= ALTER PROCEDURE [dbo].[DELETE_CUSTOMER] -- Add the parameters for the stored procedure here BIGINT AS BEGIN ---- SET NOCOUNT ON added to prevent extra result sets from SET NOCOUNT ON; ---- Try Catch-- BEGIN TRY BEGIN TRANSACTION -- Delete statements for procedure here DELETE [dbo].[tblCustomer] WHERE [CustID] = SELECT 1 COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE NVARCHAR(4000), INT, INT; SELECT = ERROR_MESSAGE(), = ERROR_SEVERITY(), = ERROR_STATE(); RAISERROR (,,); ROLLBACK TRANSACTION END CATCH END
Stored Procedure to VIEW Single Record Details:
-- ============================================= -- Author:-- Create date: <05/10/2015,,> -- Description: -- ============================================= ALTER PROCEDURE [dbo].[VIEW_CUSTOMER] -- Add the parameters for the stored procedure here BIGINT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from SET NOCOUNT ON; -- Select statements for procedure here SELECT * FROM [dbo].[tblCustomer] WHERE [CustID] = END
Let’s Start:
Open Visual Studio 2015, Click File> New > Project. In this window give a name the project and solution.
Click ok and another window will appear with project template, choose Web API:
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 apiController for the operations. To add a new Controller file we need to click right mouse an option menu will appear Click Add > Controller.
Let’s name it CustomerController. In the controller we will create action methods to perform CRUD operations:
Api Controller for CRUD operations:
namespace CRUD_APi.Controllers.apiController
{
[RoutePrefix("api/Customer")]
public class CustomerController : ApiController
{
// GET: api/Customer?RowCount=5
[HttpGet]
public IEnumerable GetCustomers(int pageSize)
{
try
{
int pageNumber = 0;
int IsPaging = 0;
CrudDataService objCrd = new CrudDataService();
List modelCust = objCrd.GetCustomerList(pageNumber, pageSize, IsPaging);
return modelCust;
}
catch
{
throw;
}
}
// GET: api/Customer/InfinitScroll
[HttpGet]
public IEnumerable GetCustomerScroll(int pageNumber, int pageSize)
{
try
{
int IsPaging = 1;
CrudDataService objCrd = new CrudDataService();
List modelCust = objCrd.GetCustomerList(pageNumber, pageSize, IsPaging);
return modelCust;
}
catch (Exception ex)
{
throw ex;
}
}
// GET: api/Customer/Create
[HttpPost]
[ResponseType(typeof(tblCustomer))]
public string Create(tblCustomer objCust)
{
try
{
CrudDataService objCrd = new CrudDataService();
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]
public tblCustomer GetCustomer(long? id)
{
try
{
CrudDataService objCrd = new CrudDataService();
tblCustomer modelCust = objCrd.GetCustomerDetails(id);
return modelCust;
}
catch
{
throw;
}
}
// GET: api/Customer/Edit
[HttpPost]
[ResponseType(typeof(tblCustomer))]
public string Edit(tblCustomer objCust)
{
try
{
CrudDataService objCrd = new CrudDataService();
Int32 message = 0;
message = objCrd.UpdateCustomer(objCust);
return message.ToString();
}
catch
{
throw;
}
}
// GET: api/Customer/Delete
[HttpDelete]
public string Delete(long? id)
{
try
{
CrudDataService objCrd = new CrudDataService();
Int32 message = 0;
message = objCrd.DeleteCustomer(id);
return message.ToString();
}
catch
{
throw;
}
}
}
}
Data Service:
As we know earlier that we will use ADO.NET, Stored Procedure so to connect data base we need to modify our config file to add Connection String for database connection:
Now we need to create another class to use connection string and open our database connection, let’s name it dbConnector.
// Database Connection
public class dbConnector
{
private SqlConnection SqlConn = null;
public SqlConnection GetConnection
{
get { return SqlConn; }
set { SqlConn = value; }
}
public dbConnector()
{
string ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;
SqlConn = new SqlConnection(ConnectionString);
}
}
To perform CRUD operations we will create a separate class called CrudDataService. In this class we have five methods that will interact with database to perform CRUD operations.
// Database Service
namespace CRUD_DataService
{
// Database Service
public class CrudDataService
{
public List GetCustomerList(int PageNo, int RowCountPerPage, int IsPaging)
{
dbConnector objConn = new dbConnector();
SqlConnection Conn = objConn.GetConnection;
Conn.Open();
try
{
List _listCustomer = new List();
if (Conn.State != System.Data.ConnectionState.Open)
Conn.Open();
SqlCommand objCommand = new SqlCommand("READ_CUSTOMER", Conn);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.AddWithValue("", PageNo);
objCommand.Parameters.AddWithValue("", RowCountPerPage);
objCommand.Parameters.AddWithValue("", IsPaging);
SqlDataReader _Reader = objCommand.ExecuteReader();
while (_Reader.Read())
{
tblCustomer objCust = new tblCustomer();
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();
}
}
}
}
public tblCustomer GetCustomerDetails(long? id)
{
dbConnector objConn = new dbConnector();
SqlConnection Conn = objConn.GetConnection;
Conn.Open();
try
{
tblCustomer objCust = new tblCustomer();
if (Conn.State != System.Data.ConnectionState.Open)
Conn.Open();
SqlCommand objCommand = new SqlCommand("VIEW_CUSTOMER", Conn);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.AddWithValue("", 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();
}
}
}
}
public Int32 InsertCustomer(tblCustomer objCust)
{
dbConnector objConn = new dbConnector();
SqlConnection Conn = objConn.GetConnection;
Conn.Open();
int result = 0;
try
{
if (Conn.State != System.Data.ConnectionState.Open)
Conn.Open();
SqlCommand objCommand = new SqlCommand("CREATE_CUSTOMER", Conn);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.AddWithValue("", objCust.CustName);
objCommand.Parameters.AddWithValue("", objCust.CustEmail);
objCommand.Parameters.AddWithValue("", objCust.CustAddress);
objCommand.Parameters.AddWithValue("", 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();
}
}
}
}
public Int32 UpdateCustomer(tblCustomer objCust)
{
dbConnector objConn = new dbConnector();
SqlConnection Conn = objConn.GetConnection;
Conn.Open();
int result = 0;
try
{
if (Conn.State != System.Data.ConnectionState.Open)
Conn.Open();
SqlCommand objCommand = new SqlCommand("UPDATE_CUSTOMER", Conn);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.AddWithValue("", objCust.CustID);
objCommand.Parameters.AddWithValue("", objCust.CustName);
objCommand.Parameters.AddWithValue("", objCust.CustEmail);
objCommand.Parameters.AddWithValue("", objCust.CustAddress);
objCommand.Parameters.AddWithValue("", 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();
}
}
}
}
public Int32 DeleteCustomer(long? id)
{
dbConnector objConn = new dbConnector();
SqlConnection Conn = objConn.GetConnection;
Conn.Open();
int result = 0;
try
{
if (Conn.State != System.Data.ConnectionState.Open)
Conn.Open();
SqlCommand objCommand = new SqlCommand("DELETE_CUSTOMER", Conn);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.AddWithValue("", 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:
Let’s assign a port to access, In this case the site base url is: http://localhost:8081/
Let’s Create Windows Form Application:
Open Visual Studio 2015, Click File> New > Project. In this window give a name the project and solution. This time we will create a Windows Form Application.
In our new application let’s create a new Form, name it CRUDForm.cs
Our new form will look like this screen:
In CRUD form we have a data grid which will load all data from database through api controller.
Form Submission Code:
namespace CRUD_WF
{
public partial class CRUDForm : Form
{
private int pageNumber = 1;
private int pageSize = 0;
private string baseUrl = string.Empty;
private string url = string.Empty;
public CRUDForm()
{
InitializeComponent();
baseUrl = txtUrl.Text.ToString().Trim();
pageSize = 5;
url = baseUrl + "api/Customer?pageSize=" + pageSize;
}
private void CRUDForm_Load(object sender, EventArgs e)
{
GetCustomer_(url);
}
private async void GetCustomer_(string url)
{
try
{
using (var objClient = new HttpClient())
{
using (var response = await objClient.GetAsync(url))
{
if (response.IsSuccessStatusCode)
{
var productJsonString = await response.Content.ReadAsStringAsync();
dgList.DataSource = JsonConvert.DeserializeObject(productJsonString).ToList();
}
}
}
}
catch
{
pageSize = 5; pageNumber = 1;
MessageBox.Show("Invalid URL!!");
}
}
private void 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();
}
}
}
private async void CreateCustomer()
{
try
{
string InsertUrl = baseUrl + "api/Customer/Create";
tblCustomer objCust = new tblCustomer();
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 = new HttpClient())
{
string contentType = "application/json";
var serializedCustomer = JsonConvert.SerializeObject(objCust);
var content = new StringContent(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!!");
}
}
private async void EditCustomer()
{
try
{
string EditUrl = baseUrl + "api/Customer/Edit";
tblCustomer objCust = new tblCustomer();
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 = new HttpClient())
{
string contentType = "application/json";
var serializedCustomer = JsonConvert.SerializeObject(objCust);
var content = new StringContent(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!!");
}
}
private void 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!!");
}
}
private async void DeleteCustomer(long? id)
{
try
{
string DeleteUrl = baseUrl + "api/Customer/Delete";
using (var objClient = new HttpClient())
{
var result = await objClient.DeleteAsync(String.Format("{0}/{1}", DeleteUrl, id));
}
GetCustomer_(url);
}
catch
{
MessageBox.Show("Invalid Customer!!");
}
}
private void btnNew_Click(object sender, EventArgs e)
{
Clear();
}
private void btnReset_Click(object sender, EventArgs e)
{
Clear();
}
private void Clear()
{
lblCustID.Text = "";
txtCustName.Text = "";
txtCustEmail.Text = "";
txtCustAddress.Text = "";
txtCustContact.Text = "";
btnSubmit.Text = "Submit";
txtCustEmail.ReadOnly = false;
}
private void txtUrl_TextChanged(object sender, EventArgs e)
{
try
{
baseUrl = txtUrl.Text.ToString().Trim();
}
catch
{
MessageBox.Show("Invalid Approach!!");
}
}
private void 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!!");
}
}
private void 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!!");
}
}
private void btnReload_Click(object sender, EventArgs e)
{
pageSize = 5;
pageNumber = 1;
GetCustomer_(url);
btnReload.Text = "Reload..";
}
private void 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.
Web Application:
OUTPUT:
Finally displaying data in both Web & Desktop Application at the same time using Web API.
Hope this will help someone …Thanks.
says:
A very detailed and good explanation on the topic. Keep writing more articles. Thanks for sharing.
Shashangka Shekhar says:
Thanks a lot 🙂