// Install as a Cake Addin<br/>#addin nuget:?package=JCoder.Db4Net.Sqlite&version=3.0.0<br/><br/>// Install as a Cake Tool<br/>#tool nuget:?package=JCoder.Db4Net.Sqlite&version=3.0.0
// Create a new instance.
IDbOperator _dber = new Sqliter()
{
ConnectionString = "Data Source=.\data\test.db3;"
};
// Use the global instance.
IDbOperator _dber = DbHub.GetOperOrNew<Sqliter>();
_dber.ConnectionString = "Data Source=.\data\test.db3;";
Sqliter使用
CanConnect（是否能够连接数据库）
// CanConnect（是否能够连接数据库）
if (_dber.CanConnect()) { return; }
// ExecuteTransaction（使用事务执行指定的SQL）
public void ExecuteTransaction()
{
_dber.StartTransaction();
try
{
for (int i = 0; i < 3; i++)
{
var _parameters = new List<DbParameter>()
{
_dber.CreateParameter("ID",i.ToString()),
_dber.CreateParameter("Name","Name123"),
_dber.CreateParameter("CreateTime",DateTime.Now),
};
var _sql = @"insert table1 (ID,Name,CreateTime) values (@ID,@Name,@CreateTime);";
var _isSucceed = _dber.ExecuteTransaction(_sql, _parameters);
}
}
catch (Exception ex)
{
_dber.RollbackTransaction();
return;
}
_dber.FinishTransaction();
}
ExecuteTransaction（使用指定的事务执行指定的SQL）
// ExecuteTransaction（使用指定的事务执行指定的SQL）
public void ExecuteTransaction()
{
for (int i = 0; i < 3; i++)
{
var _key = "Transaction" + i.ToString();
_dber.StartTransaction(_key);
var _id = i.ToString() + DateTime.Now.ToLongTimeString();
try
{
var _parameters = new List<DbParameter>()
{
_dber.CreateParameter("ID",_id),
_dber.CreateParameter("Name","Name123"),
_dber.CreateParameter("CreateTime",DateTime.Now),
};
var _sql = @"insert table1 (ID,Name,CreateTime) values (@ID,@Name,@CreateTime);";
var _isSucceed = _dber.ExecuteTransaction(_key, _sql, _parameters);
}
catch (Exception ex)
{
_dber.RollbackTransaction(_key);
return;
}
if (!_dber.FinishTransaction(_key))
{
return;
}
}
}
RollbackTransaction（回滚指定的事务）
// RollbackTransaction（回滚指定的事务）
public void RollbackTransaction()
{
var _key = "Transaction";
_dber.StartTransaction(_key);
var _id = nameof(RollbackTransaction);
try
{
var _parameters = new List<DbParameter>()
{
_dber.CreateParameter("ID",_id),
_dber.CreateParameter("Name","Name123"),
_dber.CreateParameter("CreateTime",DateTime.Now),
};
var _sql = @"insert table1 (ID,Name,CreateTime) values (@ID,@Name,@CreateTime);";
var _isSucceed = _dber.ExecuteTransaction(_key, _sql, _parameters);
}
catch (Exception ex)
{
_dber.RollbackTransaction(_key);
return;
}
_dber.RollbackTransaction(_key);
}
QueryTable（查询数据，并返回DataTable）
public DataTable QueryTable()
{
var _parameters = new List<DbParameter>()
{
_dber.CreateParameter("ID",123),
};
var _sql = "select * from table1 where ID=@ID;";
return _dber.QueryTable(_sql, _parameters);
}
QueryTables（查询数据，并返回DataSet）
public DataSet QueryTables()
{
var _parameters = new List<DbParameter>()
{
_dber.CreateParameter("ID",123),
};
var _sql = "select * from table1 where ID=@ID;";
_sql += "select * from table2 where ID=@ID;";
return _dber.QueryTables(_sql, _parameters);
}
QueryReader（查询数据，并返回DbDataReader）
public QueryReader QueryReader()
{
var _parameters = new List<DbParameter>()
{
_dber.CreateParameter("ID",123),
};
var _sql = "select * from table1 where ID=@ID;";
return _dber.QueryReader(_sql, _parameters);
}
QueryDictionary (将查询结果的首两列数据转换为Dictionary)
public Dictionary<string, string> QueryDictionary()
{
var _sql = "select Key,Value from table1 ;";
return _dber.QueryDictionary<string, string>(_sql);
}