ODBC , OLEDB and SQLClient database connection using c#.net with some Stored Procedures

• 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]==========