public static void Main(string[] args) { var employees = new Employee[] { new Employee { Id = 10, FirstName = "John", LastName = "Doe", Age = 25 }, new Employee { Id = 20, FirstName = "Samantha", LastName = "Wells", Age = 35 }, new Employee { Id = 30, FirstName = "Michel", LastName = "Jackson", Age = 45 }, }; var query = DynamicLinqCompiler.Compile<Func<IQueryable<string>>>( "from e in $0 where e.Age > $1 select e.FirstName", employees.AsQueryable(), 30); foreach (var name in query()) { Console.WriteLine(name); } // Should produce: // Samantha // Michel }
static async Task Sample20Async() { var emp = new Employee() { ID = 4, CompanyID = 2, Name = "Merged", Phone = "777" }; await MappedCommand.MergeAsync(connStr, "Employee", emp, "ID"); // using transaction await SqlScope.UsingTransactionAsync(connStr, async scope => { emp.Name += "2"; await scope.CreateMerge("Employee", emp, "ID").ExecuteNonQueryAsync(); }); }
static async Task Sample17Async() { var emp = new Employee() { CompanyID = 2, Name = "New inserted", Phone = "111" }; await MappedCommand.InsertAsync(connStr, "Employee", emp, "ID"); var id = await MappedCommand.InsertAndGetIdAsync(connStr, "Employee", emp, "ID"); // using transaction await SqlScope.UsingTransactionAsync(connStr, async scope => { emp.Name += "1"; await scope.CreateInsert("Employee", emp, false, "ID").ExecuteNonQueryAsync(); var id1 = await scope.CreateInsert("Employee", emp, true, "ID").ExecuteScalarAsync(); }); }
// Merge static void Sample20() { var emp = new Employee() { ID = 2, CompanyID = 2, Name = "Merged", Phone = "444" }; MappedCommand.Merge(connStr, "Employee", emp, "ID"); // using transaction SqlScope.UsingTransaction(connStr, scope => { emp.Name += "1"; scope.CreateMerge("Employee", emp, "ID").ExecuteNonQuery(); }); }
// Getting IDataReader from IEnumerable<T> static void Sample14() { Employee[] newEmployees = new Employee[] { new Employee() { CompanyID = 1, Name = "New Employee1", Age = 23, StartWorking = DateTime.UtcNow }, new Employee() { CompanyID = 1, Name = "New Employee2", StartWorking = DateTime.UtcNow }, new Employee() { CompanyID = 2, Name = "New Employee1" } }; IDataReader dataReader1 = newEmployees.ToDataReader(); IDataReader dataReader2 = newEmployees.Select(x => new { companyid = x.CompanyID, x.Name, phone = "111-111-111", startWorking = x.StartWorking.HasValue ? x.StartWorking : DateTime.UtcNow, x.Age }).ToDataReader(); }
// Insert static void Sample17() { var emp = new Employee() { CompanyID = 2, Name = "New inserted", Phone = "111" }; MappedCommand.Insert(connStr, "Employee", emp, "ID"); var id = MappedCommand.InsertAndGetId(connStr, "Employee", emp, "ID"); // using transaction SqlScope.UsingTransaction(connStr, scope => { emp.Name += "1"; scope.CreateInsert("Employee", emp, false, "ID").ExecuteNonQuery(); var id1 = scope.CreateInsert("Employee", emp, true, "ID").ExecuteScalar(); }); }
static async Task Sample13Async() { Employee[] newEmployees = new Employee[] { new Employee() { CompanyID = 1, Name = "New Employee1", Age = 23, StartWorking = DateTime.UtcNow }, new Employee() { CompanyID = 1, Name = "New Employee2", StartWorking = DateTime.UtcNow }, new Employee() { CompanyID = 2, Name = "New Employee1" } }; var simpleQuery = "select * from Employee where Age is not null"; // mapped command var precompiled2 = MappedCommand.Compile<Employee>("update Employee set Age = Age + 1 where ID = @ID"); // using method SqlScope.UsingTransaction await SqlScope.UsingTransactionAsync(connStr, async scope => { // using simple command var arr = await scope.CreateSimple(simpleQuery).ExecuteQueryAsync<Employee>().ToArray(); // using precompiled mapped command Employee e = new Employee() { ID = 3 }; await scope.CreateMapped(precompiled2, e).ExecuteNonQueryAsync(); e.ID = 4; await scope.CreateMapped(precompiled2, e).ExecuteNonQueryAsync(); await newEmployees.WriteToServerAsync(scope.Transaction, "Employee"); }); // using external transaction using (SqlConnection conn = new SqlConnection(connStr)) { await conn.OpenAsync(); var tran = conn.BeginTransaction(); try { SqlScope scope = new SqlScope(tran); // using simple command var arr = await scope.CreateSimple(simpleQuery).ExecuteQueryAsync<Employee>().ToArray(); // using precompiled mapped command Employee e = new Employee() { ID = 3 }; await scope.CreateMapped(precompiled2, e).ExecuteNonQueryAsync(); e.ID = 4; await scope.CreateMapped(precompiled2, e).ExecuteNonQueryAsync(); await newEmployees.WriteToServerAsync(scope.Transaction, "Employee"); tran.Commit(); } catch { tran.Rollback(); throw; } } }
static async Task Sample12Async() { Employee[] newEmployees = new Employee[] { new Employee() { CompanyID = 1, Name = "New Employee12", Age = 23, StartWorking = DateTime.UtcNow }, new Employee() { CompanyID = 1, Name = "New Employee22", StartWorking = DateTime.UtcNow }, new Employee() { CompanyID = 2, Name = "New Employee12" } }; await newEmployees.Select(x => new { companyid = x.CompanyID, x.Name, phone = "111-111-111", startWorking = x.StartWorking.HasValue ? x.StartWorking : DateTime.UtcNow, x.Age }).WriteToServerAsync(new BulkOptions(1000, 100, SqlBulkCopyOptions.Default, FieldsSelector.Source, false, true), connStr, "Employee"); // using option createTable and checkTableIfNotExistsBeforeCreation: destination table will be created // if it was not exists await newEmployees.Select(x => new { companyid = x.CompanyID, x.Name, phone = "222-222-222", startWorking = x.StartWorking.HasValue ? x.StartWorking : DateTime.UtcNow, x.Age }).WriteToServerAsync(new BulkOptions(createTable: true, checkTableIfNotExistsBeforeCreation: true), connStr, "Emp2"); }
static async Task Sample11Async() { Employee[] newEmployees = new Employee[] { new Employee() { CompanyID = 1, Name = "New Employee11", Age = 23, StartWorking = DateTime.UtcNow }, new Employee() { CompanyID = 1, Name = "New Employee21", StartWorking = DateTime.UtcNow }, new Employee() { CompanyID = 2, Name = "New Employee11" } }; await newEmployees.WriteToServerAsync(connStr, "Employee"); }
// Bulk insert of entities to database static void Sample11() { Employee[] newEmployees = new Employee[] { new Employee() { CompanyID = 1, Name = "New Employee1", Age = 23, StartWorking = DateTime.UtcNow }, new Employee() { CompanyID = 1, Name = "New Employee2", StartWorking = DateTime.UtcNow }, new Employee() { CompanyID = 2, Name = "New Employee1" } }; newEmployees.WriteToServer(connStr, "Employee"); }
static async Task Sample9Async() { // simple command int newID1 = await SimpleCommand.ExecuteQueryFirstColumnAsync<int>(connStr, "insert into Employee (CompanyID, Name, Age) values (@p0, @p1, @p2); select cast(scope_identity() as int)", 2, "Mary Grant", 30).First(); // mapped command var emp = new Employee() { CompanyID = 2, Name = "Mary Grant", Age = 30 }; int newID2 = await MappedCommand.ExecuteQueryFirstColumnAsync<Employee, int>(connStr, "insert into Employee (CompanyID, Name, Age) values (@CompanyID, @Name, @Age); select cast(scope_identity() as int)", emp).First(); }