Saturday, January 17, 2015

Program to retrieve data from database using connection oriented architecture

12:28 AM - By ajay desai 0

Explanation: - A database is a collection of tables where each table is set of records which represent an entity like book. A database is represented by Initial Catalog. The data source is the machine which contains the database. It can be a client or server machine, if it’s a server then, then the name of the server must be mentioned as data source. A user id and password are required to login to the database. A Data provider is a collection of classes and methods used for connecting an application with the database and to perform operations on the database through the application. The details of the provider, database, data source, user id and password are used to identify the data source with each the application has to be connected. These details are called as attributes of the data source.
In the above given program, we are using SqlClient Provider to connect our console application only with SQL Server database. We are using a connection-oriented architecture, according to which an application is connected to the database continuously until it issues a command to close the connection. The .NET framework provides a middleware called as ADO.NET (Activex Data objects) which is a data access programming model used for accessing the data stored in a database from any .NET application.

In the below given program, a namespace called System.Data.SqlClient is used. Which has following classes:-
Connection: It is a class which is used to establish connection between an application and a data source present on a remote machine.   This class consists of two constructors:-
1.   Connection(): - Method used for establishing connection between application and the database.
2.  Connection(string Connectionstring): Connection String is string associated with connection object ‘con’. This connection string is a collection of attributes which are required for connecting with a Data Source.

The methods and properties used in Connection class are:-
Open():- used to establish connection between DataReader and DataSource.
Close():- used to close the established connection.
State: - this property is used to get the status of the connection.  
ConnectionString: - Gets or sets the Connection string (i.e. attributes of the data source) associated with the connection object. For ex:- SqlConnection con=new SqlConnection(); con.ConnectionString=”<con str>”; (to establish connection with database)                                                                                                                                                                                                                                                                                                                                                                                                                   
II   Command: This class is used to perform Insert, Select, Update and Delete operations on the data source by sending a request to a Data Source in the form of a SQL statement.  This class consists of three methods to execute the SQL statements, these methods are:-
1.      ExecuteReader() :-  return type of this method is DataReader object as it contains the selected row. 
2.    ExecuteScalar(): - return type of this method is ‘object’ as it returns a single value of any data type.
3.      ExecuteNonQuery(): - return type of this method is ‘int’ as it tells about the number of rows affected after performing insertion, updation or deletion.

The properties of Command class are:-
CommandText: - this property sets or gets the SQL statement associated with command. For ex: - cmd.CommandText=”<sql statement>”;

III  DataReader: It is a class which holds data in the form of a single record (i.e. a row and column) retrieved from a data source like a database. This class provides only read-only access to data and it follows connection oriented architecture i.e. a DataReader object can hold the record retrieved from a data source until the application is connected with the data source. The DataReader object can read only one record from a data source at a time in a serial order. And  This class has following methods they are:-

1.      GetName(int colindex): this method returns the name of the column for a given index position like name of first column or second column or third column. As this method returns a string value, its return type is String.

2.      Read(): This method moves the record pointer (dr) from current row location to next row location and returns a Boolean value (i.e. either ‘true’ or ‘false’) to indicate presence or absence of record. Its return type is Boolean.

3.      GetValue(int colindex): This method is used for retrieve the value of a column in a table specified by the index of that column in a row. Its return type is ‘String’.

4.   NextResult(): This method moves the record pointer from current table to next table  and returns a Boolean value to indicate presence or absence of table. So its return type is Boolean.: - Gets or sets the Connection string (i.e. attributes of the data source) associated with the connection object. For ex:- SqlConnection con=new SqlConnection(); con.ConnectionString=”<con str>”; (to establish connection with database)

                                    
                                                 


                                                                                                                                                                   
using System;
using System.Data.SqlClient;

namespace Connection_oriented
{
    class Connected
    {
      static void Main(string[] args)
      {
          SqlConnection con = new SqlConnection();
          string q = "select  bookid,bookname,authname from books";
          string cs = "Data Source=IFMRJPAL-PC\\TESTDB; Initial Catalog=LIB_MGMT; 
          user id= coreadmin; password=Datateam@123";// Providing Sql server Authentication
          con.ConnectionString = cs;
          con.Open();
          SqlCommand cmd = new SqlCommand(q, con);
          SqlDataReader dr = cmd.ExecuteReader();
          Console.WriteLine("BOOKID\tBOOKNAME\tAUTHORNAME");
          Console.WriteLine();
        while (dr.Read())
       {
        Console.WriteLine("{0}\t{1}\t{2}", dr[0].ToString(), dr[1].ToString(), dr[2].ToString());
       }
            con.Close();
            Console.ReadLine();
      }
    }
}

Tags:
About the Author

I am Azeheruddin Khan having more than 6 year experience in c#, Asp.net and ms sql.My work comprise of medium and enterprise level projects using asp.net and other Microsoft .net technologies. Please feel free to contact me for any queries via posting comments on my blog,i will try to reply as early as possible. Follow me @fresher2programmer
View all posts by admin →

Get Updates

Subscribe to our e-mail newsletter to receive updates.

Share This Post

0 comments:

adsense

© 2014 Fresher2Programmer. WP Theme-junkie converted by Bloggertheme9
Powered by Blogger.
back to top