Lesson 11: CSharp and ADONET




csharp and ado.net

What is Database?

Database is an organised collection of data which is stored in the form of columns and rows
in order to provide much faster operations such as adding,searching,deleting,updating.
Generally,the data is stored in the database in the form of table(s).

My choice of database right now is sql server 2012.You may use Sql 2008 or Sql 2005 as well.
The differences between these versions will be discussed in other tutorials section about Sql Server.

If we open the sql Server Management Studio,we will see something like this:

*adeel-pc is the name of the server in which my database is held

*SQLEXPRESS2012 is the current instance of the database

*Budget is my Database

*Inside Budget,we have tables folder.Here our tables are stored

On exploring the table named as “dbo.tbl_Loan”, we will see something like this:

The vertical titles such as ID,lenderID,userID,creditCard etc. are know as fields or
columns.

The collection of the values under the titles are known as records.

 

Tuple

Group of columns or fields is known as Tuple

Databases operations with C#. In every organization; users need to have an
automated system for keeping their records safe, easier to search,update etc. So
that the reports are kept about the movement of money or item(s) in the company.
Let's see an example.

An Inventory management software keeps the records about the items present
in the store,their price(s),item(s) required to be shipped,shipment method etc.
This application is developed by the programmer and is being used by the store supervisor to feed
or manipulate the data.
Here;both the programmer(s) and database admins have worked together;in order to make the application
interact with the user.
The information is given by the store keeper through an interface;which is developed in any
programming language.All the backend data is stored in the Database such as Sql Server.

Relational Database or(RDBMS)


An RDBMS is simply a collection of database(s), in which Rows(Records)
and Columns(Fields) are organized in the form of table(s).
Table(s) can also have relationships with other table(s) through common fields between them

Using C# for Database Access

In order to understand the techniques used to manipulate the data by C#;we have to look at some articles

related to the friendship between the databases and the
.Net framework

ADO.NET

Ado.Net(Activex data objects) is the mechanism to interact with the data sources such as
Sql Server and XML, as well
as with the help of OLEDB or ODBC to other data sources
.ADO.Net rely on the .Net Framework’s managed providers.ADO.Net Dataset represents the database in
memory for operations


ADO.NET vs OLD ADO in msdn site


.NET Framework Data Providers (ADO.NET)

For a detailed and super easy article on .NET Framework Data Providers (ADO.NET) 
click this
link

Which Provider to use when connecting with Sql Server?

ODBC,OLEDB or Sql

The answer to this question is simplest and tricky as well.ODBC stands for Open Database Connectivity.ODBC is compatible with almost all the databases.It has good documentation as well as cross-platform support.
The drawbacks of ODBC is that it fetches from start to end and never look back.Hence it is forward-only.It supports only one request per connection at a time .We have to create DSN in most of the programming languages for the ODBC connection to work.

On the other hand OLEDB (Object Linking and Embedding of Databases) OLE DB is designed to provide access to all types of data in an OLE Component Object Model (COM) environment. OLE DB includes the Structured Query Language (SQL) functionality defined in ODBC but also defines interfaces suitable for gaining access to data other than SQL data.Reference taken from

OLE DB for the ODBC Programmer

Both OLEDB and ODBC are API’s (Application Programming interfaces) that let the applications interact with the Databases.

Our very dear friend has described beautifully on his site
ODBC vs. OLE DB

Why one should use SqlClient when working with Sql Server specific applications

Always keep in mind that if you want to take additional benefits of Sql Servers (specifically 2005 to 2012 and so on);you have to use SqlClient Namespace.All ODBC,OLEDB and SqlClient belong to System.Data Name space.
The SqlClient provides remarkable boost in the performance because it is native to the .Net framework and there is no translation layer required when accessing the Sql Server Database through SqlClient (unlike OLEDB and ODBC).Another benefit of SqlClient (Sql Server native driver) is the support for the parameters which can be used in any order.(Discussed later).

Here is the program,which connnects to the Sql Server;perform the operation to get the records in from the table in thew database
Following steps are involved for our application to work with the back end database

1. Establish a connection
2. Open the Connection
3. Fetch the records.
4.Display the records
5.close the connection

Look at these examples one by one

Example1

