Ado .net

What is ADO.Net

The ADO.NET is one of the Microsoft's data access technology which is used to communicate between the .NET Applicatio (Console,WCF,WPF,Windows,MVC,Web Form,etc) and data sources such as SQL Server Oracle, MySQL,XML document etc

ADO.NET provides a bridge between the front end connection are the back end database

ADO.NET is a module of .net framework which is use to establish connection between application and data sources

Data Sources can be such as SQL Server and XML.

ADO.NET consists of classes that can be used to connect, retrive, insert and delete data

All the ADO.NET classes are located into system.Data.dll and integrated with XML classes located into System.Xml.dll

ADO.NET stands for ActiveX Data Object.

It is a databse access technology created by Microsoft as part of its .Net framework that can access any kind of data source.

It's a set of object-oriented classes taht provides a rich set of data components to create high-performance,reliable and scalable database applications.

ADO.NET consists of a set of classes that can be used to connect , retrieve,insert,update and delete (i.e. performing CORD operation) form data sources.

ADO.NET mainly uses System.Data.dll and System.XML.dll

What types of Application use ADO.NET?

ADO>NET can be used to develop any type of.Net application.The following are some of the .NET applications where you can ADO.NET Data Access Technology to interact with a data source.

ASP.NET Web Form Applications
Windows Form Applications
ASP.NET MVC Application
Console Applications
ASP.NET Web API Applications

Component of ADO.NET

Components are designed for data manipulation and faster data access.

Connection, Commend,DataReader,DataAdapter,DataSet,and DataView are the compom=nents of ADO.NET that are used to perform database operations.

ADO.NET has two main components that are used for accessing and manipulating data.They are as follows:

Data provider
DataSet

What is .NET Data providers ?

The .Net Application can not directly execute our SQL code,it only understands c#.So if a.NET application needs to retrieve data or to do some insert, update and delete operation from or to a database,then the .NET application need to

Connect to the Database

Prepare an SQL Command

Execute the Command

Retrieve the results and display them in the application

SQL Connection Class of ADO.NET

SqlConnection to establish an open connection to the sql server database.
A sqlConnection object represents a unique session to a sql server data source.
ADO.NET connection is an object that provides database connectivity and the entery point to a databse.
It is a sealed class so that cannot be inherited.

Namespace:

system.Data.sqlclient

Assembly:

system.Data.sqlclient.dll

public sealed class sqlConnection: sysyem.Data.Common.Dbconnection,ICCIoneable

Inheritance:Object->DbConnection->sqlconnection

Implements:ICIoneable

Creating Object of SQLConnection class of ADO.Net

sqlconnection con =new sqlconnection(cs);

cs is a string variable which holds connection string.

The connection string that includes the source database name,and other parameters needed to estabilish the initial connection.

The default value is an empty string

Things in Connection String

Connection string has:
1. Data Source (Database Server Name)
1. Inital Catalog (Database Name)
  1. Initegrated Security=true ->Windows Authentication
  2. UserName
  3. Password ->sql server Authentication

Data Source: This identifies the server name,which could be the local machine,machine domain name or IP address

Inital Catalog: This identifies the database name.

Initegrated Security: When you have startd database authentication login with Windows authencation,Integrated Security specifies Integrated Security="True" in connection string,else when you have started the database authentication login with Server authentication integrated security specifies integrated Security="false" in the connection string

User Id: Name of the user configured in sql server

Password: password matching sql server User ID

Creating following example illustrates a typical connection string .

Data source=MyserverName;initial Catalog=MydatabaseName;Integrated Security="True;"

Connection to sql ServerName;Initial Data Sourcecatalog=MyDatabaseName;User Id=MyUserName;Password=MyPassword;"

same like this type\

Creating Object Of SQLConnectio Class of ADO.Net

String cs="Data source=MyServerName;Initial Caltlog=MyDatabaseName;Integrated Security=true;"
sqlConnection con=new sqlConnection(cs);

SQLCONNECTION CLASS OF ADO.NET

When we have to perform an operation on database, we must have to Open and close the connection.
Example:
sqlconnection con =new sqlconnection(cs);
con.Open();

Execute Database Queries Here...

Con.close();

At this stage we have to use State property of connection class.

Create a Connection to data base

Create a new web form to your visual stdio

Go to view code and write This query

Second Type Tray And Catch block

using System;
using System.Data.SqlClient;

namespace ADOconnection_04_09
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnConnect_Click(object sender, EventArgs e)
        {
            try
            {
                SqlConnection Con = new SqlConnection(@"data source=DESKTOP-CVFK5PT\MSSQLSERVER01;Initial catalog=nareshIt;Integrated security=true");
                Con.Open();
                lblMEssage.Text = "Connected to sql server sucess";
            }
            catch(Exception ex)
            {
                lblMEssage.Text = ex.Message ;
            }
        }
    }
}

To ensoure that connection are always slosed,open the connection inside of a using block,as shown in the following code fragment.

Doing so ensures that the connection is automatically closed when the code exits the block.

using(SqlConnection con = new SqlConnection(ConnectionString))

