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
<?xmlversion="1.0"encoding="utf-8" ?> <configuration> <connectionStrings> <addname="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 { classGlobalClass { publicSqlConnection cnn; publicSqlCommand cmd; publicSqlDataReader 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 publicbool 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) returntrue; else returnfalse; } //Function for Update Record
publicbool 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) returntrue; else returnfalse; } //Function for Search Record
publicbool 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(); returntrue; } else { dr.Close(); returnfalse; } } //Function for delete Record publicbool 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) returntrue; else returnfalse; }
} } |
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); elseif (!gc.search("searchUser", txtEmail.Text.Trim())) MessageBox.Show("No Records For Deletion!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); elseif (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 = newfrmReport(this); frmrep.ShowDialog();//show crystal report } else { frmInput frminp = newfrmInput(); frminp.ShowDialog();//open input email id form } } else { frmInput frminp = newfrmInput(); 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:
privatevoid 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; } }
privatevoid txtLastName_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtLastName.Text.Trim() == "") lblErrLastName.Visible = true; elseif (!regName.IsMatch(txtLastName.Text.Trim())) { lblErrLastName.Visible = true;
} else { lblErrLastName.Visible = false; } }
privatevoid 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;
}
privatevoid txtEmail_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtEmail.Text.Trim() == "") lblErrEmail.Visible = true; elseif (!regEmail.IsMatch(txtEmail.Text.Trim())) { lblErrEmail.Visible = true;
} else { lblErrEmail.Visible = false; } }
privatevoid txtWebsite_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtWebsite.Text.Trim() == "") lblErrWebsite.Visible = true; elseif (!regUrl.IsMatch(txtWebsite.Text.Trim())) { lblErrWebsite.Visible = true;
} else { lblErrWebsite.Visible = false; } }
privatevoid 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;
} privatevoid txtAge_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtAge.Text.Trim() == "") lblErrAge.Visible = true; elseif (txtAge.Text.Length < 2 || Int32.Parse(txtAge.Text.Trim()) < 18) { lblErrAge.Visible = true;
} else { lblErrAge.Visible = false; }
}
privatevoid txtPhone_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtPhone.Text.Trim() == "") lblErrPhone.Visible = true; elseif (!regPhone.IsMatch(txtPhone.Text.Trim())||txtPhone.Text.Trim().Length<13) { lblErrPhone.Visible = true;
} else { lblErrPhone.Visible = false; } }
privatevoid txtMobile_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons();//disable save, delete and update button if (txtMobile.Text.Trim() == "") lblErrMobile.Visible = true; elseif (!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:
privatevoid 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
publicvoid clearText() { try { foreach (Control c inthis.Controls) { if (c isTextBox)
(c asTextBox).Clear(); if (c isMaskedTextBox) (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 publicvoid 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 publicvoid 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(); returnfalse; } //lastname validation elseif (!regName.IsMatch(txtLastName.Text.Trim()) || txtLastName.Text.Trim()=="") { lblErrLastName.Visible = true; txtLastName.Focus(); returnfalse; } //age validation elseif (txtAge.Text.Length < 2 || Int32.Parse(txtAge.Text.Trim()) < 18 || txtAge.Text.Trim() == "") { lblErrAge.Visible = true; txtAge.Focus(); returnfalse; } elseif (txtAddress.Text.Trim() == ""||txtAddress.Text.Trim()=="") { lblErrAddress.Visible = true; txtAddress.Focus(); returnfalse; } //email validation elseif (!regEmail.IsMatch(txtEmail.Text.Trim())||txtEmail.Text.Trim()=="") { lblErrEmail.Visible = true; txtEmail.Focus(); returnfalse; } //phone validation elseif (!regPhone.IsMatch(txtPhone.Text.Trim()) || txtPhone.Text.Trim().Length < 13||txtPhone.Text.Trim()=="") { lblErrPhone.Visible = true; txtPhone.Focus(); returnfalse; } //mobile no validation elseif (!regMobile.IsMatch(txtMobile.Text.Trim())||txtMobile.Text.Length < 10 || txtMobile.Text.Trim() == "0000000000" || txtMobile.Text.Trim() == "" ) { lblErrMobile.Visible = true; txtMobile.Focus(); returnfalse; } //url validation elseif (!regUrl.IsMatch(txtWebsite.Text.Trim()) || txtWebsite.Text.Trim() == "") { lblErrWebsite.Visible = true; txtWebsite.Focus(); returnfalse; } elseif (txtCompany.Text.Trim() == "") { lblErrCompany.Visible = true; txtCompany.Focus(); returnfalse; } elseif(_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); returnfalse; } }
returntrue; } catch (Exception ee) {
MessageBox.Show(ee.Message); returnfalse; } } |
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 SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO CREATEPROCEDURE [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 insertinto 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
SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO
CREATEPROCEDURE [dbo].[deleteUser] (@email varchar(70)) AS DELETEFROM regUser WHERE email=@email; |
For Update:
USE [userRegistration] GO
SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO
ALTERPROCEDURE [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 = newRegex(@"^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$"); Regex regName = newRegex(@"^^[a-zA-Z][a-zA-Z ]*[a-zA-Z]$"); Regex regPhone = newRegex(@"^([\(]{1}[1-9]{3}[\)]{1}[ ]{1}[0-9]{3}[\-]{1}[0-9]{4})$"); Regex regMobile = newRegex(@"^\d{10}$"); Regex regAge = newRegex(@"^\d{2}$"); Regex regUrl = newRegex(@"^((https?|ftp)://|(www|ftp)\.)[a-z0-9-]+(\.[a-z0-9-]+)+([/?].*)?$"); Regex regCompany = newRegex(@"^[A-Z]([a-zA-Z0-9]|[- @\.#&!])*$");
|
Code for Navigation keys:
//first button privatevoid 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); } elseif (_recNo >= 0) { _recNo = 0; dr = dt.Rows[_recNo]; nav(dr);
} _flagChange = 0; } } else {
if (_recNo == -1) { _recNo++; dr = dt.Rows[_recNo]; nav(dr); } elseif (_recNo >= 0) { _recNo = 0; dr = dt.Rows[_recNo]; nav(dr);
} _flagChange = 0; } } catch (Exception ee) { MessageBox.Show(ee.Message); } } //last button privatevoid 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 privatevoid 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 privatevoid 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; } elseif (_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; } elseif (_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 { publicpartialclassfrmUserReg : Form { public frmUserReg() { InitializeComponent(); } //regular expressions Regex regEmail = newRegex(@"^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$"); Regex regName = newRegex(@"^^[a-zA-Z][a-zA-Z ]*[a-zA-Z]$"); Regex regPhone = newRegex(@"^([\(]{1}[1-9]{3}[\)]{1}[ ]{1}[0-9]{3}[\-]{1}[0-9]{4})$"); Regex regMobile = newRegex(@"^\d{10}$"); Regex regAge = newRegex(@"^\d{2}$"); Regex regUrl = newRegex(@"^((https?|ftp)://|(www|ftp)\.)[a-z0-9-]+(\.[a-z0-9-]+)+([/?].*)?$"); Regex regCompany = newRegex(@"^[A-Z]([a-zA-Z0-9]|[- @\.#&!])*$"); GlobalClass gc = newGlobalClass(); //flag and navigation variables publicint _flagGrid,_flagChange,_flagUpdate=0,_recNo=-1,_totalRec; publicstring email; //web service reference object fillGridWebSerRef.Service1 service = new fillGridWebSerRef.Service1(); DataSet ds = newDataSet(); DataRow dr; DataTable dt = newDataTable(); privatevoid 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(); returnfalse; } //lastname validation elseif (!regName.IsMatch(txtLastName.Text.Trim()) || txtLastName.Text.Trim()=="") { lblErrLastName.Visible = true; txtLastName.Focus(); returnfalse; } //age validation elseif (txtAge.Text.Length < 2 || Int32.Parse(txtAge.Text.Trim()) < 18 || txtAge.Text.Trim() == "") { lblErrAge.Visible = true; txtAge.Focus(); returnfalse; } elseif (txtAddress.Text.Trim() == ""||txtAddress.Text.Trim()=="") { lblErrAddress.Visible = true; txtAddress.Focus(); returnfalse; } //email validation elseif (!regEmail.IsMatch(txtEmail.Text.Trim())||txtEmail.Text.Trim()=="") { lblErrEmail.Visible = true; txtEmail.Focus(); returnfalse; } //phone validation elseif (!regPhone.IsMatch(txtPhone.Text.Trim()) || txtPhone.Text.Trim().Length < 13||txtPhone.Text.Trim()=="") { lblErrPhone.Visible = true; txtPhone.Focus(); returnfalse; } //mobile no validation elseif (!regMobile.IsMatch(txtMobile.Text.Trim())||txtMobile.Text.Length < 10 || txtMobile.Text.Trim() == "0000000000" || txtMobile.Text.Trim() == "" ) { lblErrMobile.Visible = true; txtMobile.Focus(); returnfalse; } //url validation elseif (!regUrl.IsMatch(txtWebsite.Text.Trim()) || txtWebsite.Text.Trim() == "") { lblErrWebsite.Visible = true; txtWebsite.Focus(); return false; } //company name validation elseif (txtCompany.Text.Trim() == "") { lblErrCompany.Visible = true; txtCompany.Focus(); returnfalse; } //check email id registration elseif(_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); returnfalse; } }
returntrue; } catch (Exception ee) {
MessageBox.Show(ee.Message); returnfalse; } }
privatevoid btnBrowse_Click(object sender, EventArgs e) { try { frmRecords frmrec = newfrmRecords(this); frmrec.ShowDialog(); } catch (Exception ee) { MessageBox.Show(ee.Message); } }
privatevoid 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); } }
privatevoid frmUserReg_FormClosed(object sender, FormClosedEventArgs e) { Application.Exit(); }
privatevoid 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); } elseif (_recNo >= 0) { _recNo = 0; dr = dt.Rows[_recNo]; nav(dr);
} _flagChange = 0; } } else {
if (_recNo == -1) { _recNo++; dr = dt.Rows[_recNo]; nav(dr); } elseif (_recNo >= 0) { _recNo = 0; dr = dt.Rows[_recNo]; nav(dr);
} _flagChange = 0; } } catch (Exception ee) { MessageBox.Show(ee.Message); } }
privatevoid 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); } } publicvoid 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); } }
privatevoid 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); } }
privatevoid btnDelete_Click(object sender, EventArgs e) { try { if (validate()) { if (txtEmail.Text == "") MessageBox.Show("No Records For Deletion!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); elseif (!gc.search("searchUser", txtEmail.Text.Trim())) MessageBox.Show("No Records For Deletion!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); elseif (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); } }
privatevoid 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); } }
privatevoid 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; } elseif (_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; } elseif (_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); } }
privatevoid 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 = newfrmReport(this); frmrep.ShowDialog(); } else { frmInput frminp = newfrmInput(); frminp.ShowDialog(); } } else { frmInput frminp = newfrmInput(); frminp.ShowDialog(); } } catch (Exception ee) { MessageBox.Show(ee.Message); } } publicvoid clearText() { try { foreach (Control c inthis.Controls) { if (c isTextBox)
(c asTextBox).Clear(); if (c isMaskedTextBox) (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); } }
privatevoid 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; } }
privatevoid txtLastName_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtLastName.Text.Trim() == "") lblErrLastName.Visible = true; elseif (!regName.IsMatch(txtLastName.Text.Trim())) { lblErrLastName.Visible = true;
} else { lblErrLastName.Visible = false; } }
privatevoid txtAddress_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtAddress.Text == "") lblErrAddress.Visible = true; else lblErrAddress.Visible = false;
}
privatevoid txtEmail_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtEmail.Text.Trim() == "") lblErrEmail.Visible = true; elseif (!regEmail.IsMatch(txtEmail.Text.Trim())) { lblErrEmail.Visible = true;
} else { lblErrEmail.Visible = false; } }
privatevoid txtWebsite_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtWebsite.Text.Trim() == "") lblErrWebsite.Visible = true; elseif (!regUrl.IsMatch(txtWebsite.Text.Trim())) { lblErrWebsite.Visible = true;
} else { lblErrWebsite.Visible = false; } }
privatevoid txtCompany_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtCompany.Text == "") lblErrCompany.Visible = true; else lblErrCompany.Visible = false;
}
privatevoid 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; } }
privatevoid txtAge_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtAge.Text.Trim() == "") lblErrAge.Visible = true; elseif (txtAge.Text.Length < 2 || Int32.Parse(txtAge.Text.Trim()) < 18) { lblErrAge.Visible = true;
} else { lblErrAge.Visible = false; }
}
privatevoid txtPhone_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtPhone.Text.Trim() == "") lblErrPhone.Visible = true; elseif (!regPhone.IsMatch(txtPhone.Text.Trim())||txtPhone.Text.Trim().Length<13) { lblErrPhone.Visible = true;
} else { lblErrPhone.Visible = false; } }
privatevoid txtMobile_TextChanged(object sender, EventArgs e) { _flagChange = 1; disableButtons(); if (txtMobile.Text.Trim() == "") lblErrMobile.Visible = true; elseif (!regMobile.IsMatch(txtMobile.Text.Trim())||txtMobile.Text.Length < 10 || txtMobile.Text.Trim()== "0000000000") { lblErrMobile.Visible = true;
} else { lblErrMobile.Visible = false; } }
privatevoid btnClear_Click(object sender, EventArgs e) { clearText(); disableButtons(); _recNo = -1; _flagChange = 0;
}
privatevoid txtWebsite_Leave(object sender, EventArgs e) { string weburl = txtWebsite.Text.Trim(); if (weburl.StartsWith("www.")) { weburl = "http://" + weburl; txtWebsite.Text = weburl; } } publicvoid 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] publicclassService1 : System.Web.Services.WebService { [WebMethod] publicDataSet fillGrid() { SqlConnection cnn = newSqlConnection(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 = newSqlDataAdapter(cmd); DataSet ds = newDataSet(); 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 { publicpartialclassfrmRecords : 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 = newDataSet(); DataTable dt = newDataTable(); DataTable _DataTableForPagingList; privatevoid 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(); } privateBindingList<PagingData> FillDataforGrid() { BindingList<PagingData> list = newBindingList<PagingData>(); for (int i = 0; i < _DataTableForPagingList.Rows.Count; i++) { PagingData obj = newPagingData(_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; privatevoid 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; } elseif (_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); } }
privatevoid toolStripButton1_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; elseif (ToolStripButton == btnForward) CurrentPage++; elseif (ToolStripButton == btnLast) CurrentPage = PagesCount; elseif (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture); if (CurrentPage < 1) CurrentPage = 1; elseif (CurrentPage > PagesCount) CurrentPage = PagesCount; //Rebind the Datagridview with the data. RebindGridForPageChange(); //Change the pagiantions buttons according to page number RefreshPagination(); } catch (Exception) { } } privatevoid RebindGridForPageChange() { //Rebinding the Datagridview with data int datasourcestartIndex = (CurrentPage - 1) * pageRows; Templist = newBindingList<PagingData>(); for (int i = datasourcestartIndex; i < datasourcestartIndex + pageRows; i++) { if (i >= Baselist.Count) break;
Templist.Add(Baselist[i]); }
GridViewRecords.DataSource = Templist;
} privatevoid RefreshPagination() { ToolStripButton[] items = newToolStripButton[] { 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; } privatevoid btnPrevious_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; elseif (ToolStripButton == btnForward) CurrentPage++; elseif (ToolStripButton == btnLast) CurrentPage = PagesCount; elseif (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; elseif (CurrentPage > PagesCount) CurrentPage = PagesCount;
//Rebind the Datagridview with the data. RebindGridForPageChange();
//Change the paginations buttons according to page number RefreshPagination(); } catch (Exception) { } }
privatevoid toolStripButton5_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; elseif (ToolStripButton == btnForward) CurrentPage++; elseif (ToolStripButton == btnLast) CurrentPage = PagesCount; elseif (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; elseif (CurrentPage > PagesCount) CurrentPage = PagesCount;
//Rebind the Datagridview with the data. RebindGridForPageChange();
//Change the pagiantions buttons according to page number RefreshPagination();
} catch (Exception) { } }
privatevoid toolStripButton6_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; elseif (ToolStripButton == btnForward) CurrentPage++; elseif (ToolStripButton == btnLast) CurrentPage = PagesCount; elseif (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; elseif (CurrentPage > PagesCount) CurrentPage = PagesCount;
//Rebind the Datagridview with the data. RebindGridForPageChange();
//Change the pagiantions buttons according to page number RefreshPagination(); } catch (Exception) { } }
privatevoid toolStripButton7_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; elseif (ToolStripButton == btnForward) CurrentPage++; elseif (ToolStripButton == btnLast) CurrentPage = PagesCount; elseif (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; elseif (CurrentPage > PagesCount) CurrentPage = PagesCount; //Rebind the Datagridview with the data. RebindGridForPageChange(); //Change the pagiantions buttons according to page number RefreshPagination(); } catch (Exception) { } }
privatevoid toolStripButton8_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; elseif (ToolStripButton == btnForward) CurrentPage++; elseif (ToolStripButton == btnLast) CurrentPage = PagesCount; elseif (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; elseif (CurrentPage > PagesCount) CurrentPage = PagesCount;
//Rebind the Datagridview with the data. RebindGridForPageChange();
//Change the pagiantions buttons according to page number RefreshPagination(); } catch (Exception) { } }
privatevoid toolStripButton9_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; elseif (ToolStripButton == btnForward) CurrentPage++; elseif (ToolStripButton == btnLast) CurrentPage = PagesCount; elseif (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; elseif (CurrentPage > PagesCount) CurrentPage = PagesCount;
//Rebind the Datagridview with the data. RebindGridForPageChange();
//Change the paginations buttons according to page number RefreshPagination(); } catch (Exception) { } }
privatevoid btnNext_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender);
//Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; elseif (ToolStripButton == btnForward) CurrentPage++; elseif (ToolStripButton == btnLast) CurrentPage = PagesCount; elseif (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; elseif (CurrentPage > PagesCount) CurrentPage = PagesCount;
//Rebind the Datagridview with the data. RebindGridForPageChange();
//Change the pagiantions buttons according to page number RefreshPagination(); } catch (Exception) { } }
privatevoid btnLast_Click(object sender, EventArgs e) { try { ToolStripButton ToolStripButton = ((ToolStripButton)sender); //Determining the current page if (ToolStripButton == btnBackward) CurrentPage--; elseif (ToolStripButton == btnForward) CurrentPage++; elseif (ToolStripButton == btnLast) CurrentPage = PagesCount; elseif (ToolStripButton == btnFirst) CurrentPage = 1; else CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);
if (CurrentPage < 1) CurrentPage = 1; elseif (CurrentPage > PagesCount) CurrentPage = PagesCount;
//Rebind the Datagridview with the data. RebindGridForPageChange();
//Change the paginations buttons according to page number RefreshPagination(); } catch (Exception) { } }
privatevoid 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 { publicpartialclassCopyData : ServiceBase { public CopyData() { InitializeComponent(); } Timer timerCheck = newTimer();//create timer protectedoverridevoid OnStart(string[] args) { timerCheck.Elapsed += newElapsedEventHandler(timerCheck_Elapsed); timerCheck.Interval = 10000;//set timer interval timerCheck.Enabled = true; timerCheck.Start();//start timer
}
protectedoverridevoid OnStop() { } privatevoid timerCheck_Elapsed(object sender,EventArgs e) { SqlConnection cnn = newSqlConnection("Data Source=(local);database=userRegistration;user id=sa;password=abc"); cnn.Open();//open connection SqlCommand cmd; SqlCommand cmd1; SqlCommand cmd2=newSqlCommand("proCopy",cnn);//pass stored procedure and connection object in command cmd2.CommandType=CommandType.StoredProcedure; SqlDataAdapter da = newSqlDataAdapter(cmd2.CommandText, cnn); DataTable dt = newDataTable(); 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
SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO
CREATEPROCEDURE [dbo].[proCopy]
AS BEGIN
SETNOCOUNTON;
SELECT*FROM regUser where age>=50 AND isActive='false'; END
|
To copy data for one table to another table
USE [userRegistration] GO
SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO
CREATEPROCEDURE [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 insertinto 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 SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO
CREATEPROCEDURE [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 { publicpartialclassfrmReport : 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(); } privatevoid frmReport_Load(object sender, EventArgs e) {
} publicvoid authRep() { ReportDocument cryRpt = newReportDocument(); TableLogOnInfos crtableLogoninfos = newTableLogOnInfos(); TableLogOnInfo crtableLogoninfo = newTableLogOnInfo(); ConnectionInfo crConnectionInfo = newConnectionInfo(); 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