英文:
Creating temporary table in Dapper ..net c#
问题
我试图使用Dapper创建一个临时表，但并不成功。我没有收到任何SQL异常或错误，连接已打开，但最终结果是"-1"，没有创建临时表。有人知道我在哪里错了吗？
谢谢
英文:
I'm trying to create a #temporary table using Dapper without much success. I'm not receiving any SQL exceptions or errors, the connection is opened but the endresult is "-1" and no temporary table is created. Anyone know where I am going wrong here?
Thanks
public abstract class DapperBaseRepository : IDisposable
{
private readonly IDbConnection _dbConnection;
protected DapperBaseRepository()
{
_dbConnection = new SqlConnection(configuration.GetConnectionString("DefaultConnection"));
}
public async Task<int> CreateTempTable()
{
string mappingTable = "[dbo].[#IngredientSubstitutionUpload]";
var query = @$"
CREATE TABLE {mappingTable}(
Row int NOT NULL,
OriginalIngredient nvarchar(255) NOT NULL,
OriginalSupplierCode nvarchar(255) NOT NULL,
ReplacementIngredient nvarchar(255) NOT NULL,
ReplacementSupplierCode nvarchar(255) NOT NULL)
";
await _dbConnection.ExecuteAsync(query); // return -1
// When attempting to insert here error is thrown: - Invalid object name '#IngredientSubstitutionUpload'.
// Insert into temporary table
await _dbConnection.ExecuteAsync(
$"INSERT INTO {mappingTable} VALUES (@row, @originalIngredient, @originalSupplierCode, @replacementIngredient, @replacementSupplierCode)",
substitutions.Select((x, idx) => new
{
row = idx,
originalIngredient = x.OriginalIngredient,
originalSupplierCode = x.OriginalSupplierCode,
replacementIngredient = x.ReplacementIngredient,
replacementSupplierCode = x.ReplacementSupplierCode
}));
}
public void Dispose()
{
_dbConnection.Dispose();
}
}
答案1
得分: 2
你需要在调用ExecuteAsync语句之前明确打开连接。否则，Dapper将在每次ExecuteAsync之后自动关闭连接，使每次调用都具有单独的会话（临时表将无法存活）。
示例取自https://www.learndapper.com/misc/temporary-table：
using (var connection = new SqlConnection(connectionString))
{
await conn.Open();
connection.Execute("CREATE TABLE #tmpOrder(orderId int);");
connection.Execute("INSERT INTO #tmpOrder(orderId) VALUES (1);");
return connection.Query<int>("SELECT * FROM #tmpOrder;");
}
英文:
You need to explicitly open the connection before calling the ExecuteAsyc statements. Otherwise Dapper will auto-close the connection after each ExecuteAsync giving each call a separate session (which the temp table will not survive).
Sample taken from https://www.learndapper.com/misc/temporary-table:
using (var connection = new SqlConnection(connectionString))
{
await conn.Open();
connection.Execute(@"CREATE TABLE #tmpOrder(orderId int);");
connection.Execute(@"INSERT INTO #tmpOrder(orderId) VALUES (1);");
return connection.Query<int>(@"SELECT * FROM #tmpOrder;");
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论