What is ADO.NET?
ADO.NET is not a different technology. In simple terms, you can think of ADO.NET, as a set of classes (Framework), that can be used to interact with data sources like Databases and XML files. This data can, then be consumed in any .NET application. ADO stands for Microsoft ActiveX Data Objects.
The following are, a few of the different types of .NET applications that use ADO.NET to connect to a database, execute commands, and retrieve data.
ASP.NET Web Applications
Windows Applications
Console Applications
What are .NET Data Providers?
Databases only understand SQL. If a .NET application (Web, Windows, Console etc..) has to retrieve data, then the application needs to
1. Connect to the Database
2. Prepare an SQL Command
3. Execute the Command
4. Retrieve the results and display in the application
Sample ADO.NET code to connect to SQL Server Database and retrieve data. Notice that we are using SQLConnection, SQLCommand and SQLDataReader classes . All the objects are prefixed with the word SQL. All these classes are present in System.Data.SqlClient namespace. So, we can say that the .NET data provider for SQL Server is System.Data.SqlClient.
SqlConnection con = new SqlConnection("data source=.; database=Sample; integrated security=SSPI");
SqlCommand cmd = new SqlCommand("Select * from tblProduct", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
GridView1.DataSource = rdr;
GridView1.DataBind();
con.Close();
Sample ADO.NET code to connect to Oracle Database and retrieve data. Notice that we are using OracleConnection, OracleCommand and OracleDataReader classes . All the objects are prefixed with the word Oracle. All these classes are present in System.Data.OracleClient namespace. So, we can say that the .NET data provider for Oracle is System.Data.OracleClient.
OracleConnection con = new OracleConnection("Oracle Database Connection String");
OracleCommand cmd = new OracleCommand("Select * from tblProduct", con);
con.Open();
OracleDataReader rdr = cmd.ExecuteReader();
GridView1.DataSource = rdr;
GridView1.DataBind();
con.Close();
If we want to connect to OLEDB datasources like Excel, Access etc, we can use OleDbConnection, OleDbCommand and OleDbDataReader classes. So, .NET data provider for OLEDB is System.Data.OleDb.
Different .NET Data Providers
Data Provider for SQL Server - System.Data.SqlClient
Data Provider for Oracle - System.Data.OracleClient
Data Provider for OLEDB - System.Data.OleDb
Data Provider for ODBC - System.Data.Odbc
Please note that, depending on the provider, the following ADO.NET objects have a different prefix
1. Connection - SQLConnection, OracleConnection, OleDbConnection, OdbcConnection etc
2. Command - SQLCommand, OracleCommand, OleDbCommand, OdbcCommand etc
3. DataReader - SQLDataReader, OracleDataReader, OleDbDataReader, OdbcDataReader etc
4. DataAdapter - SQLDataAdapter, OracleDataAdapter, OleDbDataAdapter, OdbcDataAdapter etc
The DataSet object is not provider specific. Once we connect to a Database, execute command, and retrieve data into .NET application. The data can then be stored in a DataSet and work independently of the database.
ADO.NET is not a different technology. In simple terms, you can think of ADO.NET, as a set of classes (Framework), that can be used to interact with data sources like Databases and XML files. This data can, then be consumed in any .NET application. ADO stands for Microsoft ActiveX Data Objects.
The following are, a few of the different types of .NET applications that use ADO.NET to connect to a database, execute commands, and retrieve data.
ASP.NET Web Applications
Windows Applications
Console Applications
What are .NET Data Providers?
Databases only understand SQL. If a .NET application (Web, Windows, Console etc..) has to retrieve data, then the application needs to
1. Connect to the Database
2. Prepare an SQL Command
3. Execute the Command
4. Retrieve the results and display in the application
Sample ADO.NET code to connect to SQL Server Database and retrieve data. Notice that we are using SQLConnection, SQLCommand and SQLDataReader classes . All the objects are prefixed with the word SQL. All these classes are present in System.Data.SqlClient namespace. So, we can say that the .NET data provider for SQL Server is System.Data.SqlClient.
SqlConnection con = new SqlConnection("data source=.; database=Sample; integrated security=SSPI");
SqlCommand cmd = new SqlCommand("Select * from tblProduct", con);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
GridView1.DataSource = rdr;
GridView1.DataBind();
con.Close();
Sample ADO.NET code to connect to Oracle Database and retrieve data. Notice that we are using OracleConnection, OracleCommand and OracleDataReader classes . All the objects are prefixed with the word Oracle. All these classes are present in System.Data.OracleClient namespace. So, we can say that the .NET data provider for Oracle is System.Data.OracleClient.
OracleConnection con = new OracleConnection("Oracle Database Connection String");
OracleCommand cmd = new OracleCommand("Select * from tblProduct", con);
con.Open();
OracleDataReader rdr = cmd.ExecuteReader();
GridView1.DataSource = rdr;
GridView1.DataBind();
con.Close();
If we want to connect to OLEDB datasources like Excel, Access etc, we can use OleDbConnection, OleDbCommand and OleDbDataReader classes. So, .NET data provider for OLEDB is System.Data.OleDb.
Different .NET Data Providers
Data Provider for SQL Server - System.Data.SqlClient
Data Provider for Oracle - System.Data.OracleClient
Data Provider for OLEDB - System.Data.OleDb
Data Provider for ODBC - System.Data.Odbc
Please note that, depending on the provider, the following ADO.NET objects have a different prefix
1. Connection - SQLConnection, OracleConnection, OleDbConnection, OdbcConnection etc
2. Command - SQLCommand, OracleCommand, OleDbCommand, OdbcCommand etc
3. DataReader - SQLDataReader, OracleDataReader, OleDbDataReader, OdbcDataReader etc
4. DataAdapter - SQLDataAdapter, OracleDataAdapter, OleDbDataAdapter, OdbcDataAdapter etc
The DataSet object is not provider specific. Once we connect to a Database, execute command, and retrieve data into .NET application. The data can then be stored in a DataSet and work independently of the database.
the following code generates an error in connection with the database says con.Open() cannot connect to Sql database. Can you post how to solve this problem?
ReplyDeletePlease Verify the Connection string in web.config file,you get that error when the connection string is not correct or
Deleteif you mention the datasource(server) name as (local) or dot also gives you the error sometimes.So its better to give the name of the server instead of "." or (local)
hi venkat
ReplyDeletecould u tell me the difference between windows application and console application
in the same way diff between website and webapplication
pls upload video regarding these differences...can u explain practically...
Console Applications don't have user interfaces and are run in the Command Prompt. Windows Forms applications do have user interfaces.
DeleteHi ..
ReplyDeleteWindows Application Is The Application That
Interacts With The Data Source Of
Windows Based Objects , Classes And Programs , Software Like
MS Office , Ms Word , Notepad etc . Which Are Of Software Application And Windows Itself A System Application
You Can Understand The Concept Of This By Reading Application Programming Interface (API) And System Calls etc
Whereas Console Application Is The Application That Interacts With Console Application i.e -> Command Line Based Applications etc
There Are Lots Of Programming Language That Helps It To Interact To The Object Oriented Levels etc Like Perl , Ruby And Python etc
Are The Console Applications That Interacts With The Command Line Applications i.e -> Console Application . The Terms Signifies The Following Meaning Behind It
Whereas Website Is Basically A Terminology Of Web Pages In Which The Pages Are Dynamically As Well Statically Connected To The Web Interface System And It's The Part Of Either Local Area Network (LAN) Or Remote Wide Area Network (WAN) . If You Know Client And Server Architecture Model Then The Concept Is Easily Understandable That Client Sends Request To The Web Server And The Web Server Connects And Gets And Stores To The Request And Make Information Available To You . Also There Are Many Levels Of Requests , Methods And Properties In What Way It Works etc . Basically It's Registered To Web Hosting Domains via URL etc.
It's a document, typically written in plain text interspersed with formatting instructions of Hypertext Markup Language (HTML, XHTML). A webpage may incorporate elements from other websites with suitable markup anchors.
Web Applications Are The Applications That Interacts With Web Interface System etc . eg -> MSQLI , HTML , XML , XHML etc :)
Thanks For Reading
Hope You Understand
https://www.facebook.com/ChuckaKhatri
Hello Sir your tutorial videos are best to understand the beginners to learn and adapt the best techniques. Just want to ask you is you can do the tutorials for creating .exe in ADO.NET with Software Copy Protection for windows application.
ReplyDeleteYou Are the Best Instructor Ever.You Have No Idea What You Have Done For Me.Love From Pakistan.
ReplyDeletecan anyone give example for ODBC
ReplyDeleteHi Venkat,
ReplyDeletewhat basic settings have do be done in SqlServer to let your examples run? I always get a SqlException on Method Connection.Open independent on the used Web server (Build in or IIS)
Regards
Hi Sir,
ReplyDeleteI have watched many videos of yours,i Really really appreciate your work, its very easy to understand very helpful,i am just speech less. thanks a lot and wish me luck for the interview.
World class tutorials!! thanks alot. God bless you!!
ReplyDeleteSupper.. Clear and more effective
ReplyDeleteHi Venkat
ReplyDeleteI am using Visual studio 2012 and SQL server 2014.
Above code generates an error while connecting to the database at con.Open();
Please provide the detailed explanation to fix this issue?
the way you teach is indeed appreciable.thanks a lot sir
ReplyDeletewhen retriving data from database with asp.net some error pls solve it
ReplyDeleteDescription: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'persons'.
Source Error:
Line 17: SqlCommand cmd = new SqlCommand("select * from persons", con);
Line 18: con.Open();
Line 19: SqlDataReader rdr = cmd.ExecuteReader();
Line 20: GridView1.DataSource = rdr;
Line 21: GridView1.DataBind();
Source File: c:\users\fit\documents\visual studio 2010\Projects\WebApplication5\WebApplication5\Default.aspx.cs Line: 19
Stack Trace:
Mohammed Sheik please do this code
Delete//First Verified Person is correct that you given your table name after that write that code
SqlCommand cmd = new SqlCommand("select * from persons", con);
con.open();
SqlDataReader rdr = cmd.ExecuteReader();
DataTable dt=new Datatable();
rdr.fill(dt);
GridView1.DataSource=dt;
GridView1.DataBind();
Con.Close();
The table name persons doesn't exist in the database that you are pointing to .. so u r getting this error.. first create the table , then execute..
DeleteA network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
DeleteWhat is the problem hier ???
You should check your database name correctly mention in Web.config file, also check your server name.
Deletefor example
just like that
Thank you so much ! You help me a lot with this tutorial . Yua are the te best to learn C# (Sharp).
ReplyDeleteAurora from Albania.
You are the best teacher venkat sir.....i have ever seen...
ReplyDeleteone day i will come to banglore ....to thank you..
thank you very much venkat sir.....from deep of my heart.
Visual Studio 2017 community version with latest updates does it have any issues with con.open(), since the program just freezes. This checked with Asp.net vb.net code
ReplyDeleteHi Venkat,
ReplyDeleteThank you for the great tutorial.
In part2, you did not user sqlDataReader. Could you please explain?
hi sir,
ReplyDeletei am facing problem for connecting to database, please tell that should i need to run my sql server first, please guide me little bit more as i am new to learning here..
Sir can you please tell which application we have to use.I didn't find aspx.cs page.
ReplyDelete