In previous article, we have discussed what are User Controls, how many ways that we can develop user controls like Creating a new control from an already existing control and Inherited or Extended controls, How to Develop a Control, classification of people who are working on Controls like Component Developer and Component Consumer, Events role in user controls, Syntax of Defining an Event, what are the Tasks of Developers and Consumers to work with Events, what are Developer Tasks, what are Consumer Tasks, How to Develop a Stop Clock Control, and finally How to Consume the Control along with some examples.
Please find below the link for accessing the article
CSharp.NET Tutorial - Day 20
Now, in this article we will discuss about ADO.NET, the namespaces like System.Data.OleDb, System.Data.SqlClient, System.Data.OracleClient, and System.Data.Odbc, what are the Classes available in namespaces like DataSet, DataTable, DataColumn, DataRow, DataView, DataRelation etc, what are the common classes available in namespaces like Connection, Command, DataReader, DataAdapter, CommandBuilder, Parameter, how to Establishing a Connection, how to Send request as a Statement, how to Capture the results given by Data Source, what is the role of constructors of Connection, what is Connection String and its syntax, what are the attributes of connection string like Providers, Data Source, User Id and Password, Database (or) Initial Catalog, Trusted_Connection = True, and DSN, what is the Connection String for Oracle, what is the Connection String for SQL Server, what are the Methods and Properties of Connection Class like Open (), Close (), State and ConnectionString, what is Command class, what are the Properties of Command Class like Connection and CommandText, what are the Methods of Command Class like ExecuteReader (), ExecuteScalar () and ExecuteNonQuery (), how to access Data from DataReader along with some examples.
ADO.NET
ADO.Net is a collection of Managed Providers which can be used for communication with the Data Sources.
-> ADO.NET is an extension to the Older ADO (Collection of Unmanaged Providers).
-> ADO.NET provides various 'types' that can be used for data source communication under the below mentioned namespaces.
System. Data
System.Data.OleDb
System.Data.SqlClient
System.Data.OracleClient
System.Data.Odbc
Now, we will see one by one.
System.Data
The Types under this namespace are used for holding and managing the data on Client machines. Under this namespace, the 'Classes' are DataSet, DataTable, DataColumn, DataRow, DataView, DataRelation etc.
System.Data.OleDb
The Types under this namespace are used for communicating with any data source like Files, Databases, Indexing Servers etc.
System.Data.SqlClient
The Types under this namespace are used only for SQL Server Database communication.
System.Data.OracleClient
The Types under this namespace are used only for Oracle Database communication.
System.Data.Odbc
The Types under this namespace are used for communicating with traditional ODBC drivers and they will in turn communicate with any kind of data source.
Most commonly, all the above mentioned namespaces contain same set of Classes as below.
· Connection
· Command
· DataReader
· DataAdapter
· CommandBuilder
· Parameter
Note: In order to distinguish between each other, here each 'class' is referred by prefixing with their namespace before the 'class' name as below.
OleDbConnection
OleDbCommand
SqlConnection
SqlCommand
OracleConnection
OracleCommand
OdbcConnection
OdbcCommand
Each and every operation what we perform on a data source has mainly three steps involved in it.
1) Establishing a Connection
2) Sending request as a Statement
3) Capturing the results given by Data Source
Now we will go in detail one by one.
1) Establishing a Connection
In this process, basically we open a channel for communication with Data Source which is present on local or remote machine in order to perform the operations. To open a channel for communication, we use 'Connection' class.
Constructors
Connection ()
Connection (string connectionstring)
'ConnectionString' is a collection of attributes which are used for connecting with a data source those are listed below.
1) Providers
2) Data Source
3) User Id and Password
4) Database (or) Initial Catalog
5) Trusted_Connection = True
6) DSN
What exactly the above mentioned attributes are will see one by one below.
1) Provider
As discussed previously, Provider should be required for communicating with data sources, where we need to use different providers for different data sources.
Oracle Msdaora
SqlServer SqlOleDb
MS-Access (or) MS-Excel Microsoft.Jet.OleDb.4.0
IndexingServer Msidxs
2) Data Source
Data Source is the name of target machine to which we need to connect with and it does not require to be specified if the data source is on local machine.
3) User Id & Password
As the databases are secured places for storing data, in order to connect with them we require a valid username and password.
For Oracle, Username is Scott and Password is tiger
For SQL Server, Username is Sa and Password is <pwd>
4) Database (or) Initial Catalog
These attributes are used when we connect with SQL Server to specify the name of Database that we need to connect with.
5) Trusted_Connection (or) Integrated Security
These attributes are used when we connect with SQL Server to specify that we want to use 'Windows Authentication'.
6) DSN
This attribute is usually used to connect with a data source using ODBC drivers.
Connection String for Oracle
"Provider = Msdora; User Id=Scott; Password = tiger [; DataSource=<server>]"
Connection String for SQL Server
"Provider = SqlOleDb; User Id = Sa; Password = <pwd>; Database = <db name> [; Data source = <server>]"
Methods and Properties of Connection Class
1) Open () - Open a connection with data source.
2) Close () - Close the connection which is opened.
3) State - Get the status of Connection.
4) ConnectionString - Get or Set a ConnectionString associated with the connection object.
We can create an object of class Connection in any of the below mentioned ways.
Connection con = new Connection ();
con.ConnectionSting = "<con string>";
We will understand it by an example. Open a New Project of type Windows and name it as "DBOperations" and then create the 'Form' as below.
using System.Data.OleDb;
Under Connect with Oracle Button
OleDbConnection oracon = new OleDbConnection("Provider = Msdora; User Id = Scott; Password = tiger");
oracon.Open();
MessageBox.Show(oracon.State.ToString());
oracon.Close();
MessageBox.Show(oracon.State.ToString());
Under Connect with SQL Button
OleDbConnection sqlcon = new OleDbConnection("Provider = SqlOleDb; User Id = Sa; Password = 123; Database = mydb; Data Source = praveen");
sqlcon.Open();
MessageBox.Show(sqlcon.State.ToString());
sqlcon.Close();
MessageBox.Show(sqlcon.State.ToString());
Sending Request as a Statement
In this process, we need to send a request to Data Source specifying the type of action we need to perform using a SQL Statement like INSERT, UPDATE, DELETE and SELECT.
'Command' class is used for sending request and executing the statements.
Command ()
Command (string sqlstmt, Connection con)
Properties of Command Class
Connection - Set or get the connection object associated with the Command.
CommandText - Set or get the statement associated with the Command.
Object of class 'Command' can be created in any of the below mentioned ways.
Command cmd = new Command ();
cmd.Connection = <con>;
(or)
Command cmd = new Command ("<sql statement>", con);
Methods of 'Command' Class
ExecuteReader () - DataReader
ExecuteScalar () - Object
ExecuteNonQuery () - int
After creating the object of 'Command' class, we need to call any of the above three methods to execute the statement. We can use those based on the requirement like mentioned below.
-> Use 'ExecuteReader' method when we need to execute a SELECT statement which will return data as Rows and Columns. This method returns an object of class 'DataReader' which will hold the data that is retrieved in the form of Rows and Columns.
-> Use 'ExecuteScalar' method when we need to execute a SELECT statement which will return a single value result. The Return type of this method is object, which will get the value in a generic type.
-> Use 'ExecuteNonQuery' method when we need to ExecuteNonQuery statements (eg: DML statements like INSERT, UPDATE and DELETE). In this case, we need to find out the number of rows affected by the statement and also the return type of the method is an 'int'.
Note: The above process of calling an appropriate method in appropriate case is the third step which is capturing of results.
Accessing Data from DataReader
DataReader is a class which holds the data in the form of Rows and Columns like table structure. In order to access the data from DataReader, it will provide us the below methods.
Read () - bool
Move record 'Pointer' from the current location to the next row and return a Boolean status which will tell whether the row to which we moved contains data in it or not. It will be TRUE if present or FALSE if not represent.
GetValue (int index) - object
It is used for retrieving field values from the row to which 'pointer' was pointing by specifying the Column index position.
Note: We can access the row pointer by pointer in a single dimensional array either by specifying column index position or name as below.
<DR> [index] -> object
<DR> [columnname] -> object
GetName (int index) - string
It returns the name of column for given index position.
NextResult () - bool
It moves the record pointer from current table to next table if a table exists and returns TRUE or else returns FALSE.
using System.Data.OleDb;
Declarations
OleDbConnection con;
OleDbCommand cmd;
OleDbDataReader dr;
Under Form Load
con = new OleDbConnection("Provider=Msdaora;User Id=Scott;Password=tiger");
cmd = new OleDbCommand("Select Deptno,Dname,Loc From Dept", con);
con.Open ();
dr = cmd.ExecuteReader();
label1.Text = dr.GetName (0);
label2.Text = dr.GetName (1);
label3.Text = dr.GetName (2);
ShowData ();
private void ShowData()
{
if (dr.Read())
{
textBox1.Text = dr.GetValue (0).ToString ();
textBox2.Text = dr.GetValue (1).ToString ();
textBox3.Text = dr.GetValue (2).ToString ();
}
else
MessageBox.Show ("Last Record");
}
Under Next Button
ShowData ();
Under Close Button
if (con.State != ConnectionState.Closed)
con.Close ();
this.Close ();
Now, design a form like below and write the below mentioned code and observe the output you will get complete understanding on database operations.
-> Set Deptno TextBox 'ReadOnly' property as TRUE
using System.Data.OleDb;
Declarations
OleDbConnection con;
OleDbCommand cmd;
OleDbDataReader dr;
string sqlstr;
Under Form Load
con = new OleDbConnection("Provider=Msdaora;User Id=Scott;Password=tiger");
cmd = new OleDbCommand();
cmd.Connection = con;
LoadData ();
private void LoadData()
{
sqlstr = "Select Deptno,Dname,Loc From Dept Order By Deptno";
SetStmt ();
dr = cmd.ExecuteReader();
ShowData ();
}
private void SetStmt()
{
if (con.State != ConnectionState.Closed)
con.Close ();
cmd.CommandText = sqlstr;
con.Open ();
}
private void ShowData()
{
if (dr.Read())
{
textBox1.Text = dr [0].ToString ();
textBox2.Text = dr [1].ToString ();
textBox3.Text = dr [2].ToString ();
}
else
MessageBox.Show ("Last record of the Table");
}
private void ExecuteDML()
{
DialogResult d = MessageBox.Show (sqlstr + "\n\nDo you wish to execute the Query?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (d == DialogResult.Yes)
{
SetStmt ();
int Count = cmd.ExecuteNonQuery();
if (Count > 0)
MessageBox.Show ("Statement Executed Successfully");
else
MessageBox.Show ("Statement Execution Failed");
LoadData ();
}
}
Under Next Button
ShowData ();
Under New Button
textBox1.Text = textBox2.Text = textBox3.Text = "";
sqlstr = "Select Max(Deptno) + 10 From Dept";
SetStmt ();
textBox1.Text = cmd.ExecuteScalar ().ToString ();
textBox2.Focus ();
Under Insert Button
Sqlstr = String.Format("Insert Into Dept (Deptno, Dname, Loc) Values({0},'{1}','{2}')", textBox1.Text, textBox2, Text, textBox3.Text);
ExecuteDML ();
Under Update Button
Sqlstr = String.Format ("Update Dept Set Dname = '{0}', Loc = '{1}' Where Deptno = {2}", textBox2.Text, textBox3.Text, textBox1.Text);
ExecuteDML ();
Under Delete Button
sqlstr = String.Format("Delete From Dept Where Deptno = {0}", textBox1.Text);
ExecuteDML ();
Under Close Button
if (con.State != ConnectionState.Closed)
con.Close ();
this.Close ();
Happy Learning….!!!!!
This post first appeared on Dot Net Programming (C#, Asp.Net, ADO.Net, WCF, WPF, Ajax, LINQ), please read the originial post: here
