英文:
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 tableawait _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;");}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论