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();
}
