Dapper1.5学习笔记1

基本的Execute操作

注：ConnectViaOledb() 返回一个连接对象

获取一个连接的方法

protected static SqliteConnection GetSqliteConnection(bool open = true)
        {
            var connection = new SqliteConnection("Data Source=:memory:");
            if (open) connection.Open();
            return connection;
        }

1，插入单条数据

 public void PseudoPositionalParameters_ExecSingle()
        {
            using (var connection = <span style="font-family: Arial, Helvetica, sans-serif;">GetSqliteConnection</span>( true )  )
            {
                var data = new { x = 6 };
                connection.Execute("create table #named_single(val int not null)");
                int count = connection.Execute("insert #named_single (val) values (?x?)", data);
                int sum = (int)connection.ExecuteScalar("select sum(val) from #named_single");
                count.IsEqualTo(1);
                sum.IsEqualTo(6);
            }
        }

2，插入多条数据

 public void PseudoPositionalParameters_ExecMulti()
        {
            using (var connection = <span style="font-family: Arial, Helvetica, sans-serif;">GetSqliteConnection</span><span style="font-family: Arial, Helvetica, sans-serif;">( true )  </span><span style="font-family: Arial, Helvetica, sans-serif;">)</span>
            {
                var data = new[]
                {
                    new { x = 1, y = 1 },
                    new { x = 3, y = 1 },
                    new { x = 6, y = 1 },
                };
                connection.Execute("create table #named_multi(val int not null)");
                int count = connection.Execute("insert #named_multi (val) values (?x?)", data);
                int sum = (int)connection.ExecuteScalar("select sum(val) from #named_multi");
                count.IsEqualTo(3);
                sum.IsEqualTo(10);
            }
        }

3，查询

 public void Issue466_SqliteHatesOptimizations()
        {
            using (var connection = <span style="font-family: Arial, Helvetica, sans-serif;"> </span><span style="font-family: Arial, Helvetica, sans-serif;">GetSqliteConnection</span><span style="font-family: Arial, Helvetica, sans-serif;">( true )  </span><span style="font-family: Arial, Helvetica, sans-serif;">)</span>

            {
                SqlMapper.ResetTypeHandlers();
                var row = connection.Query<HazNameId>("select 42 as Id").First();
                row.Id.IsEqualTo(42);
                row = connection.Query<HazNameId>("select 42 as Id").First();
                row.Id.IsEqualTo(42);

                SqlMapper.ResetTypeHandlers();
                row = connection.QueryFirst<HazNameId>("select 42 as Id");
                row.Id.IsEqualTo(42);
                row = connection.QueryFirst<HazNameId>("select 42 as Id");
                row.Id.IsEqualTo(42);
            }
        }

4，异步查询操作

  public async Task Issue466_SqliteHatesOptimizations_Async()
        {
            using (var connection = <span style="font-family: Arial, Helvetica, sans-serif;"> </span><span style="font-family: Arial, Helvetica, sans-serif;">GetSqliteConnection</span><span style="font-family: Arial, Helvetica, sans-serif;">( true )  </span><span style="font-family: Arial, Helvetica, sans-serif;">)</span>

            {
                SqlMapper.ResetTypeHandlers();
                var row = (await connection.QueryAsync<HazNameId>("select 42 as Id")).First();
                row.Id.IsEqualTo(42);
                row = (await connection.QueryAsync<HazNameId>("select 42 as Id")).First();
                row.Id.IsEqualTo(42);

                SqlMapper.ResetTypeHandlers();
                row = await connection.QueryFirstAsync<HazNameId>("select 42 as Id");
                row.Id.IsEqualTo(42);
                row = await connection.QueryFirstAsync<HazNameId>("select 42 as Id");
                row.Id.IsEqualTo(42);
            }
        }

5，执行单个操作

 private void Isse467_SqliteParameterNaming(bool prefix)
        {
            using (var connection =<span style="font-family: Arial, Helvetica, sans-serif;"> </span><span style="font-family: Arial, Helvetica, sans-serif;">GetSqliteConnection</span><span style="font-family: Arial, Helvetica, sans-serif;">( true )  </span><span style="font-family: Arial, Helvetica, sans-serif;">)</span>

            {
                var cmd = connection.CreateCommand();
                cmd.CommandText = "select @foo";
#if NET40 || NET45
                const DbType type = DbType.Int32;
#else
                const SqliteType type = SqliteType.Integer;
#endif
                cmd.Parameters.Add(prefix ? "@foo" : "foo", type).Value = 42;
                var i = Convert.ToInt32(cmd.ExecuteScalar());
                i.IsEqualTo(42);
            }
        }

6，单个查询操作query

        public void GetOnlyProperties()
        {
            using ( var connection = GetSqliteConnection( true )  )
            {

              var obj = connection.QuerySingle<HazGetOnly>("select 42 as [Id], 'def' as [Name];");
              obj.Id.IsEqualTo(42);
              obj.Name.IsEqualTo("def");
            }
        }
        class HazGetOnly
        {
            public int Id { get; }
            public string Name { get; } = "abc";
        }