WebMatrix Database.Query with custom CommandTimeout
Consider the following TestDb with TestTable and Procedure
USE TestDb
GO
--DROP TABLE dbo.TestTable
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'TestTable')
BEGIN
CREATE TABLE dbo.TestTable
(
RecordId int NOT NULL IDENTITY(1,1) PRIMARY KEY
, StringValue varchar(50) NULL
, DateValue date NULL
, DateTimeValue datetime NULL
, MoneyValue money NULL
, DecimalValue decimal(19,4) NULL
, IntValue int NULL
, BitValue bit NOT NULL
)
INSERT INTO dbo.TestTable
SELECT 'Test', CAST(GETDATE() AS DATE), GETDATE(), 100.15, 100.0015, 100, 1
UNION SELECT NULL, NULL, NULL, NULL, NULL, NULL, 0
END
GO
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'Get_TestTable')
DROP PROCEDURE dbo.Get_TestTable
GO
CREATE PROCEDURE dbo.Get_TestTable (@RecordId int = NULL) AS WAITFOR DELAY '00:00:30'; SELECT * FROM dbo.TestTable WHERE RecordId = ISNULL(@RecordId,RecordId);
GO
EXEC dbo.Get_TestTable @RecordId = NULL
When using the built-in WebMatrix Database Query Helper, you can do the following:
@{
string errorMessage = String.Empty;
int? RecordId = null;
IEnumerable<dynamic> rowsTestTable = null;
try
{
using (Database db = Database.Open("TestDb"))
{
rowsTestTable = db.Query("EXEC dbo.Get_TestTable @RecordId=@0",RecordId);
}
}
catch (Exception ex)
{
errorMessage = ex.Message;
}
}
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title></title>
</head>
<body>
@if(errorMessage == String.Empty)
{
<table border="1">
<thead>
<tr>
<th>RecordId</th>
<th>StringValue</th>
<th>DateValue</th>
<th>DateTimeValue</th>
<th>MoneyValue</th>
<th>DecimalValue</th>
<th>IntValue</th>
<th>BitValue</th>
</tr>
</thead>
<tbody>
@foreach(var row in rowsTestTable)
{
<tr>
<td>@row["RecordId"]</td>
<td>@row["StringValue"]</td>
<td>@if(@row["DateValue"] != null){@Html.Raw(String.Format("{0:MM/dd/yyyy}",@row["DateValue"]));}</td>
<td>@if(@row["DateTimeValue"] != null){@Html.Raw(String.Format("{0:MM/dd/yyyy hh:mm:ss.fff tt}",@row["DateTimeValue"]));}</td>
<td>@if(@row["MoneyValue"] != null){@Html.Raw(String.Format("{0:c}",@row["MoneyValue"]));}</td>
<td>@row["DecimalValue"]</td>
<td>@row["IntValue"]</td>
<td>@row["BitValue"]</td>
</tr>
}
</tbody>
</table>
}
<p>@errorMessage</p>
<h4>No Additional Problem - On handling of DateValue</h4>
@try
{
foreach(var row in rowsTestTable)
{
<p>@if(row.DateValue != null){@Html.Raw(DateTime.Parse(row.DateValue.ToString()))}</p>
}
}
catch (Exception ex)
{
<p>@ex.Message</p>
}
<h4>No Additional Problem - On handling of MoneyValue (and other number values)</h4>
@try
{
foreach(var row in rowsTestTable)
{
<p>@if(row.MoneyValue != null){@Html.Raw(Double.Parse(row.MoneyValue.ToString()))}</p>
}
}
catch (Exception ex)
{
<p>@ex.Message</p>
}
</body>
</html>
This gives a timed out error as the WebMatrix Database.Query helper has a fixed default 30 seconds CommandTimeout. Is it possible to override the default for an individual request by about 5 minutes?
Unable to find a solution, I went down the path of creating my own SimpleQuery helper based on doing a lot of searching and testing things until I finally found a link that I could understand and adapt.
using System.Collections.Generic; // IEnumerable<dynamic>
using System.Data; // IDataRecord
using System.Data.SqlClient; // SqlConnection
using System.Dynamic; // DynamicObject
public class SimpleQuery
{
public static IEnumerable<dynamic> Execute(string connectionString, string commandString, int commandTimeout)
{
using (var connection = new SqlConnection(connectionString))
{
using (var command = new SqlCommand(commandString, connection))
{
command.CommandTimeout = commandTimeout;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
foreach (IDataRecord record in reader)
{
yield return new DataRecordDynamicWrapper(record);
}
}
connection.Close();
}
}
}
public class DataRecordDynamicWrapper : DynamicObject
{
private IDataRecord _dataRecord;
public DataRecordDynamicWrapper(IDataRecord dataRecord) { _dataRecord = dataRecord; }
public override bool TryGetMember(GetMemberBinder binder, out object result)
{
result = _dataRecord[binder.Name];
return result != null;
}
}
}
So now with changes to the web code to use the new SimpleQuery helper, I can get almost equivalent results, but with some problems
@{
string errorMessage = String.Empty;
int? RecordId = null;
IEnumerable<dynamic> rowsTestTable = null;
try
{
string commandString = String.Format("dbo.Get_TestTable @RecordId={0}", RecordId == null ? "null" : RecordId.ToString()); // Problem 1: Have to use String.Format to embed the Parameters
rowsTestTable = SimpleQuery.Execute(System.Configuration.ConfigurationManager.ConnectionStrings["TestDb"].ConnectionString,commandString,300);
foreach(var row in rowsTestTable) { break; } // Problem 2: Have to force query execution here, so the error (if any) gets trapped here
}
catch (Exception ex)
{
errorMessage = ex.Message;
}
}
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title></title>
</head>
<body>
@if(errorMessage == String.Empty)
{
<table border="1">
<thead>
<tr>
<th>RecordId</th>
<th>StringValue</th>
<th>DateValue</th>
<th>DateTimeValue</th>
<th>MoneyValue</th>
<th>DecimalValue</th>
<th>IntValue</th>
<th>BitValue</th>
</tr>
</thead>
<tbody>
@foreach(var row in rowsTestTable)
{
<tr>
@*<td>@row["RecordId"]</td>*@ <!-- Problem 3: Can't reference as row["FieldName"], so if any field names have spaces or other special characters, can't reference -->
<td>@row.RecordId</td>
<td>@row.StringValue</td>
<td>@if(@row.DateValue != null){@Html.Raw(String.Format("{0:MM/dd/yyyy}",@row.DateValue));}</td>
<td>@if(@row.DateTimeValue != null){@Html.Raw(String.Format("{0:MM/dd/yyyy hh:mm:ss.fff tt}",@row.DateTimeValue));}</td>
<td>@if(@row.MoneyValue != null){@Html.Raw(String.Format("{0:c}",@row.MoneyValue));}</td>
<td>@row.DecimalValue</td>
<td>@row.IntValue</td>
<td>@row.BitValue</td>
</tr>
}
</tbody>
</table>
}
<p>@errorMessage</p>
<h4>Additional Problem - Unexpected handling of DateValue</h4>
@try
{
foreach(var row in rowsTestTable)
{
<p>@if(row.DateValue != null){@Html.Raw(DateTime.Parse(row.DateValue.ToString()))}</p>
}
}
catch (Exception ex)
{
<p>@ex.Message</p>
}
<h4>Additional Problem - Unexpected handling of MoneyValue (and other number values)</h4>
@try
{
foreach(var row in rowsTestTable)
{
<p>@if(row.MoneyValue != null){@Html.Raw(Double.Parse(row.MoneyValue.ToString()))}</p>
}
}
catch (Exception ex)
{
<p>@ex.Message</p>
}
</body>
</html>
Issue 1-3 is commented out in the second web code that uses the SimpleQuery helper. This I can work around, but what I am still struggling with is why NULL validation is not detected for Number and Date values.
I would appreciate detecting them correctly, so I can avoid the subsequent error when using Double.Parse or DateTime.Parse. I would also like to appreciate any general pointers / improvements to the SimpleQuery helper or anything else you see.
Thanks in advance.
source to share
Using when using my helper SimpleQuery is working on detecting Null or String.Empty since the values from my helper when converted to ToString () come as String.Empty, when they come from Database.Query they return as NULL.
@try
{
foreach(var row in rowsTestTable)
{
<p>@if(!String.IsNullOrEmpty(row.DateValue.ToString())){@Html.Raw(DateTime.Parse(row.DateValue.ToString()))}</p>
}
}
catch (Exception ex)
{
<p>@ex.Message</p>
}
So, while it doesn't explain to me why there is a difference, or how to make my SimpleQuery helper more equivalent to Database.Query, it helps me overcome the nearest problem.
source to share