Basics of ADO.NET

 

ADO.NET tells about the database connectivity with your .NET based application (windows/web).

Here I’ll tell you  how to connect and use MSSQL server with .NET.

With your Visual studio 2008 installation package Microsoft provide MSSQL 2005 express edition.in  recommended or full package it is included to be installed and when you select custom  then it’s upto you whether you install it or not. The story is no more different in Visual studio 2010 where Microsoft provide MSSQL 2008 Express Edition.

Well I’m assuming that you have installed MSSQL 2005/2008 Express Edition/Full Edition (if not then either install with VS2008 or VS2010 or download here(2005 express edition).

While installing you will be asked whether you want windows authentication or SQL Authentication.

• Windows Authentication: Here authentication is provided to the current logged in user of Windows Operating system.
• SQL Authentication: Here authentication is provided by SQL itself. by default ‘sa’ user is created and password is given by you while installing.

Creating a database:

To create a Database you need either Visual studio or Microsoft SQL Server Management Studio(MSSMS), Express edition of MSSMS is available here for free.

Here for this blog I’m using Visual Studio only.

• Open your project in visual studio.
• In solution explorer right click on your project and choose “Add New Item”.
• From Opened dialog box choose SQL Database( with .mdf extension).
• Click ok. New Database should be opened in Server Explorer Dock pane. if not then click on view from menu bar and choose  Server Explorer.
• Server explorer should have your created Database. Expand the top node. it’ll open sub modes those are – Table, Views, Diagrams etc.
• Right Click on Table and select Add new Table.
• Create tables of your choice.
• You can choose New Query to execute queries.

Connecting To Database

In .NET ADO.NET provides libraries that is used for Connecting and  executing necessary queries on SQL Server. The Library is included by code:

using System.Data;

using System.Data.SqlClient;

For connecting to Database we need a Connection String to the database which is used for creating a Connection object to the database. Here’s how we Create it

SqlConnection con = new SqlConnection(“ConnectionString”);

Connection string to a database can be found from Visual Studio itself. here’s how

Connection string consist of double quotes(“”) so change that to single quote(‘’) again it consist of unknown escape sequences so put “@” to make it literal.

Connection to database is made now.

It is opened by following code

con.Open();

Normally we put it in try catch block so that we can handle Exception if there exist any Dexception connecting SQLSERVER or DATABASE.

Remember to close the connection once you are done with Data Transfer. you can close it by following code

con.Close();

Executing Query

For executing queries for sql Database we create an object of SqlCommand object. Here’s how

SqlCommand cmd = new SqlCommand(“Query”,con);

"Query”: it’s string that consist sql query eg-“select * from Employee”

con: con is object of SqlConnection.

• For updating, inserting, delete queries we use function cmd.ExecuteNonQuery()
  • ExecuteNonQuery returns number of rows affected by query
• For select query we use cmd.ExecuteReader() or cmd.ExecuteScalar()
  • ExecuteReader returns SqlDataReader object which consists of all the rows selected by query
  • ExecuteScalar is used when select statement is expected to return only one value for example: select name  from Employee where EmployeeId=1. It returns value in object type. this object can be unboxed into compatible dataType.

I have developed a sample and simple application to understand ADO.NET’s basic application

You can download from here