Post
Ask Question

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

  1. CREATETABLE [dbo].[tblCustomer]
  2. (  
  3.     [CustID] [bigint] NOTNULL,  
  4.     [CustName] [nvarchar](50)NULL,  
  5.     [CustEmail] [nvarchar](50)NOTNULL,  
  6.     [CustAddress] [nvarchar](256)NULL,  
  7.     [CustContact] [nvarchar](50)NULL,  
  8. CONSTRAINT [PK_tblCustomer] PRIMARYKEYCLUSTERED  
  9. (  
  10.     [CustID] ASC,  
  11.     [CustEmail] ASC  
  12. )WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]  
  13. )ON [PRIMARY]  
  14.   
  15. 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

  1. ALTERPROCEDURE [dbo].[READ_CUSTOMER]  
  2.     -- Add the parameters for the stored procedure here  
  3.      @PageNo INT  
  4.     ,@RowCountPerPage INT  
  5.     ,@IsPaging INT  
  6. AS  
  7. BEGIN  
  8.     -- SET NOCOUNT ON added to prevent extra result sets from  
  9.     SETNOCOUNTON;  
  10. -- Select statements for procedure here  
  11.   
  12.     IF(@IsPaging = 0)  
  13.     BEGIN  
  14.         SELECTtop(@RowCountPerPage)*FROM [dbo].[tblCustomer]  
  15.         ORDERBY CustID DESC  
  16.     END  
  17.   
  18.     IF(@IsPaging = 1)  
  19.     BEGIN  
  20.         DECLARE @SkipRow INT  
  21.         SET @SkipRow =(@PageNo - 1)* @RowCountPerPage  
  22.   
  23.         SELECT*FROM [dbo].[tblCustomer]  
  24.         ORDERBY CustID DESC  
  25.       
  26.         OFFSET @SkipRow ROWSFETCHNEXT @RowCountPerPage ROWS ONLY  
  27.     END  
  28. END  
Stored Procedure to INSERT Record
  1. ALTERPROCEDURE [dbo].[CREATE_CUSTOMER]  
  2.     -- Add the parameters for the stored procedure here  
  3. (  
  4.      @CustName NVarchar(50)  
  5.     ,@CustEmail NVarchar(50)  
  6.     ,@CustAddress NVarchar(256)  
  7.     ,@CustContact  NVarchar(50)  
  8. )  
  9. AS  
  10. BEGIN  
  11.     ---- SET NOCOUNT ON added to prevent extra result sets from  
  12.     SETNOCOUNTON;  
  13.   
  14.     ---- Try Catch--  
  15.     BEGINTRY  
  16.         BEGINTRANSACTION  
  17.   
  18.         DECLARE @CustID Bigint  
  19.             SET @CustID =isnull(((SELECTmax(CustID)FROM [dbo].[tblCustomer])+1),'1')  
  20.   
  21.         -- Insert statements for procedure here  
  22.         INSERTINTO [dbo].[tblCustomer]([CustID],[CustName],[CustEmail],[CustAddress],[CustContact])  
  23.         VALUES(@CustID,@CustName,@CustEmail,@CustAddress,@CustContact)  
  24.         SELECT 1  
  25.         COMMITTRANSACTION  
  26.     ENDTRY  
  27.     BEGINCATCH  
  28.             DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;  
  29.             SELECT @ErrorMessage =ERROR_MESSAGE(),@ErrorSeverity =ERROR_SEVERITY(),@ErrorState =ERROR_STATE();  
  30.             RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);  
  31.         ROLLBACKTRANSACTION  
  32.     ENDCATCH  
  33.   
  34. END
