articles

Home / DeveloperSection / Articles / CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

Anonymous User26456 20-Jul-2013

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

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

Table structure:regUser

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

Table structure:regUser50

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

 

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:

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

//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

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

 

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

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

*Right click on the Web Service References from solution explorer

*Click on Add Service References

 

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

*Click on advance button

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

*click on add web reference

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

*copy URL form web browser after running web service and paste in address bar

*click on go button

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

*rename web reference name and click on Add Reference.

*after adding reference we can use its functions according to our need

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

After clicking on the browse button a new form displayed which User Interface is given below

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

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.

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

After clicking ok a window appears which looks like this

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

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.

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

After clicking on add installer two component are added (ServiceProcessInstaller, ServiceInstaller).

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

Now select ServiceProcessInstaller1 and change its Account property in property explorer to “Local System”.

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

 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.

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

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.

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

To check that windows service install successfully, type services.msc in run dialog box

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

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

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

Now a dialog box appears on the screen like this

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

Choose “Using the Report Wizard”->click on ok

Now wizard appears on the screen.

Expand Create New Connection->Expand OLE DB(ADO)

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

After expanding a new wizard window appears.

Select provider name “Microsoft OLE DB Provider for SQL Server”->click on Next

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

Now enter the authentication details of sql and click on next

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

Now click on finish

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

Now add table in selected table area and click on next.

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

Now select required fields and click on finish.

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

Now adjust fields according to your need in my case I adjust like this

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

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

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

Now crystal report is visible on our form.

CRUD Operation C# Using Stored Procedure, Web Service, Windows Service and Crystal Report

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 { }

        }

    }

}

 


Updated 30-Jan-2020
I am a content writter !

Leave Comment

Comments

Liked By