ADO.net is data access architecture for the Microsoft .NET Framework.
<!--[if !supportEmptyParas]--> <!--[endif]-->Difference between ADO and ADO.net
ADO used connected data usage, while ADO.net used disconnected data environment.
ADO used OLE DB to access data and is COM-based, while ADO.net uses XML as the format for transmitting data to and from your database and web application.
In ADO, Record set, is like a single table or query result, while in ADO.net Dataset, can contain multiple tables from any data source.
In ADO, it is sometime problematic because firewall prohibits many types of request, while in ADO.net there is no such problem because XML is completely firewall-proof.<!--[if !supportEmptyParas]--> <!--[endif]-->
Difference between ADO.net Dataset and ADO Recordset
A DataSet can represent an entire relational database in memory, complete with tables, relations, and views.
· A DataSet is designed to work without any continuing connection to the original data source.
· Data in a DataSet is bulk-loaded, rather than being loaded on demand.
· There's no concept of cursor types in a DataSet.
· DataSets have no current record pointer You can use For Each loops to move through the data.
· You can store many edits in a DataSet, and write them to the original data source in a single operation.
· Though the DataSet is universal, other objects in ADO.NET come in different versions for different data sources.
Data Source:It can be a database, text file, excel spread sheet or an XML file.
Data Provider:A set of libraries that is used to communicate with data source.Eg:SQL data provider for SQL, Oracle data provider for Oracle, OLE DB data provider for access, excel or mysql.
SQL Connection:It establishes connection.
SQL Command:It allows to manipulate database by executing stored procedure or sql statements.
SQL DataReader:It provides a forward-only, read-only, connected recordset.
DataSet:dataset is a disconnected, in-memory representation of data.It can contain multiple data table from different database.
SQL DataAdapter:It populates dataset from data source.It contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the database.
DataView:It provides a means to filter and sort data within a data table.
Points to be consider while choosing between the DataSet and DataReader objects.
DataSet object
DataReader object
Read/Write access
Read-only access
Supports multiple tables from different databases
Supports a single table based on a single SQL query of one database
Disconnected mode
Connected mode
Bind to multiple controls
Bind to a single control
Forward and backward scanning of data
Forward-only scanning of data
Slower access to data
Faster access to data
Greater overhead to enable additional features
Lightweight object with very little overhead
Supported by Visual Studio .NET tools
Must be manually coded
<!--[if !supportEmptyParas]-->
SQLDataReader makes exclusive use of connection
The SQLDataReader object makes exclusive use of its SQLConnection object as long as it is open.You are not able to execute any other SqlCommand objects on that connection as long as the SQLDataReader object is open.Therefore, you should always call SQLDataReader.close() as soon as you are done retrieving data.
Strongly Typed Dataset Object
Strongly typed Dataset object allows you to create early-bound data retrieval expression.
Advantage of Strongly Typed dataset
It is faster than late-bound data retrieval expression.
Its column name is shown in intellisense as you type code.
Opening a connection is a database-intensive task. It can be one of the slowest operations that you perform in an ASP.NET page. Furthermore, a database has a limited supply of connections, and each connection requires a certain amount of
memory overhead (approximately 40 kilobytes per connection).
If you plan to have hundreds of users hitting your Web site simultaneously, the process of opening a database connection for each user can have a severe impact on the performance of your Web site.
Fortunately, you can safely ignore these bad warnings if you take advantage of connection pooling. When database connections are pooled, a set of connections is kept open so that they can be shared among multiple users. When you request a
new connection, an active connection is removed from the pool. When you close the connection, the connection is placed back in the pool.
Connection pooling is enabled for both OleDb and
SqlClient connections by default.To take advantage of connection pooling, you must be careful to do two things in your ASP.NET pages. First, you must be careful to
use the same exact connection string whenever you open a database connection. Only those connections opened with the same connection string can be placed in the same connection pool.For this reason you should place your connection string in the web.config file and retrieve it from this file whenever you need to open a connection
To take advantage of connection pooling in your ASP.NET pages, you also must be careful to explicitly close whatever connection you open as quickly as possible. If you do not explicitly close a connection with the
Close() method, the connection is never added back to the connection pool.
SQL injection is a strategy for attacking databases.
Example1: An ASP page asks the user for a name and a password, and then sends the following string to the database:
SELECT FROM users WHERE username = 'whatever' AND password = 'mypassword'
It seems safe, but it isn't. A user might enter something like this as her user name:
' OR 1>0 --
When this is plugged into the SQL statement, the result looks like this:
SELECT FROM users WHERE username = '' OR 1>0 -- AND password = ''
This injection comments out the password portion of the statement. It results in a list of all the names in the users table, so any user could get into your system.
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->
The easiest way to prevent this sort of injection is to parse the SQL string and remove any occurrences of "--" before passing the statement.
<!--[if !supportEmptyParas]--> <!--[endif]-->
Example 2:
You also have to beware of injections that contain semicolons because semicolons delimit SQL statements. Think about the implications of a user name like this:
' OR 1>0 ; DELETE Customers ; --
There are numerous ways a malicious user might penetrate your system using SQL injection and various defenses, but
the simplest approach is to avoid dynamic SQL. Instead, use stored procedures everywhere. Thanks to the way SQL passes parameters, injections such as those above will produce errors, and the stored procedure will not execute.
Really excellent piece of information. I have a small suggestion. If you have them as a BulletPoint FAQ type, then it would be more easy for quick reading.
Thanks deepak, i would consider your point for my next submission.
deepak.vasudevan
Really excellent piece of information. I have a small suggestion. If you have them as a BulletPoint FAQ type, then it would be more easy for quick reading.
For my projects I am always using Sqldatasource to connect tables and views for basic sql operations, which is easier to bind data to data presentation controls like FormView etc... and i never used stored procedures.
I realized that some developers keep of using sqldatasource and they are using ObjectDataSource and Stored Procedures for all sql operations and to bind data to data representation controls, which is time consuming to develop.
I have large database. Can u explain me which is the best method, using sqldatasource or ObjectDataSource and stored procedures?
Is my method degrade the performance of the product which have large database?
Thanks,
Pandiya Krishnan.
Remember to click “Mark as Answer” on the post, if it helps you.
Using stored procedures in itself does not neccessarily solve the problem of SQL Injection. There's a bit more to it than that, but I'd say that's beyond the scope of your article.
Member
77 Points
74 Posts
ADO.net FAQ collection
Apr 18, 2007 06:38 AM|dotnetguts|LINK
ADO.net is data access architecture for the Microsoft .NET Framework.
<!--[if !supportEmptyParas]--> <!--[endif]-->Difference between ADO and ADO.net- ADO used connected data usage, while ADO.net used disconnected data environment.
- ADO used OLE DB to access data and is COM-based, while ADO.net uses XML as the format for transmitting data to and from your database and web application.
- In ADO, Record set, is like a single table or query result, while in ADO.net Dataset, can contain multiple tables from any data source.
- In ADO, it is sometime problematic because firewall prohibits many types of request, while in ADO.net there is no such problem because XML is completely firewall-proof.<!--[if !supportEmptyParas]--> <!--[endif]-->
Difference between ADO.net Dataset and ADO RecordsetA DataSet can represent an entire relational database in memory, complete with tables, relations, and views.
· A DataSet is designed to work without any continuing connection to the original data source.
· Data in a DataSet is bulk-loaded, rather than being loaded on demand.
· There's no concept of cursor types in a DataSet.
· DataSets have no current record pointer You can use For Each loops to move through the data.
· You can store many edits in a DataSet, and write them to the original data source in a single operation.
· Though the DataSet is universal, other objects in ADO.NET come in different versions for different data sources.
FAQ collected from http://www.dng-ado.blogspot.com
ADO.net TermsFAQ collected from http://www.dng-ado.blogspot.com
Difference between Dataset and DataReader Object
Points to be consider while choosing between the DataSet and DataReader objects.DataSet object
DataReader object
Read/Write access
Read-only access
Supports multiple tables from different databases
Supports a single table based on a single SQL query of one database
Disconnected mode
Connected mode
Bind to multiple controls
Bind to a single control
Forward and backward scanning of data
Forward-only scanning of data
Slower access to data
Faster access to data
Greater overhead to enable additional features
Lightweight object with very little overhead
Supported by Visual Studio .NET tools
Must be manually coded
<!--[if !supportEmptyParas]-->
SQLDataReader makes exclusive use of connectionThe SQLDataReader object makes exclusive use of its SQLConnection object as long as it is open. You are not able to execute any other SqlCommand objects on that connection as long as the SQLDataReader object is open. Therefore, you should always call SQLDataReader.close() as soon as you are done retrieving data.
Strongly Typed Dataset ObjectStrongly typed Dataset object allows you to create early-bound data retrieval expression.
Advantage of Strongly Typed dataset
It is faster than late-bound data retrieval expression.
Its column name is shown in intellisense as you type code.
FAQ collected from http://www.dng-ado.blogspot.com
Improving Performance with Connection Pooling
Opening a connection is a database-intensive task. It can be one of the slowest operations that you perform in an ASP.NET page. Furthermore, a database has a limited supply of connections, and each connection requires a certain amount of memory overhead (approximately 40 kilobytes per connection).
If you plan to have hundreds of users hitting your Web site simultaneously, the process of opening a database connection for each user can have a severe impact on the performance of your Web site.
Fortunately, you can safely ignore these bad warnings if you take advantage of connection pooling. When database connections are pooled, a set of connections is kept open so that they can be shared among multiple users. When you request a new connection, an active connection is removed from the pool. When you close the connection, the connection is placed back in the pool.
Connection pooling is enabled for both OleDb and SqlClient connections by default.To take advantage of connection pooling, you must be careful to do two things in your ASP.NET pages. First, you must be careful to use the same exact connection string whenever you open a database connection. Only those connections opened with the same connection string can be placed in the same connection pool. For this reason you should place your connection string in the web.config file and retrieve it from this file whenever you need to open a connectionTo take advantage of connection pooling in your ASP.NET pages, you also must be careful to explicitly close whatever connection you open as quickly as possible. If you do not explicitly close a connection with the Close() method, the connection is never added back to the connection pool.
FAQ collected from http://www.dng-ado.blogspot.com
SQL Injection ProblemSQL injection is a strategy for attacking databases.
Example1:
The easiest way to prevent this sort of injection is to parse the SQL string and remove any occurrences of "--" before passing the statement. <!--[if !supportEmptyParas]--> <!--[endif]-->An ASP page asks the user for a name and a password, and then sends the following string to the database:
SELECT FROM users WHERE username = 'whatever' AND password = 'mypassword'
It seems safe, but it isn't. A user might enter something like this as her user name:
' OR 1>0 --
When this is plugged into the SQL statement, the result looks like this:
SELECT FROM users WHERE username = '' OR 1>0 -- AND password = ''
This injection comments out the password portion of the statement. It results in a list of all the names in the users table, so any user could get into your system.
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->
Example 2:
You also have to beware of injections that contain semicolons because semicolons delimit SQL statements. Think about the implications of a user name like this:
' OR 1>0 ; DELETE Customers ; --
There are numerous ways a malicious user might penetrate your system using SQL injection and various defenses, but the simplest approach is to avoid dynamic SQL. Instead, use stored procedures everywhere. Thanks to the way SQL passes parameters, injections such as those above will produce errors, and the stored procedure will not execute.
FAQ collected from http://www.dng-ado.blogspot.com
ado.net faq ado.net dataset datareader
http://dotnetguts.blogspot.com
Logon to
Member
503 Points
582 Posts
Re: ADO.net FAQ collection
Apr 22, 2007 10:08 PM|deepak.vasudevan|LINK
Deepak Vasudevan
http://www.lavanyadeepak.tk/
-------
Member
77 Points
74 Posts
Re: ADO.net FAQ collection
Apr 23, 2007 02:18 PM|dotnetguts|LINK
Thanks deepak, i would consider your point for my next submission.
Daily Free Code
http://dotnetguts.blogspot.com
Logon to
None
0 Points
2 Posts
Re: ADO.net FAQ collection
Apr 29, 2007 12:34 AM|vermdivya|LINK
Hi,
I am beginner to .net and was searching .net faq.
Your ado.net faq collection clears my lots of doubt and help me to gain knowledge thanks., i have throughly enjoyed your post.
--
Divya
Member
421 Points
118 Posts
Re: ADO.net FAQ collection
May 23, 2007 02:53 PM|hspharic|LINK
Hi,
Its really nice and useful FAQs. Could u pls explain the diff b/w the Executescalar and ExecuteOraclescalar, and more.
Thanks,
[Yes] C.Hari
Click Answered when you satisfied my replies.
Member
31 Points
25 Posts
Re: ADO.net FAQ collection
May 24, 2007 01:11 AM|vinayak_s_h|LINK
How best does Modeling help in ADO.NET
ado.net
as answer after your question is solved, Thanks
Member
72 Points
32 Posts
Re: ADO.net FAQ collection
May 25, 2007 11:47 PM|hunterx1983|LINK
great faq
thanks you
Member
77 Points
74 Posts
Re: ADO.net FAQ collection
Jun 01, 2007 03:47 PM|dotnetguts|LINK
You can find basic difference : http://dng-ado.blogspot.com/2007/05/what-is-difference-between.html
ExecuteOraclescalar is data provider specific and so it is more faster while working with oracle database.
DotNetGuts (DNG)
http://dotnetguts.blogspot.com
Daily Free Code ado.net faq ado.net
http://dotnetguts.blogspot.com
Logon to
Member
246 Points
119 Posts
Re: ADO.net FAQ collection
Aug 17, 2007 04:11 AM|Pandiya Krishnan|LINK
Hi
I am using asp.net 2.0 and sqlserver 2005.
For my projects I am always using Sqldatasource to connect tables and views for basic sql operations, which is easier to bind data to data presentation controls like FormView etc... and i never used stored procedures.
I realized that some developers keep of using sqldatasource and they are using ObjectDataSource and Stored Procedures for all sql operations and to bind data to data representation controls, which is time consuming to develop.
I have large database. Can u explain me which is the best method, using sqldatasource or ObjectDataSource and stored procedures?
Is my method degrade the performance of the product which have large database?
Thanks,
Pandiya Krishnan.
Member
131 Points
54 Posts
Re: ADO.net FAQ collection
Aug 20, 2007 07:30 AM|ViagraFalls|LINK
Using stored procedures in itself does not neccessarily solve the problem of SQL Injection. There's a bit more to it than that, but I'd say that's beyond the scope of your article.
Other than that, nice sumup :)
Member
77 Points
74 Posts
Re: ADO.net FAQ collection
May 10, 2008 01:50 PM|dotnetguts|LINK
Go through this article it will give better understanding how simpler and faster approach to create data access layer in your application.
ADO.Net daily Free Code c# Asp .net FAQ .net layered design
http://dotnetguts.blogspot.com
Logon to