Selecting the records from the table.

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
static void Main()
{

try
{

using (SqlConnection con = new SqlConnection(@”data source=adeel-PCSQLEXPRESS2012;Database=Budget;User ID=sa;Password=sa”))
{

SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = “Select * From tbl_Loan”;
SqlDataReader dr;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{

Console.WriteLine(” LenderID:{0}nn UserID:{1}nn CreditCard:{2}nn”, dr[1].ToString(), dr[2].ToString(), dr[3].ToString());

}

dr.Close();

con.Close();
Console.ReadKey();
}

}

catch (Exception ex)
{

Console.WriteLine(ex.Message.ToString());

}

}
}

Output

Example2

Selecting the records from the table with stored procedure

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
static void Main()
{

try
{

using (SqlConnection con = new SqlConnection(@”data source=adeel-PCSQLEXPRESS2012;Database=Budget;User ID=sa;Password=sa”))
{

SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = “proc_Select”;
SqlDataReader dr;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{

Console.WriteLine(” LenderID:{0}nn UserID:{1}nn CreditCard:{2}nn”, dr[1].ToString(), dr[2].ToString(), dr[3].ToString());

}

dr.Close();

con.Close();
Console.ReadKey();
}

}

catch (Exception ex)
{

Console.WriteLine(ex.Message.ToString());

}

}
}

Output

Now listen to me what I am saying.

The logic is very simple for connecting to the data base and getting the records from the table

Step1: First we have to use System.Data name space to work with with CommandText enumeration as well as CommandBehavior enumeration.Next we are using System.Data.SqlClient name space to refer SqlConnection,SqlCommand and SqlDataReader classes.

Step2: Creating an instance of SqlConnection object and pass the value of Connectionstring to it.
As I have an instance of the database with the name SQLEXPRESS2012 in my server adeel-pc,I have used “Data Source=adeel-pc/SQLEXPRESS2012” in my Connectionstring.ConnectionString is simply the information needed to create the instance which connects our application to the database.I have mentioned the Sqlconnection in using statement because,if the exception occurs,the connection should be closed
This is the best site to get the connectionstring of your desire

www.connectionstrings.com

Step3:After entering the lines within using block; I created an instance of SqlCommand object.I then opened the connection named as con with con.Open().This is a necessary step for a command to run,otherwise “No opened connections” exception will be thrown.
There is a bit difference in Example 1 and Example 2. Example 1 is using the commandtext approach and Example 2 is using StoredProcedure approach.

I have defined that the SqlCommand object will execute query in the form of written text on the code editor window.Therefore,I specified the sql Select statement as a text to select the records.Where as; in Example 2,I have used the name of the stored procedure which is created in sql server, like this

Let me remind you,this is my table and my database.You have to use your database with your id and password and create tables (if required)
I will briefly discuss these matters in our Sql Server tutorials.So don’t worry.

Step4: SqlDataReader is ideal approach for fast streaming of data as it does not hold the copy of the table(s) in the memory.But it is forward-only (means,it reads from start till end of the stream only).The command is executed against the datareader.The ExecuteReader is a method which sends the command text to the SqlConnection nd builds the SqlDataReader with one of the CommandBehaviors.The CommandBehavior.CloseConnection closes the SqlDataReader when the command completes its execution.I prefer to close the SqlDataReader as well as SqlConnection by myself also.The While loop reads the SqlDataReader from start till the end and we are displaying the values against the mentioned fields in the indexer type notation (dr[] in our case)

Stored Procedures vs CommandText

If you want to know the exact meanings of this title;then you have to read this awesome blog

How to specify parameters in previous examples?

The reason behind adding parameters to the SqlCommand object is to project the results in Data Manipulating scenarios.

If we look back to our previous two examples;we will see that the whole results were gathered without any condition.
If we want to select the record(s) based on the specific criteria,we have to supply the parameters to the command(s) or stored procedure(s).

Revising Example 1 and Example 2 using parameters

