Thursday, March 27, 2014

ADO.NET LIBRARY

PREVIEW : ADO.NET BASIC


ADO.NET LIBRARY

  • To perform above mentioned database operations, ADO.NET technology offers some predefined classes, organized in the form of namespaces.
  • Library: System.Data 
 ADO.NET Library

ADO.NET NameSpaces:

1)  System.Data

      Contains necessary classes and namespaces to manipulate the databases.

2)  System.Data.SqlClient

      Contains necessary classes, used to interact with the SQL Server database.

3)  System.Data.OleDb

     Contains necessary classes, used to interact with any other databases. Of course, the  OleDb  namespace also  supports  to  connect  with  SQL  server  database,  but we  won‘t  use  it  for  SQL  Server,  because SqlClient  namespace  is  especially available for that.

ADO.NET Classes:

1)  Connection:

Maintains the connection with the database.

2)  Command:

Executes  a  query  statement  (select  statement),  non-query  statement  (insert statement  /  delete  statement  /  update  statement)  or  a  stored  procedure  / function at backend.

3)  DataReader:

It acts as a buffer, which holds the data, after execution of a query statement at backend.

4)  DataAdapter:

Executes a query statement at backend.

5)  Parameter:

Sends a parameter (argument) value to a backend stored procedure / function.

6)  DataSet:

Acts as a buffer, which holds multiple tables at-a-time.

7)  DataTable:

Acts as a buffer, which holds a single table (collection of rows and columns).

8)  DataRow:

Acts as a buffer, which holds a single row.

9)  DataColumn:

Acts as a buffer, which holds a single column.

Note: All of above are the classes; you need to create object(s) for those classes.

Connecting the Database

Library: SqlConnection / OleDbConnectio
     Connection
               ConnectionString
               Open()
               Close()
  • ConnectionString:  This  property  contains  the  connection  string,  used  for  the connection.
  • Open(): This method opens the database connection.
  • Close(): This method disconnects the database connection.

Connecting with SQL Server:

Import the Library (at the top):
using System.Data.SqlClient;

Construct the “Connection” class object:
SqlConnection cn = new SqlConnection();

Assign the Connection string:
cn.ConnectionString  =  " data  source=<name  of  the  server>;user  id=<user name>;password=<password>;initial catalog=<database name> ";
Note: The " initial catalog " specifies the name of the SQL Server database, in which your table exists.

Open the connection:
cn.Open();

Close the connection:
cn.Close();

Connecting with Oracle / MS Access / Fox Pro:

Import the Library (at the top):
using System.Data.OleDb;

Construct the “Connection” class object:
OleDbConnection cn = new OleDbConnection();

Prepare the Connection string:
Oracle  :   cn.ConnectionString  =  provider=oraoledb.oracle.1;  data source=<name  of  the  server>;  user  id=<user  name>;password=<password> ";

MS Access  / dBASE  / FoxPro  : cn.ConnectionString  =  provider=microsoft.jet.oledb.4.0;data source=<path of the database file> "

Open the connection:
cn.Open();

Close the connection:
cn.Close();

Demo Project for ADO.NET With DATABASE

  • Create a new Windows Forms Application. It automatically creates Form1.
  • Set the following properties of Form1:
                1.  WindowState: Maximized
                2.  IsMdiContainer: True
                3.  Text: ADO.NET Demo
  • Drag and drop MenuStrip control into the form.
  • Design the MenuStrip as shown in this screen:
ADO.NET With DATABASE

  • Click on Project menu Add Windows Form. 
  • Enter the form name as DatabaseConnectionDemo.
  • Click on Add.
  • Double click on Database Connection Demo menu item and write the code:

Program for MENUSTRIP                                      

private void databaseConnectionDemoToolStripMenuItem_Click(object sender, EventArgs e)
{
   DatabaseConnectionDemo f = new DatabaseConnectionDemo();
   f.MdiParent = this;
   f.Show();
}

  • Open  the  ―DatabaseConnectionDemo‖  form,  from  Solution  Explorer  and  design  it  as shown:

Program for ADO.NET With DATABASE               

using System.Data.SqlClient;
using System.Data.OleDb;
private void button1_Click(object sender, EventArgs e)
{
   SqlConnection cn = new SqlConnection();
   cn.ConnectionString = "data source=localhost;user id=sa;password=123";
   cn.Open();
   MessageBox.Show("Successfully Connected with SQL Server");
   cn.Close();
}
private void button2_Click(object sender, EventArgs e)
{
   OleDbConnection cn = new OleDbConnection();
   cn.ConnectionString = "provider=oraoledb.oracle.1;user id=scott;password=tiger";
   cn.Open();
   MessageBox.Show("Successfully Connected with Oracle");
   cn.Close();
}
private void button3_Click(object sender, EventArgs e)
{
    OleDbConnection cn = new OleDbConnection();
    cn.ConnectionString = "provider=microsoft.jet.oledb.4.0;data 
    source=c:\\database1.mdb";
    cn.Open();
    MessageBox.Show("Successfully Connected with MS Access");
    cn.Close();
}

No comments:

Post a Comment