HOW TO: Open ADO Connection and RecordSet Objects in Visual C# .NET (308611)

SUMMARY

Create ADO Connection and Recordset Objects in Visual C# .NET

1. Create a new Visual C# .NET Windows application project.
2. On the Project menu, click Add Reference.
3. Click the COM tab. Click Microsoft ActiveX Data Objects 2.X Library.
4. Open the Code window for Form1.
5. So that your declarations will be form-level in scope, add the following code to the top of the Form1 class section, above the public Form1 function:

        private ADODB.Connection cn = new ADODB.Connection();
        private ADODB.Recordset rs = new ADODB.Recordset();
        private string cnStr;
        private string query;
   					

6. Use the following code samples in the Form1 function below InitializeComponent();. The first example creates a Connection object and connects to a SQL Server Pubs database. The second example creates a RecordSet object.
   
   To connect to the database:
   
   Note User ID <user name> must have permissions to perform these operations on the database.

        //Connection string.
        cnStr = "Provider=SQLOLEDB;Initial Catalog=Pubs;Data Source=servername;User ID=<username>;Password=;<strong password>";
        //query
        query = "Select * From Authors";
   
        //Connection via Connection open Property.
        cn.Open(cnStr, null, null, 0);
        cn.Close(); 
   
        //Connection via ConnectionString Property.
        cn.ConnectionString = cnStr;
        cn.Open(null, null, null, 0);
        cn.Close(); 
      
   						

   To retrieve the RecordSet from database:

        //Open Recordset via Connection object.
        cn.Open(cnStr, null, null, 0);
        rs.Open(query, cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);
        rs.Close();
        cn.Close();
    
        //Open Recordset without connection object.
        rs.Open(query, cnStr, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1);		
        rs.Close();
    
   					

7. Modify the ConnectionString object for your SQL Server. Press F11 to step through the code and note the different ways to create a Connection or RecordSet object.