Ejemplo n.º 1
0
        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
        }
Ejemplo n.º 2
0
        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();
            });
        }
Ejemplo n.º 3
0
        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();
            });
        }
Ejemplo n.º 4
0
        // 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();
            });
        }
Ejemplo n.º 5
0
        // 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();
        }
Ejemplo n.º 6
0
        // 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();
            });
        }
Ejemplo n.º 7
0
        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;
                }
            }
        }
Ejemplo n.º 8
0
        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");

        }
Ejemplo n.º 9
0
        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");
        }
Ejemplo n.º 10
0
        // 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");
        }
Ejemplo n.º 11
0
        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();
        }