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

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

<?xml version='1.0'encoding='utf-8' ?>

<configuration>   

  <connectionStrings>

    <add name='cnn' connectionString='data source=(local);database=userRegistration;user id=sa;password=abc'

      providerName='System.Data.SqlCli

Code for Navigation keys:

nt' />  

  </connectionStrings>

</configuration>

 

Add Class and define Insert, update and delete function

GlobalClass.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

 

namespace NewUserRegistration

{

    class GlobalClass

    {

        public SqlConnection cnn;

        public SqlCommand cmd;

        public SqlDataReader dr;

        public GlobalClass()

        {

            cnn = new SqlConnection();

            cnn.ConnectionString = ConfigurationManager.ConnectionStrings['cnn'].ToString();

            if (cnn.State == ConnectionState.Closed)

            {

                cnn.Open();//Open Connection

            }

        }

//Function for Insert Record

        public bool saveRecord(string proName, string firstName, string lastName, int age, string address, string email, string phone, string mobile, string website, string company)

        {

            cmd = new SqlCommand(proName, cnn); //passing procedure name and connection object

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue('@firstName', firstName);

            cmd.Parameters.AddWithValue('@lastName', lastName);

            cmd.Parameters.AddWithValue('@age', age);

            cmd.Parameters.AddWithValue('@addr', address);

            cmd.Parameters.AddWithValue('@email', email);

            cmd.Parameters.AddWithValue('@phone', phone);

            cmd.Parameters.AddWithValue('@mobile', mobile);

            cmd.Parameters.AddWithValue('@website', website);

            cmd.Parameters.AddWithValue('@company', company);

            int res = cmd.ExecuteNonQuery();

            if (res == 1)

                return true;

            else

                return false;

        }

//Function for Update Record

 

        public bool updateRecord(string proName, string firstName, string lastName, int age, string address, string email, string phone, string mobile, string website, string company, int id)

        {

            cmd = new SqlCommand(proName, cnn);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue('@firstName', firstName);

            cmd.Parameters.AddWithValue('@lastName', lastName);

            cmd.Parameters.AddWithValue('@age', age);

            cmd.Parameters.AddWithValue('@addr', address);

            cmd.Parameters.AddWithValue('@email', email);

            cmd.Parameters.AddWithValue('@phone', phone);

            cmd.Parameters.AddWithValue('@mobile', mobile);

            cmd.Parameters.AddWithValue('@website', website);

            cmd.Parameters.AddWithValue('@company', company);

            cmd.Parameters.AddWithValue('@id', id);

            int res = cmd.ExecuteNonQuery();

            if (res == 1)

                return true;

            else

                return false;

        }

//Function for Search Record

 

        public bool search(string proName, string email)

        {

            cmd = new SqlCommand();

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = proName;

            cmd.Connection = cnn;

            cmd.Parameters.AddWithValue('@email', email);

            dr = cmd.ExecuteReader();

            if (dr.HasRows)

            {

                dr.Close();

                return true;

            }

            else

            {

                dr.Close();

                return false;

            }

        }

//Function for delete Record

        public bool deleteRecord(string proName, string email)

        {

            cmd = new SqlCommand(proName, cnn);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue('@email', email);

            int res = cmd.ExecuteNonQuery();

            if (res == 1)

                return true;

            else

                return false;

        }

 

 

    }

}

 

Write following code on save button click event:

  try

            {

                if (validate())//call validate function for validation

                {

                    string weburl = txtWebsite.Text.Trim();

                    if (weburl.StartsWith('www.'))

                    {

                        weburl = 'http://' + weburl;

                        if (gc.saveRecord('insertUser', txtFirstName.Text.Trim(), txtLastName.Text.Trim(), Int32.Parse(txtAge.Text.Trim()), txtAddress.Text.Trim(), txtEmail.Text.Trim(), txtPhone.Text.Trim(), txtMobile.Text.Trim(), weburl, txtCompany.Text.Trim()))//call saveRecord function for saving record

                        {

                            MessageBox.Show('Record Saved Sucessfully!', 'Information', MessageBoxButtons.OK, MessageBoxIcon.Information);

                            clearText();//clear textboxes

                            disableButtons();//disable save, delete and update button

                            dt.Clear();                           

                            ds = gc.fillGrid();//fill data source from records

                            dt = ds.Tables[0];

                            _totalRec = dt.Rows.Count;

                            _recNo = -1;

                            _flagChange = 0;

                        }

                        else

                            MessageBox.Show('Record Not Saved!', 'Information', MessageBoxButtons.OK, MessageBoxIcon.Information);

                    }

                    else

                    {

                        if (gc.saveRecord('insertUser', txtFirstName.Text.Trim(), txtLastName.Text.Trim(), Int32.Parse(txtAge.Text.Trim()), txtAddress.Text.Trim(), txtEmail.Text.Trim(), txtPhone.Text.Trim(), txtMobile.Text.Trim(), weburl, txtCompany.Text.Trim()))

                        {

                            MessageBox.Show('Record Saved Sucessfully!', 'Information', MessageBoxButtons.OK, MessageBoxIcon.Information);

                            clearText();//clear textboxes

                            ds = gc.fillGrid();//fill data source from records

                            dt = ds.Tables[0];

                            _recNo = -1;

                            _flagChange = 0;

                        }

                        else

                            MessageBox.Show('Record Not Saved!', 'Information', MessageBoxButtons.OK, MessageBoxIcon.Information);

                    }

                }

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

 

Write following code on delete button click event:

 

try

            {

                if (validate())//call validate function for validation

                {

                    if (txtEmail.Text == '')

                        MessageBox.Show('No Records For Deletion!', 'Error', MessageBoxButtons.OK, MessageBoxIcon.Error);

                    else if (!gc.search('searchUser', txtEmail.Text.Trim()))

                        MessageBox.Show('No Records For Deletion!', 'Error', MessageBoxButtons.OK, MessageBoxIcon.Error);

                    else if (MessageBox.Show('Are you sure?', 'Confirm Delete', MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

                    {

                        if (gc.deleteRecord('deleteUser', txtEmail.Text))

                        {

 

                            MessageBox.Show('Record Deleted!', 'Information', MessageBoxButtons.OK, MessageBoxIcon.Information);

                            dt.Clear();

                            dt.Dispose();

                            clearText();//clear textboxes

                            disableButtons();//disable save delete update button

                            ds = gc.fillGrid();

                            dt = ds.Tables[0];

                            _totalRec = dt.Rows.Count;

                            _recNo = -1;

                            _flagChange = 0;

                        }

                    }

                }

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

 

 

Write following code on update button click event:

try

            {

              

                if (_flagChange == 1)

                {

                    if (!validate())

                    {

                       

                    }

                    else

                    {

                        if (_recNo == -1)

                        {

                            MessageBox.Show('No record for update!', 'Error', MessageBoxButtons.OK, MessageBoxIcon.Error);

                        }

                        else

                        {

                            dr = dt.Rows[_recNo];

                            if (gc.updateRecord('updateUser', txtFirstName.Text.Trim(), txtLastName.Text.Trim(), Int32.Parse(txtAge.Text.Trim()), txtAddress.Text.Trim(), txtEmail.Text.Trim(), txtPhone.Text.Trim(), txtMobile.Text.Trim(), txtWebsite.Text.Trim(), txtCompany.Text.Trim(), Int32.Parse(dr['userId'].ToString())))

                            {

                                ds = gc.fillGrid();

                                dt = ds.Tables[0];

                                _flagChange = 0;

                                _recNo = -1;

                                _flagUpdate = 0;

                                MessageBox.Show('Record Updated!');

                                clearText();

                                disableButtons();

                               

                            }

                            else

                                MessageBox.Show('Record not updated!', 'Error', MessageBoxButtons.OK, MessageBoxIcon.Error);

                        }

                    }

                }

                else

                {

                    MessageBox.Show('Nothing for update!');

                }

              

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

 

 

Write following code on report button click event:

try

            {

                email = txtEmail.Text.Trim();

                if (_recNo >= 0)

                {

                    dr = dt.Rows[_recNo];

                    if (txtEmail.Text != '' && email == dr['email'].ToString())

                    {

                        frmReport frmrep = new frmReport(this);

                        frmrep.ShowDialog();//show crystal report

                    }

                    else

                    {

                        frmInput frminp = new frmInput();

                        frminp.ShowDialog();//open input email id form

                    }

                }

                else

                {

                    frmInput frminp = new frmInput();

                    frminp.ShowDialog();//open input email id form

                }

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

 

 

 

 

Write following code on clear button click event:

 

            clearText();//clear textboxes

            disableButtons();//disable save, delete and update button

            _recNo = -1;

            _flagChange = 0;

 

       

 

 

 

Code on textbox change event:

private void txtFirstName_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();//disable save, delete and update button

            if (txtFirstName.Text.Trim() == '')

                lblErrFirstName.Visible = true;

            if (!regName.IsMatch(txtFirstName.Text.Trim()))

            {

                lblErrFirstName.Visible = true;

 

            }

            else

            {

                lblErrFirstName.Visible = false;

            }

        }

 

        private void txtLastName_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();//disable save, delete and update button

            if (txtLastName.Text.Trim() == '')

                lblErrLastName.Visible = true;

            else if (!regName.IsMatch(txtLastName.Text.Trim()))

            {

                lblErrLastName.Visible = true;

 

            }

            else

            {

                lblErrLastName.Visible = false;

            }

        }

 

 

        private void txtAddress_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();//disable save, delete and update button

            if (txtAddress.Text == '')

                lblErrAddress.Visible = true;

            else

                lblErrAddress.Visible = false;

 

        }

 

        private void txtEmail_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();//disable save, delete and update button

            if (txtEmail.Text.Trim() == '')

                lblErrEmail.Visible = true;

            else if (!regEmail.IsMatch(txtEmail.Text.Trim()))

            {

                lblErrEmail.Visible = true;

 

            }

            else

            {

                lblErrEmail.Visible = false;

            }

        }

 

        private void txtWebsite_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();//disable save, delete and update button

            if (txtWebsite.Text.Trim() == '')

                lblErrWebsite.Visible = true;

            else if (!regUrl.IsMatch(txtWebsite.Text.Trim()))

            {

                lblErrWebsite.Visible = true;

 

            }

            else

            {

                lblErrWebsite.Visible = false;

            }

        }

 

        private void txtCompany_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();//disable save, delete and update button

            if (txtCompany.Text == '')

                lblErrCompany.Visible = true;

            else

                lblErrCompany.Visible = false;

           

        }

   private void txtAge_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();//disable save, delete and update button

            if (txtAge.Text.Trim() == '')

                lblErrAge.Visible = true;

            else if (txtAge.Text.Length < 2 || Int32.Parse(txtAge.Text.Trim()) < 18)

            {

                lblErrAge.Visible = true;

 

            }

            else

            {

                lblErrAge.Visible = false;

            }

          

        }

 

        private void txtPhone_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();//disable save, delete and update button

            if (txtPhone.Text.Trim() == '')

                lblErrPhone.Visible = true;

            else if (!regPhone.IsMatch(txtPhone.Text.Trim())||txtPhone.Text.Trim().Length<13)

            {

                lblErrPhone.Visible = true;

 

            }

            else

            {

                lblErrPhone.Visible = false;

            }

        }

 

        private void txtMobile_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();//disable save, delete and update button

            if (txtMobile.Text.Trim() == '')

                lblErrMobile.Visible = true;

            else if (!regMobile.IsMatch(txtMobile.Text.Trim())||txtMobile.Text.Length < 10 || txtMobile.Text.Trim()== '0000000000')

            {

                lblErrMobile.Visible = true;

 

            }

            else

            {

                lblErrMobile.Visible = false;

            }

        }

 

