WebMatrix数据库.使用自定义CommandTimeout进行查询
本文关键字：查询 CommandTimeout 自定义 数据库 WebMatrix | 更新日期: 2023-09-27 18:27:22
考虑以下带有TestTable和Procedure 的TestDb
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
当使用WebMatrix内置的数据库查询助手时，您可以执行以下操作：
@{
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>
由于WebMatrix Database.Query帮助程序已修复默认的30秒CommandTimeout，因此这会产生超时过期错误是否有任何方法可以将单个查询的默认值覆盖到大约5分钟
由于没有找到解决方案，我在大量搜索的基础上创建了自己的SimpleQuery助手，并进行了尝试，直到我最终找到了一个能够理解和适应的代码引用。
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;
}
}
}
因此，现在通过更改web代码来使用新的SimpleQuery助手，我可以获得几乎相同的结果，但存在一些问题
@{
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>
问题1-3在使用SimpleQuery助手的第二个web代码中进行了注释。我可以解决这些问题，但我仍然在努力解决为什么没有检测到Number和Date值的NULL检查。
我希望能帮助正确地检测这些，这样我就可以避免在使用Double.Parse或DateTime.Parse时出现后续错误。我也希望SimpleQuery助手或您看到的任何其他东西能有任何通用的指针/改进。
提前谢谢。
您可以尝试切换到使用Dapper。它的语法与WebMatrix非常相似。Data可以以IEnumerable<dynamic>或强类型返回结果（如果您愿意），并允许您根据每个查询覆盖命令超时。
https://github.com/StackExchange/dapper-dot-net
在使用我的SimpleQuery Helper时使用以下内容可以检测Null或String.Empty，因为当我的Helper转换为ToString（）时，其值将以String.Empy形式出现，而来自Database.Query时，它们将以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>
}
因此，虽然这并不能向我解释为什么存在差异，也不能解释如何使SimpleQueryHelper更等效于Database.Query，但它确实帮助我克服了眼前的问题。