Example 1 Revised

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
static void Main()
{

try
{

using (SqlConnection con = new SqlConnection(@”data source=adeel-PCSQLEXPRESS2012;Database=Budget;User ID=sa;Password=sa”))
{

SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
double loanAmount = 12000;
cmd.CommandText = “Select * From tbl_Loan Where loanAmount <=@loanAmount"; cmd.Parameters.AddWithValue("@loanAmount", loanAmount); cmd.Parameters["@loanAmount"].Direction = ParameterDirection.Input; SqlDataReader dr; dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { Console.WriteLine(" LenderID:{0}nn UserID:{1}nn CreditCard:{2}nn", dr[1].ToString(), dr[2].ToString(), dr[3].ToString()); } dr.Close(); con.Close(); Console.ReadKey(); } } catch (Exception ex) { Console.WriteLine(ex.Message.ToString()); } } }

Output

How about using parameters in Stored Procedures?

We are modifying the Stored Procedure with an input parameter @loanAmount of type ‘double’ as follows

Example 2 can be re-written as

Example2 Revised

Selecting the records from the table with stored procedure

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
static void Main()
{

try
{

using (SqlConnection con = new SqlConnection(@”data source=adeel-PCSQLEXPRESS2012;Database=Budget;User ID=sa;Password=sa”))
{

SqlCommand cmd = new SqlCommand();
con.Open();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
float loanAmount = 12000;
cmd.CommandText = “proc_Select”;
cmd.Parameters.AddWithValue(“@loanAmount”, loanAmount);
cmd.Parameters[“@loanAmount”].Direction = ParameterDirection.Input;
SqlDataReader dr;
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

while (dr.Read())
{

Console.WriteLine(” LenderID:{0}nn UserID:{1}nn CreditCard:{2}nn”, dr[1].ToString(), dr[2].ToString(), dr[3].ToString());

}

dr.Close();

con.Close();
Console.ReadKey();
}

}

catch (Exception ex)
{

Console.WriteLine(ex.Message.ToString());

}

}
}

Output

How about Output Parameters in Stored Procedures?

Oh! this is so bothersome…….Wait! This is nothing like a Rocket Science
Look at the example

Example 3

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
static void Main()
{

try
{

using (SqlConnection con = new SqlConnection(@”data source=adeel-PCSQLEXPRESS2012;Database=Budget;User ID=sa;Password=sa”))
{

SqlCommand cmd = new SqlCommand();

con.Open();
cmd.Connection = con;
cmd.CommandText = “my_proc2”;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(“@count”, SqlDbType.Int);
cmd.Parameters[“@count”].Direction = ParameterDirection.Output;

SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
int ans = (int)cmd.Parameters[“@count”].Value;
Console.WriteLine(ans);

con.Close();
Console.ReadKey();
}

}

catch (Exception ex)
{

Console.WriteLine(ex.Message.ToString());

}

}
}

Output

Why using embedded Sql is a bad practice?

Just forget the old style of writing the query on SqlCommandText like this:

string name=”spreadpeace”;
SqlCommandText.CommandText=”Select * From myTable Where Name='” + name + “‘”;

Follow this :

string name=”spreadpeace”;
SqlCommandText.CommandText=”Select * From myTable Where Name=@name”;
SqlCommandText.Parameters.AddWithValue(“@name”,name);

Why?It is because of sql injection attack

What’s that man…….
Relax guys….just skim through this article

How about DataAdapters,DataSets and the DataTables?

DataSet

The object DataSet is a a representation about the table(s),View(s),Data Table(s) and XML data.This DataSet is an object of the name space System.Data.We can also define the relationships between the table(s),apply constraints,order them through programming.
Point to be noted that the DataSet

Typed vs Untyped DataSet

Untyped DataSet

This DataSet is generic representation of the memory resident table(s) that it contains.
As this DataSet is generic;we have to take special care for accessing its data by using proper type casting.

Typed DataSet

The Typed DataSet(s) has predefined data types for every columns of the table(s) which it holds in memory.The class which has typed dataset,maps its properties to the Columns or Names of the table(s) that it contains.

DataSets vs DataReaders

DataSets were introduced in the .Net Framework for the disconnected connection to the database unlike the recordset which was not a disconnected connection to the database in old Visual Studio applications.
DataReader is not disconnected from the source when it is alive and it is forward only.While DataSet contain a copy of the data in the memory without engaging the real source.

The DataTable Object

I have taken an excerpt from an official site of MicroSoft to define the DataTable object

An ADO.NET DataSet contains a collection of zero or more tables represented by DataTable objects. The DataTableCollection contains all the DataTable objects in a DataSet.
A DataTable is defined in the System.Data namespace and represents a single table of memory-resident data. It contains a collection of columns represented by a DataColumnCollection, and constraints represented by a ConstraintCollection, which together define the schema of the table. A DataTable also contains a collection of rows represented by the DataRowCollection, which contains the data in the table. Along with its current state, a DataRow retains both its current and original versions to identify changes to the values stored in the row.

Reference http://msdn.microsoft.com/en-us/library/zb0sdh0b(v=vs.110).aspx

DataSet vs DataTable

The DataSet object consumes more memory than the DataTable object.If we are accessing only one table without complex relationships with other tables;then DataTable is the proper choice.But DataSet is more powerful than DataTable for maintaining several DataTables,their relations,constraints etc.Datatable contains only one table in memory resident form.

The DataView Object

The DataView object of System.Data namespace;is a way to display the data as per requirement by sorting the c columns of the DataTable.The DataView is mostly used in GUI based applications

The DataAdapter Object

The SqlDataAdapter acts as a bridge between the DataSet object and SQL server database for retrieving or manipulating the data. We can use SqlDataAdapter object in combination with Dataset Object. The SqlDataAdapter can perform select, insert, update and delete operations in Dataset.

See all of them in action

Examples

Example1 using DataSet and DataAdapter

using System;
using System.Data;
using System.Data.SqlClient;

public class Program
{
static void Main()
{

SqlConnection con =new SqlConnection(@”data source=adeel-PCSQLEXPRESS2012;Database=Budget;User ID=sa;Password=mypassword”);

SqlCommand cmd =new SqlCommand(“SELECT * FROM tbl_Loan”, con);

// Initializing the DataSet object ds
DataSet ds = new DataSet();
//Initializing the DataAdapter object
SqlDataAdapter da = new SqlDataAdapter();

// assigning our command cmd to the dataadapter Select Command
da.SelectCommand = cmd;

// Fill the dataset with the dataAdapter’s retrive results
da.Fill(ds);

/* looping through each row in the dataset and

* writing the corresponding columns values on the console

*/
foreach (DataRow row in ds.Tables[0].Rows)
{
Console.WriteLine(“lenderID: {0}”, (String)row[“lenderID”]);
Console.WriteLine(“creditCard No: {0}”, (String)row[“creditCard”]);
}
con.Close();

Console.ReadKey();
}

}

Example 2 using the DataTable with DataAdapter

using System;
using System.Data;
using System.Data.SqlClient;

public class Program
{
static void Main()
{

SqlConnection con =new SqlConnection(@”data source=adeel-PCSQLEXPRESS2012;Database=Budget;User ID=sa;Password=adeelgr8one”);

SqlCommand cmd =new SqlCommand(“SELECT * FROM tbl_Loan”, con);

// Initializing the DataTable object dt
DataTable dt = new DataTable();
//Initializing the DataAdapter object
SqlDataAdapter da = new SqlDataAdapter();

// assigning our command cmd to the dataadapter Select Command
da.SelectCommand = cmd;

// Fill the DataTable with the dataAdapter’s retrive results
da.Fill(dt);

/* looping through each row in the DataTable and

* writing the corresponding columns values on the console

*/
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(“lenderID: {0}”, (String)row[“lenderID”]);
Console.WriteLine(“creditCard No: {0}”, (String)row[“creditCard”]);
}
con.Close();

Console.ReadKey();
}

}

