Simply Learn Programming

Insert,Update,Delete,Display data from sql server database with parameterized query using csharp

03/06/2017   01:08:52 PM

Hello,in this C# tutorial i will explain how to insert data in to sql server database.also modify(update and delete) ,select this data and display in textboxes.in this tutorial used parameterized queries instead simple sql queries

First we will see what is parameterized queries

Use of parameterized query:-To avoid SQL Injection

What is sql injection ?

SQL injection is a technique where malicious users can inject SQL commands into an SQL statement via website page input and
Injected SQL commands can alter or modify SQL statement and compromise the security of a web application.
 
1 .Create a database name as Employee using SQL SERVER. Create a Table name as EmployeeDetails in this database.
create following fields in the table
 
Column Name    
DataTypes
EmpId
Int [Identity Property Set =True]
EmpName
varchar(50)
EmpDesgn
varchar(50)
EmpSalary
money

 

2. Open VisualStudio.net / Select C# / Windows Application / Rename Form1.cs as EmployeeDetails.

 

3. Make the design of the form as Follows.

 

4.Add the following code in to App.config file in </configuration> tag

<connectionStrings>
<add name="cn" connectionString="Data Source=.\sqlexpress;Initial Catalog=Employee;Integrated Security=True"/>
</connectionStrings>

 

5.Add a refrence System.Configurtation

Right click on refrences folder in project folder / Add Refernces / From Refrence Manager window click on Assemblies option / click on Framework option / select System.Configuration from list /click on ok

Now System.Configuration added in project

 

6.Create a class dbcon.cs in project and add the following code in that class

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace IUDSparameterized
{
    class dbcon
    {
        public static string dbconnectivity()
        {
            return ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
        }
    
    }
}

 

7.Add the following code for add,update,delete,select and display data from database

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
 
namespace IUDSparameterized
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
SqlConnection cn = new SqlConnection(dbcon.dbconnectivity());
SqlCommand cmd;
SqlDataReader dr;
private void btnAdd_Click(object sender, EventArgs e)
{
try
{
if (txtEmpName.Text == "" || txtEmpDegn.Text == "" || txtSalary.Text == "")
{
MessageBox.Show("All Fields Are Compulsory");
}
else
{
cmd = new SqlCommand("Insert INTO EmployeeDetails Values(@P2,@P3,@P4)", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@P2", txtEmpName.Text);
cmd.Parameters.AddWithValue("@P3", txtEmpDegn.Text);
cmd.Parameters.AddWithValue("@P4", txtSalary.Text);
cn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Data Inserted");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
}
 
private void btnSelect_Click(object sender, EventArgs e)
{
try
{
if (txtempid.Text == "")
{
MessageBox.Show("Enter Employee Id To Search");
}
else
{
cmd = new SqlCommand("Select * From EmployeeDetails where EmpId=@id", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@id", txtempid.Text);
cn.Open();
dr = cmd.ExecuteReader();
if (dr.Read())
{
txtEmpName.Text = dr[1].ToString();
txtEmpDegn.Text = dr[2].ToString();
txtSalary.Text = dr[3].ToString();
}
dr.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
}
 
private void btnReset_Click(object sender, EventArgs e)
{
foreach (Control T in Controls)
{
if (T is TextBox)
{
T.Text = "";
}
}
}
 
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
if (txtempid.Text == "")
{
MessageBox.Show("Enter Employee Id To Update");
}
else
{
cmd = new SqlCommand("Update EmployeeDetails SET EmpName=@P2, EmpDesgn=@P3, EmpSalary=@P4 where EmpId=@P1", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@P1", txtempid.Text);
cmd.Parameters.AddWithValue("@P2", txtEmpName.Text);
cmd.Parameters.AddWithValue("@P3", txtEmpDegn.Text);
cmd.Parameters.AddWithValue("@P4", txtSalary.Text);
cn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Data Updated");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
}
 
private void btnDel_Click(object sender, EventArgs e)
{
try
{
if (txtempid.Text == "")
{
MessageBox.Show("Enter Employee Id To Delete");
}
else
{
cmd = new SqlCommand("Delete EmployeeDetails where EmpId=@EmpId", cn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@EmpId", txtempid.Text);
cn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Data Deleted");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (cn.State == ConnectionState.Open)
{
cn.Close();
}
}
}
}
}
 

 

 


Output
                   

Support us by Sharing

 Tweet
Share