Stored Procedure to UPDATE Record
  1. ALTERPROCEDURE [dbo].[UPDATE_CUSTOMER]  
  2.     -- Add the parameters for the stored procedure here  
  3.      @CustID BIGINT  
  4.     ,@CustName NVarchar(50)  
  5.     ,@CustEmail NVarchar(50)  
  6.     ,@CustAddress NVarchar(256)  
  7.     ,@CustContact  NVarchar(50)  
  8. AS  
  9. BEGIN  
  10.     ---- SET NOCOUNT ON added to prevent extra result sets from  
  11.     SETNOCOUNTON;  
  12.   
  13.     ---- Try Catch--  
  14.     BEGINTRY  
  15.         BEGINTRANSACTION  
  16.   
  17.         -- Update statements for procedure here  
  18.         UPDATE [dbo].[tblCustomer]  
  19.         SET [CustName] = @CustName,  
  20.             [CustAddress] = @CustAddress,  
  21.             [CustContact] = @CustContact  
  22.         WHERE [CustID] = @CustID AND [CustEmail] = @CustEmail  
  23.         SELECT 1  
  24.         COMMITTRANSACTION  
  25.     ENDTRY  
  26.     BEGINCATCH  
  27.             DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;  
  28.             SELECT @ErrorMessage =ERROR_MESSAGE(),@ErrorSeverity =ERROR_SEVERITY(),@ErrorState =ERROR_STATE();  
  29.             RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);  
  30.         ROLLBACKTRANSACTION  
  31.     ENDCATCH  
  32.   
  33. END
Stored Procedure to DELETE Record
  1. ALTERPROCEDURE [dbo].[DELETE_CUSTOMER]  
  2.     -- Add the parameters for the stored procedure here  
  3.       @CustID BIGINT  
  4. AS  
  5. BEGIN  
  6.     ---- SET NOCOUNT ON added to prevent extra result sets from  
  7.     SETNOCOUNTON;  
  8.   
  9.     ---- Try Catch--  
  10.     BEGINTRY  
  11.         BEGINTRANSACTION  
  12.   
  13.         -- Delete statements for procedure here  
  14.         DELETE [dbo].[tblCustomer]  
  15.         WHERE [CustID] = @CustID   
  16.         SELECT 1  
  17.         COMMITTRANSACTION  
  18.     ENDTRY  
  19.     BEGINCATCH  
  20.             DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;  
  21.             SELECT @ErrorMessage =ERROR_MESSAGE(),@ErrorSeverity =ERROR_SEVERITY(),@ErrorState =ERROR_STATE();  
  22.             RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);  
  23.         ROLLBACKTRANSACTION  
  24.     ENDCATCH  
  25.   
  26. END
Stored Procedure to VIEW Single Record Details
  1. ALTERPROCEDURE [dbo].[VIEW_CUSTOMER]  
  2.     -- Add the parameters for the stored procedure here  
  3.     @CustID BIGINT  
  4. AS  
  5. BEGIN  
  6.     -- SET NOCOUNT ON added to prevent extra result sets from  
  7.     SETNOCOUNTON;  
  8. -- Select statements for procedure here  
  9.   
  10.     SELECT*FROM [dbo].[tblCustomer]  
  11.     WHERE [CustID] = @CustID   
  12. 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
  1. namespace CRUD_APi.Controllers.apiController {  
  2.     [RoutePrefix("api/Customer")]  
  3.     publicclassCustomerController: ApiController {  
  4.         // GET: api/Customer?RowCount=5  
  5.         [HttpGet]  
  6.         publicIEnumerable < tblCustomer > GetCustomers(int pageSize) {  
  7.             try {  
  8.                 int pageNumber = 0;  
  9.                 int IsPaging = 0;  
  10.                 CrudDataService objCrd = newCrudDataService();  
  11.                 List < tblCustomer > modelCust = objCrd.GetCustomerList(pageNumber, pageSize, IsPaging);  
  12.                 return modelCust;  
  13.             } catch {  
  14.                 throw;  
  15.             }  
  16.         }  
  17.   
  18.         // GET: api/Customer/InfinitScroll  
  19.         [HttpGet]  
  20.         publicIEnumerable < tblCustomer > GetCustomerScroll(int pageNumber, int pageSize) {  
  21.             try {  
  22.                 int IsPaging = 1;  
  23.                 CrudDataService objCrd = newCrudDataService();  
  24.                 List < tblCustomer > modelCust = objCrd.GetCustomerList(pageNumber, pageSize, IsPaging);  
  25.                 return modelCust;  
  26.             } catch (Exception ex) {  
  27.                 throw ex;  
  28.             }  
  29.         }  
  30.   
  31.   
  32.         // GET: api/Customer/Create  
  33.         [HttpPost]  
  34.         [ResponseType(typeof(tblCustomer))]  
  35.         publicstring Create(tblCustomer objCust) {  
  36.             try {  
  37.                 CrudDataService objCrd = newCrudDataService();  
  38.                 Int32 message = 0;  
  39.   
  40.                 if ((objCust.CustName != null) && (objCust.CustEmail != null)) message = objCrd.InsertCustomer(objCust);  
  41.                 else message = -1;  
  42.                 return message.ToString();  
  43.             } catch {  
  44.                 throw;  
  45.             }  
  46.         }  
  47.   
  48.         // GET: api/Customer/Get  
  49.         [HttpGet]  
  50.         publictblCustomer GetCustomer(long ? id) {  
  51.             try {  
  52.                 CrudDataService objCrd = newCrudDataService();  
  53.                 tblCustomer modelCust = objCrd.GetCustomerDetails(id);  
  54.                 return modelCust;  
  55.             } catch {  
  56.                 throw;  
  57.             }  
  58.         }  
  59.   
  60.   
  61.         // GET: api/Customer/Edit  
  62.         [HttpPost]  
  63.         [ResponseType(typeof(tblCustomer))]  
  64.         publicstring Edit(tblCustomer objCust) {  
  65.             try {  
  66.                 CrudDataService objCrd = newCrudDataService();  
  67.                 Int32 message = 0;  
  68.                 message = objCrd.UpdateCustomer(objCust);  
  69.                 return message.ToString();  
  70.   
  71.             } catch {  
  72.                 throw;  
  73.             }  
  74.         }  
  75.   
  76.         // GET: api/Customer/Delete  
  77.         [HttpDelete]  
  78.         publicstring Delete(long ? id) {  
  79.             try {  
  80.                 CrudDataService objCrd = newCrudDataService();  
  81.                 Int32 message = 0;  
  82.                 message = objCrd.DeleteCustomer(id);  
  83.                 return message.ToString();  
  84.             } catch {  
  85.                 throw;  
  86.             }  
  87.         }  
  88.     }  
  89. }  
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.
  1. <connectionStrings>  
  2. <addnameaddname="dbConn"connectionString="Data source=DESKTOP-4L9DM2J; Initial Catalog=SampleDB;  User Id=sa; Password=sa@123"providerName="System.Data.SqlClient"/>  
  3. </connectionStrings>
