check if id exists in database c#

In this article I will explain how to check if Id already exists in DB.
Out Put Looks Like This.

I created a New Project in windows form using c#

Now in Form1.cs, I designed my UI

I created a table name Employee in my database.

Now i am creating the Stored Procedure for Inserting the data in database.                                                     

USE [dotnetdb]
GO

/****** Object:  StoredProcedure [dbo].[SP_InsertEmpinfo]    Script Date: 07/23/2013 13:54:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create procedure [dbo].[SP_InsertEmpinfo]
(
@EmpId nvarchar(50),
@EmpName nvarchar(50),
@EmpAddress nvarchar(50),
@EmpDesignation nvarchar(50)
)

As Begin

Insert into Employee (EmpId,EmpName,EmpAddress,EmpDesignation) values (@EmpId,@EmpName,@EmpAddress,@EmpDesignation)
end

GO

Now i am writing the code on buttonclick in Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace CheckId
{
    public partial class Form1 : Form
    {
     public  string empid;
        
        public Form1()
        {
            InitializeComponent();
        }

        private void submit_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection("Data Source = CHINNU;Initial Catalog = dotnetdb;Uid = sa;Password = password123;"))
            {

                if (!empId())
                {
                }

                else
                {
                    SqlCommand cmd = new SqlCommand("SP_InsertEmpinfo", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@EmpId", SqlDbType.NVarChar).Value = txtempid.Text;
                    cmd.Parameters.Add("@EmpName", SqlDbType.NVarChar).Value = txtempname.Text;
                    cmd.Parameters.Add("@EmpAddress", SqlDbType.NVarChar).Value = txtempaddress.Text;
                    cmd.Parameters.Add("@EmpDesignation", SqlDbType.NVarChar).Value = txtdesignation.Text;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }  
        }


        public bool empId()
        {

            using (SqlConnection con = new SqlConnection("Data Source = CHINNU;Initial Catalog = dotnetdb;Uid = sa;Password = password123;"))
            {
                con.Open();

                string query = "select EmpId from employee where EmpId= '" + txtempid.Text + "'";

                SqlCommand cmd = new SqlCommand(query, con);

                SqlDataReader dr;

                dr = cmd.ExecuteReader();

                while (dr.Read())
                {

                    empid = dr["EmpId"].ToString();

                    if (empid != "0")
                    {

                        MessageBox.Show("Id Already Exists");

                        return false;
                    }
                    con.Close();
                }


                return true;
            }
        }

                
    }
}

Now I am adding App.config for Connection.

Now Press F5.

Now I am Entering the employee information as shown below.

Now i inserted the employee information in my db,again i am trying to inserting the same information.

  It throws an error message like this.                                                                                                           

Output:                                                                                                                                                            

Read More»

insert update delete edit in datagridview using c#

In this Article I will explain How to insert, update, edit, delete, clear using datagridview in C# from one Form to another Form.

In my previous articles I explained How to display data in datagridview, How to add auto generate SNo in datagridview and also How to add Edit and Deletelink buttons in datagridview.

We can see my Out Put looks like

In this example I am having Two Forms.

I designed Form1 looks like this, Please check my previouspost if you having any doubts

Now I am adding another form, Form2.cs and I designed my Form2.cs as shown below

After completion of UI design.

My Solution explorer looks as shown below.

I design my table with name Employee looks like this

For this table i written two stored procedures for Insert and update.

USE [dotnetdb]
GO

/****** Object:  StoredProcedure [dbo].[SP_InsertEmpinfo]    Script Date: 07/21/2013 15:09:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create procedure [dbo].[SP_InsertEmpinfo]
(
@EmpId nvarchar(50),
@EmpName nvarchar(50),
@EmpAddress nvarchar(50),
@EmpDesignation nvarchar(50)
)

As Begin

Insert into Employee (EmpId,EmpName,EmpAddress,EmpDesignation) values (@EmpId,@EmpName,@EmpAddress,@EmpDesignation)
end

GO


USE [dotnetdb]
GO

/****** Object:  StoredProcedure [dbo].[SP_UpdateEmpInfo]    Script Date: 07/21/2013 15:09:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[SP_UpdateEmpInfo]


(
@Empid nvarchar(50),
@EmpName nvarchar(50),

@EmpAddress nvarchar(50),
@EmpDesignation nvarchar(50)

)

As Begin


update Employee set EmpName = @EmpName,EmpAddress = @EmpAddress,EmpDesignation = @EmpDesignation where Empid = @Empid

End

GO

After Creating Stored Procedure, I Written the code for Insert,Update,Edit,Delete,Clear. In Form1.cs and Form2.cs.

Form2.cs Code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DatagridviewExample
{
    public partial class Form2 : Form
    {
        public static string Employeeid;

        SqlConnection con = new SqlConnection("Data Source = CHINNU;Initial Catalog = dotnetdb;Uid = sa;Password = password123;");

        public Form2()
            
        {

            if (Form1.empid != null)
            {

                Employeeid = Form1.empid;
            }
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(Form1.empid))
            {
                
                con.Open();
                SqlCommand Command = new SqlCommand("Select * from employee where EmpId='" + Employeeid + "'", con);
                SqlDataReader Reader = Command.ExecuteReader();
                if (Reader.HasRows)
                {
                    if (Reader.Read())
                    {
                        txtempid.Text = Reader.GetValue(0).ToString();
                        txtempid.Enabled = false;
                        
                        txtempname.Text = Reader.GetValue(1).ToString();
                        txtempaddress.Text = Reader.GetValue(2).ToString();

                        txtdesignation.Text = Reader.GetValue(3).ToString();
                  
                    }
                    con.Close();
                }

            }
        }

        private void submit_Click(object sender, EventArgs e)
        {

            string empupdateid = txtempid.Text;
            if (empupdateid != Employeeid)
            {
                SqlCommand cmd = new SqlCommand("SP_InsertEmpinfo", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@EmpId", SqlDbType.NVarChar).Value = txtempid.Text;
                cmd.Parameters.Add("@EmpName", SqlDbType.NVarChar).Value = txtempname.Text;
                cmd.Parameters.Add("@EmpAddress", SqlDbType.NVarChar).Value = txtempaddress.Text;
                cmd.Parameters.Add("@EmpDesignation", SqlDbType.NVarChar).Value = txtdesignation.Text;
                con.Open();
                cmd.ExecuteNonQuery();
                lblmessage.Text = "Data inserted";
                con.Close();
            }

            else
            {
                SqlCommand cmd = new SqlCommand("SP_UpdateEmpInfo", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@EmpId", SqlDbType.NVarChar).Value = txtempid.Text;
                cmd.Parameters.Add("@EmpName", SqlDbType.NVarChar).Value = txtempname.Text;
                cmd.Parameters.Add("@EmpAddress", SqlDbType.NVarChar).Value = txtempaddress.Text;
                cmd.Parameters.Add("@EmpDesignation", SqlDbType.NVarChar).Value = txtdesignation.Text;
                con.Open();
                cmd.ExecuteNonQuery();
                lblmessage.Text = "Data Updated";
                con.Close();


            }

        }

        private void btnReset_Click(object sender, EventArgs e)
        {

            txtempname.Text = "";
            txtempaddress.Text = "";
            txtdesignation.Text = "";
           
                lblmessage.Text = "Data Cleared";
            

        }




    }
}

Form1.cs having datagridview with Edit and Delete LinkButtons.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DatagridviewExample
{


    public partial class Form1 : Form
    {
         SqlConnection con = new SqlConnection("Data Source = CHINNU;Initial Catalog = dotnetdb;Uid = sa;Password = password123;");
        public static string empid;

        public string emp
        {
            get { return empid; }
            set { empid = value; }

        }
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            displayDataGridView();

            DataGridViewLinkColumn Editlink = new DataGridViewLinkColumn();
            Editlink.UseColumnTextForLinkValue = true;
            Editlink.HeaderText = "Edit";
            Editlink.DataPropertyName = "lnkColumn";
            Editlink.LinkBehavior = LinkBehavior.SystemDefault;
            Editlink.Text = "Edit";
            dataGridView1.Columns.Add(Editlink);

            DataGridViewLinkColumn Deletelink = new DataGridViewLinkColumn();
            Deletelink.UseColumnTextForLinkValue = true;
            Deletelink.HeaderText = "delete";
            Deletelink.DataPropertyName = "lnkColumn";
            Deletelink.LinkBehavior = LinkBehavior.SystemDefault;
            Deletelink.Text = "Delete";
            dataGridView1.Columns.Add(Deletelink);

        }


        public void displayDataGridView()
        {


           
            
                SqlCommand cmd;
                cmd = new SqlCommand("select * from Employee", con);
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];

                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.AllowUserToAddRows = false;
                int i = 1;
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    row.Cells["SNO"].Value = i;
                    i++;
                }


            

        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 5)
            {

            
                     empid = Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells["EmpId"].Value);
                Form2 fm2 = new Form2();
                fm2.Show();
                
            }

            if (e.ColumnIndex == 6)
            {
                empid = Convert.ToString(dataGridView1.Rows[e.RowIndex].Cells["EmpId"].Value);
                
                    SqlDataAdapter da = new SqlDataAdapter("delete from employee where EmpId = '"+empid+"'",con);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                  displayDataGridView();
                    dataGridView1.Refresh();
                
            }
        }
                 
        }
    }

App.Config:

After Completing all the steps Press F5.

OutPut:

Inserting the data with Label message.

The Data we inserted in Form2 it was displayed in Form1 as shown below.

Now if you want to Edit the Information,when we click on edit it shows form2.cs

I updated the EmpAdress from Us to America.

Now after Updating Form2 it will display Form1 with updated information.

Now if we click on delete the row will be deleted from the datagridview as well as from database as shown below in Datagridview.

Read More»

how to add edit and delete buttons in datagridview in c#

In This Article I will Explain How to add Edit and Delete Button Links in DataGridView.

In my Pervious Article I Explained How to display Data in Datagridview and How to Set Auto Generate SNO in DataGridView.

In My previous post ,DataGridview will looks like this.

Now  i want to add link buttons to datagridview by using  C#                                                                       

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DatagridviewExample
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            displayDataGridView();

            DataGridViewLinkColumn Editlink = new DataGridViewLinkColumn();
            Editlink.UseColumnTextForLinkValue = true;
            Editlink.HeaderText = "Edit";
            Editlink.DataPropertyName = "lnkColumn";
            Editlink.LinkBehavior = LinkBehavior.SystemDefault;
            Editlink.Text = "Edit";
            dataGridView1.Columns.Add(Editlink);

            DataGridViewLinkColumn Deletelink = new DataGridViewLinkColumn();
            Deletelink.UseColumnTextForLinkValue = true;
            Deletelink.HeaderText = "delete";
            Deletelink.DataPropertyName = "lnkColumn";
            Deletelink.LinkBehavior = LinkBehavior.SystemDefault;
            Deletelink.Text = "Delete";
            dataGridView1.Columns.Add(Deletelink);

        }


        public void displayDataGridView()
        {


            SqlConnection con = new SqlConnection("Data Source = CHINNU;Initial Catalog = dotnetdb;Uid = sa;Password = password123;");
            {
                SqlCommand cmd;
                cmd = new SqlCommand("select * from Employee", con);
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];

                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.AllowUserToAddRows = false;
                int i = 1;
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    row.Cells["SNO"].Value = i;
                    i++;
                }


            }

        }
    }
}

OutPut:

Read More»

How to create rdlc reports in c#

In this article I will explain How to create RDLC reports in C#.

See my Previous article how to generate rdlc reports using code
How to Page number in rdlc reports
How to add no data found message in rdlc report

I Created a table in Sqldatabase as shown below. 

  

                       
 

Now I am going to implement rdlc reports, using vs2010.

Create a new project 

Select the windows and select form give solution name as show below.

Click ok.

Right Click on the solution explorer select new item

Select the Dataset as shown below, Click on add.

After Clicking add It will displays  the Dataset like this

Right click on Dataset add TableAdapter

I will display TableAdapter Configuration Wizard.

Click on NewConnection .

Add connection window will display now.

Select the server name.

Select database name.

If we want test the connection,Click on test connection Click ok->ok.

Click Next

Click next.

Now we have to choose a command type.

I am doing with Sql select staments in this example.

Click next.

Select the Query builder as shown below

Select the table name, In my DB having only one table

Click on Add .

After adding Close the window.

In querybulider window i want to show all the colum’s information,so I selected all.

We can also test how the data will display but clicking the QueryBuilder and click Ok.

Click next.

Click next.

Click Next.and finally click finish.

Now,right click on solution explorer, click on add new item select the report wizard as shown below.

After clicking on add, report wizard window will display

Select the Data source name what we created in our solution.

Select available datasets.

Click on Next.

 In the arrange fields window,we have to drag all the fields as shown.

Click on next->next.

Click Finish.

We are able to see  .rdlc like this.

Now Click on Form1.cs,From Toolbox in Reporting drag the report viewer as shown below.

In the ReportViewer Tasks, select the Report.

After selecting, Press F5.

We can see output:

See my Previous article how to generate rdlc reports using code

Read More»

auto generate serial number in datagridview in c#

In this article I want to explain how to Auto generate the Serial No in datagridview.

In my previous article I explained how to display data indatagridview, for that datagridview I want to show Auto generate Serial No for this datagridview.

For auto generating the Serial No is very full to show the data with numbering.

Click on Data Gridview Tasks as shown below

Click on Add Column, Enter the Name and Column name. Click on ADD.

I entered name as SNO,and header text as SNO,It will shows like below

 After Completing the steps, write the code in Datagridview method as shown. below

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DatagridviewExample
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            displayDataGridView();
        }


        public void displayDataGridView()
        {


            SqlConnection con = new SqlConnection("Data Source = CHINNU;Initial Catalog = dotnetdb;Uid = sa;Password = password123;");
            {
                SqlCommand cmd;
                cmd = new SqlCommand("select * from Employee", con);
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];

                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.AllowUserToAddRows = false;
//serial no code starts
                int i = 1;
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    row.Cells["SNO"].Value = i;
                    i++;
                }


            }

//serial no code Ends

        }
    }
}

Out put:

Read More»

display data in datagridview c#

In this Article I will explain how to display data in DataGridView in C# win forms.

I created a Database name Chinnu; I created a simple table as shown below.

 Create a new project in C#, select Windows in Visual c# as shown below

Click Ok.

Click on Form1 in solution explorer ,drag the data gridview in Form1 as shown,

After dragging datagridview I will looks like this.

We have to add AppConfig for Database connection, Right click on the solution explorer  as shown below.

Select the Application configuration file click on add.

Right click on Datagridview ,click view code

Write the code in Form1.cs and Appconfig as shown below.
Important: Don't forget to call the DataGridView method in Form Load.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DatagridviewExample
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            displayDataGridView();
        }


        public void displayDataGridView()
        {


            SqlConnection con = new SqlConnection("Data Source = CHINNU;Initial Catalog = dotnetdb;Uid = sa;Password = password123;");
            {
                SqlCommand cmd;
                cmd = new SqlCommand("select * from Employee", con);
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];

                dataGridView1.AutoGenerateColumns = false;
                dataGridView1.AllowUserToAddRows = false;


            }

        }
    }
}
// Place this code in App config



    
  

Out Put

Read More»