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