Output is same for all these examples

SqlCommand.ExecuteScalar
Sometimes we want to return a value as a result of the execution of the query.Execute scalar returns this value.
For example

using System.Data;
using System.Data.SqlClient;

public class Program
{
static void Main()
{
CountRows();
Console.ReadKey();
}
static void CountRows()
{
using (SqlConnection con = new SqlConnection(@”data source=adeel-PCSQLEXPRESS2012;Database=Budget;User ID=sa;Password=sa”))
{
string RowCount = “”;
string str = “SELECT * FROM tbl_Loan; ”
+ “SELECT Count(*) As TotalRows”;
con.Open();
SqlCommand cmd = new SqlCommand(str, con);
RowCount = cmd.ExecuteScalar().ToString();
Console.WriteLine(“Row Count:{0}”, RowCount);
con.Close();
Console.ReadKey();
}
}

}

Output

Row Count:1

I will discuss the relationships in datatables,Creating Typed DataSet in Windows Forms after completing the GUI based tutorials.

This topic has covered all the core areas in programming with C# and and ADO.NET

Lesson 11 Completed….


Syed Adeel Ahmed
Analyst, Programmer, Educationist and Blogger at Technofranchise
Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.
Syed Adeel Ahmed on FacebookSyed Adeel Ahmed on GoogleSyed Adeel Ahmed on InstagramSyed Adeel Ahmed on Twitter

Published by

Syed Adeel Ahmed

Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.