Now we need to create another class to use connection string and open our database connection, let’s name it dbConnector.
  1. // Database Connection  
  2. publicclassdbConnector  
  3. {  
  4. privateSqlConnection SqlConn = null;  
  5.   
  6. publicSqlConnection GetConnection  
  7.         {  
  8. get { return SqlConn; }  
  9. set { SqlConn = value; }  
  10.         }  
  11.   
  12. public dbConnector()  
  13.         {  
  14. string ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;  
  15.             SqlConn = newSqlConnection(ConnectionString);  
  16.         }  
  17. }  
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.
  1. // Database Service  
  2.   
  3. namespace CRUD_DataService {  
  4.     // Database Service  
  5.     publicclassCrudDataService {  
  6.         publicList < tblCustomer > GetCustomerList(int PageNo, int RowCountPerPage, int IsPaging) {  
  7.             dbConnector objConn = newdbConnector();  
  8.             SqlConnection Conn = objConn.GetConnection;  
  9.             Conn.Open();  
  10.   
  11.             try {  
  12.                 List < tblCustomer > _listCustomer = newList < tblCustomer > ();  
  13.   
  14.                 if (Conn.State != System.Data.ConnectionState.Open) Conn.Open();  
  15.   
  16.                 SqlCommand objCommand = newSqlCommand("READ_CUSTOMER", Conn);  
  17.                 objCommand.CommandType = CommandType.StoredProcedure;  
  18.                 objCommand.Parameters.AddWithValue("@PageNo", PageNo);  
  19.                 objCommand.Parameters.AddWithValue("@RowCountPerPage", RowCountPerPage);  
  20.                 objCommand.Parameters.AddWithValue("@IsPaging", IsPaging);  
  21.                 SqlDataReader _Reader = objCommand.ExecuteReader();  
  22.   
  23.                 while (_Reader.Read()) {  
  24.                     tblCustomer objCust = newtblCustomer();  
  25.                     objCust.CustID = Convert.ToInt32(_Reader["CustID"]);  
  26.                     objCust.CustName = _Reader["CustName"].ToString();  
  27.                     objCust.CustEmail = _Reader["CustEmail"].ToString();  
  28.                     objCust.CustAddress = _Reader["CustAddress"].ToString();  
  29.                     objCust.CustContact = _Reader["CustContact"].ToString();  
  30.                     _listCustomer.Add(objCust);  
  31.                 }  
  32.   
  33.                 return _listCustomer;  
  34.             } catch {  
  35.                 throw;  
  36.             } finally {  
  37.                 if (Conn != null) {  
  38.                     if (Conn.State == ConnectionState.Open) {  
  39.                         Conn.Close();  
  40.                         Conn.Dispose();  
  41.                     }  
  42.                 }  
  43.             }  
  44.         }  
  45.   
  46.         publictblCustomer GetCustomerDetails(long ? id) {  
  47.   
  48.             dbConnector objConn = newdbConnector();  
  49.             SqlConnection Conn = objConn.GetConnection;  
  50.             Conn.Open();  
  51.   
  52.             try {  
  53.                 tblCustomer objCust = newtblCustomer();  
  54.   
  55.                 if (Conn.State != System.Data.ConnectionState.Open) Conn.Open();  
  56.   
  57.                 SqlCommand objCommand = newSqlCommand("VIEW_CUSTOMER", Conn);  
  58.                 objCommand.CommandType = CommandType.StoredProcedure;  
  59.                 objCommand.Parameters.AddWithValue("@CustID", id);  
  60.                 SqlDataReader _Reader = objCommand.ExecuteReader();  
  61.   
  62.                 while (_Reader.Read()) {  
  63.                     objCust.CustID = Convert.ToInt32(_Reader["CustID"]);  
  64.                     objCust.CustName = _Reader["CustName"].ToString();  
  65.                     objCust.CustEmail = _Reader["CustEmail"].ToString();  
  66.                     objCust.CustAddress = _Reader["CustAddress"].ToString();  
  67.                     objCust.CustContact = _Reader["CustContact"].ToString();  
  68.                 }  
  69.   
  70.                 return objCust;  
  71.             } catch {  
  72.                 throw;  
  73.             } finally {  
  74.                 if (Conn != null) {  
  75.                     if (Conn.State == ConnectionState.Open) {  
  76.                         Conn.Close();  
  77.                         Conn.Dispose();  
  78.                     }  
  79.                 }  
  80.             }  
  81.         }  
  82.   
  83.         publicInt32 InsertCustomer(tblCustomer objCust) {  
  84.             dbConnector objConn = newdbConnector();  
  85.             SqlConnection Conn = objConn.GetConnection;  
  86.             Conn.Open();  
  87.   
  88.             int result = 0;  
  89.   
  90.             try {  
  91.                 if (Conn.State != System.Data.ConnectionState.Open) Conn.Open();  
  92.   
  93.                 SqlCommand objCommand = newSqlCommand("CREATE_CUSTOMER", Conn);  
  94.                 objCommand.CommandType = CommandType.StoredProcedure;  
  95.                 objCommand.Parameters.AddWithValue("@CustName", objCust.CustName);  
  96.                 objCommand.Parameters.AddWithValue("@CustEmail", objCust.CustEmail);  
  97.                 objCommand.Parameters.AddWithValue("@CustAddress", objCust.CustAddress);  
  98.                 objCommand.Parameters.AddWithValue("@CustContact", objCust.CustContact);  
  99.   
  100.                 result = Convert.ToInt32(objCommand.ExecuteScalar());  
  101.   
  102.                 if (result > 0) {  
  103.                     return result;  
  104.                 } else {  
  105.                     return 0;  
  106.                 }  
  107.             } catch {  
  108.                 throw;  
  109.             } finally {  
  110.                 if (Conn != null) {  
  111.                     if (Conn.State == ConnectionState.Open) {  
  112.                         Conn.Close();  
  113.                         Conn.Dispose();  
  114.                     }  
  115.                 }  
  116.             }  
  117.         }  
  118.   
  119.         publicInt32 UpdateCustomer(tblCustomer objCust) {  
  120.             dbConnector objConn = newdbConnector();  
  121.             SqlConnection Conn = objConn.GetConnection;  
  122.             Conn.Open();  
  123.   
  124.             int result = 0;  
  125.   
  126.             try {  
  127.                 if (Conn.State != System.Data.ConnectionState.Open) Conn.Open();  
  128.   
  129.                 SqlCommand objCommand = newSqlCommand("UPDATE_CUSTOMER", Conn);  
  130.                 objCommand.CommandType = CommandType.StoredProcedure;  
  131.                 objCommand.Parameters.AddWithValue("@CustID", objCust.CustID);  
  132.                 objCommand.Parameters.AddWithValue("@CustName", objCust.CustName);  
  133.                 objCommand.Parameters.AddWithValue("@CustEmail", objCust.CustEmail);  
  134.                 objCommand.Parameters.AddWithValue("@CustAddress", objCust.CustAddress);  
  135.                 objCommand.Parameters.AddWithValue("@CustContact", objCust.CustContact);  
  136.   
  137.                 result = Convert.ToInt32(objCommand.ExecuteScalar());  
  138.   
  139.                 if (result > 0) {  
  140.                     return result;  
  141.                 } else {  
  142.                     return 0;  
  143.                 }  
  144.             } catch {  
  145.                 throw;  
  146.             } finally {  
  147.                 if (Conn != null) {  
  148.                     if (Conn.State == ConnectionState.Open) {  
  149.                         Conn.Close();  
  150.                         Conn.Dispose();  
  151.                     }  
  152.                 }  
  153.             }  
  154.         }  
  155.   
  156.         publicInt32 DeleteCustomer(long ? id) {  
  157.             dbConnector objConn = newdbConnector();  
  158.             SqlConnection Conn = objConn.GetConnection;  
  159.             Conn.Open();  
  160.   
  161.             int result = 0;  
  162.   
  163.             try {  
  164.                 if (Conn.State != System.Data.ConnectionState.Open) Conn.Open();  
  165.   
  166.                 SqlCommand objCommand = newSqlCommand("DELETE_CUSTOMER", Conn);  
  167.                 objCommand.CommandType = CommandType.StoredProcedure;  
  168.                 objCommand.Parameters.AddWithValue("@CustID", id);  
  169.                 result = Convert.ToInt32(objCommand.ExecuteScalar());  
  170.   
  171.                 if (result > 0) {  
  172.                     return result;  
  173.                 } else {  
  174.                     return 0;  
  175.                 }  
  176.             } catch {  
  177.                 throw;  
  178.             } finally {  
  179.                 if (Conn != null) {  
  180.                     if (Conn.State == ConnectionState.Open) {  
  181.                         Conn.Close();  
  182.                         Conn.Dispose();  
  183.                     }  
  184.                 }  
  185.             }  
  186.         }  
  187.     }  
  188. } 
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
  1. namespace CRUD_WF  
  2. {  
  3. publicpartialclassCRUDForm : Form  
  4.     {  
  5. privateint pageNumber = 1;  
  6. privateint pageSize = 0;  
  7. privatestring baseUrl = string.Empty;  
  8. privatestring url = string.Empty;  
  9.   
  10. public CRUDForm()  
  11.         {  
  12.             InitializeComponent();  
  13.             baseUrl = txtUrl.Text.ToString().Trim();  
  14.             pageSize = 5;  
  15.             url = baseUrl + "api/Customer?pageSize=" + pageSize;  
  16.         }  
  17.   
  18. privatevoid CRUDForm_Load(object sender, EventArgs e)  
  19.         {  
  20.             GetCustomer_(url);  
  21.         }  
  22.   
  23. privateasyncvoid GetCustomer_(string url)  
  24.         {  
  25. try  
  26.             {  
  27. using (var objClient = newHttpClient())  
  28.                 {  
  29. using (var response = await objClient.GetAsync(url))  
  30.                     {  
  31. if (response.IsSuccessStatusCode)  
  32.                         {  
  33. var productJsonString = await response.Content.ReadAsStringAsync();  
  34.                             dgList.DataSource = JsonConvert.DeserializeObject<tblCustomer[]>(productJsonString).ToList();  
  35.                         }  
  36.                     }  
  37.                 }  
  38.             }  
  39. catch  
  40.             {  
  41.                 pageSize = 5; pageNumber = 1;  
  42. MessageBox.Show("Invalid URL!!");  
  43.             }  
  44.         }  
  45.   
  46. privatevoid btnSubmit_Click(object sender, EventArgs e)  
  47.         {  
  48. if (btnSubmit.Text != "Update")  
  49.             {  
  50.                 CreateCustomer();  
  51.             }  
  52. else  
  53.             {  
  54. if (lblCustID.Text == "")  
  55.                 {  
  56. MessageBox.Show("Please Select a Customer to Edit");  
  57.                 }  
  58. else  
  59.                 {  
  60.                     EditCustomer();  
  61.                 }  
  62.             }  
  63.         }  
  64.   
  65. privateasyncvoid CreateCustomer()  
  66.         {  
  67. try  
  68.             {  
  69. string InsertUrl = baseUrl + "api/Customer/Create";  
  70. tblCustomer objCust = newtblCustomer();  
  71.                 objCust.CustName = txtCustName.Text.ToString();  
  72.                 objCust.CustEmail = txtCustEmail.Text.ToString();  
  73.                 objCust.CustAddress = txtCustAddress.Text.ToString();  
  74.                 objCust.CustContact = txtCustContact.Text.ToString();  
  75.   
  76. if ((objCust != null) && (objCust.CustEmail != ""))  
  77.                 {  
  78. using (var objClient = newHttpClient())  
  79.                     {  
  80. string contentType = "application/json";  
  81. var serializedCustomer = JsonConvert.SerializeObject(objCust);  
  82. var content = newStringContent(serializedCustomer, Encoding.UTF8, contentType);  
  83. var result = await objClient.PostAsync(InsertUrl, content);  
  84.                         GetCustomer_(url);  
  85.                         Clear();  
  86.                     }  
  87.                 }  
  88. else  
  89.                 {  
  90. MessageBox.Show("Email Id is Must!");  
  91.                 }  
  92.             }  
  93. catch  
  94.             {  
  95. MessageBox.Show("Invalid Customer!!");  
  96.             }  
  97.         }  
  98.   
  99. privateasyncvoid EditCustomer()  
  100.         {  
  101. try  
  102.             {  
  103. string EditUrl = baseUrl + "api/Customer/Edit";  
  104. tblCustomer objCust = newtblCustomer();  
  105.   
  106.                 objCust.CustID = Convert.ToInt32(lblCustID.Text);  
  107.                 objCust.CustName = txtCustName.Text.ToString();  
  108.                 objCust.CustEmail = txtCustEmail.Text.ToString();  
  109.                 objCust.CustAddress = txtCustAddress.Text.ToString();  
  110.                 objCust.CustContact = txtCustContact.Text.ToString();  
  111.   
  112. if ((objCust != null) && (objCust.CustEmail != ""))  
  113.                 {  
  114. using (var objClient = newHttpClient())  
  115.                     {  
  116. string contentType = "application/json";  
  117. var serializedCustomer = JsonConvert.SerializeObject(objCust);  
  118. var content = newStringContent(serializedCustomer, Encoding.UTF8, contentType);  
  119. var result = await objClient.PostAsync(EditUrl, content);  
  120.                         GetCustomer_(url);  
  121.                     }  
  122.                 }  
  123. else  
  124.                 {  
  125. MessageBox.Show("Email Id is Must!");  
  126.                 }  
  127.             }  
  128. catch  
  129.             {  
  130. MessageBox.Show("Invalid Customer!!");  
  131.             }  
  132.         }  
  133.   
  134. privatevoid btnDelete_Click(object sender, EventArgs e)  
  135.         {  
  136. try  
  137.             {  
  138. if (lblCustID.Text == "")  
  139.                 {  
  140. MessageBox.Show("Please Select a Customer to Delete");  
  141.                 }  
  142. else  
  143.                 {  
  144. 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);  
  145. if (result.Equals(DialogResult.OK))  
  146.                     {  
  147. long CustID = Convert.ToInt64(lblCustID.Text);  
  148.                         DeleteCustomer(CustID);  
  149.                     }  
  150.                 }  
  151.             }  
  152. catch  
  153.             {  
  154. MessageBox.Show("Invalid Customer!!");  
  155.             }  
  156.         }  
  157.   
  158. privateasyncvoid DeleteCustomer(long? id)  
  159.         {  
  160. try  
  161.             {  
  162. string DeleteUrl = baseUrl + "api/Customer/Delete";  
  163. using (var objClient = newHttpClient())  
  164.                 {  
  165. var result = await objClient.DeleteAsync(String.Format("{0}/{1}", DeleteUrl, id));  
  166.                 }  
  167.   
  168.                 GetCustomer_(url);  
  169.             }  
  170. catch  
  171.             {  
  172. MessageBox.Show("Invalid Customer!!");  
  173.             }  
  174.         }  
  175.   
  176. privatevoid btnNew_Click(object sender, EventArgs e)  
  177.         {  
  178.             Clear();  
  179.         }  
  180.   
  181. privatevoid btnReset_Click(object sender, EventArgs e)  
  182.         {  
  183.             Clear();  
  184.         }  
  185.   
  186. privatevoid Clear()  
  187.         {  
  188.             lblCustID.Text = "";  
  189.             txtCustName.Text = "";  
  190.             txtCustEmail.Text = "";  
  191.             txtCustAddress.Text = "";  
  192.             txtCustContact.Text = "";  
  193.             btnSubmit.Text = "Submit";  
  194.             txtCustEmail.ReadOnly = false;  
  195.         }  
  196.   
  197. privatevoid txtUrl_TextChanged(object sender, EventArgs e)  
  198.         {  
  199. try  
  200.             {  
  201.                 baseUrl = txtUrl.Text.ToString().Trim();  
  202.             }  
  203. catch  
  204.             {  
  205. MessageBox.Show("Invalid Approach!!");  
  206.             }  
  207.         }  
  208.   
  209. privatevoid btnNext_Click(object sender, EventArgs e)  
  210.         {  
  211. try  
  212.             {  
  213. if (pageNumber == 0)  
  214.                     pageNumber = 1;  
  215.   
  216.                 pageSize = 5; pageNumber++;  
  217.   
  218. string url = baseUrl + "api/Customer?pageNumber=" + pageNumber + "&pageSize=" + pageSize;  
  219.                 GetCustomer_(url);  
  220.                 btnReload.Text = "Page View: " + pageNumber.ToString() + "/Reload..";  
  221.             }  
  222. catch  
  223.             {  
  224. MessageBox.Show("Invalid Approach!!");  
  225.             }  
  226.         }  
  227.   
  228. privatevoid btnPrev_Click(object sender, EventArgs e)  
  229.         {  
  230. try  
  231.             {  
  232.                 pageSize = 5; pageNumber--;  
  233. if (pageNumber == 0)  
  234.                     pageNumber = pageNumber + 1;  
  235.   
  236. string url = baseUrl + "api/Customer?pageNumber=" + pageNumber + "&pageSize=" + pageSize;  
  237.                 GetCustomer_(url);  
  238.                 btnReload.Text = "Page View: " + pageNumber.ToString() + "/Reload..";  
  239.             }  
  240. catch  
  241.             {  
  242. MessageBox.Show("Invalid Approach!!");  
  243.             }  
  244.         }  
  245.   
  246. privatevoid btnReload_Click(object sender, EventArgs e)  
  247.         {  
  248.             pageSize = 5;  
  249.             pageNumber = 1;  
  250.             GetCustomer_(url);  
  251.             btnReload.Text = "Reload..";  
  252.         }  
  253.   
  254. privatevoid dgList_SelectionChanged(object sender, EventArgs e)  
  255.         {  
  256. try  
  257.             {  
  258. if (dgList.SelectedCells.Count > 0)  
  259.                 {  
  260. int selectedrowindex = dgList.SelectedCells[0].RowIndex;  
  261. DataGridViewRow selectedRow = dgList.Rows[selectedrowindex];  
  262.   
  263.                     lblCustID.Text = Convert.ToString(selectedRow.Cells[0].Value);  
  264.                     txtCustName.Text = Convert.ToString(selectedRow.Cells[1].Value);  
  265.                     txtCustEmail.Text = Convert.ToString(selectedRow.Cells[2].Value);  
  266.                     txtCustAddress.Text = Convert.ToString(selectedRow.Cells[3].Value);  
  267.                     txtCustContact.Text = Convert.ToString(selectedRow.Cells[4].Value);  
  268.                     btnSubmit.Text = "Update";  
  269.                     txtCustEmail.ReadOnly = true;  
  270.                 }  
  271.             }  
  272. catch  
  273.             {  
  274. MessageBox.Show("Invalid Customer!!");  
  275.             }  
  276.   
  277.         }  
  278.     }  
  279. }
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!


Similar Articles