ExecuteNonQuery, ExecuteScalar, ExecuteReader

This blog explains you clearly about ExecuteNonQuery, ExecuteScalar and ExecuteReader.

ExecuteNonQuery

Executes a Transact-SQL statement against the connection and returns the number of rows affected.

ExecuteNonQuery() is one of the most frequently used method in SqlCommand Object and is used for executing statements that do not return result set.

You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

ExecuteScalar

Execute scalar is an Single value single column.

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value like sum(),max(),min(),avg()) from a database. This requires less code than using the ExecuteReader method, and then performing the operations that you need to generate the single value using the data returned by a SqlDataReader.

ExecuteReader

The ExecuteReader() in C# SqlCommand Object sends the SQL statements to the Connection Object and populate a SqlDataReader Object based on the SQL statement.

Execute Reader is an Read only forward only.

Do not use: when database query is going to provide for sure exactly 1 record. It may be getting record by its id (which is PK in the database) - GetOrderById and such. In this case use ExecuteNonQuery with output parameters.

Use: when database query is going to provide a set of records. It may be search or report.

Configuration Files in Asp.net

Differences between Machine.config, Web.config, App.config

Machine.config

• Machine.config file is at the highest level in the configuration hierarchy.
• The settings of Machine.config file are applied to the whole machine.
• Machine.config file settings are applied to all the web applications residing on the server.
• Machine.config is created when you install ASP.NET in your machine.
• The Machine.config file contains default and machine-specific values for all supported setting.
• Machine setting are normally controlled by the system admin and app should never be given write access to it. Only one Machine.config file exists on a server.
• This is also called machine level configuration file.

Web.config

• Web.config is created when you create a web application.
• The settings made in the Web.config file are applied to that particular web application.
• Web.config file is to override the settings from the Machine.config file.
• An application can override most default values stored in the Machine.config file by creating one or more Web.config files.
• Web.config is called application level configuration file.

App.Config

• App.Config is used for windows applications.
• App.Config file to be created by only for user not automatically. user can create more than one App.Config file and use in the application(Windows)
• When you build the application in vs.net, it will be automatically renamed to .exe.config and this file has to be delivered along with your application.

Machine.config is the file that has the master settings for the machine and affects not only asp.net but other .NET applications. It is advisable not to mess with it. Since the configuration settings cascade, it is advisable to use the application level web.config for app specific settings.