Windows Forms: Insert Update Delete and View data from SQL Database using Web Service in C#
By FoxLearn 6/8/2017 7:15:50 PM 6.29K
Step 1: Click New Project, then select Visual C# on the left, then Windows and then select Windows Forms Application. Name your project "CRUD" and then click OK
Similarly, you need to create a Web project called MyService, then add a Web Service to MyService project
Step 2: Right click on your project select Manage NuGet Packages -> Search metro framework, dapper -> Install
If you don't see the metro framework in your toolbox, you can view How to download and install metro framework
Step 3: Design metro form as below
Step 4: Create a student class, then add to MyS
public class Student { public int StudentID { get; set; } public string FullName { get; set; } public string Birthday { get; set; } public string Email { get; set; } public string Address { get; set; } public string ImageUrl { get; set; } public bool Gender { get; set; } }
Step 5: You should create a new database, then add a student table to your database
CREATE TABLE [dbo].[Students]( [StudentID] [int] IDENTITY(1,1) NOT NULL, [FullName] [nvarchar](100) NULL, [Birthday] [varchar](10) NULL, [Gender] [bit] NULL, [Email] [varchar](100) NULL, [Address] [nvarchar](250) NULL, [ImageUrl] [varchar](250) NULL, CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED ( [StudentID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Create a stored procedure to insert, update data to student table
create procedure [dbo].[sp_Students_Update] ( @StudentID int output, @FullName nvarchar(100), @Birthday varchar(10), @Gender bit, @Email varchar(100), @Address nvarchar(250), @ImageUrl varchar(250) ) as update Students set FullName = @FullName, Birthday = @Birthday, Gender = @Gender, Email = @Email, [Address] = @Address, ImageUrl = @ImageUrl where StudentID = @StudentID go create procedure [dbo].[sp_Students_Insert] ( @StudentID int output, @FullName nvarchar(100), @Birthday varchar(10), @Gender bit, @Email varchar(100), @Address nvarchar(250), @ImageUrl varchar(250) ) as insert into Students(FullName, Birthday, Gender, Email, [Address], ImageUrl) values(@FullName, @Birthday, @Gender, @Email, @Address, @ImageUrl) set @StudentID = SCOPE_IDENTITY()
Step 6: Create an EntityState to manage objects state
public enum EntityState { Unchanged, Added, Changed, Deleted }
Step 7: Add a connection string to the Web.config file
<configuration> <connectionStrings> <add name = "cn" connectionString="Data Source=.;Initial Catalog=dbstudent;User ID=sa;Password=123@qaz;" providerName="System.Data.SqlClient"/> </connectionStrings> </configuration>
Step 8: Add code to StudentService.asmx
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Web; using System.Web.Services; using Dapper; using System.Configuration; namespace MyService { /// <summary> /// Summary description for StudentService /// </summary> [WebService(Namespace = "http://tempuri.org/")] [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] [System.ComponentModel.ToolboxItem(false)] // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. // [System.Web.Script.Services.ScriptService] public class StudentService : System.Web.Services.WebService { [WebMethod] public int Insert(Student obj) { using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString)) { if (db.State == ConnectionState.Closed) db.Open(); DynamicParameters p = new DynamicParameters(); p.Add("@StudentID", dbType: DbType.Int32, direction: ParameterDirection.Output); p.AddDynamicParams(new { FullName = obj.FullName, Email = obj.Email, Address = obj.Address, Gender = obj.Gender, Birthday = obj.Birthday, ImageUrl = obj.ImageUrl }); int result = db.Execute("sp_Students_Insert", p, commandType: CommandType.StoredProcedure); if (result != 0) return p.Get<int>("@StudentID");//Get output parameter return 0; } } [WebMethod] public bool Update(Student obj) { using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString)) { if (db.State == ConnectionState.Closed) db.Open(); //Execute stored procedure to update data int result = db.Execute("sp_Students_Update", new { StudentID = obj.StudentID, FullName = obj.FullName, Email = obj.Email, Address = obj.Address, Gender = obj.Gender, Birthday = obj.Birthday, ImageUrl = obj.ImageUrl }, commandType: CommandType.StoredProcedure); return result != 0; } } [WebMethod] public List<Student> GetAll() { using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString)) { if (db.State == ConnectionState.Closed) db.Open(); return db.Query<Student>("select *from Students", commandType: CommandType.Text).ToList(); } } [WebMethod] public bool Delete(int studentId) { using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString)) { if (db.State == ConnectionState.Closed) db.Open(); int result = db.Execute("delete from Students where StudentID = @StudentID", new { StudentID = studentId }, commandType: CommandType.Text); return result != 0; } } } }
Step 8: You need to add a reference to MyService, then add code to handle your form as below
using CRUD.CRUDService; using System; using System.Collections.Generic; using System.ComponentModel; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace CRUD { public partial class Form1 : MetroFramework.Forms.MetroForm { EntityState objState = EntityState.Unchanged; public Form1() { InitializeComponent(); } private void btnBrowse_Click(object sender, EventArgs e) { using (OpenFileDialog ofd = new OpenFileDialog() { Filter = "JPEG|*.jpg|PNG|*.png", ValidateNames = true }) { if (ofd.ShowDialog() == DialogResult.OK) { //Load image to picture box pic.Image = Image.FromFile(ofd.FileName); Student obj = studentBindingSource.Current as Student; if (obj != null) obj.ImageUrl = ofd.FileName; } } } private void Form1_Load(object sender, EventArgs e) { try { //Get data from web service StudentServiceSoapClient client = new StudentServiceSoapClient(); studentBindingSource.DataSource = client.GetAll(); pContainer.Enabled = false; Student obj = studentBindingSource.Current as Student; if (obj != null) { if (!string.IsNullOrEmpty(obj.ImageUrl)) pic.Image = Image.FromFile(obj.ImageUrl); } } catch (Exception ex) { MetroFramework.MetroMessageBox.Show(this, ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); } } private void btnAdd_Click(object sender, EventArgs e) { objState = EntityState.Added; pic.Image = null; pContainer.Enabled = true; //Add data to binding source List<Student> list = ((IEnumerable<Student>)studentBindingSource.DataSource).ToList(); list.Add(new Student()); studentBindingSource.DataSource = list.AsEnumerable(); studentBindingSource.MoveLast(); txtFullName.Focus(); } private void btnCancel_Click(object sender, EventArgs e) { pContainer.Enabled = false; studentBindingSource.ResetBindings(false); this.Form1_Load(sender, e); } private void btnEdit_Click(object sender, EventArgs e) { objState = EntityState.Changed; pContainer.Enabled = true; txtFullName.Focus(); } private void metroGrid_CellClick(object sender, DataGridViewCellEventArgs e) { try { Student obj = studentBindingSource.Current as Student; if (obj != null) { if (!string.IsNullOrEmpty(obj.ImageUrl)) pic.Image = Image.FromFile(obj.ImageUrl); } } catch(Exception ex) { MetroFramework.MetroMessageBox.Show(this, ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); } } private void btnDelete_Click(object sender, EventArgs e) { objState = EntityState.Deleted; if (MetroFramework.MetroMessageBox.Show(this, "Are you sure want to delete this record?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { try { Student obj = studentBindingSource.Current as Student; if (obj != null) { //Delete data via web service StudentServiceSoapClient client = new StudentServiceSoapClient(); bool result = client.Delete(obj.StudentID); if (result) { studentBindingSource.RemoveCurrent(); pContainer.Enabled = false; pic.Image = null; objState = EntityState.Unchanged; } } } catch (Exception ex) { MetroFramework.MetroMessageBox.Show(this, ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } private void btnSave_Click(object sender, EventArgs e) { try { studentBindingSource.EndEdit(); Student obj = studentBindingSource.Current as Student; if (obj != null) { //Call web service to save data StudentServiceSoapClient client = new StudentServiceSoapClient(); if (objState == EntityState.Added) obj.StudentID = client.Insert(obj); else if (objState == EntityState.Changed) client.Update(obj); metroGrid.Refresh(); pContainer.Enabled = false; objState = EntityState.Unchanged; } } catch (Exception ex) { MetroFramework.MetroMessageBox.Show(this, ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error); } } private void chkGender_CheckStateChanged(object sender, EventArgs e) { if (chkGender.CheckState == CheckState.Checked) chkGender.Text = "Female"; else if (chkGender.CheckState == CheckState.Unchecked) chkGender.Text = "Male"; else chkGender.Text = "???"; } } }
VIDEO TUTORIALS
- How to Insert Update Delete View and Search data from SQL Server in C#
- How to Insert Update Delete View and Search data from MS Access in C#
- How to Insert Update Delete and View data from SQL Database using NPoco ORM in C#
- Insert Update Delete and View data from SQL Database using ORM Lite in C#
- How to Insert Update Delete Search Records in C#
- How to Insert Update Delete From Database in VB.NET
- How to Insert Update Delete and View data from SQL Database in C# using ADO.NET
- How to Insert Update Delete View data from SQL Database in C#