- This article is for basic learners that need to know about database connectivity using C#.net with OLEDB,ODBC and SQLClient.
- AddItems and DeleteItems buttons use stored procedures to insert and delete data.
- This is the screen shot of the simple application.
==================start code example=================
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace ZONE
{
public partial class Form1 : Form
{
///
/// my database server running on my machine.
///
string OLEDBString="Provider=SQLNCLI;Server=.;Database=zone;Trusted_Connection=yes;";
string ODBCString="Driver={SQL Native Client};Server=.;Database=zone;Trusted_Connection=yes;";
string constring="";
public Form1()
{
InitializeComponent();
}
--------------------------------------------------------------------------------
private void Form1_Load(object sender, EventArgs e)
{
rdbODBC.Checked = true;
fillGrid();
}
--------------------------------------------------------------------------------
private void btnadd_Click(object sender, EventArgs e)
{
string s1 = txtId.Text;
string s2 = txtname.Text;
string s3 = txtage.Text;
string s4 = txtsex.Text;
if (rdbODBC.Checked)// Check whether the “ODBC Radio Button” is checked or not
{
OdbcConnection con = new OdbcConnection();
con.ConnectionString = constring;// assign odbc connection string
OdbcCommand cmd = new OdbcCommand();
try
{
con.Open();
cmd.Connection = con;
cmd.CommandText = "INSERT INTO EMPLOYEE(empID,empName,empAge,empSex) VALUES('" + s1 + "','" + s2 + "','" + s3 + "','" + s4 + "')";
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
dgrid1.Refresh();
fillGrid();
dgrid1.Refresh();
MessageBox.Show("Successfully Inserted.");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
else if (rdbOLEDB.Checked)// Check whether the “OLEDB Radio Button” is checked or not
{
OleDbConnection con2 = new OleDbConnection();
con2.ConnectionString = constring; // assign oledb connection string
OleDbCommand cmd2 = new OleDbCommand();
try
{
con2.Open();
cmd2.Connection = con2;
cmd2.CommandText = "INSERT INTO EMPLOYEE(empID,empName,empAge,empSex) VALUES('" + s1 + "','" + s2 + "','" + s3 + "','" + s4 + "')";
cmd2.ExecuteNonQuery();
cmd2.Parameters.Clear();
dgrid1.Refresh();
fillGrid();
dgrid1.Refresh();
MessageBox.Show("Successfully Inserted.");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con2.Close();
}
}
}
----------------------------------------------------------------------------
private void btnDelete_Click(object sender, EventArgs e)
{
string s1 = txtId.Text;
if (rdbODBC.Checked)// Check whether the “ODBC Radio Button” is checked or not
{
OdbcConnection con = new OdbcConnection();
con.ConnectionString = constring;// assign the odbc connection string
OdbcCommand cmd = new OdbcCommand();
try
{
con.Open();
cmd.Connection = con;
cmd.CommandText = "DELETE FROM EMPLOYEE WHERE empID='" + s1 + "'";
cmd.ExecuteNonQuery();
dgrid1.Refresh();
fillGrid();
dgrid1.Refresh();
MessageBox.Show("Successfully Deleted.");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
dgrid1.Refresh();
}
}
else if (rdbOLEDB.Checked)// Check whether the “OLEDB Radio Button” is checked or not
{
OleDbConnection con = new OleDbConnection();
con.ConnectionString = constring;// assign oledb connection string
OleDbCommand cmd = new OleDbCommand();
try
{
con.Open();
cmd.Connection = con;
cmd.CommandText = "DELETE FROM EMPLOYEE WHERE empID='" + s1 + "'";
cmd.ExecuteNonQuery();
dgrid1.Refresh();
fillGrid();
dgrid1.Refresh();
MessageBox.Show("Successfully Deleted.");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
}
------------------------------------------------------------------------------
private void btnClose_Click(object sender, EventArgs e)
{
Application.Exit();
}
-------------------------------------------------------------------------------
public void fillGrid()
{
if (rdbODBC.Checked)// Check whether the “ODBC Radio Button” is checked or not
{
OdbcConnection con = new OdbcConnection();
con.ConnectionString = constring;// assign odbc connection string
OdbcCommand cmd = new OdbcCommand();
DataTable dt = new DataTable();
OdbcDataAdapter adap = new OdbcDataAdapter();
try
{
con.Open();
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM EMPLOYEE";
adap.SelectCommand = cmd;
adap.Fill(dt);
adap.SelectCommand.ExecuteNonQuery();
dgrid1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
else if (rdbOLEDB.Checked)// Check whether the “OLEDB Radio Button” is checked or not
{
OleDbConnection con = new OleDbConnection();
con.ConnectionString = constring;// assign oledb connection string
OleDbCommand cmd = new OleDbCommand();
DataTable dt = new DataTable();
OleDbDataAdapter adap = new OleDbDataAdapter();
try
{
con.Open();
cmd.Connection = con;
cmd.CommandText = "SELECT * FROM EMPLOYEE";
adap.SelectCommand = cmd;
adap.Fill(dt);
adap.SelectCommand.ExecuteNonQuery();
dgrid1.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
txtId.Text = "";
txtage.Text = "";
txtname.Text = "";
txtsex.Text = "";
}
-----------------------------------------------------------------------------
private void rdbODBC_CheckedChanged(object sender, EventArgs e)
{
constring = ODBCString;// assign odbc connection string
}
--------------------------------------------------------------------------------
private void rdbOLEDB_CheckedChanged(object sender, EventArgs e)
{
constring = OLEDBString;// assign oledb connection string
}
--------------------------------------------------------------------------------
private void addProcedure_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
con.ConnectionString = "Server=.;Database=zone;Trusted_Connection=True;";
try
{
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.addEmployeeData";// my add procedure name
cmd.Parameters.Add("@empID",SqlDbType.Int).Value = txtId.Text;
cmd.Parameters.Add("@empName", SqlDbType.NVarChar).Value = txtname.Text;
cmd.Parameters.Add("@empAge", SqlDbType.NVarChar).Value = txtage.Text;
cmd.Parameters.Add("@empSex", SqlDbType.NVarChar).Value = txtsex.Text;
cmd.ExecuteNonQuery();
fillGrid();
MessageBox.Show("Execute procedure with no errors.Added successfully.");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
-------------------------------------------------------------------------------
private void btnDeleteProcedure_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
con.ConnectionString = "Server=.; Database=zone; Trusted_Connection=yes;";
try
{
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.deleteEmployeeData";// my delete procedure name
cmd.Parameters.Add("@empID", SqlDbType.Int).Value = txtId.Text;
cmd.ExecuteNonQuery();
fillGrid();
MessageBox.Show("Execute procedure with no errors.Deleted successfully.");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
con.Close();
}
}
}
}
=====================End code example================
***************************************************************
==================start procedure [addEmployeeData]=======
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: lalindu
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[addEmployeeData]
-- Add the parameters for the stored procedure here
@empID int ,
@empName nvarchar(100) ,
@empAge nvarchar(50) ,
@empSex nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO EMPLOYEE(empID,empName,empAge,empSex)
VALUES(@empID,@empName,@empAge,@empSex);
END
=================End Procedure [addEmployeeData]===========
===================start procedure [deleteEmployeeData]=======
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Lalindu
-- Create date: 2008-08-11
-- Description: Delete from the table
-- =============================================
ALTER PROCEDURE [dbo].[deleteEmployeeData]
-- Add the parameters for the stored procedure here
@empID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DELETE FROM EMPLOYEE WHERE empID=@empID;
END
===================End Procedure [deleteEmployeeData]==========
