tag:blogger.com,1999:blog-78397963674099304402024-02-20T09:14:45.927-08:00Ado.net FAQ<b>Ado.net FAQ, contains some commonly asked technical questions.</b> FAQ have been collected from various book, elearning tool, ebooks, newsgroup posts, various mailing lists, online forums. I have tried to give credit for source wherever possible, but If you think that you should be credited for an answer, please send email. <br><br>
<b><big>For .Net Articles, Links, Code and much more logon to <a href="http://www.DailyFreeCode.com">DailyFreeCode.com</a></big></b>Unknownnoreply@blogger.comBlogger18125tag:blogger.com,1999:blog-7839796367409930440.post-997942494757802352007-05-04T15:03:00.001-07:002007-05-04T15:03:43.431-07:00What is difference between ExecuteReader, ExecuteNonQuery and ExecuteScalar.<ul><li><b style="">ExecuteReader</b><span style=""> : Use for accessing data. It provides a forward-only, read-only, connected recordset.<o:p></o:p></span></li><li><b style="">ExecuteNonQuery</b><span style=""> : Use for data manipulation, such as Insert, Update, Delete.<o:p></o:p></span></li><li><b style="">ExecuteScalar </b><span style="">: Use for retriving 1 row 1 col. value., i.e. Single value.<span style=""> </span>eg: for retriving aggregate function.<span style=""> </span>It is faster than other ways of retriving a single value from DB.<o:p></o:p></span></li></ul>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7839796367409930440.post-29838911372111163392007-05-04T10:55:00.000-07:002007-05-04T10:56:39.275-07:00Improving Performance with Connection Pooling<p class="doctext">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).</p> <p class="doctext">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.</p> <p class="doctext">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.</p> <p class="doctext"><u>Connection pooling is enabled for both </u><tt><u><span style="font-size: 10pt;">OleDb</span></u></tt><u> and </u><tt><u><span style="font-size: 10pt;">SqlClient</span></u></tt><u> connections by default.<o:p></o:p></u></p> <p class="doctext"><u>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.<span style=""> </span>For this reason you should place your connection string in the </u><tt><u><span style="font-size: 10pt;">web.config</span></u></tt><u> file and retrieve it from this file whenever you need to open a connection<o:p></o:p></u></p> <p class="doctext">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 <tt><span style="font-size: 10pt;">Close()</span></tt> method, the connection is never added back to the connection pool.</p> <p class="doctext"><u>connection pooling options that you can add to the SQL Server connection string:<o:p></o:p></u></p> <ul><li><!--[if !supportLists]--><span style="font-size: 8pt; font-family: Wingdings;"><span style=""><span style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;"></span></span></span><!--[endif]--><tt><span style="font-size: 10pt;">Connection Lifetime</span></tt>— Destroys a connection after a certain number of seconds. The default value is 0, which indicates that connections should never be destroyed.</li><li><!--[if !supportLists]--><span style="font-size: 8pt; font-family: Wingdings;"><span style=""></span></span><tt><span style="font-size: 10pt;">Connection Reset</span></tt>— Indicates whether connections should be reset when they are returned to the pool. The default value is true.</li><li><!--[if !supportLists]--><span style="font-size: 8pt; font-family: Wingdings;"><span style=""></span></span><tt><span style="font-size: 10pt;">Enlist</span></tt>— Indicates whether a connection should be automatically enlisted in the current transaction context. The default value is true.</li><li><!--[if !supportLists]--><span style="font-size: 8pt; font-family: Wingdings;"><span style=""></span></span><tt><span style="font-size: 10pt;">Max Pool Size</span></tt>— The maximum number of connections allowed in a single connection pool. The default value is 100.</li><li><!--[if !supportLists]--><span style="font-size: 8pt; font-family: Wingdings;"><span style=""><span style="font-family: "Times New Roman"; font-style: normal; font-variant: normal; font-weight: normal; font-size: 7pt; line-height: normal; font-size-adjust: none; font-stretch: normal;"></span></span></span><!--[endif]--><tt><span style="font-size: 10pt;">Min Pool Size</span></tt>— The minimum number of connections allowed in a single connection pool. The default value is 0.</li><li><!--[if !supportLists]--><span style="font-size: 8pt; font-family: Wingdings;"><span style=""></span></span><tt><span style="font-size: 10pt;">Pooling</span></tt>— Determines whether connection pooling is enabled or disabled. The default value is true.</li></ul> <p style="margin: 0in 0in 0.0001pt;"><o:p> </o:p></p>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-7839796367409930440.post-66196681962108953112007-05-04T10:53:00.000-07:002007-05-04T10:54:24.889-07:00Strongly Typed Dataset Object<p class="MsoNormal">Strongly typed Dataset object allows you to create early-bound data retrieval expression.</p> <p style="font-style: italic;" class="MsoNormal">Advantage of Strongly Typed dataset</p> <ul><li>It is faster than late-bound data retrieval expression.</li><li><span style="font-size: 12pt; font-family: "Times New Roman";">Its column name is shown in intellisense as you type code. </span></li></ul>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7839796367409930440.post-37682063580652584572007-05-04T10:47:00.000-07:002007-05-04T10:51:39.364-07:00Difference between Dataset and DataReader : Points to be consider while choosing between the DataSet and DataReader objects<table class="MsoNormalTable" style="border: medium none ; margin-left: -0.05in; border-collapse: collapse;" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 14.35pt;"> <td style="border: 1pt solid windowtext; padding: 0in 5.4pt; width: 3.2in; height: 14.35pt; font-family: trebuchet ms; text-align: center;" valign="top" width="307"> <h4 style="margin-left: 0in;">DataSet object<o:p></o:p></h4> </td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0in 5.4pt; width: 225.1pt; height: 14.35pt; font-family: trebuchet ms; text-align: center;" valign="top" width="300"> <h4 style="margin-left: 0in;">DataReader object<o:p></o:p></h4> </td> </tr> <tr style="height: 11.95pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 3.2in; height: 11.95pt;" valign="top" width="307"> <p class="MsoNormal">Read/Write access<o:p></o:p></p> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 225.1pt; height: 11.95pt;" valign="top" width="300"> <p class="MsoNormal">Read-only access<o:p></o:p></p> </td> </tr> <tr style="height: 23.9pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 3.2in; height: 23.9pt;" valign="top" width="307"> <p class="MsoNormal">Supports multiple tables from different databases<o:p></o:p></p> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 225.1pt; height: 23.9pt;" valign="top" width="300"> <p class="MsoNormal">Supports a single table based on a single SQL query of one database<o:p></o:p></p> </td> </tr> <tr style="height: 11.95pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 3.2in; height: 11.95pt;" valign="top" width="307"> <p class="MsoNormal">Disconnected mode<o:p></o:p></p> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 225.1pt; height: 11.95pt;" valign="top" width="300"> <p class="MsoNormal">Connected mode<o:p></o:p></p> </td> </tr> <tr style="height: 11.95pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 3.2in; height: 11.95pt;" valign="top" width="307"> <p class="MsoNormal">Bind to multiple controls<o:p></o:p></p> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 225.1pt; height: 11.95pt;" valign="top" width="300"> <p class="MsoNormal">Bind to a single control<o:p></o:p></p> </td> </tr> <tr style="height: 11.95pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 3.2in; height: 11.95pt;" valign="top" width="307"> <p class="MsoNormal">Forward and backward scanning of data<o:p></o:p></p> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 225.1pt; height: 11.95pt;" valign="top" width="300"> <p class="MsoNormal">Forward-only scanning of data<o:p></o:p></p> </td> </tr> <tr style="height: 11.95pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 3.2in; height: 11.95pt;" valign="top" width="307"> <p class="MsoNormal">Slower access to data<o:p></o:p></p> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 225.1pt; height: 11.95pt;" valign="top" width="300"> <p class="MsoNormal">Faster access to data<o:p></o:p></p> </td> </tr> <tr style="height: 11.95pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 3.2in; height: 11.95pt;" valign="top" width="307"> <p class="MsoNormal">Greater overhead to enable additional features<o:p></o:p></p> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 225.1pt; height: 11.95pt;" valign="top" width="300"> <p class="MsoNormal">Lightweight object with very little overhead<o:p></o:p></p> </td> </tr> <tr style="height: 12.75pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; width: 3.2in; height: 12.75pt;" valign="top" width="307"> <p class="MsoNormal">Supported by Visual Studio .NET tools<o:p></o:p></p> </td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 225.1pt; height: 12.75pt;" valign="top" width="300"> <p class="MsoNormal">Must be manually coded<o:p></o:p></p> </td> </tr> </tbody></table>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-7839796367409930440.post-91765591385543055922007-05-03T10:52:00.000-07:002007-05-04T10:58:46.525-07:00SQL Injection Problem<p class="MsoNormal">SQL injection is a strategy for attacking databases.</p> <p class="MsoNormal"><u><span style="font-weight: bold;">Example1:</span><br /></u>An ASP page asks the user for a name and a password, and then sends the following string to the database:<br />SELECT FROM users WHERE username = 'whatever' AND password = 'mypassword'<br /><br />It seems safe, but it isn't. A user might enter something like this as her user name:<br />' OR 1>0 --<br /><br />When this is plugged into the SQL statement, the result looks like this:<br />SELECT FROM users WHERE username = '' OR 1>0 -- AND password = ''<br /><br />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.<br /><!--[if !supportLineBreakNewLine]--><br /><!--[endif]--></p> <p style="font-weight: bold;" class="MsoNormal"><u>The easiest way to prevent this sort of injection is to parse the SQL string and remove any occurrences of "--" before passing the statement. <o:p></o:p></u></p> <p class="MsoNormal"><o:p> </o:p></p> <p class="MsoNormal"><u style="font-weight: bold;">Example 2:</u><br />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:<br />' OR 1>0 ; DELETE Customers ; -- </p> <p class="MsoNormal"><br />There are numerous ways a malicious user might penetrate your system using SQL injection and various defenses, but <u style="font-weight: bold;">the simplest approach is to avoid dynamic SQL. Instead, use stored procedures everywhere</u>. Thanks to the way SQL passes parameters, injections such as those above will produce errors, and the stored procedure will not execute. <span style="font-size:14;"><o:p></o:p></span></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7839796367409930440.post-8438103505350890432007-04-07T10:38:00.000-07:002007-04-07T10:42:11.774-07:00Explain DataViewIt provides a means to filter and sort data within a data table.<br />Example:<br />DataView myDataView = new DataView(myDataSet.Tables["Customers"]);<br /><br />// Sort the view based on the FirstName column<br />myDataView.Sort = "CustomerID";<br /><br />// Filter the dataview to only show customers with the CustomerID of ALFKI<br />myDataView.RowFilter = "CustomerID='ALFKI'";Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7839796367409930440.post-69786873004158117312007-04-07T10:33:00.000-07:002007-04-07T10:38:20.428-07:00Explain DataAdapter ObjectIt 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.<br /><br />Example:<br />SqlDataAdapter daEmp = new SqlDataAdapter( "select EmpID, EmpName, Salary from Employees", conn);<br /><br /><strong>Fill Method</strong><br />It is used to populate dataset.<br />example: daEmp.Fill(dsEmp,"Employee");<br /><br /><strong>Update Method</strong><br />It is used to update database.<br />example: daEmp.Update(dsEmp,"Employee");Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7839796367409930440.post-10747460116998280542007-04-07T10:29:00.000-07:002007-04-07T10:32:50.005-07:00Explain DataSet ObjectDataset is a disconnected, in-memory representation of data. It can contain multiple data table from different database.<br /><br />They contain multiple Datatable objects, which contain columns and rows, just like normal data base tables. You can even define relations between tables to create parent-child relationships. <br /><br />Example<br />DataSet dsEmp = new DataSet();<br /><br />For more understanding look for DataAdapter ObjectUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-7839796367409930440.post-58403056541902206842007-04-07T10:22:00.000-07:002007-04-07T10:29:05.503-07:00Explain DataReader ObjectIt provides a forward-only, read-only, connected recordset.<br /><br />It is most efficient to use when data need not to be updated, and requires forward only traverse. In other words, it is the fastest method to read data.<br /><br />Example:<br /><ol><li>Filling dropdownlistbox. </li><li>Comparing username and password in database.</li></ol><p>SqlDataReader rdr = cmd.ExecuteReader();</p><p>//Reading data</p><p>while (rdr.Read())<br />{<br /></p><p>//Display data</p><p>string contact = (string)rdr["ContactName"];<br />string company = (string)rdr["CompanyName"];<br />string city = (string)rdr["City"];</p><p>}</p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7839796367409930440.post-78501704574156361842007-04-07T10:20:00.000-07:002007-04-07T10:22:09.923-07:00What is Command ObjectIt allows to manipulate database by executing stored procedure or sql statements.<br /><br />A SqlCommand object allows you to specify what type of interaction you want to perform with a data base. <br /><br />For example, you can do select, insert, modify, and delete commands on rows of data in a data base table.<br />SqlCommand cmd = new SqlCommand("select * from Employees", conn);Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7839796367409930440.post-58924189647159681192007-04-07T10:14:00.000-07:002007-04-07T10:20:10.815-07:00What is Connection ObjectIt establishes connection.<br />The connection helps identify the data base server, the data base name, user name, password, and other parameters that are required for connecting to the data base. <br /><br />Example:<br />SqlConnection conn = new SqlConnection( "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7839796367409930440.post-27462647837740443282007-04-07T10:13:00.000-07:002007-04-07T10:14:54.244-07:00What is Data ProviderA 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.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7839796367409930440.post-11424739177016468822007-04-07T10:09:00.000-07:002007-04-07T10:13:50.514-07:00What is Data SourceIt can be a database, text file, excel spread sheet or an XML file.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7839796367409930440.post-80772756158075021582007-04-07T10:07:00.000-07:002007-04-07T10:09:02.330-07:00Name ADO.NET Objects<ol><li>Connection Object</li><li>Command Object</li><li>DataReader Object</li><li>DataSet Object</li><li>DataAdapter Object</li></ol>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7839796367409930440.post-57267612250190229852007-04-07T10:03:00.001-07:002007-04-07T10:03:32.337-07:00Difference between ADO.net Dataset and ADO RecordsetA DataSet can represent an entire relational database in memory, complete with tables, relations, and views.<br />· A DataSet is designed to work without any continuing connection to the original data source.<br />· Data in a DataSet is bulk-loaded, rather than being loaded on demand.<br />· There's no concept of cursor types in a DataSet.<br />· DataSets have no current record pointer You can use For Each loops to move through the data.<br />· You can store many edits in a DataSet, and write them to the original data source in a single operation.<br />· Though the DataSet is universal, other objects in ADO.NET come in different versions for different data sources.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7839796367409930440.post-57468292405769353202007-04-07T10:00:00.000-07:002007-04-07T10:02:28.173-07:00Difference between ADO and ADO.net1. ADO used connected data usage, while ADO.net used disconnected data environment.<br />2. 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.<br />3. 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.<br />4. 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.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7839796367409930440.post-27307581903943705072007-04-07T09:57:00.000-07:002007-04-07T09:59:24.388-07:00What is ADO.net<p>ADO.net is data access architecture for the Microsoft .NET Framework.</p><p>ADO.NET is an object-oriented set of libraries that allows you to interact with data sources. Commonly, the data source is a data base, but it could also be a text file, an Excel spread sheet, or an XML file. </p>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-7839796367409930440.post-82404331481736339402007-03-03T10:57:00.000-08:002007-05-04T16:30:43.003-07:00Connection Open Error : SQLDataReader makes exclusive use of connection<p style="MARGIN: 0in 0in 0pt">The SQLDataReader object makes exclusive use of its SQLConnection object as long as it is open.<span style="font-size:0;"> </span>You are not able to execute any other SqlCommand objects on that connection as long as the SQLDataReader object is open.<span style="font-size:0;"> </span>Therefore, you should always call SQLDataReader.close() as soon as you are done retrieving data.</p>Unknownnoreply@blogger.com0