Dapper now (and for quite some time) deals with this internally. It just works™ Original (outdated) answer: You aren’t wrong. The reason I hadn’t noticed this inconvenience is that for legacy reasons (specifically: we used to use LINQ-to-SQL exclusively) our primary connection-like-thing is a DataContext – so we re-expose the dapper methods as extension methods … Read more
The best I was able to achieve was 50k records in 4 seconds using this approach SqlTransaction trans = connection.BeginTransaction(); connection.Execute(@” insert Member(Username, IsActive) values(@Username, @IsActive)”, members, transaction: trans); trans.Commit();
It does support input/output parameters (including RETURN value) if you use DynamicParameters, but in this case the simpler option is simply: var id = connection.QuerySingle<int>( @” INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff); SELECT CAST(SCOPE_IDENTITY() as int)”, new { Stuff = mystuff}); Note that on more recent versions of SQL Server (2005+) you can use the … Read more
We are looking at building a few helpers, still deciding on APIs and if this goes in core or not. See: https://code.google.com/archive/p/dapper-dot-net/issues/6 for progress. In the mean time you can do the following val = “my value”; cnn.Execute(“insert into Table(val) values (@val)”, new {val}); cnn.Execute(“update Table set val = @val where Id = @id”, new … Read more
This post shows how to query a highly normalised SQL database, and map the result into a set of highly nested C# POCO objects. Ingredients: 8 lines of C#. Some reasonably simple SQL that uses some joins. Two awesome libraries. The insight that allowed me to solve this problem is to separate the MicroORM from … Read more
QueryMultiple supports the ability to deal with multiple result sets. The only design restriction we added was totally disabling buffering for the grid reader. This means the whole API is streaming. In the simplest case you can use: var grid = connection.QueryMultiple(“select 1 select 2”); grid.Read<int>().First().IsEqualTo(1); grid.Read<int>().First().IsEqualTo(2); In the slightly more sophisticated case you can … Read more
Sure, a function to create and dispose your Connection will work great. protected void Execute(Action<IDbConnection> query) { using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings[“myDB”].ConnectionString)) { query.Invoke(db); } } And your simplified call site: public void SaveCustomer(CustomerDTO custDTO) { Execute(db => db.Execute(saveCustSp, custDTO, CommandType.StoredProcedure)); } With Return Values: public T Get<T>(Func<IDbConnection, T> query) { using (IDbConnection db … Read more
There is now (n Dapper 1.26 and higher) direct support for table-valued parameters baked into dapper. In the case of stored procedures, since the data type is built into the sproc API, all you need to do is supply a DataTable: var data = connection.Query<SomeType>(…, new { id=123, name=”abc”, values = someTable }, …); For … Read more
In the simple case you can do: var user = cnn.Query<User>(“spGetUser”, new {Id = 1}, commandType: CommandType.StoredProcedure).First(); If you want something more fancy, you can do: var p = new DynamicParameters(); p.Add(“@a”, 11); p.Add(“@b”, dbType: DbType.Int32, direction: ParameterDirection.Output); p.Add(“@c”, dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); cnn.Execute(“spMagicProc”, p, commandType: CommandType.StoredProcedure); int b = p.Get<int>(“@b”); int c = p.Get<int>(“@c”); … Read more
Alternatively, you can use one query with a lookup: var lookup = new Dictionary<int, Course>(); conn.Query<Course, Location, Course>(@” SELECT c.*, l.* FROM Course c INNER JOIN Location l ON c.LocationId = l.Id “, (c, l) => { Course course; if (!lookup.TryGetValue(c.Id, out course)) lookup.Add(c.Id, course = c); if (course.Locations == null) course.Locations = new List<Location>(); … Read more