عملیات CRUD با Web Api و Windows Form در MVC
دوشنبه 18 آبان 1394در این مقاله قصد داریم عملیات CRUD را با استفاده از web api و sp ها انجام دهیم ، در پایین با استفاده از آدرس Url ایجاد شده آن را داخل textbox در windows form بگذاریم و اطلاعاتی که در جدول مان هست را نمایش دهد ویرایش کند و حذف نمایند.
web api یک نوع قالب برای پروژه های مبتنی بر وب در .NET است، که بر مبنای اصول و الگوهای موجود در asp.net mvc ساخته شده است، این سبک جدید برای ایجاد وب سرویس ها در .NET است، Windows form,asp.net webform,asp.net mvc و... استفاده می شود.
یک پایگاه داده بسازید و داخل آن یک جدول به نام 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
باید 4 sp به صورت زیر بسازید:
1-یک Stored Procedure برای برگرداندن اطلاعات مشتری
2-یک Stored Procedure برای افزودن اطلاعات مشتری
3-یک Stored Procedure برای به روز رسانی اطلاعات مشتری
4-یک stored Procedure برای حذف کردن اطلاعات مشتری
کوئری 1: برگرداندن اطلاعات مشتری
-- ============================================= -- Author: <Shashangka,,Shekhar> -- Create date: <05/10/2015,,> -- Description: <With this SP we will Retrieve Customer Record from Customer Table,,> -- ============================================= ALTER PROCEDURE [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 SET NOCOUNT ON; -- Select statements for procedure here IF(@IsPaging = 0) BEGIN SELECT top(@RowCountPerPage)* FROM [dbo].[tblCustomer] ORDER BY CustID DESC END IF(@IsPaging = 1) BEGIN DECLARE @SkipRow INT SET @SkipRow = (@PageNo - 1) * @RowCountPerPage SELECT * FROM [dbo].[tblCustomer] ORDER BY CustID DESC OFFSET @SkipRow ROWS FETCH NEXT @RowCountPerPage ROWS ONLY END END
کوئری 2: افزودن اطلاعات مشتری
-- ============================================= -- Author: <Shashangka,,Shekhar> -- Create date: <05/10/2015,,> -- Description: <With this SP we will Insert Customer Record to Customer Table,,> -- ============================================= ALTER PROCEDURE [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 SET NOCOUNT ON; ---- Try Catch-- BEGIN TRY BEGIN TRANSACTION DECLARE @CustID Bigint SET @CustID = isnull(((SELECT max(CustID) _ FROM [dbo].[tblCustomer])+1),'1') -- Insert statements for procedure here INSERT INTO [dbo].[tblCustomer] _ ([CustID],[CustName],[CustEmail],[CustAddress],[CustContact]) VALUES(@CustID,@CustName,@CustEmail,@CustAddress,@CustContact) SELECT 1 COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(),_ @ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState); ROLLBACK TRANSACTION END CATCH END
کوئری 3: به روز رسانی اطلاعات مشتری
-- ============================================= -- Author: <Shashangka,,Shekhar> -- Create date: <05/10/2015,,> -- Description: <With this SP we will Update Customer Record to Customer Table,,> -- ============================================= ALTER PROCEDURE [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 SET NOCOUNT ON; ---- Try Catch-- BEGIN TRY BEGIN TRANSACTION -- Update statements for procedure here UPDATE [dbo].[tblCustomer] SET [CustName] = @CustName, [CustAddress] = @CustAddress, [CustContact] = @CustContact WHERE [CustID] = @CustID AND [CustEmail] = @CustEmail SELECT 1 COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(),_ @ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState); ROLLBACK TRANSACTION END CATCH END
کوئری 4: حذف کردن اطلاعات مشتری
-- ============================================= -- Author: <Shashangka,,Shekhar> -- Create date: <05/10/2015,,> -- Description: <With this SP we will Delete Customer Record from Customer Table,,> -- ============================================= ALTER PROCEDURE [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 SET NOCOUNT ON; ---- Try Catch-- BEGIN TRY BEGIN TRANSACTION -- Delete statements for procedure here DELETE [dbo].[tblCustomer] WHERE [CustID] = @CustID SELECT 1 COMMIT TRANSACTION END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(),_ @ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState); ROLLBACK TRANSACTION END CATCH END
و یک کوئری برای نمایش لیست مشتریان:
-- ============================================= -- Author: <Shashangka,,Shekhar> -- Create date: <05/10/2015,,> -- Description: <With this SP we will Retrieve Single Customer Record from Customer Table,,> -- ============================================= ALTER PROCEDURE [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 SET NOCOUNT ON; -- Select statements for procedure here SELECT * FROM [dbo].[tblCustomer] WHERE [CustID] = @CustID END
یک پروژه ی جدید به صورت زیر بسازید:
موقع ایجاد پروژه نوع پروژه را از نوع web api انتخاب نمایید:
یک controller جدید بسازید و نوع آن را از نوع web api انتخاب نمایید.
یک پوشه ی جدید بسازید به نام apicontroller و یک controller جدید از نوع web api بسازید
namespace CRUD_APi.Controllers.apiController { [RoutePrefix("api/Customer")] public class CustomerController : ApiController { // GET: api/Customer?RowCount=5 [HttpGet] public IEnumerable<tblCustomer> GetCustomers(int pageSize) { try { int pageNumber = 0; int IsPaging = 0; CrudDataService objCrd = new CrudDataService(); List<tblCustomer> modelCust = objCrd.GetCustomerList(pageNumber, pageSize, IsPaging); return modelCust; } catch { throw; } } // GET: api/Customer/InfinitScroll [HttpGet] public IEnumerable<tblCustomer> GetCustomerScroll(int pageNumber, int pageSize) { try { int IsPaging = 1; CrudDataService objCrd = new CrudDataService(); List<tblCustomer> 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; } } } }
یک class libray به نام CRUD_DataService می سازیم، و دو کلاس ایجاد نمایید ، یکی از کلاس ها برای تعریف storded procedure و تعریف هر 5 متد در آن است و یکی از کلاس ها برای اتصال به پایگاه داده است.
کلاس برای اتصال پایگاه داده :
// 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); } }
یک کلاس برای تعریف متد های افزودن ، ویرایش ، حذف و نمایش لیست و برگرداندن اطلاعات است
که به صورت زیر خواهد بود:
// Database Service namespace CRUD_DataService { // Database Service public class CrudDataService { public List<tblCustomer> GetCustomerList(int PageNo, int RowCountPerPage, int IsPaging) { dbConnector objConn = new dbConnector(); SqlConnection Conn = objConn.GetConnection; Conn.Open(); try { List<tblCustomer> _listCustomer = new List<tblCustomer>(); if (Conn.State != System.Data.ConnectionState.Open) Conn.Open(); SqlCommand objCommand = new SqlCommand("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 = 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("@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(); } } } } 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("@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(); } } } } 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("@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(); } } } } 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("@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 کردن سایت روی IIS:
یک پورت برای دسترسی اختصاص دهید:
می خواهیم با استفاده از url به دست آمده اطلاعات داخل آن نمایش داده شود و بتوان ایجاد یا حذف و یا ویرایش کرد.
یک پروژه ی جدید Windows form ای درست نمایید:
صفحه به صورت زیر نمایش داده می شود:
ما الان یک datagrid داریم ، که تمام اطلاعات را از طریق API controller نمایش داده می شود.
کد داخل windows form به صورت است:
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<tblCustomer[]>(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!!"); } } } }
نکته ی دیگر این است که حتما باید dll که مربوط به json است به نام Netonsoft.Json اضافه شود.
خروجی در Windows Form به صورت زیر است:
خروجی در web api به صورت زیر خواهد بود:
- ASP.net MVC
- 2k بازدید
- 5 تشکر