How to use Multiple Active Result Sets with ADO.NET

Multiple Active Result Sets (MARS) is a feature supported by ADO.NET that allows the execution of multiple batches on a single connection. In previous versions, only one batch could be executed at a time against a single connection. When using a MARS-enabled connection, multiple logical batches can be executed on a single connection. Executing multiple batches with MARS does not imply simultaneous execution of operations.

To access multiple result sets using SqlDataReader objects, multiple SqlCommand objects will need to be used. When MARS is enabled, each command object used adds an additional session to the connection.

The following program demonstrates how to use a Sql Server Connection with MARS enabled.

Fig 1. MARS-enabled connection string
Fig 2. Data access class with two commands.
Fig 3. Main program.
Fig 4. Running the example.


Download example source code.

How to execute simple Database Queries with VB .NET

The SqlCommand class in the .NET Framework Data Provider has four methods that you can use to execute SQL
statements:

  1. ExecuteScalar: Executes a query that returns a single scalar value.
  2. ExecuteReader: Executes a query that returns a result set.
  3. ExecuteNonQuery: Executes a data update statements or a catalog update statement.
  4. ExecuteXmlReader: Executes a query that returns an Extensible Markup Language (XML) result set, this method is only avaliable in the SqlCommand class.

To execute a simple database query

  1. Import the System.Configuration namespace
  2. Use the ConfigurationManager.ConnectionStrings property to get a collection of connection strings from the application configuration file.
  3. Index into the collection of connection strings by using the programmatic name of the connection string you want to access.
  4. Use the ConnectionString property to get the connection string information.
  5. Create a connection object.
  6. Create a command object.
  7. If you want to execute an SQL statement, set the CommandType property of the command object to the
    CommandType.Text enumeration value. If you want to call a stored procedure, set the CommandType property of the command
    object to the CommandType.StoredProcedure enumeration value.
  8. Call the Open method on the connection object.
  9. Call the ExecuteScalar method on the command object. Assign the result to a suitably typed variable.
  10. Call the Close method on the connection object.

The following example shows how to execute a query to determine the number of products in the AdventureWorks2016CTP3 database
on the local SQL Server instance.

Fig 1. Main program
Fig 2. App config
Fig 3. Output program


Download example source code.

User Identification and Authentication with Transact-SQL

Doing user authentication in SQL Server can be customized; you can use all kinds of data from a database to authenticate users. Every application needs to deal with security, making sure that sensitive data cannot be accessed by the wrong users.
You can write your own custom logic to verify user names and passwords and make sure the information is stored.

Fig 1. The database diagram.

In the database under a secure account with a password that couldn’t easily be guessed by a user. The easiest way to accomplish this is to one-way encrypt user passwords on store procedure.

A simple but fully functional example is shown below.

Fig 2. The T-SQL code.

This code will insert one row, corresponding to the new user, in the users table.

The SQL Server way to store passwords is by wrapping them in a built-in encrypting function called HASHBYTES .

Download example source code.