public void TransferBelowMaxAmount()
{
accountManager.DoTransfer(217, 217);
//asserts to read from db...
int numCreditRecords = (int)adoTemplateCredit.ExecuteScalar(CommandType.Text, "select count(*) from Credits");
int numDebitRecords = (int)adoTemplateDebit.ExecuteScalar(CommandType.Text, "select count(*) from Debits");
Assert.AreEqual(1, numCreditRecords);
Assert.AreEqual(1, numDebitRecords);
}
public object ExecuteScalar(string cmdText)
{
try
{
return(_AdoTemplate.ExecuteScalar(CommandType.Text, cmdText));
}
catch (Exception e)
{
dsExceptionHandler.HandleException(e);
ReConnection(e);
throw e;// new Goodway.CommonModel.Exception.DbException(DbResult.SqlError, e.Message ?? "" + Environment.NewLine + e.StackTrace ?? "", cmdText);
}
}
/// <summary>
/// 分页最好是通过存储过程去分页取数据，因为数据库sql语法不一样
/// 这里只支持了mysql分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="selectSql"></param>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PagedList <T> GetPageList <T>(int pageIndex, int pageSize, string orderBy)
{
Type type = typeof(T);
List <PropertyInfo> propertys = type.GetProperties().ToList();
var attribute = type.GetCustomAttributes(typeof(TableMapAttribute), false).FirstOrDefault();
if (attribute == null)
{
throw new Exception("类" + type.Name + "必须添加'TableMapAttribute'属性");
}
string tableName = ((TableMapAttribute)attribute).TableName;
string sql = "select * from " + tableName + " where 1=1";
//对于有IsDelete属性的，过滤已删除的数据
if (propertys.Exists(p => p.Name.ToLower() == "isdelete"))
{
sql += " and IsDelete=0 ";
}
if (!string.IsNullOrEmpty(orderBy))
{
sql += " order by " + orderBy;
}
int totalItemCount = (int)AdoTemplate.ExecuteScalar(CommandType.Text, "select count(1) from (" + sql + ") as A");
sql += " limit " + (pageIndex - 1) * pageSize + "," + pageSize + "";
DataTable dt = AdoTemplate.DataTableCreate(CommandType.Text, sql);
T[] list = ObjectHelper.CopyToObjects <T>(dt);
PagedList <T> pageList = new PagedList <T>(list, pageIndex, pageSize, totalItemCount);
return(pageList);
}
public string GetSysSheepfoldId(string administrator, string operatorId)
{
string sql = "select sf.\"Id\" from \"T_Sheepfold\" sf where sf.\"SysFlag\"=true";
object obj = AdoTemplate.ExecuteScalar(CommandType.Text, sql);
string id;
if (obj == null)
{
id = Guid.NewGuid().ToString();
sql = "INSERT into \"T_Sheepfold\" (\"Id\",\"Name\",\"Administrator\",\"OperatorId\",\"CreateTime\",\"SysFlag\",\"Remark\")VALUES(:id,:name,(select u.\"Id\" from \"T_User\" u where u.\"UserName\"='admin'),:operatorId,:createTime,:sysFlag,:remark)";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
pms.AddWithValue("name", "系统预留");
pms.AddWithValue("operatorId", operatorId);
pms.AddWithValue("createTime", DateTime.Now);
pms.AddWithValue("sysFlag", true);
pms.AddWithValue("remark", "系统预留，虚拟圈舍，死亡或者出售");
AdoTemplate.ExecuteNonQuery(CommandType.Text, sql, pms);
}
else
{
id = obj.ToString();
}
return(id);
}
public void OracleTest()
{
//Data Source=XE;User ID=hr;Unicode=True
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("System.Data.OracleClient");
dbProvider.ConnectionString = "Data Source=XE;User ID=hr;Password=hr;Unicode=True";
AdoTemplate adoTemplate = new AdoTemplate(dbProvider);
decimal count = (decimal)adoTemplate.ExecuteScalar(CommandType.Text, "select count(*) from emp");
Assert.AreEqual(14, count);
EmpProc empProc = new EmpProc(dbProvider);
IDictionary dict = empProc.GetEmployees();
foreach (DictionaryEntry entry in dict)
{
Console.WriteLine("Key = " + entry.Key + ", Value = " + entry.Value);
}
IList employeeList = dict["employees"] as IList;
foreach (Employee employee in employeeList)
{
Console.WriteLine(employee);
}
}
public int GetCount(int lowerAgeLimit, string name)
{
IDbParameters dbParameters = AdoTemplate.CreateDbParameters();
dbParameters.Add("age", DbType.Int32).Value = lowerAgeLimit;
dbParameters.Add("name", DbType.String).Value = name;
return((int)AdoTemplate.ExecuteScalar(CommandType.Text, "SELECT COUNT(*) FROM TestObjects where age > @age and name = @name", dbParameters));
}
public int Insert(MyTableItem item)
{
var param = AdoTemplate.CreateDbParameters();
param.AddWithValue("name", item.Name).DbType = System.Data.DbType.String;
param.AddWithValue("age", item.Age).DbType = System.Data.DbType.Int32;
return((int)AdoTemplate.ExecuteScalar(System.Data.CommandType.Text, "INSERT INTO MyTable (Name, Age) VALUES (@name, @age);SELECT CAST(scope_identity() AS int)", param));
}
public int GetUserCount(string id)
{
string sql = "select count(1) from T_user where id=@id";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
return(Convert.ToInt32(AdoTemplate.ExecuteScalar(CommandType.Text, sql, pms)));
}
public int GetDutyCountByName(string name)
{
string sql = "select * from \"T_Duty\" where \"Name\"=:name";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("name", name);
return(Convert.ToInt32(AdoTemplate.ExecuteScalar(CommandType.Text, sql, pms)));
}
public int GetUserCount(string userName)
{
string sql = "select count(\"Id\") from \"T_User\" where \"UserName\"=:userName";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("userName", userName);
object obj = AdoTemplate.ExecuteScalar(CommandType.Text, sql, pms);
return(obj == null ? 0 : int.Parse(obj.ToString()));
}
public bool IsFormulaNutrientEditable(string id)
{
string sql = "select \"IsEditable\" from \"T_FormulaNutrient\" where \"Id\"=:id";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
object obj = AdoTemplate.ExecuteScalar(CommandType.Text, sql, pms);
return(obj == null ? false : bool.Parse(obj.ToString()));
}
public bool IsSheepExist(string id)
{
string sql = "SELECT count(s.\"Id\") FROM \"T_Sheep\" s where s.\"Id\"=:id";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
object obj = AdoTemplate.ExecuteScalar(CommandType.Text, sql, pms);
return(obj == null ? false : Convert.ToInt32(obj.ToString()) == 1);
}
public void DataSetDemo()
{
CustomerDataSetDao customerDataSetDao = ctx["customerDataSetDao"] as CustomerDataSetDao;
DataSet dataSet = customerDataSetDao.GetCustomers();
Assert.AreEqual(91, dataSet.Tables["Table"].Rows.Count);
dataSet = customerDataSetDao.GetCustomers("Customers");
Assert.AreEqual(91, dataSet.Tables["Customers"].Rows.Count);
dataSet = customerDataSetDao.GetCustomersLike("M%");
Assert.AreEqual(12, dataSet.Tables["Table"].Rows.Count);
dataSet = new DataSet();
dataSet.Locale = CultureInfo.InvariantCulture;
customerDataSetDao.FillCustomers(dataSet);
Assert.AreEqual(91, dataSet.Tables["Customers"].Rows.Count);
AddAndEditRow(dataSet);
try
{
customerDataSetDao.UpdateWithCommandBuilder(dataSet);
int count =
(int)adoTemplate.ExecuteScalar(CommandType.Text, "select count(*) from Customers where CustomerId = 'NewID'");
Assert.AreEqual(1, count);
count = (int)adoTemplate.ExecuteScalar(CommandType.Text, "select count(*) from Customers where CustomerId = 'ALFKI' and Phone = '030-0074322'");
Assert.AreEqual(1, count);
}
finally
{
//revert changes
adoTemplate.ExecuteNonQuery(CommandType.Text, "delete from Customers where CustomerId = 'NewID'");
adoTemplate.ExecuteNonQuery(CommandType.Text,
"update Customers SET Phone='030-0074321' where CustomerId = 'ALFKI'");
}
}
public int GetSheepCountBySerialNum(string serialNumber)
{
string sql = "select count(1) from \"T_Sheep\" s where s.\"SerialNumber\"=:serialNumber";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("serialNumber", serialNumber);
return(Convert.ToInt32(AdoTemplate.ExecuteScalar(CommandType.Text, sql, pms)));
}
public object GetlatestDeliveryDateBySheepId(string sheepId)
{
string sql = "select MAX(\"DeliveryDate\") from \"T_Delivery\" where \"SheepId\"=:sheepId and \"IsDel\"=false";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("sheepId", sheepId);
return(AdoTemplate.ExecuteScalar(CommandType.Text, sql, pms));
}
public object GetLatestAbortionDateBySheepId(string sheepId)
{
string sql = "SELECT MAX(\"AbortionDate\") from \"T_Abortion\" where \"SheepId\"=:sheepId and \"IsDel\"=false";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("sheepId", sheepId);
return(AdoTemplate.ExecuteScalar(CommandType.Text, sql, pms));
}
public decimal GetBalanceById(string id)
{
string sql = "select balance from T_bookkeeping where id=@id";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("id", id);
object obj = AdoTemplate.ExecuteScalar(CommandType.Text, sql, pms);
return(Convert.ToDecimal(obj));
}
public string Login(string userName, string password)
{
string sql = "select \"Id\" from \"T_User\" u where u.\"UserName\"=:userName and u.\"Password\"=:password and u.\"IsEnabled\"=true";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("userName", userName);
pms.AddWithValue("password", GetPwd(password));
object result = AdoTemplate.ExecuteScalar(CommandType.Text, sql, pms);
return(result == null ? string.Empty : result.ToString());
}
public int GetUserCount(string password, string id)
{
string sql = "select count(\"Id\") from \"T_User\" where \"Id\"=:id and \"Password\"=:password";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("password", GetPwd(password));
pms.AddWithValue("id", id);
return(Convert.ToInt32(AdoTemplate.ExecuteScalar(CommandType.Text, sql, pms)));
}
public void ProductDaoTests()
{
Assert.AreEqual(830, orderDao.GetAll().Count);
Order order = new Order();
Customer customer = customerDao.Get("PICCO");
order.Customer = customer;
order.ShipCity = "New York";
orderDao.Save(order);
int orderId = order.Id;
order = orderDao.Get(orderId);
Assert.AreEqual("PICCO", order.Customer.Id);
Assert.AreEqual("New York", order.ShipCity);
//SessionFactoryUtils.GetSession(sessionFactory, true).Flush();
// Required trip to db to get idendity column, so data is visible
int ordersCount = (int)AdoTemplate.ExecuteScalar(CommandType.Text, "select count(*) from Orders");
Assert.AreEqual(831, ordersCount);
Assert.AreEqual(831, orderDao.GetAll().Count);
order.ShipCity = "Sao Paulo";
orderDao.Update(order);
order = orderDao.Get(orderId);
Assert.AreEqual("PICCO", order.Customer.Id);
Assert.AreEqual("Sao Paulo", order.ShipCity);
orderDao.Delete(order);
//Without flushing, nothing changes in the database:
SessionFactoryUtils.GetSession(sessionFactory, true).Flush();
ordersCount = (int)AdoTemplate.ExecuteScalar(CommandType.Text, "select count(*) from Orders");
Assert.AreEqual(830, ordersCount);
try
{
order = orderDao.Get(orderId);
Assert.Fail("Should have thrown HibernateObjectRetrievalFailureException when finding order with Id = " + orderId);
}
catch (HibernateObjectRetrievalFailureException e)
{
Assert.AreEqual("Order", e.PersistentClassName);
}
}
public void CustomerDaoTests()
{
Assert.AreEqual(91, customerDao.GetAll().Count);
Customer c = new Customer(new DefaultCustomerClassificationCalculator());
c.Id = "MPOLL";
c.CompanyName = "Interface21";
customerDao.Save(c);
c = customerDao.Get("MPOLL");
Assert.AreEqual(c.Id, "MPOLL");
Assert.AreEqual(c.CompanyName, "Interface21");
//Without flushing, nothing changes in the database:
int customerCount = (int)AdoTemplate.ExecuteScalar(CommandType.Text, "select count(*) from Customers");
Assert.AreEqual(91, customerCount);
//Flush the session to execute sql in the db.
SessionFactoryUtils.GetSession(sessionFactory, true).Flush();
//Now changes are visible outside the session but within the same database transaction
customerCount = (int)AdoTemplate.ExecuteScalar(CommandType.Text, "select count(*) from Customers");
Assert.AreEqual(92, customerCount);
Assert.AreEqual(92, customerDao.GetAll().Count);
c.CompanyName = "SpringSource";
customerDao.Update(c);
c = customerDao.Get("MPOLL");
Assert.AreEqual(c.Id, "MPOLL");
Assert.AreEqual(c.CompanyName, "SpringSource");
customerDao.Delete(c);
SessionFactoryUtils.GetSession(sessionFactory, true).Flush();
customerCount = (int)AdoTemplate.ExecuteScalar(CommandType.Text, "select count(*) from Customers");
Assert.AreEqual(92, customerCount);
try
{
c = customerDao.Get("MPOLL");
Assert.Fail("Should have thrown HibernateObjectRetrievalFailureException when finding customer with Id = MPOLL");
}
catch (HibernateObjectRetrievalFailureException e)
{
Assert.AreEqual("Customer", e.PersistentClassName);
}
}
public virtual int RetrieveCallReportTaskListCount(int loginId)
{
if (loginId != 0)
{
return((int)
AdoTemplate.ExecuteScalar(
CommandType.Text,
RETRIEVE_CALL_REPORT_TASK_LIST_COUNT,
"LoginId",
DbType.Int32,
0,
loginId
));
}
return(0);
}
/// <summary>
/// 分页(最好是通过存储过程去分页取数据，因为数据库sql语法不一样)
/// 这里只支持了mysql分页
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PagedList <T> GetPageList <T>(string sql, IDbParameters param, int pageIndex, int pageSize, string orderBy)
{
PagedList <T> pageList = null;
if (!string.IsNullOrEmpty(orderBy))
{
sql += " order by " + orderBy;
}
var obj = AdoTemplate.ExecuteScalar(CommandType.Text, "select count(1) from (" + sql + ") as A", param);
int totalItemCount = int.Parse(obj.ToString());
sql += " limit " + (pageIndex - 1) * pageSize + "," + pageSize + "";
DataTable dt = AdoTemplate.DataTableCreateWithParams(CommandType.Text, sql, param);
T[] list = ObjectHelper.CopyToObjects <T>(dt);
pageList = new PagedList <T>(list, pageIndex, pageSize, totalItemCount);
return(pageList);
}
public List <Mating> GetMating(MatingFilter filter, int pageIndex, int pageSize, out int totalCount)
{
string querySql = "SELECT \"row_number\" () OVER (ORDER BY M.\"MatingDate\" desc) \"rownum\",M.\"Id\",M.\"FemaleId\",M.\"MaleId\",M.\"MatingDate\",M.\"IsRemindful\",M.\"PrincipalId\",M.\"OperatorId\",M.\"CreateTime\",M.\"Remark\", sf.\"SerialNumber\" AS \"FemaleSerialNumber\", sm.\"SerialNumber\" AS \"MaleSerialNumber\", bf.\"Name\" AS \"FemaleBreed\", bm.\"Name\" AS \"MaleBreed\", u.\"UserName\" AS \"OperatorName\", e.\"Name\" AS \"PrincipalName\" FROM \"T_Mating\" M JOIN \"T_Sheep\" sm ON sm.\"Id\" =M.\"MaleId\" JOIN \"T_Sheep\" sf ON sf.\"Id\" =M.\"FemaleId\" JOIN \"T_Breed\" bf ON bf.\"Id\" = sf.\"BreedId\" JOIN \"T_Breed\" bm ON bm.\"Id\" = sm.\"BreedId\" JOIN \"T_User\" u ON u.\"Id\" =M.\"OperatorId\" JOIN \"T_Employee\" e ON e.\"Id\" =M.\"PrincipalId\"";
string countSql = "SELECT COUNT (m.\"Id\") FROM \"T_Mating\" M JOIN \"T_Sheep\" sm ON sm.\"Id\" =M.\"MaleId\" JOIN \"T_Sheep\" sf ON sf.\"Id\" =M.\"FemaleId\" JOIN \"T_Breed\" bf ON bf.\"Id\" = sf.\"BreedId\" JOIN \"T_Breed\" bm ON bm.\"Id\" = sm.\"BreedId\" JOIN \"T_User\" u ON u.\"Id\" =M.\"OperatorId\" JOIN \"T_Employee\" e ON e.\"Id\" =M.\"PrincipalId\"";
List <Mating> list = GetPagedData <Mating, MatingFilter>(pageIndex, pageSize, out totalCount, countSql, querySql, filter);
//EDC预产期
string getEDCSql = "select \"Value\" from \"T_SheepParameter\" sp JOIN \"T_Settings\" s on s.\"Id\"=sp.\"SettingsId\" where s.\"Type\"=:type";
IDbParameters pms = AdoTemplate.CreateDbParameters();
pms.AddWithValue("type", (int)SettingsEnum.PreDeliveryRemaindful);
int edcDays = Convert.ToInt32(AdoTemplate.ExecuteScalar(CommandType.Text, getEDCSql, pms));
list.ForEach(m => { m.EDC = m.MatingDate.AddDays(edcDays); });
return(list);
}
/// <summary>
/// 插入记录
/// </summary>
/// <param name="h"></param>
/// <returns></returns>
public int Insert(DataTable dt)
{
if (dt.Rows.Count > 0)
{
string tableName = dt.TableName;
IDbParameters param = AdoTemplate.CreateDbParameters();
string sql = "insert into " + tableName + "(";
string cols = "";
string vals = "";
DataRow dr = dt.Rows[0];
foreach (DataColumn col in dt.Columns)
{
string key = col.ColumnName;
if (dr[key] != DBNull.Value && key != "_is_add")
{
cols += key + ",";
vals += "@" + key + ",";
param.AddWithValue(key, dr[key]);
}
}
cols = cols.Substring(0, cols.Length - 1);
vals = vals.Substring(0, vals.Length - 1);
sql += cols + ")values(" + vals + ") select @@identity";
Object objValue = AdoTemplate.ExecuteScalar(CommandType.Text, sql, param);
if (objValue == null)
{
return(0);
}
else
{
return(Convert.ToInt32(objValue));
}
}
else
{
return(0);
}
}
public void OracleTest()
{
//Data Source=XE;User ID=hr;Unicode=True
IDbProvider dbProvider = DbProviderFactory.GetDbProvider("System.Data.OracleClient");
dbProvider.ConnectionString = "Data Source=XE;User ID=hr;Password=hr;Unicode=True";
AdoTemplate adoTemplate = new AdoTemplate(dbProvider);
decimal count = (decimal) adoTemplate.ExecuteScalar(CommandType.Text, "select count(*) from emp");
Assert.AreEqual(14, count);
EmpProc empProc = new EmpProc(dbProvider);
IDictionary dict = empProc.GetEmployees();
foreach (DictionaryEntry entry in dict)
{
Console.WriteLine("Key = " + entry.Key + ", Value = " + entry.Value);
}
IList employeeList = dict["employees"] as IList;
foreach (Employee employee in employeeList)
{
Console.WriteLine(employee);
}
}
/// <summary>
/// Execute the stored procedure using 'ExecuteScalar'
/// </summary>
/// <param name="inParams">Value of input parameters.</param>
/// <returns>Dictionary with any named output parameters and the value of the
/// scalar under the key "scalar".</returns>
protected virtual IDictionary ExecuteScalarByNamedParam(IDictionary inParams)
{
ValidateNamedParameters(inParams);
return(AdoTemplate.ExecuteScalar(NewCommandCreator(inParams)));
}
protected virtual IDictionary ExecuteScalar(params object[] inParameterValues)
{
ValidateParameters(inParameterValues);
return(AdoTemplate.ExecuteScalar(NewCommandCreatorWithParamValues(inParameterValues)));
}
/// <summary>
/// 查询数据统计数量
/// </summary>
/// <typeparam name="TFilter">过滤器类型</typeparam>
/// <param name="sql">统计sql语句</param>
/// <param name="filter">查询过滤器</param>
/// <returns></returns>
int GetDataCount(string sql, IDbParameters pms)
{
var result = AdoTemplate.ExecuteScalar(CommandType.Text, sql, pms);
return(result == DBNull.Value ? 0 : Convert.ToInt32(result));
}
/// <summary>
/// Counts the rows in given table.
/// </summary>
/// <param name="tableName">Name of the table to count rows in.</param>
/// <returns>The number of rows in the table</returns>
protected int CountRowsInTable(String tableName)
{
return((int)adoTemplate.ExecuteScalar(CommandType.Text, "SELECT COUNT(0) FROM " + tableName));
}
private void PopulateTestData()
{
adoTemplate.ExecuteScalar(CommandType.Text, "insert into TestObjects values (10, 'Jack')");
adoTemplate.ExecuteScalar(CommandType.Text, "insert into TestObjects values (20, 'Jill')");
}
