SQLDatabase.NET

SQLDatabaseResultSet

The SQLDatabaseResultSet class object returns results from SQL statements. The class is the primary mechanism to fetch responses from the library when using Multiple Active Result Sets.

Multiple Active Result Sets (MARS) is a feature that allow the execution of multiple sql statements as batches against a single connection and single command object. However MARS operations execute synchronously from first statement to last statement.

All valid statements can be used in MARS. The execution order can be checked, by using Position property of SQLDatabaseResultSet class.

The return is always an Array of SQLDatabaseResultSet[] as multiple result sets are returned. Unlike SqlDatabaseConnection, SqlDatabaseCommand the class starts with capital SQL instead of PascalCase the object name is SQLDatabaseResultSet.



Some properties contain data only when ExtendedResultSets is set to true. You must pass true or false in ExecuteReader(true), ExecuteNonQuery(true) and ExecuteScalar(true).

Property ExtendedResultSet Description
Position No

It provides the position of the query in execution order. e.g. 1 2 etc
SQLText Yes

The SQL statement which was executed.
Schemas Yes

Schema names in SQLText e.g. sales.Table, dbo.Table, sdbn.Table only sales, dbo, sdbn etc are shown. Schema's are in the order as they were in sql text.
Tables Yes

Table names without schema name in SQLText.
Parameters Yes

Parameters in the query, parameters can be used using @ sign.

ProcessingTime Yes

Processing time as long (Int64) in milliseconds for this particular query.
Columns No

Name of all the columns in this result set.
DataTypes No

Column data types as defined during table creation, null for views.
Rows No

Rows as List of object array. e.g. List < object[] >()
RowCount No

Total number of rows in the result set.
ColumnCount No

Total number of columns in the result set.
RowsAffected No

Number of affected rows by this particular query 0 for select and int for INSERT, UPDATE and DELETE statements.
ErrorMessage No

Empty if execution was success otherwise error message which occurred during execution of this SQLText..

 

string constr = "uri=file://Orders.db;MultipleActiveResultSets=true;";
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(constr))
{
	cnn.Open();
	using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
	{

// Two SELECT Statements.
cmd.CommandText = "SELECT * FROM Customers; Select * from Products;";

// Following is valid when using Linq
//List < SQLDatabaseResultSet > rs = cmd.ExecuteReader(true).ToList();

SQLDatabaseResultSet[] ResultSets = cmd.ExecuteReader(true);//true for ExtendedResultSets

if ( (ResultSets != null) && (ResultSets.Length > 0) )
{
foreach(SQLDatabaseResultSet ResultSet in ResultSets)
{
     // process result set here...
}
}
}
}