public static void InsertUpdateDeleteForOneRecord(DbDataAdapter dbAdapter)
{
// add new employee by POCO model
var newEmployee = new Employee()
{
EmployeeID = 1000, FirstName = "John", LastName = "Smith"
};
dbAdapter.Insert("Employees", newEmployee);
Console.WriteLine("Added new employee: John Smith (ID=1000)");
// add new employee by dictionary
dbAdapter.Insert("Employees", new Dictionary <string, object>()
{
{ "EmployeeID", 1001 },
{ "FirstName", "Jim" },
{ "LastName", "Gordon" }
});
Console.WriteLine("Added new employee: Jim Gordon (ID=1001)");
// update employee by poco model
newEmployee.FirstName = "Bart";
var newEmployeeByIdQuery = new Query("Employees", (QField)"EmployeeID" == (QConst)newEmployee.EmployeeID);
dbAdapter.Update(newEmployeeByIdQuery, newEmployee);
Console.WriteLine("New first name for EmployeeID=1000: {0}",
dbAdapter.Select(new Query("Employees", (QField)"EmployeeID" == (QConst)1000).Select("FirstName")).Single <string>());
// update employee by dictionary
dbAdapter.Update(
new Query("Employees", (QField)"EmployeeID" == (QConst)1001),
new Dictionary <string, object>()
{
{ "FirstName", "Bruce" },
{ "LastName", "Wayne" }
}
);
var employee_1001_data = dbAdapter.Select(new Query("Employees", (QField)"EmployeeID" == (QConst)1001)).ToDictionary();
Console.WriteLine("New name for EmployeeID=1001: {0} {1}", employee_1001_data["FirstName"], employee_1001_data["LastName"]);
// update only some fields from model
newEmployee.LastName = "Simpson";
newEmployee.FirstName = "Homer";
dbAdapter.Update(newEmployeeByIdQuery, new { LastName = "LastName" });
var newEmployeeNameFromDb = dbAdapter.Select(new Query(newEmployeeByIdQuery).Select("FirstName", "LastName")).ToDictionary();
Console.WriteLine("First+Last for EmployeeID=1000 after update: {0} {1}",
newEmployeeNameFromDb["FirstName"], newEmployeeNameFromDb["LastName"]);
}
static void Main(string[] args)
{
// configure ADO.NET and NReco.Data components
var dbFactory = new LoggingDbFactory(Microsoft.Data.Sqlite.SqliteFactory.Instance)
{
LastInsertIdSelectText = "SELECT last_insert_rowid()"
};
var dbCmdBuilder = new DbCommandBuilder(dbFactory);
var dbConn = dbFactory.CreateConnection();
dbConn.ConnectionString = "Data Source=" + Path.Combine(Directory.GetCurrentDirectory(), "northwind.db");
var dbAdapter = new DbDataAdapter(dbConn, dbCmdBuilder);
// lets perform some queries to illustrate that logging works
var employeesCnt = dbAdapter.Select(new Query("Employees").Select(QField.Count)).Single <int>();
dbConn.Open(); // open connection for transaction
try {
using (var tr = dbConn.BeginTransaction()) {
dbAdapter.Transaction = tr;
// some updates
dbAdapter.Insert("Employees", new {
EmployeeID = 1001,
FirstName = "Test",
LastName = "Test"
});
var deleted = dbAdapter.DeleteAsync(new Query("Employees", (QField)"EmployeeID" > (QConst)1000)).Result;
tr.Rollback(); // do not save these changes
dbAdapter.Transaction = null; // clear transaction context
}
} finally {
dbConn.Close();
}
Console.WriteLine("Press any key to continue...");
Console.ReadKey();
}