On website textbox leave event:

  private void txtWebsite_Leave(object sender, EventArgs e)

        {

            string weburl = txtWebsite.Text.Trim();

            if (weburl.StartsWith('www.'))

            {

                weburl = 'http://' + weburl;// Add Http:// when url starts from www                                                 txtWebsite.Text = weburl;

            }

        }

 

Following functions are used in the code according to the need:

// function for clear text fields

 

public void clearText()

        {

            try

            {

                foreach (Control c in this.Controls)

                {

                    if (c is TextBox)

 

                        (c as TextBox).Clear();

                    if (c is MaskedTextBox)

                        (c as MaskedTextBox).Clear();

                   

                }

                lblErrFirstName.Visible = false;

                lblErrLastName.Visible = false;

                lblErrAge.Visible = false;

                lblErrAddress.Visible = false;

                lblErrEmail.Visible = false;

                lblErrPhone.Visible = false;

                lblErrMobile.Visible = false;

                lblErrWebsite.Visible = false;

                lblErrCompany.Visible = false;

 

                _flagChange = 0;

            }

// function for disable button

   public void disableButtons()

        {

            if (txtFirstName.Text.Trim() == '' && txtLastName.Text.Trim() == '' && txtAge.Text.Trim() == '' && txtAddress.Text.Trim() == '' && txtEmail.Text.Trim() == '' && txtPhone.Text == '(   )    -' && txtMobile.Text.Trim() == '' && txtWebsite.Text.Trim() == '' && txtCompany.Text.Trim() == '')

            {

                btnDelete.Enabled = false;

                btnSave.Enabled = false;

                btnUpdate.Enabled = false;

            }

            else

            {

                btnDelete.Enabled = true;

                btnSave.Enabled = true;

                btnUpdate.Enabled = true;

            }

        }

// function for navigation between records

public void nav(DataRow objds)

        {

            try

            {

                txtFirstName.Text = dr['firstName'].ToString();

                txtLastName.Text = dr['lastName'].ToString();

                txtAge.Text = dr['age'].ToString();

                txtAddress.Text = dr['address'].ToString();

                txtEmail.Text = dr['email'].ToString();

                txtPhone.Text = dr['phone'].ToString();

                txtMobile.Text = dr['mobile'].ToString();

                txtWebsite.Text = dr['website'].ToString();

                txtCompany.Text = dr['company'].ToString();

              

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

 

// function for validation

bool validate()

        {

            try

            {

              

                //firstname validation

                if (!regName.IsMatch(txtFirstName.Text.Trim()) || txtFirstName.Text.Trim()=='')

                {

                    lblErrFirstName.Visible = true;

                    txtFirstName.Focus();

                    return false;

                }

                //lastname validation

                 else if (!regName.IsMatch(txtLastName.Text.Trim()) || txtLastName.Text.Trim()=='')

                {

                    lblErrLastName.Visible = true;

                    txtLastName.Focus();

                    return false;

                }

                //age validation

                else if (txtAge.Text.Length < 2 || Int32.Parse(txtAge.Text.Trim()) < 18 || txtAge.Text.Trim() == '')

                {

                    lblErrAge.Visible = true;

                    txtAge.Focus();

                    return false;

                }

                 else if (txtAddress.Text.Trim() == ''||txtAddress.Text.Trim()=='')

                 {

                     lblErrAddress.Visible = true;

                     txtAddress.Focus();

                     return false;

                 }

                 //email validation

                 else if (!regEmail.IsMatch(txtEmail.Text.Trim())||txtEmail.Text.Trim()=='')

                 {

                     lblErrEmail.Visible = true;

                     txtEmail.Focus();

                     return false;

                 }

                 //phone validation

                else if (!regPhone.IsMatch(txtPhone.Text.Trim()) || txtPhone.Text.Trim().Length < 13||txtPhone.Text.Trim()=='')

                 {

                     lblErrPhone.Visible = true;

                     txtPhone.Focus();

                     return false;                                       

                 }

                 //mobile no validation

                else if (!regMobile.IsMatch(txtMobile.Text.Trim())||txtMobile.Text.Length < 10 || txtMobile.Text.Trim() == '0000000000' || txtMobile.Text.Trim() == '' )

                 {

                     lblErrMobile.Visible = true;

                     txtMobile.Focus();

                     return false;

                 }

                 //url validation

                else if (!regUrl.IsMatch(txtWebsite.Text.Trim()) || txtWebsite.Text.Trim() == '')

                 {

                     lblErrWebsite.Visible = true;

                     txtWebsite.Focus();

                     return false;

                 }

                else if (txtCompany.Text.Trim() == '')

                {

                    lblErrCompany.Visible = true;

                    txtCompany.Focus();

                    return false;

                }

                else if(_flagUpdate==0)

                {

                if (gc.search('searchUser', txtEmail.Text.Trim()))

                {

                    lblEmail.Visible = true;

                    txtEmail.Focus();                 

                    MessageBox.Show('This Email is already registred!', 'Error', MessageBoxButtons.OK, MessageBoxIcon.Error);

                    return false;

                }

                }

               

                return true;

            }

            catch (Exception ee)

            {

               

                MessageBox.Show(ee.Message);

                return false;

            }

        }

 

On form closing event write following code:

  if (_flagChange == 1) //Check that if you make any change

            {

                if (MessageBox.Show('Are you sure to exit without saving?', 'Confirm Close', MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

                {

                    e.Cancel = false;

                }

                else

                    e.Cancel = true;

            }

 

Following stored procedures are used:

For Insert:

USE [userRegistration]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[insertUser]

     

      (@firstName varchar(50),

      @lastName varchar(50),

      @age int,

      @addr varchar(100),

      @email varchar(70),

      @phone varchar(15),

      @mobile varchar(10),

      @website varchar(70),

      @company varchar(100)

      )

AS

insert into regUser(firstName,lastName,age,[address],email,phone,mobile,website,company,isActive) values(@firstName,@lastName,@age,@addr,@email,@phone,@mobile,@website,@company,'false');

 

 

 

 

For Delete:

USE [userRegistration]

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[deleteUser]

      (@email varchar(70))

AS

DELETE FROM regUser WHERE email=@email;

 

For Update:

USE [userRegistration]

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

ALTER PROCEDURE [dbo].[updateUser]

       (@firstName varchar(50),

       @lastName varchar(50),

       @age int,

       @addr varchar(100),

       @email varchar(70),

       @phone varchar(15),

       @mobile varchar(10),

       @website varchar(100),

       @company varchar(70),

       @id int

       )

AS

UPDATE  regUser SET firstName=@firstName,lastName=@lastName,age=@age,[address]

=@addr,email=@email,phone=@phone,mobile=@mobile,website=@website,company=@company WHERE userId=@id;

 

Following Regular Expression used in program:

Regex regEmail = new Regex(@'^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$');

Regex regName = new Regex(@'^^[a-zA-Z][a-zA-Z ]*[a-zA-Z]$');

Regex regPhone = new Regex(@'^([\(]{1}[1-9]{3}[\)]{1}[ ]{1}[0-9]{3}[\-]{1}[0-9]{4})$');

Regex regMobile = new Regex(@'^\d{10}$');

Regex regAge = new Regex(@'^\d{2}$');

Regex regUrl = new Regex(@'^((https?|ftp)://|(www|ftp)\.)[a-z0-9-]+(\.[a-z0-9-]+)+([/?].*)?$');

Regex regCompany = new Regex(@'^[A-Z]([a-zA-Z0-9]|[- @\.#&!])*$');

 

 

Code for Navigation keys:

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

//first button

private void btnFirst_Click(object sender, EventArgs e)

        {

           

            try

            {

                btnFirst.Enabled = false;

                btnPre.Enabled = false;

                btnNext.Enabled = true;

                btnLast.Enabled = true;

                if (_flagChange == 1)

                {

                    if (MessageBox.Show('Are you sure to move without saving?', 'Confirmation', MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

                    {

 

                        if (_recNo == -1)

                        {

                            _recNo++;

                            dr = dt.Rows[_recNo];

                            nav(dr);

                        }

                        else if (_recNo >= 0)

                        {

                            _recNo = 0;

                            dr = dt.Rows[_recNo];

                            nav(dr);

 

                        }

                        _flagChange = 0;

                    }

                }

                else

                {

 

                    if (_recNo == -1)

                    {

                        _recNo++;

                        dr = dt.Rows[_recNo];

                        nav(dr);

                    }

                    else if (_recNo >= 0)

                    {

                        _recNo = 0;

                        dr = dt.Rows[_recNo];

                        nav(dr);

 

                    }

                    _flagChange = 0;

                }

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

//last button

        private void btnLast_Click(object sender, EventArgs e)

        {

            try

            {

                btnLast.Enabled = false;

                btnNext.Enabled = false;

                btnPre.Enabled = true;

                btnFirst.Enabled = true;

                if (_flagChange == 1)

                {

                    if (MessageBox.Show('Are you sure to move without saving?', 'Confirmation', MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

                    {

 

                        dr = dt.Rows[dt.Rows.Count - 1];

                        nav(dr);

                        _recNo = _totalRec - 1;

                        _flagChange = 0;

                    }

                }

                else

                {

                    dr = dt.Rows[dt.Rows.Count - 1];

                    nav(dr);

                    _recNo = _totalRec - 1;

                    _flagChange = 0;

                }

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

//next button

private void btnNext_Click(object sender, EventArgs e)

        {

            try

            {                              

 

                if (_flagChange == 1)

                {

                    if (MessageBox.Show('Are you sure to move without saving?', 'Confirmation', MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

                    {

                        if (_flagGrid == 1)

                        {

 

                            _recNo++;

                            if (_recNo < _totalRec)

                            {

                                dr = ds.Tables[0].Rows[_recNo];

                                nav(dr);

                            }

                            if (_recNo == _totalRec - 1)

                            {

                                btnNext.Enabled = false;

                                btnLast.Enabled = false;

                            }

                            if (_recNo > 0)

                            {

                                btnFirst.Enabled = true;

                                btnPre.Enabled = true;

                            }

                            _flagGrid = 0;

                        }

                        else

                        {

 

                            if (_recNo < _totalRec - 1)

                            {

                                _recNo++;

                                ds = gc.fillGrid();

                                dr = dt.Rows[_recNo];

                                nav(dr);

 

                            }

                            if (_recNo == _totalRec - 1)

                            {

                                btnNext.Enabled = false;

                                btnLast.Enabled = false;

                            }

                            if (_recNo > 0)

                            {

                                btnFirst.Enabled = true;

                                btnPre.Enabled = true;

                            }

                        }

                        _flagChange = 0;

                    }

                                    

                }

                else

                {

                    if (_flagGrid == 1)

                    {

                        _recNo++;

                        if (_recNo < _totalRec)

                        {

                            dr = ds.Tables[0].Rows[_recNo];

                            nav(dr);

 

                        }

                     if (_recNo == _totalRec - 1)

                        {

                            btnNext.Enabled = false;

                            btnLast.Enabled = false;

                        }

                     if (_recNo > 0)

                     {

                         btnFirst.Enabled = true;

                         btnPre.Enabled = true;

                     }

                        _flagGrid = 0;

                    }

                    else

                    {

 

                        if (_recNo < _totalRec - 1)

                        {

                            _recNo++;

                            ds = gc.fillGrid();

                            dr = dt.Rows[_recNo];

                            nav(dr);

 

                        }

                       if (_recNo == _totalRec - 1)

                        {

                            btnNext.Enabled = false;

                            btnLast.Enabled = false;

                        }

                       if (_recNo > 0)

                       {

                           btnFirst.Enabled = true;

                           btnPre.Enabled = true;

                       }

                    }

                    _flagChange = 0;

                }

               

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

//previous button

private void btnPre_Click(object sender, EventArgs e)

        {

            try

            {

               

                if (_flagChange == 1)

                {

                    if (MessageBox.Show('Are you sure to move without saving?', 'Confirmation', MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

                    {

 

                        if (_flagGrid == 1)

                        {

                            if (_recNo > 0)

                            {

                                _recNo--;

                                dr = dt.Rows[_recNo];

                                nav(dr);

                                btnLast.Enabled = true;

                                btnNext.Enabled = true;

                              

                            }

 

                            _flagGrid = 0;

                        }

                        else

                        {

                            if (_recNo == _totalRec)

                            {

                               

                                dr = dt.Rows[_recNo - 2];

                                nav(dr);

                                _recNo = _recNo - 2;

                            }

                            else if (_recNo >= 0)

                            {

                                if (_recNo != 0)

                                {

                                    _recNo--;                                 

                                    dr = dt.Rows[_recNo];

                                    nav(dr);

                                }

                                btnLast.Enabled = true;

                                btnNext.Enabled = true;

                                if (_recNo == 0)

                                {

                                    btnFirst.Enabled = false;

                                    btnPre.Enabled = false;

                                }

                            }

                          }

                        _flagChange = 0;

                    }

                }

                else

                {

 

                    if (_flagGrid == 1)

                    {

                        if (_recNo > 0)

                        {

                            _recNo--;

                            dr = dt.Rows[_recNo];

                            nav(dr);

                            btnLast.Enabled = true;

                            btnNext.Enabled = true;

                        }

 

                        _flagGrid = 0;

                    }

                    else

                    {

                        if (_recNo == _totalRec)

                        {

                          

                            dr = dt.Rows[_recNo - 2];

                            nav(dr);

                            _recNo = _recNo - 2;

                        }

                        else if (_recNo >= 0)

                        {

                            if (_recNo != 0)

                            {

                                _recNo--;

                              

                                dr = dt.Rows[_recNo];

                                nav(dr);

                            }

                            btnLast.Enabled = true;

                            btnNext.Enabled = true;

                            if (_recNo == 0)

                            {

                                btnFirst.Enabled = false;

                                btnPre.Enabled = false;

                            }

 

                        }

                    }

                    _flagChange = 0;

                }

               

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

 

Full code of User Registration form:

frmUserReg.cs

 

using System;

using System.Data;

using System.Windows.Forms;

using System.Text.RegularExpressions;

 

namespace NewUserRegistration

{

    public partial class frmUserReg : Form

    {

        public frmUserReg()

        {

            InitializeComponent();

        }

        //regular expressions

        Regex regEmail = new Regex(@'^([\w\.\-]+)@([\w\-]+)((\.(\w){2,3})+)$');

        Regex regName = new Regex(@'^^[a-zA-Z][a-zA-Z ]*[a-zA-Z]$');

        Regex regPhone = new Regex(@'^([\(]{1}[1-9]{3}[\)]{1}[ ]{1}[0-9]{3}[\-]{1}[0-9]{4})$');

        Regex regMobile = new Regex(@'^\d{10}$');

        Regex regAge = new Regex(@'^\d{2}$');

        Regex regUrl = new Regex(@'^((https?|ftp)://|(www|ftp)\.)[a-z0-9-]+(\.[a-z0-9-]+)+([/?].*)?$');

        Regex regCompany = new Regex(@'^[A-Z]([a-zA-Z0-9]|[- @\.#&!])*$');

        GlobalClass gc = new GlobalClass();

        //flag and navigation variables

        public int _flagGrid,_flagChange,_flagUpdate=0,_recNo=-1,_totalRec;

        public string email;

        //web service reference object

        fillGridWebSerRef.Service1 service = new fillGridWebSerRef.Service1();

        DataSet ds = new DataSet();

        DataRow dr;

        DataTable dt = new DataTable();

        private void btnSave_Click(object sender, EventArgs e)

        {

            try

            {

                if (validate())//call validate function for validation

                {

                    string weburl = txtWebsite.Text.Trim();

                    if (weburl.StartsWith('www.'))

                    {

                        weburl = 'http://' + weburl;

                        //call saveRecord function for saving record

                        if (gc.saveRecord('insertUser', txtFirstName.Text.Trim(), txtLastName.Text.Trim(), Int32.Parse(txtAge.Text.Trim()), txtAddress.Text.Trim(), txtEmail.Text.Trim(), txtPhone.Text.Trim(), txtMobile.Text.Trim(), weburl, txtCompany.Text.Trim()))

                        {

                            MessageBox.Show('Record Saved Sucessfully!', 'Information', MessageBoxButtons.OK, MessageBoxIcon.Information);

                            clearText();//clear textboxes

                            disableButtons();//disable save, delete and update button

                            dt.Clear();                           

                            ds = gc.fillGrid();//fill data source from records

                            dt = ds.Tables[0];

                            _totalRec = dt.Rows.Count;

                            _recNo = -1;//set record pointer variable to -1

                            _flagChange = 0;//reset flag variable

                        }

                        else

                            MessageBox.Show('Record Not Saved!', 'Information', MessageBoxButtons.OK, MessageBoxIcon.Information);

                    }

                    else

                    {

                        //call saveRecord function for saving record

                        if (gc.saveRecord('insertUser', txtFirstName.Text.Trim(), txtLastName.Text.Trim(), Int32.Parse(txtAge.Text.Trim()), txtAddress.Text.Trim(), txtEmail.Text.Trim(), txtPhone.Text.Trim(), txtMobile.Text.Trim(), weburl, txtCompany.Text.Trim()))

                        {

                            MessageBox.Show('Record Saved Sucessfully!', 'Information', MessageBoxButtons.OK, MessageBoxIcon.Information);

                            clearText();

                            ds = gc.fillGrid();

                            dt = ds.Tables[0];

                            _recNo = -1;

                            _flagChange = 0;

                        }

                        else

                            MessageBox.Show('Record Not Saved!', 'Information', MessageBoxButtons.OK, MessageBoxIcon.Information);

                    }

                }

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

        bool validate()

        {

            try

            {

              

                //firstname validation

                if (!regName.IsMatch(txtFirstName.Text.Trim()) || txtFirstName.Text.Trim()=='')

                {

                    lblErrFirstName.Visible = true;

                    txtFirstName.Focus();

                    return false;

                }

                //lastname validation

                 else if (!regName.IsMatch(txtLastName.Text.Trim()) || txtLastName.Text.Trim()=='')

                {

                    lblErrLastName.Visible = true;

                    txtLastName.Focus();

                    return false;

                }

                //age validation

                else if (txtAge.Text.Length < 2 || Int32.Parse(txtAge.Text.Trim()) < 18 || txtAge.Text.Trim() == '')

                {

                    lblErrAge.Visible = true;

                    txtAge.Focus();

                    return false;

                }

                 else if (txtAddress.Text.Trim() == ''||txtAddress.Text.Trim()=='')

                 {

                     lblErrAddress.Visible = true;

                     txtAddress.Focus();

                     return false;

                 }

                 //email validation

                 else if (!regEmail.IsMatch(txtEmail.Text.Trim())||txtEmail.Text.Trim()=='')

                 {

                     lblErrEmail.Visible = true;

                     txtEmail.Focus();

                     return false;

                 }

                 //phone validation

                else if (!regPhone.IsMatch(txtPhone.Text.Trim()) || txtPhone.Text.Trim().Length < 13||txtPhone.Text.Trim()=='')

                 {

                     lblErrPhone.Visible = true;

                     txtPhone.Focus();

                     return false;                                       

                 }

                 //mobile no validation

                else if (!regMobile.IsMatch(txtMobile.Text.Trim())||txtMobile.Text.Length < 10 || txtMobile.Text.Trim() == '0000000000' || txtMobile.Text.Trim() == '' )

                 {

                     lblErrMobile.Visible = true;

                     txtMobile.Focus();

                     return false;

                 }

                 //url validation

                else if (!regUrl.IsMatch(txtWebsite.Text.Trim()) || txtWebsite.Text.Trim() == '')

                 {

                     lblErrWebsite.Visible = true;

                     txtWebsite.Focus();

                     return false;

                 }

                    //company name validation

                else if (txtCompany.Text.Trim() == '')

                {

                    lblErrCompany.Visible = true;

                    txtCompany.Focus();

                    return false;

                }

                    //check email id registration

                else if(_flagUpdate==0)

                {

                if (gc.search('searchUser', txtEmail.Text.Trim()))

                {

                    lblEmail.Visible = true;

                    txtEmail.Focus();                 

                    MessageBox.Show('This Email is already registred!', 'Error', MessageBoxButtons.OK, MessageBoxIcon.Error);

                    return false;

                }

                }

               

                return true;

            }

            catch (Exception ee)

            {

               

                MessageBox.Show(ee.Message);

                return false;

            }

        }

 

       private void btnBrowse_Click(object sender, EventArgs e)

        {

            try

            {

                frmRecords frmrec = new frmRecords(this);

                frmrec.ShowDialog();

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

           }

 

        private void frmUserReg_Load(object sender, EventArgs e)

        {

            try

            {

                btnNext.Enabled = false;

                btnPre.Enabled = false;

               

                _flagChange = 0;

                ds = gc.fillGrid();

                dt = ds.Tables[0];

                _totalRec = dt.Rows.Count;

                disableButtons();

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

 

        private void frmUserReg_FormClosed(object sender, FormClosedEventArgs e)

        {

            Application.Exit();

        }

       

       

        private void btnFirst_Click(object sender, EventArgs e)

        {

           

            try

            {

                btnFirst.Enabled = false;

                btnPre.Enabled = false;

                btnNext.Enabled = true;

                btnLast.Enabled = true;

                if (_flagChange == 1)

                {

                    if (MessageBox.Show('Are you sure to move without saving?', 'Confirmation', MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

                    {

 

                        if (_recNo == -1)

                        {

                            _recNo++;

                            dr = dt.Rows[_recNo];

                            nav(dr);

                        }

                        else if (_recNo >= 0)

                        {

                            _recNo = 0;

                            dr = dt.Rows[_recNo];

                            nav(dr);

 

                        }

                        _flagChange = 0;

                    }

                }

                else

                {

 

                    if (_recNo == -1)

                    {

                        _recNo++;

                        dr = dt.Rows[_recNo];

                        nav(dr);

                    }

                    else if (_recNo >= 0)

                    {

                        _recNo = 0;

                        dr = dt.Rows[_recNo];

                        nav(dr);

 

                    }

                    _flagChange = 0;

                }

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

 

        private void btnLast_Click(object sender, EventArgs e)

        {

            try

            {

                btnLast.Enabled = false;

                btnNext.Enabled = false;

                btnPre.Enabled = true;

                btnFirst.Enabled = true;

                if (_flagChange == 1)

                {

                    if (MessageBox.Show('Are you sure to move without saving?', 'Confirmation', MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

                    {

 

                        dr = dt.Rows[dt.Rows.Count - 1];

                        nav(dr);

                        _recNo = _totalRec - 1;

                        _flagChange = 0;

                    }

                }

                else

                {

                    dr = dt.Rows[dt.Rows.Count - 1];

                    nav(dr);

                    _recNo = _totalRec - 1;

                    _flagChange = 0;

                }

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

        public void nav(DataRow objds)

        {

            try

            {

                //setting textboxs value

                txtFirstName.Text = dr['firstName'].ToString();

                txtLastName.Text = dr['lastName'].ToString();

                txtAge.Text = dr['age'].ToString();

                txtAddress.Text = dr['address'].ToString();

                txtEmail.Text = dr['email'].ToString();

                txtPhone.Text = dr['phone'].ToString();

                txtMobile.Text = dr['mobile'].ToString();

                txtWebsite.Text = dr['website'].ToString();

                txtCompany.Text = dr['company'].ToString();

              

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

 

        private void btnNext_Click(object sender, EventArgs e)

        {

            try

            {                              

 

                if (_flagChange == 1)

                {

                    //confirmation message

                    if (MessageBox.Show('Are you sure to move without saving?', 'Confirmation', MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

                    {

                        if (_flagGrid == 1)

                        {

 

                            _recNo++;

                            if (_recNo < _totalRec)

                            {

                                dr = ds.Tables[0].Rows[_recNo];

                                nav(dr);

                            }

                            if (_recNo == _totalRec - 1)

                            {

                                btnNext.Enabled = false;

                                btnLast.Enabled = false;

                            }

                            if (_recNo > 0)

                            {

                                btnFirst.Enabled = true;

                                btnPre.Enabled = true;

                            }

                            _flagGrid = 0;

                        }

                        else

                        {

 

                            if (_recNo < _totalRec - 1)

                            {

                                _recNo++;

                                ds = gc.fillGrid();

                                dr = dt.Rows[_recNo];

                                nav(dr);

 

                            }

                            if (_recNo == _totalRec - 1)

                            {

                                btnNext.Enabled = false;

                                btnLast.Enabled = false;

                            }

                            if (_recNo > 0)

                            {

                                btnFirst.Enabled = true;

                                btnPre.Enabled = true;

                            }

                        }

                        _flagChange = 0;

                    }

                                    

                }

                else

                {

                    if (_flagGrid == 1)

                    {

                        _recNo++;

                        if (_recNo < _totalRec)

                        {

                            dr = ds.Tables[0].Rows[_recNo];

                            nav(dr);

 

                        }

                     if (_recNo == _totalRec - 1)

                        {

                            btnNext.Enabled = false;

                            btnLast.Enabled = false;

                        }

                     if (_recNo > 0)

                     {

                         btnFirst.Enabled = true;

                         btnPre.Enabled = true;

                     }

                        _flagGrid = 0;

                    }

                    else

                    {

 

                        if (_recNo < _totalRec - 1)

                        {

                            _recNo++;

                            ds = gc.fillGrid();

                            dr = dt.Rows[_recNo];

                            nav(dr);

 

                        }

                       if (_recNo == _totalRec - 1)

                        {

                            btnNext.Enabled = false;

                            btnLast.Enabled = false;

                        }

                       if (_recNo > 0)

                       {

                           btnFirst.Enabled = true;

                           btnPre.Enabled = true;

                       }

                    }

                    _flagChange = 0;

                }

               

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

 

        private void btnDelete_Click(object sender, EventArgs e)

        {

            try

            {

                if (validate())

                {

                    if (txtEmail.Text == '')

                        MessageBox.Show('No Records For Deletion!', 'Error', MessageBoxButtons.OK, MessageBoxIcon.Error);

                    else if (!gc.search('searchUser', txtEmail.Text.Trim()))

                        MessageBox.Show('No Records For Deletion!', 'Error', MessageBoxButtons.OK, MessageBoxIcon.Error);

                    else if (MessageBox.Show('Are you sure?', 'Confirm Delete', MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

                    {

                        if (gc.deleteRecord('deleteUser', txtEmail.Text))

                        {

 

                            MessageBox.Show('Record Deleted!', 'Information', MessageBoxButtons.OK, MessageBoxIcon.Information);

                            dt.Clear();

                            dt.Dispose();

                            clearText();

                            disableButtons();

                            ds = gc.fillGrid();

                            dt = ds.Tables[0];

                            _totalRec = dt.Rows.Count;

                            _recNo = -1;

                            _flagChange = 0;

                        }

                    }

                }

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

 

        private void btnUpdate_Click(object sender, EventArgs e)

        {

            try

            {

              

                if (_flagChange == 1)

                {

                    if (!validate())

                    {

                       

                    }

                    else

                    {

                        if (_recNo == -1)

                        {

                            MessageBox.Show('No record for update!', 'Error', MessageBoxButtons.OK, MessageBoxIcon.Error);

                        }

                        else

                        {

                            dr = dt.Rows[_recNo];

                            if (gc.updateRecord('updateUser', txtFirstName.Text.Trim(), txtLastName.Text.Trim(), Int32.Parse(txtAge.Text.Trim()), txtAddress.Text.Trim(), txtEmail.Text.Trim(), txtPhone.Text.Trim(), txtMobile.Text.Trim(), txtWebsite.Text.Trim(), txtCompany.Text.Trim(), Int32.Parse(dr['userId'].ToString())))

                            {

                                ds = gc.fillGrid();

                                dt = ds.Tables[0];

                                _flagChange = 0;

                                _recNo = -1;

                                _flagUpdate = 0;

                                MessageBox.Show('Record Updated!');

                                clearText();

                                disableButtons();

                               

                            }

                            else

                                MessageBox.Show('Record not updated!', 'Error', MessageBoxButtons.OK, MessageBoxIcon.Error);

                        }

                    }

                }

                else

                {

                    MessageBox.Show('Nothing for update!');

                }

              

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

 

        private void btnPre_Click(object sender, EventArgs e)

        {

            try

            {

               

                if (_flagChange == 1)

                {

                    if (MessageBox.Show('Are you sure to move without saving?', 'Confirmation', MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

                    {

 

                        if (_flagGrid == 1)

                        {

                            if (_recNo > 0)

                            {

                                _recNo--;

                                dr = dt.Rows[_recNo];

                                nav(dr);

                                btnLast.Enabled = true;

                                btnNext.Enabled = true;

                              

                            }

 

                            _flagGrid = 0;

                        }

                        else

                        {

                            if (_recNo == _totalRec)

                            {

                               

                                dr = dt.Rows[_recNo - 2];

                                nav(dr);

                                _recNo = _recNo - 2;

                            }

                            else if (_recNo >= 0)

                            {

                                if (_recNo != 0)

                                {

                                    _recNo--;                                 

                                    dr = dt.Rows[_recNo];

                                    nav(dr);

                                }

                                btnLast.Enabled = true;

                                btnNext.Enabled = true;

                                if (_recNo == 0)

                                {

                                    btnFirst.Enabled = false;

                                    btnPre.Enabled = false;

                                }

                            }

                          }

                        _flagChange = 0;

                    }

                }

                else

                {

 

                    if (_flagGrid == 1)

                    {

                        if (_recNo > 0)

                        {

                            _recNo--;

                            dr = dt.Rows[_recNo];

                            nav(dr);

                            btnLast.Enabled = true;

                            btnNext.Enabled = true;

                        }

 

                        _flagGrid = 0;

                    }

                    else

                    {

                        if (_recNo == _totalRec)

                        {

                          

                            dr = dt.Rows[_recNo - 2];

                            nav(dr);

                            _recNo = _recNo - 2;

                        }

                        else if (_recNo >= 0)

                        {

                            if (_recNo != 0)

                            {

                                _recNo--;

                              

                                dr = dt.Rows[_recNo];

                                nav(dr);

                            }

                            btnLast.Enabled = true;

                            btnNext.Enabled = true;

                            if (_recNo == 0)

                            {

                                btnFirst.Enabled = false;

                                btnPre.Enabled = false;

                            }

 

                        }

                    }

                    _flagChange = 0;

                }

               

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

 

        private void btnReport_Click(object sender, EventArgs e)

        {

 

            try

            {

                email = txtEmail.Text.Trim();

                if (_recNo >= 0)

                {

                    dr = dt.Rows[_recNo];

                    if (txtEmail.Text != '' && email == dr['email'].ToString())

                    {

                        frmReport frmrep = new frmReport(this);

                        frmrep.ShowDialog();

                    }

                    else

                    {

                        frmInput frminp = new frmInput();

                        frminp.ShowDialog();

                    }

                }

                else

                {

                    frmInput frminp = new frmInput();

                    frminp.ShowDialog();

                }

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

        public void clearText()

        {

            try

            {

                foreach (Control c in this.Controls)

                {

                    if (c is TextBox)

 

                        (c as TextBox).Clear();

                    if (c is MaskedTextBox)

                        (c as MaskedTextBox).Clear();

                   

                }

                //set visiblity of error labels

                lblErrFirstName.Visible = false;

                lblErrLastName.Visible = false;

                lblErrAge.Visible = false;

                lblErrAddress.Visible = false;

                lblErrEmail.Visible = false;

                lblErrPhone.Visible = false;

                lblErrMobile.Visible = false;

                lblErrWebsite.Visible = false;

                lblErrCompany.Visible = false;

 

                _flagChange = 0;

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

 

        private void txtFirstName_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();

            if (txtFirstName.Text.Trim() == '')

                lblErrFirstName.Visible = true;

            if (!regName.IsMatch(txtFirstName.Text.Trim()))

            {

                lblErrFirstName.Visible = true;

 

            }

            else

            {

                lblErrFirstName.Visible = false;

            }

        }

 

        private void txtLastName_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();

            if (txtLastName.Text.Trim() == '')

                lblErrLastName.Visible = true;

            else if (!regName.IsMatch(txtLastName.Text.Trim()))

            {

                lblErrLastName.Visible = true;

 

            }

            else

            {

                lblErrLastName.Visible = false;

            }

        }

 

 

        private void txtAddress_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();

            if (txtAddress.Text == '')

                lblErrAddress.Visible = true;

            else

                lblErrAddress.Visible = false;

 

        }

 

        private void txtEmail_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();

            if (txtEmail.Text.Trim() == '')

                lblErrEmail.Visible = true;

            else if (!regEmail.IsMatch(txtEmail.Text.Trim()))

            {

                lblErrEmail.Visible = true;

 

            }

            else

            {

                lblErrEmail.Visible = false;

            }

        }

 

        private void txtWebsite_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();

            if (txtWebsite.Text.Trim() == '')

                lblErrWebsite.Visible = true;

            else if (!regUrl.IsMatch(txtWebsite.Text.Trim()))

            {

                lblErrWebsite.Visible = true;

 

            }

            else

            {

                lblErrWebsite.Visible = false;

            }

        }

 

        private void txtCompany_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();

            if (txtCompany.Text == '')

                lblErrCompany.Visible = true;

            else

                lblErrCompany.Visible = false;

          

        }

 

        private void frmUserReg_FormClosing(object sender, FormClosingEventArgs e)

        {

            if (_flagChange == 1)

            {

                if (MessageBox.Show('Are you sure to exit without saving?', 'Confirm Close', MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)

                {

                    e.Cancel = false;

                }

                else

                    e.Cancel = true;

            }

        }

 

        private void txtAge_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();

            if (txtAge.Text.Trim() == '')

                lblErrAge.Visible = true;

            else if (txtAge.Text.Length < 2 || Int32.Parse(txtAge.Text.Trim()) < 18)

            {

                lblErrAge.Visible = true;

 

            }

            else

            {

                lblErrAge.Visible = false;

            }

          

        }

 

        private void txtPhone_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();

            if (txtPhone.Text.Trim() == '')

                lblErrPhone.Visible = true;

            else if (!regPhone.IsMatch(txtPhone.Text.Trim())||txtPhone.Text.Trim().Length<13)

            {

                lblErrPhone.Visible = true;

 

            }

            else

            {

                lblErrPhone.Visible = false;

            }

        }

 

        private void txtMobile_TextChanged(object sender, EventArgs e)

        {

            _flagChange = 1;

            disableButtons();

            if (txtMobile.Text.Trim() == '')

                lblErrMobile.Visible = true;

            else if (!regMobile.IsMatch(txtMobile.Text.Trim())||txtMobile.Text.Length < 10 || txtMobile.Text.Trim()== '0000000000')

            {

                lblErrMobile.Visible = true;

 

            }

            else

            {

                lblErrMobile.Visible = false;

            }

        }

 

        private void btnClear_Click(object sender, EventArgs e)

        {

            clearText();

            disableButtons();

            _recNo = -1;

            _flagChange = 0;

 

        }

 

        private void txtWebsite_Leave(object sender, EventArgs e)

        {

            string weburl = txtWebsite.Text.Trim();

            if (weburl.StartsWith('www.'))

            {

                weburl = 'http://' + weburl;

                txtWebsite.Text = weburl;

            }

        }

        public void disableButtons()

        {

            if (txtFirstName.Text.Trim() == '' && txtLastName.Text.Trim() == '' && txtAge.Text.Trim() == '' && txtAddress.Text.Trim() == '' && txtEmail.Text.Trim() == '' && txtPhone.Text == '(   )    -' && txtMobile.Text.Trim() == '' && txtWebsite.Text.Trim() == '' && txtCompany.Text.Trim() == '')

            {

                btnDelete.Enabled = false;

                btnSave.Enabled = false;

                btnUpdate.Enabled = false;

            }

            else

            {

                btnDelete.Enabled = true;

                btnSave.Enabled = true;

                btnUpdate.Enabled = true;

            }

        }

 

       

    }

}

 

 

Web Service and Paging:

For creating a web service follow these steps:

*Click on new project

*Select web from Installed Templates

*Select ASP.NET Web Service Application

*Give Web Service Application name

*Click on ok

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]

    public class Service1 : System.Web.Services.WebService

    {

        [WebMethod]

        public DataSet fillGrid()

        {

            SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings['cnn'].ToString());

            SqlCommand cmd;

           if (cnn.State == ConnectionState.Closed)

                cnn.Open();//open Connection

            cmd = new SqlCommand('selectRecords',cnn);//pass strored procedure and connection object

            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(cmd);

           DataSet ds = new DataSet();

             da.Fill(ds);

              return ds;

          }

    }

}

 

 

How to add reference of Web Service in our project

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

{

    public partial class frmRecords : Form

    {

        frmUserReg _frmReg;      

        int CurrentPage = 1;

        int PagesCount = 1;

        int pageRows = 30;

        BindingList<PagingData> Baselist = null;

        BindingList<PagingData> Templist = null;

        public frmRecords(frmUserReg frmReg)

        {

            InitializeComponent();

            _frmReg = frmReg;

        }

        fillGridWebSerRef.Service1 service = new fillGridWebSerRef.Service1();//object of web service

        DataSet ds = new DataSet();      

        DataTable dt = new DataTable();    

        DataTable _DataTableForPagingList;

        private void frmRecords_Load(object sender, EventArgs e)

        {

          

            ds = service.fillGrid();//fill records from web service

            dt = ds.Tables[0];    

            GridViewRecords.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;

            GridViewRecords.Columns['firstName'].HeaderText = 'First Name';         

            GridViewRecords.Columns['lastName'].HeaderText = 'Last Name';    

            GridViewRecords.Columns['age'].HeaderText = 'Age';           

            GridViewRecords.Columns['address'].HeaderText = 'Address';

            GridViewRecords.Columns['email'].HeaderText = 'Email';

            GridViewRecords.Columns['phone'].HeaderText = 'Phone No.';

            GridViewRecords.Columns['mobile'].HeaderText = 'Mobile No';

            GridViewRecords.Columns['website'].HeaderText = 'Website';

            GridViewRecords.Columns['company'].HeaderText = 'Company';

            _DataTableForPagingList = dt;

            Baselist = FillDataforGrid();

            GridViewRecords.DataSource = Baselist;

            PagesCount = Convert.ToInt32(Math.Ceiling(Baselist.Count * 1.0 / pageRows));

            RefreshPagination();

            RebindGridForPageChange();

        }

        private BindingList<PagingData> FillDataforGrid()

        {

            BindingList<PagingData> list = new BindingList<PagingData>();

            for (int i = 0; i < _DataTableForPagingList.Rows.Count; i++)

            {

                PagingData obj = new PagingData(_DataTableForPagingList.Rows[i]['firstName'].ToString(), _DataTableForPagingList.Rows[i]['lastName'].ToString(), Convert.ToInt32(_DataTableForPagingList.Rows[i]['age'].ToString()), _DataTableForPagingList.Rows[i]['address'].ToString(), _DataTableForPagingList.Rows[i]['email'].ToString(), _DataTableForPagingList.Rows[i]['phone'].ToString(), _DataTableForPagingList.Rows[i]['mobile'].ToString(), _DataTableForPagingList.Rows[i]['website'].ToString(), _DataTableForPagingList.Rows[i]['company'].ToString());

                list.Add(obj);

            }

            return list;

        }

        int currentRow;

        private void GridViewRecords_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)

        {

            try

            {

                currentRow = Int32.Parse(e.RowIndex.ToString());

                if (currentRow != -1)

                {

                    //setting textboxes value of user registration form

                    _frmReg.txtFirstName.Text = GridViewRecords['firstName', currentRow].Value.ToString();

                    _frmReg.txtLastName.Text = GridViewRecords['lastName', currentRow].Value.ToString();

                    _frmReg.txtAge.Text = GridViewRecords['age', currentRow].Value.ToString();

                    _frmReg.txtAddress.Text = GridViewRecords['address', currentRow].Value.ToString();

                    _frmReg.txtEmail.Text = GridViewRecords['email', currentRow].Value.ToString();

                    _frmReg.txtPhone.Text = GridViewRecords['phone', currentRow].Value.ToString();

                    _frmReg.txtMobile.Text = GridViewRecords['mobile', currentRow].Value.ToString();

                    _frmReg.txtWebsite.Text = GridViewRecords['website', currentRow].Value.ToString();

                    _frmReg.txtCompany.Text = GridViewRecords['company', currentRow].Value.ToString();

                    _frmReg._flagGrid = 1;

                    string filter = 'email='' + GridViewRecords['email', currentRow].Value.ToString() + ''';

                    DataRow[] dr1 = dt.Select(filter);//filter the row on the basis of email

                    _frmReg._recNo = dt.Rows.IndexOf(dr1[0]);//get the selected row number

                    if (_frmReg._recNo >= dt.Rows.Count - 1)

                    {

                        _frmReg.btnNext.Enabled = false;

                        _frmReg.btnLast.Enabled = false;

                        _frmReg.btnFirst.Enabled=true;

                        _frmReg.btnPre.Enabled=true;

                    }

                    else if (_frmReg._recNo <= 0)

                    {

                        _frmReg.btnNext.Enabled = true;

                        _frmReg.btnLast.Enabled = true;

                        _frmReg.btnFirst.Enabled = false;

                        _frmReg.btnPre.Enabled = false;

                    }

                    else

                    {

                        _frmReg.btnNext.Enabled = true;

                        _frmReg.btnLast.Enabled = true;

                        _frmReg.btnFirst.Enabled = true;

                        _frmReg.btnPre.Enabled = true;

                    }

                    _frmReg._flagChange = 0;

                    this.Close();

                }

            }

            catch (Exception ee)

            {

                MessageBox.Show(ee.Message);

            }

        }

 

        private void toolStripButton1_Click(object sender, EventArgs e)

        {

            try

            {

                ToolStripButton ToolStripButton = ((ToolStripButton)sender);

 

                //Determining the current page

                if (ToolStripButton == btnBackward)

                    CurrentPage--;

                else if (ToolStripButton == btnForward)

                    CurrentPage++;

                else if (ToolStripButton == btnLast)

                    CurrentPage = PagesCount;

                else if (ToolStripButton == btnFirst)

                    CurrentPage = 1;

                else

                    CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);

                if (CurrentPage < 1)

                    CurrentPage = 1;

                else if (CurrentPage > PagesCount)

                    CurrentPage = PagesCount;

                //Rebind the Datagridview with the data.

                RebindGridForPageChange();

                //Change the pagiantions buttons according to page number

                RefreshPagination();

            }

            catch (Exception) { }

        }

        private void RebindGridForPageChange()

        {

            //Rebinding the Datagridview with data

            int datasourcestartIndex = (CurrentPage - 1) * pageRows;

            Templist = new BindingList<PagingData>();

            for (int i = datasourcestartIndex; i < datasourcestartIndex + pageRows; i++)

            {

                if (i >= Baselist.Count)

                    break;

 

                Templist.Add(Baselist[i]);

            }

 

            GridViewRecords.DataSource = Templist;

      

        }

        private void RefreshPagination()

        {

            ToolStripButton[] items = new ToolStripButton[] { toolStripButton1, toolStripButton2, toolStripButton3, toolStripButton4, toolStripButton5 };

            //pageStartIndex contains the first button number of pagination.

            int pageStartIndex = 1;

 

            if (PagesCount > 5 && CurrentPage > 2)

                pageStartIndex = CurrentPage - 2;

 

            if (PagesCount > 5 && CurrentPage > PagesCount - 2)

                pageStartIndex = PagesCount - 4;

 

            for (int i = pageStartIndex; i < pageStartIndex + 5; i++)

            {

                if (i > PagesCount)

                {

                    items[i - pageStartIndex].Visible = false;

                }

                else

                {

                    //Changing the page numbers

                    items[i - pageStartIndex].Text = i.ToString(CultureInfo.InvariantCulture);

 

                    //Setting the Appearance of the page number buttons

                    if (i == CurrentPage)

                    {

                        items[i - pageStartIndex].BackColor = System.Drawing.ColorTranslator.FromHtml('#83D6F6');

                        items[i - pageStartIndex].ForeColor = Color.White;

                    }

                    else

                    {

                        items[i - pageStartIndex].BackColor = Color.White;

                        items[i - pageStartIndex].ForeColor = System.Drawing.ColorTranslator.FromHtml('#83D6F6');

                    }

                }

            }

            //Enabling or Disalbing pagination first, last, previous , next buttons

            if (CurrentPage == 1)

                btnBackward.Enabled = btnFirst.Enabled = false;

            else

                btnBackward.Enabled = btnFirst.Enabled = true;

 

            if (CurrentPage == PagesCount)

                btnForward.Enabled = btnLast.Enabled = false;

 

            else

                btnForward.Enabled = btnLast.Enabled = true;

        }

        private void btnPrevious_Click(object sender, EventArgs e)

        {

            try

            {

                ToolStripButton ToolStripButton = ((ToolStripButton)sender);

 

                //Determining the current page

                if (ToolStripButton == btnBackward)

                    CurrentPage--;

                else if (ToolStripButton == btnForward)

                    CurrentPage++;

                else if (ToolStripButton == btnLast)

                    CurrentPage = PagesCount;

                else if (ToolStripButton == btnFirst)

                    CurrentPage = 1;

                else

                    CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);

 

                if (CurrentPage < 1)

                    CurrentPage = 1;

                else if (CurrentPage > PagesCount)

                    CurrentPage = PagesCount;

 

                //Rebind the Datagridview with the data.

                RebindGridForPageChange();

 

                //Change the paginations buttons according to page number

                RefreshPagination();

            }

            catch (Exception) { }

        }

 

        private void toolStripButton5_Click(object sender, EventArgs e)

        {

            try

            {

                ToolStripButton ToolStripButton = ((ToolStripButton)sender);

 

                //Determining the current page

                if (ToolStripButton == btnBackward)

                    CurrentPage--;

                else if (ToolStripButton == btnForward)

                    CurrentPage++;

                else if (ToolStripButton == btnLast)

                    CurrentPage = PagesCount;

                else if (ToolStripButton == btnFirst)

                    CurrentPage = 1;

                else

                    CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);

 

                if (CurrentPage < 1)

                    CurrentPage = 1;

                else if (CurrentPage > PagesCount)

                    CurrentPage = PagesCount;

 

                //Rebind the Datagridview with the data.

                RebindGridForPageChange();

 

                //Change the pagiantions buttons according to page number

                RefreshPagination();

 

            }

            catch (Exception) { }

        }

 

        private void toolStripButton6_Click(object sender, EventArgs e)

        {

            try

            {

                ToolStripButton ToolStripButton = ((ToolStripButton)sender);

 

                //Determining the current page

                if (ToolStripButton == btnBackward)

                    CurrentPage--;

                else if (ToolStripButton == btnForward)

                    CurrentPage++;

                else if (ToolStripButton == btnLast)

                    CurrentPage = PagesCount;

                else if (ToolStripButton == btnFirst)

                    CurrentPage = 1;

                else

                    CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);

 

                if (CurrentPage < 1)

                    CurrentPage = 1;

                else if (CurrentPage > PagesCount)

                    CurrentPage = PagesCount;

 

                //Rebind the Datagridview with the data.

                RebindGridForPageChange();

 

                //Change the pagiantions buttons according to page number

                RefreshPagination();

            }

            catch (Exception) { }

        }

 

        private void toolStripButton7_Click(object sender, EventArgs e)

        {

            try

            {

                ToolStripButton ToolStripButton = ((ToolStripButton)sender);

 

                //Determining the current page

                if (ToolStripButton == btnBackward)

                    CurrentPage--;

                else if (ToolStripButton == btnForward)

                    CurrentPage++;

                else if (ToolStripButton == btnLast)

                    CurrentPage = PagesCount;

                else if (ToolStripButton == btnFirst)

                    CurrentPage = 1;

                else

                    CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);

 

                if (CurrentPage < 1)

                    CurrentPage = 1;

                else if (CurrentPage > PagesCount)

                    CurrentPage = PagesCount;

                //Rebind the Datagridview with the data.

                RebindGridForPageChange();

                //Change the pagiantions buttons according to page number

                RefreshPagination();

            }

            catch (Exception) { }

        }

 

        private void toolStripButton8_Click(object sender, EventArgs e)

        {

            try

            {

                ToolStripButton ToolStripButton = ((ToolStripButton)sender);

 

                //Determining the current page

                if (ToolStripButton == btnBackward)

                    CurrentPage--;

                else if (ToolStripButton == btnForward)

                    CurrentPage++;

                else if (ToolStripButton == btnLast)

                    CurrentPage = PagesCount;

                else if (ToolStripButton == btnFirst)

                    CurrentPage = 1;

                else

                    CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);

 

                if (CurrentPage < 1)

                    CurrentPage = 1;

                else if (CurrentPage > PagesCount)

                    CurrentPage = PagesCount;

 

                //Rebind the Datagridview with the data.

                RebindGridForPageChange();

 

                //Change the pagiantions buttons according to page number

                RefreshPagination();

            }

            catch (Exception) { }

        }

 

        private void toolStripButton9_Click(object sender, EventArgs e)

        {

            try

            {

                ToolStripButton ToolStripButton = ((ToolStripButton)sender);

 

                //Determining the current page

                if (ToolStripButton == btnBackward)

                    CurrentPage--;

                else if (ToolStripButton == btnForward)

                    CurrentPage++;

                else if (ToolStripButton == btnLast)

                    CurrentPage = PagesCount;

                else if (ToolStripButton == btnFirst)

                    CurrentPage = 1;

                else

                    CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);

 

                if (CurrentPage < 1)

                    CurrentPage = 1;

                else if (CurrentPage > PagesCount)

                    CurrentPage = PagesCount;

 

                //Rebind the Datagridview with the data.

                RebindGridForPageChange();

 

                //Change the paginations buttons according to page number

                RefreshPagination();

            }

            catch (Exception) { }

        }

 

        private void btnNext_Click(object sender, EventArgs e)

        {

            try

            {

                ToolStripButton ToolStripButton = ((ToolStripButton)sender);

 

                //Determining the current page

                if (ToolStripButton == btnBackward)

                    CurrentPage--;

                else if (ToolStripButton == btnForward)

                    CurrentPage++;

                else if (ToolStripButton == btnLast)

                    CurrentPage = PagesCount;

                else if (ToolStripButton == btnFirst)

                    CurrentPage = 1;

                else

                    CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);

 

                if (CurrentPage < 1)

                    CurrentPage = 1;

                else if (CurrentPage > PagesCount)

                    CurrentPage = PagesCount;

 

                //Rebind the Datagridview with the data.

                RebindGridForPageChange();

 

                //Change the pagiantions buttons according to page number

                RefreshPagination();

            }

            catch (Exception) { }

        }

 

        private void btnLast_Click(object sender, EventArgs e)

        {

            try

            {

                ToolStripButton ToolStripButton = ((ToolStripButton)sender);

                //Determining the current page

                if (ToolStripButton == btnBackward)

                    CurrentPage--;

                else if (ToolStripButton == btnForward)

                    CurrentPage++;

                else if (ToolStripButton == btnLast)

                    CurrentPage = PagesCount;

                else if (ToolStripButton == btnFirst)

                    CurrentPage = 1;

                else

                    CurrentPage = Convert.ToInt32(ToolStripButton.Text, CultureInfo.InvariantCulture);

 

                if (CurrentPage < 1)

                    CurrentPage = 1;

                else if (CurrentPage > PagesCount)

                    CurrentPage = PagesCount;

 

                //Rebind the Datagridview with the data.

                RebindGridForPageChange();

 

                //Change the paginations buttons according to page number

                RefreshPagination();

            }

            catch (Exception) { }

        }

 

        private void btnClose_Click(object sender, EventArgs e)

        {

            this.Close();

        }

 

 

    }

}

 

 

 

Windows Service in C#

In this project I make a windows service for copy record from regUser table to regUser50 where user age is greater than or equal to 50.

Steps for creating Windows Service:

*Click on new project

*Select Windows Service from new project dialog box.

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

{

    public partial class CopyData : ServiceBase

    {

        public CopyData()

        {

            InitializeComponent();

        }

        Timer timerCheck = new Timer();//create timer

        protected override void OnStart(string[] args)

        {

            timerCheck.Elapsed += new ElapsedEventHandler(timerCheck_Elapsed);

            timerCheck.Interval = 10000;//set timer interval

            timerCheck.Enabled = true;

            timerCheck.Start();//start timer

          

        }

 

        protected override void OnStop()

        {

        }

        private void timerCheck_Elapsed(object sender,EventArgs e)

        {

            SqlConnection cnn = new SqlConnection('Data Source=(local);database=userRegistration;user id=sa;password=abc');

            cnn.Open();//open connection

            SqlCommand cmd;

            SqlCommand cmd1;

            SqlCommand cmd2=new SqlCommand('proCopy',cnn);//pass stored procedure and connection object in command

            cmd2.CommandType=CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(cmd2.CommandText, cnn);

            DataTable dt = new DataTable();

            da.Fill(dt);

            foreach (DataRow dr in dt.Rows)

            {

                cmd1 = new SqlCommand('insertUser50', cnn);

                cmd1.CommandType = CommandType.StoredProcedure;

                //add parameters

                cmd1.Parameters.AddWithValue('@firstName', dr['firstName']);

                cmd1.Parameters.AddWithValue('@lastName', dr['lastName']);

                cmd1.Parameters.AddWithValue('@age', dr['age']);

                cmd1.Parameters.AddWithValue('@addr', dr['address']);

                cmd1.Parameters.AddWithValue('@email', dr['email']);

                cmd1.Parameters.AddWithValue('@phone', dr['phone']);

                cmd1.Parameters.AddWithValue('@mobile', dr['mobile']);

                cmd1.Parameters.AddWithValue('@website', dr['website']);

                cmd1.Parameters.AddWithValue('@company', dr['company']);

                if (cmd1.ExecuteNonQuery() > 0)

                {

                    cmd = new SqlCommand('updateUser2', cnn);

                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue('@email', dr['email']);

                    cmd.ExecuteNonQuery();

                }

            }

            cnn.Close();//close connection

            dt.Dispose();//dispose data table

          

        }

    }

}

 

Stored Procedures used in code:-

To getting user list whom age is greater than or equal to 50

USE [userRegistration]

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[proCopy]

     

AS

BEGIN

     

      SET NOCOUNT ON;

 

      SELECT * FROM regUser where age>=50 AND isActive='false';

END

 

 

To copy data for one table to another table

USE [userRegistration]

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[insertUser50]

      (@firstName varchar(50),

      @lastName varchar(50),

      @age int,

      @addr varchar(100),

      @email varchar(70),

      @phone varchar(15),

      @mobile varchar(10),

      @website varchar(70),

      @company varchar(100)

      )

AS

insert into regUser50(firstName,lastName,age,[address],email,phone,mobile,website,company)values(@firstName,@lastName,@age,@addr,@email,@phone,@mobile,@website,@company);

 

 

To update the table first table

USE [userRegistration]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[updateUser2]

(@email varchar(70))   

AS

UPDATE regUser SET isActive='true' WHERE email=@email;

After completing code. Add “ProjectInstaller” to your project.

To add project installer right click on design view and select add installer.

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

{

    public partial class frmReport : Form

    {

        frmUserReg _usrReg;

        frmInput _inp;

        public frmReport(frmUserReg usrReg)

        {

            InitializeComponent();

            authRep();

            _usrReg = usrReg;

 

            crystalReportViewer1.SelectionFormula = '{regUser.email}='' + _usrReg.email + ''';

            crystalReportViewer1.RefreshReport();

        }

        public frmReport(frmInput inp)

        {

            InitializeComponent();

            authRep();

            _inp = inp;

            crystalReportViewer1.SelectionFormula = '{regUser.email}='' + _inp.email + ''';

            crystalReportViewer1.RefreshReport();

        }

        private void frmReport_Load(object sender, EventArgs e)

        {

 

 

        }

        public void authRep()

        {

            ReportDocument cryRpt = new ReportDocument();

            TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();

            TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();

            ConnectionInfo crConnectionInfo = new ConnectionInfo();

            Tables CrTables;

 

            try

            {

                cryRpt.Load(@'D:\DEV\NewUserRegistration\NewUserRegistration\UserReport.rpt');

 

                crConnectionInfo.ServerName = '(local)';

                crConnectionInfo.DatabaseName = 'userRegistration';

                crConnectionInfo.UserID = 'sa';

                crConnectionInfo.Password = 'abc';

 

                CrTables = cryRpt.Database.Tables;

                foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)

                {

                    crtableLogoninfo = CrTable.LogOnInfo;

                    crtableLogoninfo.ConnectionInfo = crConnectionInfo;

                    CrTable.ApplyLogOnInfo(crtableLogoninfo);

                }

 

 

 

                crystalReportViewer1.ReportSource = cryRpt;

                crystalReportViewer1.Refresh();

 

            }

            catch { }

        }

    }

}

 

Last updated:1/30/2020 10:10:12 PM
Anonymous User

Anonymous User

I am a content writter !

0 Comments

Leave Comment