private void MultiRead_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(ExampleDatabaseFile))
{
return;
}
//build connection string
cb.Clear(); //clear any existing settings.
cb.Uri = ExampleDatabaseFile;
cb.SchemaName = "db";
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(cb.ConnectionString))
{
cnn.Open();
Parallel.For(0, Environment.ProcessorCount, new ParallelOptions {
MaxDegreeOfParallelism = Environment.ProcessorCount
}, i => {
try
{
using (SqlDatabaseCommand command = new SqlDatabaseCommand())
{
command.Connection = cnn;
command.CommandText = "SELECT ProductId, ProductName FROM Products ORDER BY ProductId LIMIT 10 OFFSET " + (10 * i) + ";";
SqlDatabaseDataReader rd = command.ExecuteReader();
while (rd.Read())
{
Debug.Write(Thread.CurrentThread.ManagedThreadId + "\t");
for (int c = 0; c < rd.VisibleFieldCount; c++)
{
Debug.Write(rd.GetValue(c) + "\t");
}
Debug.WriteLine("");
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
});
}
}
static void MarsEnabled()
{
// MARS (MultipleActiveResultSets) can decrease read time when there are multiple queries
// It results in better performance since queries can be combined.
// Very useful for large forms and web pages which require data from multiple tables.
// Instead of running each query and processing results get all results at once.
if (string.IsNullOrWhiteSpace(ExampleDatabaseFile))
{
return;
}
//build connection string
cb.Clear(); //clear any previous settings
cb.Uri = "file://" + ExampleDatabaseFile; //Set the database file
cb.MultipleActiveResultSets = true; //We need multiple result sets
cb.ExtendedResultSets = false; //extended result set is false but can be set during command execution e.g. command.ExecuteReader(true)
cb.SchemaName = "db"; //schema name
//"SchemaName=db;uri=file://" + ExampleDatabaseFile + ";MultipleActiveResultSets=true;"
using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(cb.ConnectionString))
{
cnn.Open();
try
{
using (SqlDatabaseCommand command = new SqlDatabaseCommand())
{
command.Connection = cnn;
//execute two queries against two different tables.
//command.CommandText = "SELECT ProductId, ProductName FROM Products; SELECT CustomerId, LastName || ' , ' || FirstName FROM Customers LIMIT 10;";
// For easy to read above command can also be written as following:
command.CommandText = "SELECT ProductId, ProductName FROM Products LIMIT 10 OFFSET 10 ; "; // Query index 0
command.CommandText += "SELECT CustomerId, LastName || ',' || FirstName FROM Customers LIMIT 10 ; "; //Query index 1
SQLDatabaseResultSet[] cmdrs = command.ExecuteReader(false);// parameter bool type is for ExtendedResultSet
if ((cmdrs != null) && (cmdrs.Length > 0))
{
if (!string.IsNullOrWhiteSpace(cmdrs[0].ErrorMessage))
{
Console.WriteLine(cmdrs[0].ErrorMessage);
return;
}
//this loop is just an example how to loop through all rows and columns.
for (int r = 0; r < cmdrs[0].RowCount; r++) //loop through each row of result set query index zero ( 0 )
{
for (int c = 0; c < cmdrs[0].ColumnCount; c++)
{
//Console.WriteLine(cmdrs[0].Rows[r][c]);
}
}
//Loading data from products table which is at index 0 : cmdrs[0]
for (int r = 0; r < cmdrs[0].RowCount; r++) //loop through each row of result set index zero ( 0 ) which is products table
{
//cmdrs[0].Rows[r][0] : in Rows[r][0] r = row and [0] is column index.
Console.WriteLine(string.Format("{0} - {1}", cmdrs[0].Rows[r][0], cmdrs[0].Rows[r][1]));
}
//Loading data from customers table which is at index 1 cmdrs[1]
for (int r = 0; r < cmdrs[1].RowCount; r++) //loop through each row of result set index one ( 1 ) which is customers table
{
//cmdrs[0].Rows[r][0] : Rows[r][0] r = row and [0] is column index.
Console.WriteLine(string.Format("{0} - {1}", cmdrs[1].Rows[r][0], cmdrs[1].Rows[r][1]));
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
