CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report
In this article I will tell you how to perform insert, update and delete operation in C# using stored procedure, and I will also covers topics like web service, windows service and crystal report.
Theseare the steps to archive our goal.
Design the user Interface
Table structure:regUser
Table structure:regUser50
Define connection string in app.config
|
<?xml version="1.0"encoding="utf-8" ?> <configuration> <connectionStrings> <add name="cnn" connectionString="data source=(local);database=userRegistration;user id=sa;password=abc" providerName="System.Data.SqlCli Code for Navigation keys: nt" /> </connectionStrings> </configuration> |
Add Class and define Insert, update and delete function
GlobalClass.cs
|
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration;
namespace NewUserRegistration { class GlobalClass { public SqlConnection cnn; public SqlCommand cmd; public SqlDataReader dr; public GlobalClass() { cnn = new SqlConnection(); cnn.ConnectionString = ConfigurationManager.ConnectionStrings["cnn"].ToString(); if (cnn.State == ConnectionState.Closed) { cnn.Open();//Open Connection } } //Function for Insert Record public bool saveRecord(string proName, string firstName, string lastName, int age, string address, string email, string phone, string mobile, string website, string company) { cmd = new SqlCommand(proName, cnn); //passing procedure name and connection object cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@firstName", firstName); cmd.Parameters.AddWithValue("@lastName", lastName); cmd.Parameters.AddWithValue("@age", age); cmd.Parameters.AddWithValue("@addr", address); cmd.Parameters.AddWithValue("@email", email); cmd.Parameters.AddWithValue("@phone", phone); cmd.Parameters.AddWithValue("@mobile", mobile); cmd.Parameters.AddWithValue("@website", website); cmd.Parameters.AddWithValue("@company", company); int res = cmd.ExecuteNonQuery(); if (res == 1) return true; else return false; } //Function for Update Record
public bool updateRecord(string proName, string firstName, string lastName, int age, string address, string email, string phone, string mobile, string website, string company, int id) { cmd = new SqlCommand(proName, cnn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@firstName", firstName); cmd.Parameters.AddWithValue("@lastName", lastName); cmd.Parameters.AddWithValue("@age", age); cmd.Parameters.AddWithValue("@addr", address); cmd.Parameters.AddWithValue("@email", email); cmd.Parameters.AddWithValue("@phone", phone); cmd.Parameters.AddWithValue("@mobile", mobile); cmd.Parameters.AddWithValue("@website", website); cmd.Parameters.AddWithValue("@company", company); cmd.Parameters.AddWithValue("@id", id); int res = cmd.ExecuteNonQuery(); if (res == 1) return true; else return false; } //Function for Search Record
public bool search(string proName, string email) { cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = proName; cmd.Connection = cnn; cmd.Parameters.AddWithValue("@email", email); dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Close(); return true; } else { dr.Close(); return false; } } //Function for delete Record public bool deleteRecord(string proName, string email) { cmd = new SqlCommand(proName, cnn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@email", email); int res = cmd.ExecuteNonQuery(); if (res == 1) return true; else return false; }
} } |
Write following code on save button click event:
|
try { if (validate())//call validate function for validation { string weburl = txtWebsite.Text.Trim(); if (weburl.StartsWith("www.")) { weburl = "http://" + weburl; if (gc.saveRecord("insertUser", txtFirstName.Text.Trim(), txtLastName.Text.Trim(), Int32.Parse(txtAge.Text.Trim()), txtAddress.Text.Trim(), txtEmail.Text.Trim(), txtPhone.Text.Trim(), txtMobile.Text.Trim(), weburl, txtCompany.Text.Trim()))//call saveRecord function for saving record { MessageBox.Show("Record Saved Sucessfully!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); clearText();//clear textboxes disableButtons();//disable save, delete and update button dt.Clear(); ds = gc.fillGrid();//fill data source from records dt = ds.Tables[0]; _totalRec = dt.Rows.Count; _recNo = -1; _flagChange = 0; } else MessageBox.Show("Record Not Saved!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { if (gc.saveRecord("insertUser", txtFirstName.Text.Trim(), txtLastName.Text.Trim(), Int32.Parse(txtAge.Text.Trim()), txtAddress.Text.Trim(), txtEmail.Text.Trim(), txtPhone.Text.Trim(), txtMobile.Text.Trim(), weburl, txtCompany.Text.Trim())) { MessageBox.Show("Record Saved Sucessfully!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); clearText();//clear textboxes ds = gc.fillGrid();//fill data source from records dt = ds.Tables[0]; _recNo = -1; _flagChange = 0; } else MessageBox.Show("Record Not Saved!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } catch (Exception ee) { MessageBox.Show(ee.Message); } |
Write following code on delete button click event:
|
try { if (validate())//call validate function for validation { if (txtEmail.Text == "") MessageBox.Show("No Records For Deletion!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); else if (!gc.search("searchUser", txtEmail.Text.Trim())) MessageBox.Show("No Records For Deletion!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); else if (MessageBox.Show("Are you sure?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { if (gc.deleteRecord("deleteUser", txtEmail.Text)) {
MessageBox.Show("Record Deleted!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); dt.Clear(); dt.Dispose(); clearText();//clear textboxes disableButtons();//disable save delete update button ds = gc.fillGrid(); dt = ds.Tables[0]; _totalRec = dt.Rows.Count; _recNo = -1; _flagChange = 0; } } } } catch (Exception ee) { MessageBox.Show(ee.Message); }
|
Write following code on update button click event:
|
try {
if (_flagChange == 1) { if (!validate()) {
} else { if (_recNo == -1) { MessageBox.Show("No record for update!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { dr = dt.Rows[_recNo]; if (gc.updateRecord("updateUser", txtFirstName.Text.Trim(), txtLastName.Text.Trim(), Int32.Parse(txtAge.Text.Trim()), txtAddress.Text.Trim(), txtEmail.Text.Trim(), txtPhone.Text.Trim(), txtMobile.Text.Trim(), txtWebsite.Text.Trim(), txtCompany.Text.Trim(), Int32.Parse(dr["userId"].ToString()))) { ds = gc.fillGrid(); dt = ds.Tables[0]; _flagChange = 0; _recNo = -1; _flagUpdate = 0; MessageBox.Show("Record Updated!"); clearText(); disableButtons();
} else MessageBox.Show("Record not updated!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } else { MessageBox.Show("Nothing for update!"); }
} catch (Exception ee) { MessageBox.Show(ee.Message); }
|
Write following code on report button click event:
|
try { email = txtEmail.Text.Trim(); if (_recNo >= 0) { dr = dt.Rows[_recNo]; if (txtEmail.Text != "" && email == dr["email"].ToString()) { frmReport frmrep = new frmReport(this); frmrep.ShowDialog();//show crystal report } else { frmInput frminp = new frmInput(); frminp.ShowDialog();//open input email id form } } else { frmInput frminp = new frmInput(); frminp.ShowDialog();//open input email id form } } catch (Exception ee) { MessageBox.Show(ee.Message); }
|
Write following code on clear button click event:
|
clearText();//clear textboxes disableButtons();//disable save, delete and update button _recNo = -1; _flagChange = 0;
|
Code on textbox change event:
|
private void txtFirstName_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtFirstName.Text.Trim() == "") lblErrFirstName.Visible = true; if (!regName.IsMatch(txtFirstName.Text.Trim())) { lblErrFirstName.Visible = true;
} else { lblErrFirstName.Visible = false; } }
private void txtLastName_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtLastName.Text.Trim() == "") lblErrLastName.Visible = true; else if (!regName.IsMatch(txtLastName.Text.Trim())) { lblErrLastName.Visible = true;
} else { lblErrLastName.Visible = false; } }
private void txtAddress_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtAddress.Text == "") lblErrAddress.Visible = true; else lblErrAddress.Visible = false;
}
private void txtEmail_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtEmail.Text.Trim() == "") lblErrEmail.Visible = true; else if (!regEmail.IsMatch(txtEmail.Text.Trim())) { lblErrEmail.Visible = true;
} else { lblErrEmail.Visible = false; } }
private void txtWebsite_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtWebsite.Text.Trim() == "") lblErrWebsite.Visible = true; else if (!regUrl.IsMatch(txtWebsite.Text.Trim())) { lblErrWebsite.Visible = true;
} else { lblErrWebsite.Visible = false; } }
private void txtCompany_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtCompany.Text == "") lblErrCompany.Visible = true; else lblErrCompany.Visible = false;
} private void txtAge_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtAge.Text.Trim() == "") lblErrAge.Visible = true; else if (txtAge.Text.Length < 2 || Int32.Parse(txtAge.Text.Trim()) < 18) { lblErrAge.Visible = true;
} else { lblErrAge.Visible = false; }
}
private void txtPhone_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtPhone.Text.Trim() == "") lblErrPhone.Visible = true; else if (!regPhone.IsMatch(txtPhone.Text.Trim())||txtPhone.Text.Trim().Length<13) { lblErrPhone.Visible = true;
} else { lblErrPhone.Visible = false; } }
private void txtMobile_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtMobile.Text.Trim() == "") lblErrMobile.Visible = true; else if (!regMobile.IsMatch(txtMobile.Text.Trim())||txtMobile.Text.Length < 10 || txtMobile.Text.Trim()== "0000000000") { lblErrMobile.Visible = true;
} else { lblErrMobile.Visible = false; } } |
On website textbox leave event:
|
private void txtWebsite_Leave(object sender, EventArgs e) { string weburl = txtWebsite.Text.Trim(); if (weburl.StartsWith("www.")) { weburl = "http://" + weburl;// Add Http:// when url starts from www txtWebsite.Text = weburl; } } |
Following functions are used in the code according to the need:
|
// function for clear text fields
public void clearText() { try { foreach (Control c in this.Controls) { if (c is TextBox)
(c as TextBox).Clear(); if (c is MaskedTextBox) (c as MaskedTextBox).Clear();
} lblErrFirstName.Visible = false; lblErrLastName.Visible = false; lblErrAge.Visible = false; lblErrAddress.Visible = false; lblErrEmail.Visible = false; lblErrPhone.Visible = false; lblErrMobile.Visible = false; lblErrWebsite.Visible = false; lblErrCompany.Visible = false;
_flagChange = 0; } // function for disable button public void disableButtons() { if (txtFirstName.Text.Trim() == "" && txtLastName.Text.Trim() == "" && txtAge.Text.Trim() == "" && txtAddress.Text.Trim() == "" && txtEmail.Text.Trim() == "" && txtPhone.Text == "( ) -" && txtMobile.Text.Trim() == "" && txtWebsite.Text.Trim() == "" && txtCompany.Text.Trim() == "") { btnDelete.Enabled = false; btnSave.Enabled = false; btnUpdate.Enabled = false; } else { btnDelete.Enabled = true; btnSave.Enabled = true; btnUpdate.Enabled = true; } } // function for navigation between records public void nav(DataRow objds) { try { txtFirstName.Text = dr["firstName"].ToString(); txtLastName.Text = dr["lastName"].ToString(); txtAge.Text = dr["age"].ToString(); txtAddress.Text = dr["address"].ToString(); txtEmail.Text = dr["email"].ToString(); txtPhone.Text = dr["phone"].ToString(); txtMobile.Text = dr["mobile"].ToString(); txtWebsite.Text = dr["website"].ToString(); txtCompany.Text = dr["company"].ToString();
} catch (Exception ee) { MessageBox.Show(ee.Message); } }
// function for validation bool validate() { try {
//firstname validation if (!regName.IsMatch(txtFirstName.Text.Trim()) || txtFirstName.Text.Trim()=="") { lblErrFirstName.Visible = true; txtFirstName.Focus(); return false; } //lastname validation else if (!regName.IsMatch(txtLastName.Text.Trim()) || txtLastName.Text.Trim()=="") { lblErrLastName.Visible = true; txtLastName.Focus(); return false; } //age validation else if (txtAge.Text.Length < 2 || Int32.Parse(txtAge.Text.Trim()) < 18 || txtAge.Text.Trim() == "") { lblErrAge.Visible = true; txtAge.Focus(); return false; } else if (txtAddress.Text.Trim() == ""||txtAddress.Text.Trim()=="") { lblErrAddress.Visible = true; txtAddress.Focus(); return false; } //email validation else if (!regEmail.IsMatch(txtEmail.Text.Trim())||txtEmail.Text.Trim()=="") { lblErrEmail.Visible = true; txtEmail.Focus(); return false; } //phone validation else if (!regPhone.IsMatch(txtPhone.Text.Trim()) || txtPhone.Text.Trim().Length < 13||txtPhone.Text.Trim()=="") { lblErrPhone.Visible = true; txtPhone.Focus(); return false; } //mobile no validation else if (!regMobile.IsMatch(txtMobile.Text.Trim())||txtMobile.Text.Length < 10 || txtMobile.Text.Trim() == "0000000000" || txtMobile.Text.Trim() == "" ) { lblErrMobile.Visible = true; txtMobile.Focus(); return false; } //url validation else if (!regUrl.IsMatch(txtWebsite.Text.Trim()) || txtWebsite.Text.Trim() == "") { lblErrWebsite.Visible = true; txtWebsite.Focus(); return false; } else if (txtCompany.Text.Trim() == "") { lblErrCompany.Visible = true; txtCompany.Focus(); return false; } else if(_flagUpdate==0) { if (gc.search("searchUser", txtEmail.Text.Trim())) { lblEmail.Visible = true; txtEmail.Focus(); MessageBox.Show("This Email is already registred!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return false; } }
return true; } catch (Exception ee) {
MessageBox.Show(ee.Message); return false; } } |
On form closing event write following code:
|
if (_flagChange == 1) //Check that if you make any change { if (MessageBox.Show("Are you sure to exit without saving?", "Confirm Close", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { e.Cancel = false; } else e.Cancel = true; } |
Following stored procedures are used:
For Insert:
|
USE [userRegistration] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[insertUser]
(@firstName varchar(50), @lastName varchar(50), @age int, @addr varchar(100), @email varchar(70), @phone varchar(15), @mobile varchar(10), @website varchar(70), @company varchar(100) ) AS insert into regUser(firstName,lastName,age,[address],email,phone,mobile,website,company,isActive) values(@firstName,@lastName,@age,@addr,@email,@phone,@mobile,@website,@company,'false'); |
For Delete:
|
USE [userRegistration] GO
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[deleteUser] (@email varchar(70)) AS DELETE FROM regUser WHERE email=@email; |
For Update:
|
USE [userRegistration] GO
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
ALTER PROCEDURE [dbo].[updateUser] (@firstName varchar(50), @lastName varchar(50), @age int, @addr varchar(100), @email varchar(70), @phone varchar(15), @mobile varchar(10), @website varchar(100), @company varchar(70), @id int ) AS UPDATE regUser SET firstName=@firstName,lastName=@lastName,age=@age,[address] =@addr,email=@email,phone=@phone,mobile=@mobile,website=@website,company=@company WHERE userId=@id; |
Following Regular Expression used in program:
|
Regex regEmail = new Regex(@"^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$"); Regex regName = new Regex(@"^^[a-zA-Z][a-zA-Z ]*[a-zA-Z]$"); Regex regPhone = new Regex(@"^([\(]{1}[1-9]{3}[\)]{1}[ ]{1}[0-9]{3}[\-]{1}[0-9]{4})$"); Regex regMobile = new Regex(@"^\d{10}$"); Regex regAge = new Regex(@"^\d{2}$"); Regex regUrl = new Regex(@"^((https?|ftp)://|(www|ftp)\.)[a-z0-9-]+(\.[a-z0-9-]+)+([/?].*)?$"); Regex regCompany = new Regex(@"^[A-Z]([a-zA-Z0-9]|[- @\.#&!])*$");
|
Code for Navigation keys:
|
//first button private void btnFirst_Click(object sender, EventArgs e) {
try { btnFirst.Enabled = false; btnPre.Enabled = false; btnNext.Enabled = true; btnLast.Enabled = true; if (_flagChange == 1) { if (MessageBox.Show("Are you sure to move without saving?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) {
if (_recNo == -1) { _recNo++; dr = dt.Rows[_recNo]; nav(dr); } else if (_recNo >= 0) { _recNo = 0; dr = dt.Rows[_recNo]; nav(dr);
} _flagChange = 0; } } else {
if (_recNo == -1) { _recNo++; dr = dt.Rows[_recNo]; nav(dr); } else if (_recNo >= 0) { _recNo = 0; dr = dt.Rows[_recNo]; nav(dr);
} _flagChange = 0; } } catch (Exception ee) { MessageBox.Show(ee.Message); } } //last button private void btnLast_Click(object sender, EventArgs e) { try { btnLast.Enabled = false; btnNext.Enabled = false; btnPre.Enabled = true; btnFirst.Enabled = true; if (_flagChange == 1) { if (MessageBox.Show("Are you sure to move without saving?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) {
dr = dt.Rows[dt.Rows.Count - 1]; nav(dr); _recNo = _totalRec - 1; _flagChange = 0; } } else { dr = dt.Rows[dt.Rows.Count - 1]; nav(dr); _recNo = _totalRec - 1; _flagChange = 0; } } catch (Exception ee) { MessageBox.Show(ee.Message); } } //next button private void btnNext_Click(object sender, EventArgs e) { try {
if (_flagChange == 1) { if (MessageBox.Show("Are you sure to move without saving?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { if (_flagGrid == 1) {
_recNo++; if (_recNo < _totalRec) { dr = ds.Tables[0].Rows[_recNo]; nav(dr); } if (_recNo == _totalRec - 1) { btnNext.Enabled = false; btnLast.Enabled = false; } if (_recNo > 0) { btnFirst.Enabled = true; btnPre.Enabled = true; } _flagGrid = 0; } else {
if (_recNo < _totalRec - 1) { _recNo++; ds = gc.fillGrid(); dr = dt.Rows[_recNo]; nav(dr);
} if (_recNo == _totalRec - 1) { btnNext.Enabled = false; btnLast.Enabled = false; } if (_recNo > 0) { btnFirst.Enabled = true; btnPre.Enabled = true; } } _flagChange = 0; }
} else { if (_flagGrid == 1) { _recNo++; if (_recNo < _totalRec) { dr = ds.Tables[0].Rows[_recNo]; nav(dr);
} if (_recNo == _totalRec - 1) { btnNext.Enabled = false; btnLast.Enabled = false; } if (_recNo > 0) { btnFirst.Enabled = true; btnPre.Enabled = true; } _flagGrid = 0; } else {
if (_recNo < _totalRec - 1) { _recNo++; ds = gc.fillGrid(); dr = dt.Rows[_recNo]; nav(dr);
} if (_recNo == _totalRec - 1) { btnNext.Enabled = false; btnLast.Enabled = false; } if (_recNo > 0) { btnFirst.Enabled = true; btnPre.Enabled = true; } } _flagChange = 0; }
} catch (Exception ee) { MessageBox.Show(ee.Message); } } //previous button private void btnPre_Click(object sender, EventArgs e) { try {
if (_flagChange == 1) { if (MessageBox.Show("Are you sure to move without saving?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) {
if (_flagGrid == 1) { if (_recNo > 0) { _recNo--; dr = dt.Rows[_recNo]; nav(dr); btnLast.Enabled = true; btnNext.Enabled = true;
}
_flagGrid = 0; } else { if (_recNo == _totalRec) {
dr = dt.Rows[_recNo - 2]; nav(dr); _recNo = _recNo - 2; } else if (_recNo >= 0) { if (_recNo != 0) { _recNo--; dr = dt.Rows[_recNo]; nav(dr); } btnLast.Enabled = true; btnNext.Enabled = true; if (_recNo == 0) { btnFirst.Enabled = false; btnPre.Enabled = false; } } } _flagChange = 0; } } else {
if (_flagGrid == 1) { if (_recNo > 0) { _recNo--; dr = dt.Rows[_recNo]; nav(dr); btnLast.Enabled = true; btnNext.Enabled = true; }
_flagGrid = 0; } else { if (_recNo == _totalRec) {
dr = dt.Rows[_recNo - 2]; nav(dr); _recNo = _recNo - 2; } else if (_recNo >= 0) { if (_recNo != 0) { _recNo--;
dr = dt.Rows[_recNo]; nav(dr); } btnLast.Enabled = true; btnNext.Enabled = true; if (_recNo == 0) { btnFirst.Enabled = false; btnPre.Enabled = false; }
} } _flagChange = 0; }
} catch (Exception ee) { MessageBox.Show(ee.Message); } } |
Full code of User Registration form:
frmUserReg.cs
|
using System; using System.Data; using System.Windows.Forms; using System.Text.RegularExpressions;
namespace NewUserRegistration { public partial class frmUserReg : Form { public frmUserReg() { InitializeComponent(); } //regular expressions Regex regEmail = new Regex(@"^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$"); Regex regName = new Regex(@"^^[a-zA-Z][a-zA-Z ]*[a-zA-Z]$"); Regex regPhone = new Regex(@"^([\(]{1}[1-9]{3}[\)]{1}[ ]{1}[0-9]{3}[\-]{1}[0-9]{4})$"); Regex regMobile = new Regex(@"^\d{10}$"); Regex regAge = new Regex(@"^\d{2}$"); Regex regUrl = new Regex(@"^((https?|ftp)://|(www|ftp)\.)[a-z0-9-]+(\.[a-z0-9-]+)+([/?].*)?$"); Regex regCompany = new Regex(@"^[A-Z]([a-zA-Z0-9]|[- @\.#&!])*$"); GlobalClass gc = new GlobalClass(); //flag and navigation variables public int _flagGrid,_flagChange,_flagUpdate=0,_recNo=-1,_totalRec; public string email; //web service reference object fillGridWebSerRef.Service1 service = new fillGridWebSerRef.Service1(); DataSet ds = new DataSet(); DataRow dr; DataTable dt = new DataTable(); private void btnSave_Click(object sender, EventArgs e) { try { if (validate())//call validate function for validation { string weburl = txtWebsite.Text.Trim(); if (weburl.StartsWith("www.")) { weburl = "http://" + weburl; //call saveRecord function for saving record if (gc.saveRecord("insertUser", txtFirstName.Text.Trim(), txtLastName.Text.Trim(), Int32.Parse(txtAge.Text.Trim()), txtAddress.Text.Trim(), txtEmail.Text.Trim(), txtPhone.Text.Trim(), txtMobile.Text.Trim(), weburl, txtCompany.Text.Trim())) { MessageBox.Show("Record Saved Sucessfully!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); clearText();//clear textboxes disableButtons();//disable save, delete and update button dt.Clear(); ds = gc.fillGrid();//fill data source from records dt = ds.Tables[0]; _totalRec = dt.Rows.Count; _recNo = -1;//set record pointer variable to -1 _flagChange = 0;//reset flag variable } else MessageBox.Show("Record Not Saved!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { //call saveRecord function for saving record if (gc.saveRecord("insertUser", txtFirstName.Text.Trim(), txtLastName.Text.Trim(), Int32.Parse(txtAge.Text.Trim()), txtAddress.Text.Trim(), txtEmail.Text.Trim(), txtPhone.Text.Trim(), txtMobile.Text.Trim(), weburl, txtCompany.Text.Trim())) { MessageBox.Show("Record Saved Sucessfully!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); clearText(); ds = gc.fillGrid(); dt = ds.Tables[0]; _recNo = -1; _flagChange = 0; } else MessageBox.Show("Record Not Saved!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } } } catch (Exception ee) { MessageBox.Show(ee.Message); } } bool validate() { try {
//firstname validation if (!regName.IsMatch(txtFirstName.Text.Trim()) || txtFirstName.Text.Trim()=="") { lblErrFirstName.Visible = true; txtFirstName.Focus(); return false; } //lastname validation else if (!regName.IsMatch(txtLastName.Text.Trim()) || txtLastName.Text.Trim()=="") { lblErrLastName.Visible = true; txtLastName.Focus(); return false; } //age validation else if (txtAge.Text.Length < 2 || Int32.Parse(txtAge.Text.Trim()) < 18 || txtAge.Text.Trim() == "") { lblErrAge.Visible = true; txtAge.Focus(); return false; } else if (txtAddress.Text.Trim() == ""||txtAddress.Text.Trim()=="") { lblErrAddress.Visible = true; txtAddress.Focus(); return false; } //email validation else if (!regEmail.IsMatch(txtEmail.Text.Trim())||txtEmail.Text.Trim()=="") { lblErrEmail.Visible = true; txtEmail.Focus(); return false; } //phone validation else if (!regPhone.IsMatch(txtPhone.Text.Trim()) || txtPhone.Text.Trim().Length < 13||txtPhone.Text.Trim()=="") { lblErrPhone.Visible = true; txtPhone.Focus(); return false; } //mobile no validation else if (!regMobile.IsMatch(txtMobile.Text.Trim())||txtMobile.Text.Length < 10 || txtMobile.Text.Trim() == "0000000000" || txtMobile.Text.Trim() == "" ) { lblErrMobile.Visible = true; txtMobile.Focus(); return false; } //url validation else if (!regUrl.IsMatch(txtWebsite.Text.Trim()) || txtWebsite.Text.Trim() == "") { lblErrWebsite.Visible = true; txtWebsite.Focus(); return false; } //company name validation else if (txtCompany.Text.Trim() == "") { lblErrCompany.Visible = true; txtCompany.Focus(); return false; } //check email id registration else if(_flagUpdate==0) { if (gc.search("searchUser", txtEmail.Text.Trim())) { lblEmail.Visible = true; txtEmail.Focus(); MessageBox.Show("This Email is already registred!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return false; } }
return true; } catch (Exception ee) {
MessageBox.Show(ee.Message); return false; } }
private void btnBrowse_Click(object sender, EventArgs e) { try { frmRecords frmrec = new frmRecords(this); frmrec.ShowDialog(); } catch (Exception ee) { MessageBox.Show(ee.Message); } }
private void frmUserReg_Load(object sender, EventArgs e) { try { btnNext.Enabled = false; btnPre.Enabled = false;
_flagChange = 0; ds = gc.fillGrid(); dt = ds.Tables[0]; _totalRec = dt.Rows.Count; disableButtons(); } catch (Exception ee) { MessageBox.Show(ee.Message); } }
private void frmUserReg_FormClosed(object sender, FormClosedEventArgs e) { Application.Exit(); }
private void btnFirst_Click(object sender, EventArgs e) {
try { btnFirst.Enabled = false; btnPre.Enabled = false; btnNext.Enabled = true; btnLast.Enabled = true; if (_flagChange == 1) { if (MessageBox.Show("Are you sure to move without saving?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) {
if (_recNo == -1) { _recNo++; dr = dt.Rows[_recNo]; nav(dr); } else if (_recNo >= 0) { _recNo = 0; dr = dt.Rows[_recNo]; nav(dr);
} _flagChange = 0; } } else {
if (_recNo == -1) { _recNo++; dr = dt.Rows[_recNo]; nav(dr); } else if (_recNo >= 0) { _recNo = 0; dr = dt.Rows[_recNo]; nav(dr);
} _flagChange = 0; } } catch (Exception ee) { MessageBox.Show(ee.Message); } }
private void btnLast_Click(object sender, EventArgs e) { try { btnLast.Enabled = false; btnNext.Enabled = false; btnPre.Enabled = true; btnFirst.Enabled = true; if (_flagChange == 1) { if (MessageBox.Show("Are you sure to move without saving?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) {
dr = dt.Rows[dt.Rows.Count - 1]; nav(dr); _recNo = _totalRec - 1; _flagChange = 0; } } else { dr = dt.Rows[dt.Rows.Count - 1]; nav(dr); _recNo = _totalRec - 1; _flagChange = 0; } } catch (Exception ee) { MessageBox.Show(ee.Message); } } public void nav(DataRow objds) { try { //setting textboxs value txtFirstName.Text = dr["firstName"].ToString(); txtLastName.Text = dr["lastName"].ToString(); txtAge.Text = dr["age"].ToString(); txtAddress.Text = dr["address"].ToString(); txtEmail.Text = dr["email"].ToString(); txtPhone.Text = dr["phone"].ToString(); txtMobile.Text = dr["mobile"].ToString(); txtWebsite.Text = dr["website"].ToString(); txtCompany.Text = dr["company"].ToString();
} catch (Exception ee) { MessageBox.Show(ee.Message); } }
private void btnNext_Click(object sender, EventArgs e) { try {
if (_flagChange == 1) { //confirmation message if (MessageBox.Show("Are you sure to move without saving?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { if (_flagGrid == 1) {
_recNo++; if (_recNo < _totalRec) { dr = ds.Tables[0].Rows[_recNo]; nav(dr); } if (_recNo == _totalRec - 1) { btnNext.Enabled = false; btnLast.Enabled = false; } if (_recNo > 0) { btnFirst.Enabled = true; btnPre.Enabled = true; } _flagGrid = 0; } else {
if (_recNo < _totalRec - 1) { _recNo++; ds = gc.fillGrid(); dr = dt.Rows[_recNo]; nav(dr);
} if (_recNo == _totalRec - 1) { btnNext.Enabled = false; btnLast.Enabled = false; } if (_recNo > 0) { btnFirst.Enabled = true; btnPre.Enabled = true; } } _flagChange = 0; }
} else { if (_flagGrid == 1) { _recNo++; if (_recNo < _totalRec) { dr = ds.Tables[0].Rows[_recNo]; nav(dr);
} if (_recNo == _totalRec - 1) { btnNext.Enabled = false; btnLast.Enabled = false; } if (_recNo > 0) { btnFirst.Enabled = true; btnPre.Enabled = true; } _flagGrid = 0; } else {
if (_recNo < _totalRec - 1) { _recNo++; ds = gc.fillGrid(); dr = dt.Rows[_recNo]; nav(dr);
} if (_recNo == _totalRec - 1) { btnNext.Enabled = false; btnLast.Enabled = false; } if (_recNo > 0) { btnFirst.Enabled = true; btnPre.Enabled = true; } } _flagChange = 0; }
} catch (Exception ee) { MessageBox.Show(ee.Message); } }
private void btnDelete_Click(object sender, EventArgs e) { try { if (validate()) { if (txtEmail.Text == "") MessageBox.Show("No Records For Deletion!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); else if (!gc.search("searchUser", txtEmail.Text.Trim())) MessageBox.Show("No Records For Deletion!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); else if (MessageBox.Show("Are you sure?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { if (gc.deleteRecord("deleteUser", txtEmail.Text)) {
MessageBox.Show("Record Deleted!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); dt.Clear(); dt.Dispose(); clearText(); disableButtons(); ds = gc.fillGrid(); dt = ds.Tables[0]; _totalRec = dt.Rows.Count; _recNo = -1; _flagChange = 0; } } } } catch (Exception ee) { MessageBox.Show(ee.Message); } }
private void btnUpdate_Click(object sender, EventArgs e) { try {
if (_flagChange == 1) { if (!validate()) {
} else { if (_recNo == -1) { MessageBox.Show("No record for update!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { dr = dt.Rows[_recNo]; if (gc.updateRecord("updateUser", txtFirstName.Text.Trim(), txtLastName.Text.Trim(), Int32.Parse(txtAge.Text.Trim()), txtAddress.Text.Trim(), txtEmail.Text.Trim(), txtPhone.Text.Trim(), txtMobile.Text.Trim(), txtWebsite.Text.Trim(), txtCompany.Text.Trim(), Int32.Parse(dr["userId"].ToString()))) { ds = gc.fillGrid(); dt = ds.Tables[0]; _flagChange = 0; _recNo = -1; _flagUpdate = 0; MessageBox.Show("Record Updated!"); clearText(); disableButtons();
} else MessageBox.Show("Record not updated!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } else { MessageBox.Show("Nothing for update!"); }
} catch (Exception ee) { MessageBox.Show(ee.Message); } }
private void btnPre_Click(object sender, EventArgs e) { try {
if (_flagChange == 1) { if (MessageBox.Show("Are you sure to move without saving?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) {
if (_flagGrid == 1) { if (_recNo > 0) { _recNo--; dr = dt.Rows[_recNo]; nav(dr); btnLast.Enabled = true; btnNext.Enabled = true;
}
_flagGrid = 0; } else { if (_recNo == _totalRec) {
dr = dt.Rows[_recNo - 2]; nav(dr); _recNo = _recNo - 2; } else if (_recNo >= 0) { if (_recNo != 0) { _recNo--; dr = dt.Rows[_recNo]; nav(dr); } btnLast.Enabled = true; btnNext.Enabled = true; if (_recNo == 0) { btnFirst.Enabled = false; btnPre.Enabled = false; } } } _flagChange = 0; } } else {
if (_flagGrid == 1) { if (_recNo > 0) { _recNo--; dr = dt.Rows[_recNo]; nav(dr); btnLast.Enabled = true; btnNext.Enabled = true; }
_flagGrid = 0; } else { if (_recNo == _totalRec) {
dr = dt.Rows[_recNo - 2]; nav(dr); _recNo = _recNo - 2; } else if (_recNo >= 0) { if (_recNo != 0) { _recNo--;
dr = dt.Rows[_recNo]; nav(dr); } btnLast.Enabled = true; btnNext.Enabled = true; if (_recNo == 0) { btnFirst.Enabled = false; btnPre.Enabled = false; }
} } _flagChange = 0; }
} catch (Exception ee) { MessageBox.Show(ee.Message); } }
private void btnReport_Click(object sender, EventArgs e) {
try { email = txtEmail.Text.Trim(); if (_recNo >= 0) { dr = dt.Rows[_recNo]; if (txtEmail.Text != "" && email == dr["email"].ToString()) { frmReport frmrep = new frmReport(this); frmrep.ShowDialog(); } else { frmInput frminp = new frmInput(); frminp.ShowDialog(); } } else { frmInput frminp = new frmInput(); frminp.ShowDialog(); } } catch (Exception ee) { MessageBox.Show(ee.Message); } } public void clearText() { try { foreach (Control c in this.Controls) { if (c is TextBox)
(c as TextBox).Clear(); if (c is MaskedTextBox) (c as MaskedTextBox).Clear();
} //set visiblity of error labels lblErrFirstName.Visible = false; lblErrLastName.Visible = false; lblErrAge.Visible = false; lblErrAddress.Visible = false; lblErrEmail.Visible = false; lblErrPhone.Visible = false; lblErrMobile.Visible = false; lblErrWebsite.Visible = false; lblErrCompany.Visible = false;
_flagChange = 0; } catch (Exception ee) { MessageBox.Show(ee.Message); } }
private void txtFirstName_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtFirstName.Text.Trim() == "") lblErrFirstName.Visible = true; if (!regName.IsMatch(txtFirstName.Text.Trim())) { lblErrFirstName.Visible = true;
} else { lblErrFirstName.Visible = false; } }
private void txtLastName_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtLastName.Text.Trim() == "") lblErrLastName.Visible = true; else if (!regName.IsMatch(txtLastName.Text.Trim())) { lblErrLastName.Visible = true;
} else { lblErrLastName.Visible = false; } }
private void txtAddress_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtAddress.Text == "") lblErrAddress.Visible = true; else lblErrAddress.Visible = false;
}
private void txtEmail_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtEmail.Text.Trim() == "") lblErrEmail.Visible = true; else if (!regEmail.IsMatch(txtEmail.Text.Trim())) { lblErrEmail.Visible = true;
} else { lblErrEmail.Visible = false; } }
private void txtWebsite_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtWebsite.Text.Trim() == "") lblErrWebsite.Visible = true; else if (!regUrl.IsMatch(txtWebsite.Text.Trim())) { lblErrWebsite.Visible = true;
} else { lblErrWebsite.Visible = false; } }
private void txtCompany_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtCompany.Text == "") lblErrCompany.Visible = true; else lblErrCompany.Visible = false;
}
private void frmUserReg_FormClosing(object sender, FormClosingEventArgs e) { if (_flagChange == 1) { if (MessageBox.Show("Are you sure to exit without saving?", "Confirm Close", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { e.Cancel = false; } else e.Cancel = true; } }
private void txtAge_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtAge.Text.Trim() == "") lblErrAge.Visible = true; else if (txtAge.Text.Length < 2 || Int32.Parse(txtAge.Text.Trim()) < 18) { lblErrAge.Visible = true;
} else { lblErrAge.Visible = false; }
}
private void txtPhone_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtPhone.Text.Trim() == "") lblErrPhone.Visible = true; else if (!regPhone.IsMatch(txtPhone.Text.Trim())||txtPhone.Text.Trim().Length<13) { lblErrPhone.Visible = true;
} else { lblErrPhone.Visible = false; } }
private void txtMobile_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtMobile.Text.Trim() == "") lblErrMobile.Visible = true; else if (!regMobile.IsMatch(txtMobile.Text.Trim())||txtMobile.Text.Length < 10 || txtMobile.Text.Trim()== "0000000000") { lblErrMobile.Visible = true;
} else { lblErrMobile.Visible = false; } }
private void btnClear_Click(object sender, EventArgs e) { clearText(); disableButtons(); _recNo = -1; _flagChange = 0;
}
private void txtWebsite_Leave(object sender, EventArgs e) { string weburl = txtWebsite.Text.Trim(); if (weburl.StartsWith("www.")) { weburl = "http://" + weburl; txtWebsite.Text = weburl; } } public void disableButtons() { if (txtFirstName.Text.Trim() == "" && txtLastName.Text.Trim() == "" && txtAge.Text.Trim() == "" && txtAddress.Text.Trim() == "" && txtEmail.Text.Trim() == "" && txtPhone.Text == "( ) -" && txtMobile.Text.Trim() == "" && txtWebsite.Text.Trim() == "" && txtCompany.Text.Trim() == "") { btnDelete.Enabled = false; btnSave.Enabled = false; btnUpdate.Enabled = false; } else { btnDelete.Enabled = true; btnSave.Enabled = true; btnUpdate.Enabled = true; } }
} }
|
Web Service and Paging:
For creating a web service follow these steps:
*Click on new project
*Select web from Installed Templates
*Select ASP.NET Web Service Application
*Give Web Service Application name
*Click on ok
Code of Web Service file:
Service1.asmx
|
using System.Web.Services; using System.Data.SqlClient; using System.Data; using System.Configuration; namespace FillRecordWS { /// <summary> /// Summary description for Service1 /// </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 Service1 : System.Web.Services.WebService { [WebMethod] public DataSet fillGrid() { SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnn"].ToString()); SqlCommand cmd; if (cnn.State == ConnectionState.Closed) cnn.Open();//open Connection cmd = new SqlCommand("selectRecords",cnn);//pass strored procedure and connection object cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); return ds; } } }
|
How to add reference of Web Service in our project
*Right click on the Web Service References from solution explorer
*Click on Add Service References
*Click on advance button
*click on add web reference
*copy URL form web browser after running web service and paste in address bar
*click on go button
*rename web reference name and click on Add Reference.
*after adding reference we can use its functions according to our need
After clicking on the browse button a new form displayed which User Interface is given below
The grid of this form is populated by web service.
Code of populating records and paging:
frmRecords.cs
|
using System; using System.ComponentModel; using System.Data; using System.Drawing; using System.Windows.Forms; using System.Globalization;
namespace NewUserRegistration { public partial class frmRecords : Form { frmUserReg _frmReg; int CurrentPage = 1; int PagesCount = 1; int pageRows = 30; BindingList<PagingData> Baselist = null; BindingList<PagingData> Templist = null; public frmRecords(frmUserReg frmReg) { InitializeComponent(); _frmReg = frmReg; } fillGridWebSerRef.Service1 service = new fillGridWebSerRef.Service1();//object of web service DataSet ds = new DataSet(); DataTable dt = new DataTable(); DataTable _DataTableForPagingList; private void frmRecords_Load(object sender, EventArgs e) {
ds = service.fillGrid();//fill records from web service dt = ds.Tables[0]; GridViewRecords.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; GridViewRecords.Columns["firstName"].HeaderText = "First Name"; GridViewRecords.Columns["lastName"].HeaderText = "Last Name"; GridViewRecords.Columns["age"].HeaderText = "Age"; GridViewRecords.Columns["address"].HeaderText = "Address"; GridViewRecords.Columns["email"].HeaderText = "Email"; GridViewRecords.Columns["phone"].HeaderText = "Phone No."; GridViewRecords.Columns["mobile"].HeaderText = "Mobile No"; GridViewRecords.Columns["website"].HeaderText = "Website"; GridViewRecords.Columns["company"].HeaderText = "Company"; _DataTableForPagingList = dt; Baselist = FillDataforGrid(); GridViewRecords.DataSource = Baselist; PagesCount = Convert.ToInt32(Math.Ceiling(Baselist.Count * 1.0 / pageRows)); RefreshPagination(); RebindGridForPageChange(); } private BindingList<PagingData> FillDataforGrid() { BindingList<PagingData> list = new BindingList<PagingData>(); for (int i = 0; i < _DataTableForPagingList.Rows.Count; i++) { PagingData obj = new PagingData(_DataTableForPagingList.Rows[i]["firstName"].ToString(), _DataTableForPagingList.Rows[i]["lastName"].ToString(), Convert.ToInt32(_DataTableForPagingList.Rows[i]["age"].ToString()), _DataTableForPagingList.Rows[i]["address"].ToString(), _DataTableForPagingList.Rows[i]["email"].ToString(), _DataTableForPagingList.Rows[i]["phone"].ToString(), _DataTableForPagingList.Rows[i]["mobile"].ToString(), _DataTableForPagingList.Rows[i]["website"].ToString(), _DataTableForPagingList.Rows[i]["company"].ToString()); list.Add(obj); } return list; } int currentRow; private void GridViewRecords_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e) { try { currentRow = Int32.Parse(e.RowIndex.ToString()); if (currentRow != -1) { //setting textboxes value of user registration form _frmReg.txtFirstName.Text = GridViewRecords["firstName", currentRow].Value.ToString(); _frmReg.txtLastName.Text = GridViewRecords["lastName", currentRow].Value.ToString(); _frmReg.txtAge.Text = GridViewRecords["age", currentRow].Value.ToString(); _frmReg.txtAddress.Text = GridViewRecords["address", currentRow].Value.ToString(); _frmReg.txtEmail.Text = GridViewRecords["email", currentRow].Value.ToString(); _frmReg.txtPhone.Text = GridViewRecords["phone", currentRow].Value.ToString(); _frmReg.txtMobile.Text = GridViewRecords["mobile", currentRow].Value.ToString(); _frmReg.txtWebsite.Text = GridViewRecords["website", currentRow].Value.ToString(); _frmReg.txtCompany.Text = GridViewRecords["company", currentRow].Value.ToString(); _frmReg._flagGrid = 1; string filter = "email='" + GridViewRecords["email", currentRow].Value.ToString() + "'"; DataRow[] dr1 = dt.Select(filter);//filter the row on the basis of email _frmReg._recNo = dt.Rows.IndexOf(dr1[0]);//get the selected row number if (_frmReg._recNo >= dt.Rows.Count - 1) { _frmReg.btnNext.Enabled = false; _frmReg.btnLast.Enabled = false; _frmReg.btnFirst.Enabled=true; _frmReg.btnPre.Enabled=true; } else if (_frmReg._recNo <= 0) { _frmReg.btnNext.Enabled = true; _frmReg.btnLast.Enabled = true; _frmReg.btnFirst.Enabled = false; _frmReg.btnPre.Enabled = false; } else { _frmReg.btnNext.Enabled = true; _frmReg.btnLast.Enabled = true; _frmReg.btnFirst.Enabled = true; _frmReg.btnPre.Enabled = true; } _frmReg._flagChange = 0; this.Close(); } } catch (Exception ee) { MessageBox.Show(ee.Message); } }
private void toolStripButton1_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; else if (ToolStripButton == btnForward) CurrentPage++; else if (ToolStripButton == btnLast) CurrentPage = PagesCount; else if (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture); if (CurrentPage < 1) CurrentPage = 1; else if (CurrentPage > PagesCount) CurrentPage = PagesCount; //Rebind the Datagridview with the data. RebindGridForPageChange(); //Change the pagiantions buttons according to page number RefreshPagination(); } catch (Exception) { } } private void RebindGridForPageChange() { //Rebinding the Datagridview with data int datasourcestartIndex = (CurrentPage - 1) * pageRows; Templist = new BindingList<PagingData>(); for (int i = datasourcestartIndex; i < datasourcestartIndex + pageRows; i++) { if (i >= Baselist.Count) break;
Templist.Add(Baselist[i]); }
GridViewRecords.DataSource = Templist;
} private void RefreshPagination() { ToolStripButton[] items = new ToolStripButton[] { toolStripButton1, toolStripButton2, toolStripButton3, toolStripButton4, toolStripButton5 }; //pageStartIndex contains the first button number of pagination. int pageStartIndex = 1;
if (PagesCount > 5 && CurrentPage > 2) pageStartIndex = CurrentPage - 2;
if (PagesCount > 5 && CurrentPage > PagesCount - 2) pageStartIndex = PagesCount - 4;
for (int i = pageStartIndex; i < pageStartIndex + 5; i++) { if (i > PagesCount) { items[i - pageStartIndex].Visible = false; } else { //Changing the page numbers items[i - pageStartIndex].Text = i.ToString(CultureInfo.InvariantCulture);
//Setting the Appearance of the page number buttons if (i == CurrentPage) { items[i - pageStartIndex].BackColor = System.Drawing.ColorTranslator.FromHtml("#83D6F6"); items[i - pageStartIndex].ForeColor = Color.White; } else { items[i - pageStartIndex].BackColor = Color.White; items[i - pageStartIndex].ForeColor = System.Drawing.ColorTranslator.FromHtml("#83D6F6"); } } } //Enabling or Disalbing pagination first, last, previous , next buttons if (CurrentPage == 1) btnBackward.Enabled = btnFirst.Enabled = false; else btnBackward.Enabled = btnFirst.Enabled = true;
if (CurrentPage == PagesCount) btnForward.Enabled = btnLast.Enabled = false;
else btnForward.Enabled = btnLast.Enabled = true; } private void btnPrevious_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; else if (ToolStripButton == btnForward) CurrentPage++; else if (ToolStripButton == btnLast) CurrentPage = PagesCount; else if (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; else if (CurrentPage > PagesCount) CurrentPage = PagesCount;
//Rebind the Datagridview with the data. RebindGridForPageChange();
//Change the paginations buttons according to page number RefreshPagination(); } catch (Exception) { } }
private void toolStripButton5_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; else if (ToolStripButton == btnForward) CurrentPage++; else if (ToolStripButton == btnLast) CurrentPage = PagesCount; else if (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; else if (CurrentPage > PagesCount) CurrentPage = PagesCount;
//Rebind the Datagridview with the data. RebindGridForPageChange();
//Change the pagiantions buttons according to page number RefreshPagination();
} catch (Exception) { } }
private void toolStripButton6_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; else if (ToolStripButton == btnForward) CurrentPage++; else if (ToolStripButton == btnLast) CurrentPage = PagesCount; else if (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; else if (CurrentPage > PagesCount) CurrentPage = PagesCount;
//Rebind the Datagridview with the data. RebindGridForPageChange();
//Change the pagiantions buttons according to page number RefreshPagination(); } catch (Exception) { } }
private void toolStripButton7_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; else if (ToolStripButton == btnForward) CurrentPage++; else if (ToolStripButton == btnLast) CurrentPage = PagesCount; else if (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; else if (CurrentPage > PagesCount) CurrentPage = PagesCount; //Rebind the Datagridview with the data. RebindGridForPageChange(); //Change the pagiantions buttons according to page number RefreshPagination(); } catch (Exception) { } }
private void toolStripButton8_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; else if (ToolStripButton == btnForward) CurrentPage++; else if (ToolStripButton == btnLast) CurrentPage = PagesCount; else if (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; else if (CurrentPage > PagesCount) CurrentPage = PagesCount;
//Rebind the Datagridview with the data. RebindGridForPageChange();
//Change the pagiantions buttons according to page number RefreshPagination(); } catch (Exception) { } }
private void toolStripButton9_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; else if (ToolStripButton == btnForward) CurrentPage++; else if (ToolStripButton == btnLast) CurrentPage = PagesCount; else if (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; else if (CurrentPage > PagesCount) CurrentPage = PagesCount;
//Rebind the Datagridview with the data. RebindGridForPageChange();
//Change the paginations buttons according to page number RefreshPagination(); } catch (Exception) { } }
private void btnNext_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; else if (ToolStripButton == btnForward) CurrentPage++; else if (ToolStripButton == btnLast) CurrentPage = PagesCount; else if (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; else if (CurrentPage > PagesCount) CurrentPage = PagesCount;
//Rebind the Datagridview with the data. RebindGridForPageChange();
//Change the pagiantions buttons according to page number RefreshPagination(); } catch (Exception) { } }
private void btnLast_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender); //Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; else if (ToolStripButton == btnForward) CurrentPage++; else if (ToolStripButton == btnLast) CurrentPage = PagesCount; else if (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; else if (CurrentPage > PagesCount) CurrentPage = PagesCount;
//Rebind the Datagridview with the data. RebindGridForPageChange();
//Change the paginations buttons according to page number RefreshPagination(); } catch (Exception) { } }
private void btnClose_Click(object sender, EventArgs e) { this.Close(); }
} }
|
Windows Service in C#
In this project I make a windows service for copy record from regUser table to regUser50 where user age is greater than or equal to 50.
Steps for creating Windows Service:
*Click on new project
*Select Windows Service from new project dialog box.
After clicking ok a window appears which looks like this
Click on “click here to switch to code view”. Now code view appears on the screen
Code:
|
using System; using System.Data; using System.ServiceProcess; using System.Timers; using System.Data.SqlClient;
namespace WinServCopyRec { public partial class CopyData : ServiceBase { public CopyData() { InitializeComponent(); } Timer timerCheck = new Timer();//create timer protected override void OnStart(string[] args) { timerCheck.Elapsed += new ElapsedEventHandler(timerCheck_Elapsed); timerCheck.Interval = 10000;//set timer interval timerCheck.Enabled = true; timerCheck.Start();//start timer
}
protected override void OnStop() { } private void timerCheck_Elapsed(object sender,EventArgs e) { SqlConnection cnn = new SqlConnection("Data Source=(local);database=userRegistration;user id=sa;password=abc"); cnn.Open();//open connection SqlCommand cmd; SqlCommand cmd1; SqlCommand cmd2=new SqlCommand("proCopy",cnn);//pass stored procedure and connection object in command cmd2.CommandType=CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(cmd2.CommandText, cnn); DataTable dt = new DataTable(); da.Fill(dt); foreach (DataRow dr in dt.Rows) { cmd1 = new SqlCommand("insertUser50", cnn); cmd1.CommandType = CommandType.StoredProcedure; //add parameters cmd1.Parameters.AddWithValue("@firstName", dr["firstName"]); cmd1.Parameters.AddWithValue("@lastName", dr["lastName"]); cmd1.Parameters.AddWithValue("@age", dr["age"]); cmd1.Parameters.AddWithValue("@addr", dr["address"]); cmd1.Parameters.AddWithValue("@email", dr["email"]); cmd1.Parameters.AddWithValue("@phone", dr["phone"]); cmd1.Parameters.AddWithValue("@mobile", dr["mobile"]); cmd1.Parameters.AddWithValue("@website", dr["website"]); cmd1.Parameters.AddWithValue("@company", dr["company"]); if (cmd1.ExecuteNonQuery() > 0) { cmd = new SqlCommand("updateUser2", cnn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@email", dr["email"]); cmd.ExecuteNonQuery(); } } cnn.Close();//close connection dt.Dispose();//dispose data table
} } } |
Stored Procedures used in code:-
To getting user list whom age is greater than or equal to 50
|
USE [userRegistration] GO
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[proCopy]
AS BEGIN
SET NOCOUNT ON;
SELECT * FROM regUser where age>=50 AND isActive='false'; END
|
To copy data for one table to another table
|
USE [userRegistration] GO
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[insertUser50] (@firstName varchar(50), @lastName varchar(50), @age int, @addr varchar(100), @email varchar(70), @phone varchar(15), @mobile varchar(10), @website varchar(70), @company varchar(100) ) AS insert into regUser50(firstName,lastName,age,[address],email,phone,mobile,website,company)values(@firstName,@lastName,@age,@addr,@email,@phone,@mobile,@website,@company);
|
To update the table first table
|
USE [userRegistration] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[updateUser2] (@email varchar(70)) AS UPDATE regUser SET isActive='true' WHERE email=@email; |
After completing code. Add “ProjectInstaller” to your project.
To add project installer right click on design view and select add installer.
After clicking on add installer two component are added (ServiceProcessInstaller, ServiceInstaller).
Now select ServiceProcessInstaller1 and change its Account property in property explorer to “Local System”.
After that select ServiceInstaller1 and change its Start Type property to “Automatic” and “Service Name” property to the desired name you want to be displayed.
Now build the project.
Installing windows service
For install windows service open “Visual Studio Command Prompt” as an Administrator. Now install windows service by Installutil command.
To check that windows service install successfully, type services.msc in run dialog box
Here you can see that windows service is running.
Crystal Report in C#
Crystal report is a report designer tool to design and generate report from wide range of data source such as database, xml file etc. Here I will tell you step by step how to create crystal report.
Click on project->add new item->Select Crystal Report->click add
Now a dialog box appears on the screen like this
Choose “Using the Report Wizard”->click on ok
Now wizard appears on the screen.
Expand Create New Connection->Expand OLE DB(ADO)
After expanding a new wizard window appears.
Select provider name “Microsoft OLE DB Provider for SQL Server”->click on Next
Now enter the authentication details of sql and click on next
Now click on finish
Now add table in selected table area and click on next.
Now select required fields and click on finish.
Now adjust fields according to your need in my case I adjust like this
Now our crystal report is ready to use.
Now add a new form and drag and drop Crystal Report Viewer from toolbox-> click on smart tag-> click on choose a crystal report->select crystal report file->click on ok
Now crystal report is visible on our form.
We can filter crystal report by following code:
|
crystalReportViewer1.SelectionFormula = "{regUser.email}='" + _usrReg.email + "'"; crystalReportViewer1.RefreshReport(); |
Code of frmReport.cs
|
using System; using System.Windows.Forms; using CrystalDecisions.CrystalReports.Engine; using CrystalDecisions.Shared;
namespace NewUserRegistration { public partial class frmReport : Form { frmUserReg _usrReg; frmInput _inp; public frmReport(frmUserReg usrReg) { InitializeComponent(); authRep(); _usrReg = usrReg;
crystalReportViewer1.SelectionFormula = "{regUser.email}='" + _usrReg.email + "'"; crystalReportViewer1.RefreshReport(); } public frmReport(frmInput inp) { InitializeComponent(); authRep(); _inp = inp; crystalReportViewer1.SelectionFormula = "{regUser.email}='" + _inp.email + "'"; crystalReportViewer1.RefreshReport(); } private void frmReport_Load(object sender, EventArgs e) {
} public void authRep() { ReportDocument cryRpt = new ReportDocument(); TableLogOnInfos crtableLogoninfos = new TableLogOnInfos(); TableLogOnInfo crtableLogoninfo = new TableLogOnInfo(); ConnectionInfo crConnectionInfo = new ConnectionInfo(); Tables CrTables;
try { cryRpt.Load(@"D:\DEV\NewUserRegistration\NewUserRegistration\UserReport.rpt");
crConnectionInfo.ServerName = "(local)"; crConnectionInfo.DatabaseName = "userRegistration"; crConnectionInfo.UserID = "sa"; crConnectionInfo.Password = "abc";
CrTables = cryRpt.Database.Tables; foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables) { crtableLogoninfo = CrTable.LogOnInfo; crtableLogoninfo.ConnectionInfo = crConnectionInfo; CrTable.ApplyLogOnInfo(crtableLogoninfo); }
crystalReportViewer1.ReportSource = cryRpt; crystalReportViewer1.Refresh();
} catch { } } } } |
Leave Comment