基本的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";
}