private static void DataReadMapping(SqlDatabase database, MethodType type) { IEnumerable<Employee> employeeData; switch (type) { case MethodType.One: { // Create an output row mapper that maps all properties based on the column names IRowMapper<Employee> mapper = MapBuilder<Employee>.BuildAllProperties(); // Create a stored procedure accessor that uses this output mapper var accessor = database.CreateSqlStringAccessor("SELECT * FROM Employees", mapper); // Execute the accessor to obtain the results employeeData = accessor.Execute(); break; } case MethodType.Two: { employeeData = database.ExecuteSqlStringAccessor<Employee>("SELECT * FROM Employees"); break; } case MethodType.Three: { IRowMapper<Employee> mapper = MapBuilder<Employee>.MapAllProperties() .MapByName(x => x.LastName) .DoNotMap(x => x.TitleOfCourtesy) .Map(x => x.City).ToColumn("City") .Map(x => x.HireDate).WithFunc(x => x.GetDateTime(x.GetOrdinal("HireDate"))) .Build(); var accessor = database.CreateSqlStringAccessor("SELECT * FROM Employees", mapper); employeeData = accessor.Execute(); break; } default: throw new NotSupportedException(); } // Perform a client-side query on the returned data var results = from employee in employeeData where employee.Country == "USA" orderby employee.EmployeeID select new { Name = employee.FirstName }; results.ToList().ForEach(obj => Console.WriteLine(obj.Name)); var products = database.ExecuteSprocAccessor<Product>("Ten Most Expensive Products"); products.ToList().ForEach(product => Console.WriteLine(product.TenMostExpensiveProducts, product.UnitPrice)); var sales = database.ExecuteSprocAccessor<Sale>("SalesByCategory", "Beverages", "1998"); sales.ToList().ForEach(sale => Console.WriteLine(sale.ProductName, sale.TotalPurchase)); }
public static TableInfo GetTableInfo(string tableName, string cs) { // string cs = @"Server=sam-cld-43089-2\DEV;Database=test;Trusted_Connection=True;"; // string tableName = "t"; string querycols = @"select column_name as Name,data_type as SQLType from information_schema.columns where TABLE_SCHEMA+'.'+table_name = '" + tableName + "' order by ordinal_position"; string queryPK = @"SELECT column_name as primarykeycolumn FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME and ku.TABLE_SCHEMA+'.'+ ku.table_name='"+tableName+"'"; var db = new SqlDatabase(cs); var cols = db.ExecuteSqlStringAccessor<ColumnData>(querycols).ToList(); var pkName = db.ExecuteScalar(CommandType.Text, queryPK); var pkNames = pkName.ToString(); var ti = new TableInfo(); ti.PK = cols.Single(x => x.Name == pkNames); var usualCols = cols.Where(x => x.Name != pkNames); ti.TableName = tableName; ti.Columns = new List<ColumnData>(cols); ti.UsualColumns = new List<ColumnData>(usualCols); return ti; }