{

con.Open();

//Do work here ; connection close on following line.

}

Here we not write close() function when bracet is clpose then auto meticly is close

Interview question

Tell the 2 useges of using keyword?

Using is used for adding namespace in a file Ecample:Using System.Sqlclint
Using is used for automatically closing the Connection. (EX Up side Using)

using blocj is used to the connection automatically.we don't need to call close() method expilicitly,using block do this for ours implicitly when the code exits the block

Constructors of SQLConnection class of ADO.Net

sqlConnection()

initializes a new instance of the sqlconnection class

sqlConnection(String) string parameter it is a connection string

initializes a new instance of the sqlConnection close given a string a string that contains the connection string.

sqlconnection(string,sqlcredential)

initializes a m=new instance of the sqlConnection class given a connection string,that dose not use integrated security=true and a sql credential object that contains the user id and password.

SQLConnection class of ADO.Net

When the connection of an object is instatiated,the constructor take a connection sting that contains the infor,ation about the database server,server type ,dtabase name,connection type,and database user credentials.

Once the connection string is passed and connection object is created,you can establish a connection with the database

A connection string is usally stored in the web.config file or app.config of an application.

Connection String to declare a connection string,which is usally defined in the app.config or web.config file,so its availalbe in your application.the typical entry of a connection string is written below:

</connectionStrings>

SQL Command Class: -

SqlCommand class in ADO.Net

The ADO.NET sqlcommand class in c# is used to store and execute The SQL statement against the SQL Server Database.
sqlcommnad class is used to prepare an SQL statement or storeprocedure that we want to execute on a sql Server database.

SqlCommand Signature

It is a sealed class means it cannot be inherited.
public sealed class sqlcommand:system.Data.comman.Dbcommand,Icl..

Sql Command Class

If a .NET application (Web,Desktop,Console etc.) has to retrive data,then the application need to

1 Connect to the database
2 Prepare an SQL Command //2,3 in SqlCommand
3 Execute The Command
4 Retrive the result and display in the Application
This is the normal flow which is used by any .Net application,not only to retrieve data but also to insert,update or delete data.

The SqlCommand class in c# provides the following five overloaded Constructors.

1 public SqlCommand();

2 public SqlCommand(string cmd Text);

3 public SqlCommand(string cmd Text,SqlConnection connection);

4 public SqlCommand(string cmd Text,SqlConnection connection,SqlTransation transaction);

5 public SqlCommand(string cmd Text,SqlConnection connection,SqlTransation transaction,SqlCommandColumnEncryptionSetting columnEncryption setting);

1 SqlCommand();

This constructor is used to initializes a new instance of the system.Data.sqlClient.sqlCommand class.

2 SqlCommand(string cmdText) //(1 parameter)

It is used to initializes a new instance of the System.Data.SqlClint.sqlCommand class with the text of the query. Here,the cmdTexy is the text of the query that we want to execute.

3 public SqlCommand(string cmd Text,SqlConnection connection); //(2 parameter)

It is used to initializes a new instance of the System.Data.SqlClient.SqlCommand Class with the text the text of the query and a system.Data.SqlConnection.
Here, the codText is the Text of the query that we want to execute and the parameter connectio is the connection to an instance of SQL Server.

4 public SqlCommand(string cmd Text,SqlConnection connection,SqlTransation transaction); //(3 parameter)

It is used to onitializese a new instance of the System,Data.SqlClient.SqlCommand class with the text of the query, a SqlConnectioninstance,and the sqlTransaction instance.

Here,the parameter cmdText is the text of the query.

The parameter connection is a SqlConnection that represents the connection to an instance of SQL Server abd The Parameter transaction is the SqlTransaction in which the SqlCommand executes.

5 public SqlCommand(string cmd Text,SqlConnection connection,SqlTransation transaction,SqlCommandColumnEncryptionSetting columnEncryption setting); //(4 parameter )

SqlCommand(string cmdText,sqlConnection connection,sqlTransaction transaction transaction,sqlcommandcolmnEncryptionSetting columnEncryptionSetting);

it is used to initilizes a new instance of the System.Data.SqlClient.sqlCommand class with specified command text,connection,transaction,and encryption setting.

We already discussed the first three parameters which are the same as the previous. Here, the fourth parameter i.e. columnEncryptionsetting is the encryption setting.

Important Method of SqlCommand Class

Among all specified methods, following are the most commonly used methods of the sqlcommand class.

ExecuteReader()

ExecuteNonQuery()

ExecuteScalar()

Stored Procedure:-

A stored procedure is a database object which store the pre-compiled set of queries

When a stored procedure is called → A setored procedure is called query (or) Queries will run directly

Curd OPerations with Store procedures

C-Create

R-Read;

U-Update;

D- Delete

In this process, we read data from ASP.Net web form and supply this data to the storeed procedure from ADO.Net. finally, we call the stored procedure from ADO.Net Stored Procedure will execute The query in The database.

Creating a stored procedure without parameter

Syntax: Create Procedure Procedure_Name

BEGIN

STATEMENT 01

STATEMENT 02

END