| | SQL Data Providers | |
To write programs for accessing, manipulating, or updating database, we have to first create a database. To create a database, open the server explorer window. If its not visible press Ctrl+Alt+S.
In the �Servers� tree expand the �SQL servers� node. You will find your computer name added there. Right click on it and select �New Database�. Name the new database as �bank� as shown in the following dialog box:
We have checked the �Use Windows NT Integrated Security� radio button. This program will hence work only when we log on with our username and password. Press OK and a new database called bank will appear in the tree. Now we need to create a table in our database called account. To create a new table, expand the bank node and right click on �Tables�, select �New Table� from the menu. A new blank table would appear. Now we need to fill in the entries and specify the attributes of the table. This is how we want our table to be:
We want three attributes in the table viz:- accno (int), name (varchar) and balance(float). Save the table and name it as: - account. Close this window. Expand the table�s tree and you will find that account has been added. Double click on this and start filling in the entries as shown:
Now lets write a program to access these values and print them out Here the code for the program:
using System;
using System.Data;
using System.Data.SqlClient;
class myclass
{
static void Main( string[ ] args)
{
string str= "Database = bank ; server = kicit ; Trusted_Connection =true";
string cmd = "SELECT accno, name, balance from account" ;
SqlConnection con = new SqlConnection ( str ) ;
SqlCommand com = new SqlCommand ( cmd, con ) ;
con.Open();
SqlDataReader r = com.ExecuteReader ( ) ;
while ( r.Read ( ) )
Console.WriteLine ( r[0] + " " + r[1] + " " + r[2] ) ;
con.Close ( ) ;
}
}
The output is :
1 Rahul 200000
2 Subash 300000
3 Anita 100000
4 Preeti 120000
5 Kapil 8000
Explanation: -
First we need to get connected to the database. To do so, we have to write a connection string. The connection string includes database name, server, database provider, user name, password and whether it�s a trusted connection or not. In our program the database name is bank and server name is kicit. We have initialized Trusted Connection to true. This means even if we don�t specify the username and password its fine. If the value is set to false we have to specify a username and password. This connection string is stored in str.
The command string is stored in cmd. cmd contains the query to be executed. The query we have used here is �SELECT accno, name, balance from account�. This is an Sql query, which selects the specified fields:- accno, name and balance from the �account� table.
To make a connection, we pass the connection string to the SqlConnection constructor. To the SqlCommand constructor we pass the connection as well as the command string. The Open( ) method opens a database connection with the property settings specified by the connection string. The SqlDataReader class reads a stream of rows from an SQL Server database. To create an SqlDataReader class we have to call the ExecuteReader( ) method belonging to the SqlCommand class. This method will execute the command and return an SqlDataReader object. The Read( ) method iterates through the records advancing the SqlDataReader by one record every time. Here we have used the ordinal indexer to access individual values of a row referenced by the SqlDataReader. We could have also used the GetString( ) method which gets the value of the specified column as a string i.e. we could have written:
r.GetString(0)
to get the accno field of the row referenced by the SqlDataReader. There is another way of doing it: -r[�name�]. In the end the connection is closed using the Close( ) method.
| | OLEDB Data Providers | |
For OLE DB .NET Data Provider we will have to create our tables in Microsoft Access. These tables should have the same values and fields. After creating such a table in Microsoft Access, save it as bank.mdb in the C directory. The program will look like this:
using System;
using System.Data;
using System.Data.OleDb;
class myclass
{
static void Main( string[ ] args)
{
string str="Provider =Microsoft.Jet.OLEDB.4.0 ; Data Source = c:\\bank.mdb";string cmd = "SELECT accno, name, balance from account" ;
OleDbConnection con = new OleDbConnection ( str ) ;
OleDbCommand com = new OleDbCommand ( cmd, con ) ;
con.Open();
OleDbDataReader r = com.ExecuteReader ( ) ;
while ( r.Read ( ) )
Console.WriteLine ( r[0] + " " + r[1] + " " + r[2] ) ;
con.Close ( ) ;
}
}
The output here remains the same. We have only changed the connection string and the names of classes, just replacing �Sql� everywhere with �OleDb�.
There are three types of OleDb .NET Data Providers viz: - SQLOLEDB, MSDAORA and Microsoft.Jet.OLEDB.4.0. We have used the last one. This is specified in the connection string used.
No comments:
Post a Comment