Introduction
This article talks you through the use of an ADODB connection in a .NET application using the C# language through SQLEXPRESS. The example details the data access using ADODB, fetching a recordset and inserting a record into the database.
Step 1 : Add a Reference for ADODB
![image1.jpg]()
Step 2 : Use ADODB in program
![image2.jpg]()
/* THIS IS DEMO OF ADODB CONNECTION IN C#.NET USING SQLEXPRESS
* AND SQL SERVER
* IN THIS PROGRAM SHOW HOW TO RETRIEVE RECORD USING ADODB RECORDSET
* AND EXECUTE QUERY
* AUTHER : SATYAVEER SINGH
* EMAIL: [email protected]
* INDIA
*/
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using ADODB; // ADODB
namespace ADODBCONNECTION
{
public partial class Form1 : Form
{
ADODB.Connection conn; // ADODB CONNECTION
bool flag;
string sql;
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
// string str = "Provider=SQLOLEDB.1;uid=sa;password=1;database=Student;DataSource={local}";
string str = "Provider=SQLOLEDB;Data Source=.\\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Student;Initial File
Name=E:\\DotNetWebSite\\ADODBCONNECTION\\ADODBCONNECTION\\Student.mdf";
conn = new ADODB.Connection();
conn.Open(str, "", "", -1); // connection Open
CmdAdd.Enabled = true; // Enable and Desable the buttons
cmdDel.Enabled = true;
cmdmodify.Enabled = true;
cmdCan.Enabled = false;
cmdCan.Enabled = false;
cmdSave.Enabled = false;
groupBox1.Enabled = false;
fillrecord();
}
private void CmdAdd_Click(object sender, EventArgs e)
{
sql = "select max(rollno) as rn from detail";
int num;
string str;
ADODB.Recordset rs = new ADODB.Recordset();
groupBox1.Enabled = true;
groupBox3.Enabled = false;
CmdAdd.Enabled = false;
cmdDel.Enabled = false;
cmdmodify.Enabled = false;
cmdCan.Enabled = true;
cmdCan.Enabled = true;
cmdSave.Enabled = true;
txtage.Text = "";
txtclass.Text = "";
txtname.Text = "";
flag = false;
rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 0);
if (rs.Fields[0].Value == null)
{
num = 1;
}
else
{
num = int.Parse(rs.Fields["rn"].Value.ToString()) + 1;
}
if (num >= 0 && num < 10)
{
str = "00" + num.ToString();
}
else if (num >= 10 && num < 100)
{
str = "0" + num.ToString();
}
else
{
str = num.ToString();
}
txtroll.Text = str;
}
private void cmdCan_Click(object sender, EventArgs e)
{
CmdAdd.Enabled = true;
cmdDel.Enabled = true;
cmdmodify.Enabled = true;
cmdCan.Enabled = false;
cmdCan.Enabled = false;
cmdSave.Enabled = false;
if (flag == false)
{
txtage.Text = "";
txtclass.Text = "";
txtname.Text = "";
txtroll.Text = "";
}
groupBox1.Enabled = false;
groupBox3.Enabled = true;
flag = false;
}
private void cmdmodify_Click(object sender, EventArgs e)
{
flag = true;
groupBox3.Enabled = false;
groupBox1.Enabled = true;
CmdAdd.Enabled = false;
cmdDel.Enabled = false;
cmdmodify.Enabled = false;
cmdCan.Enabled = true;
cmdCan.Enabled = true;
cmdSave.Enabled = true;
}
private void cmdSave_Click(object sender, EventArgs e)
{
string sql;
object ret;
if (flag == false)
{// Sql query for insert data
sql = "insert into detail values('" + txtroll.Text + "','" + txtname.Text + "','" + txtclass.Text + "'," + txtage.Text + ")";
}
else
{// Sql query for ipdate data
sql = "update detail set name='" + txtname.Text + "',class='" + txtclass.Text + "',age=" + txtage.Text + " where rollno='" + txtroll.Text + "'";
}
conn.Execute(sql, out ret, 0); // Execute the query through ADODB CONNECTION
CmdAdd.Enabled = true;
cmdDel.Enabled = true;
cmdmodify.Enabled = true;
cmdCan.Enabled = false;
cmdCan.Enabled = false;
cmdSave.Enabled = false;
groupBox1.Enabled = false;
groupBox3.Enabled = true;
flag = false;
fillrecord();
}
private void cmdDel_Click(object sender, EventArgs e)
{
object ret;
if (txtroll.Text == "")
{
MessageBox.Show("there is no record to delete plz select record", "Alert");
}
else
{// Query To delete Record
sql = "delete from detail where rollno='" + txtroll.Text + "'";
conn.Execute(sql, out ret, 0);
txtage.Text = "";
txtclass.Text = "";
txtname.Text = "";
txtroll.Text = "";
fillrecord();
}
}
private void fillrecord() // Show tha all Record
{
ADODB.Recordset rs = new ADODB.Recordset(); // ADODB RECORDSET
sql = "select rollno,name from detail";
// RECORDSET OPEN
rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 0);
listBox1.Items.Clear();
while (rs.EOF == false)
{
listBox1.Items.Add(rs.Fields[0].Value.ToString() + " || " + rs.Fields[1].Value.ToString());
rs.MoveNext();
}
}
private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
string rollno;
rollno = listBox1.SelectedItem.ToString().Substring(0, 3);
ADODB.Recordset rs = new ADODB.Recordset();
sql = "select * from detail where rollno='" + rollno + "'";
rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 0);
txtroll.Text = rollno;
txtname.Text = rs.Fields[1].Value.ToString();
txtclass.Text = rs.Fields[2].Value.ToString();
txtage.Text = rs.Fields[3].Value.ToString();
}
}
}
![image3.jpg]()
Add record
![image4.jpg]()