public T FindByid(string sql, string parmName, string parmValue)
{
Debug.Assert(!string.IsNullOrWhiteSpace(parmName));
Debug.Assert(!string.IsNullOrWhiteSpace(parmValue));
T result = default(T);
iDB2Parameter[] db2Parms = new iDB2Parameter[] { new iDB2Parameter('@' + parmName, parmValue) };
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, db2Parms))
{
if (rdr.Read())
{
T t = Activator.CreateInstance <T>();
PropertyInfo[] propertyInfo = t.GetType().GetProperties();
int i = 0;
foreach (PropertyInfo pi in propertyInfo)
{
try
{
pi.SetValue(t, rdr.GetValue(i++));
}
catch (Exception ex)
{
log.Error("字段类型错误，" + pi.Name + ":" + ex.Message);
}
}
result = t;
}
}
return(result);
}
public List <T> FindAll(string sql)
{
List <T> list = new List <T>();
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql))
{
if (rdr.Read())
{
do
{
T t = Activator.CreateInstance <T>();
PropertyInfo[] propertyInfo = t.GetType().GetProperties();
int i = 0;
foreach (PropertyInfo pi in propertyInfo)
{
try
{
pi.SetValue(t, rdr.GetValue(i++));
}
catch (Exception ex)
{
log.Error("字段类型错误，" + pi.Name + ":" + ex.Message);
}
}
list.Add(t);
} while (rdr.Read());
}
}
return(list);
}
public List <User> findAll()
{
string sql = "SELECT USER_NO,USERNAME,PASSWORD FROM \"USER\" ORDER BY USERNAME";
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, null))
{
if (rdr.Read())
{
List <User> list = new List <User>();
do
{
User user = new User();
user.userNo = rdr.GetInt32(0);
user.username = rdr.GetString(1);
user.password = rdr.GetString(2);
list.Add(user);
} while (rdr.Read());
return(list);
}
else
{
return(null);
}
}
}
public List <T> nativeQuerySql(string sql, IDictionary <string, object> parms)
{
iDB2Parameter[] db2Parms = null;
List <T> list = new List <T>();
if (parms != null)
{
List <iDB2Parameter> parmsList = new List <iDB2Parameter>();
foreach (var item in parms)
{
Debug.Assert(item.Value != null);
parmsList.Add(new iDB2Parameter('@' + item.Key, item.Value));
}
db2Parms = parmsList.ToArray();
}
try {
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, db2Parms))
{
if (rdr.Read())
{
do
{
T t = Activator.CreateInstance <T>();
PropertyInfo[] propertyInfo = t.GetType().GetProperties();
int i = 0;
foreach (PropertyInfo pi in propertyInfo)
{
try
{
pi.SetValue(t, rdr.GetValue(i++));
}
catch (Exception ex)
{
log.Error("字段类型错误，" + pi.Name + ":" + ex.Message);
}
}
list.Add(t);
} while (rdr.Read());
}
}
}
catch (Exception ex)
{
log.Error("SQL错误:" + ex.Message);
log.Error("SQL错误:" + sql);
if (parms != null)
{
log.Error("SQL错误:" + parms.toJson());
}
throw ex;
}
return(list);
}
public IDictionary <string, string> FindAllDic(string sql)
{
IDictionary <string, string> list = new Dictionary <string, string>();
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql))
{
if (rdr.Read())
{
do
{
try
{
list.Add(rdr.GetString(0), rdr.GetString(1));
}
catch (Exception ex)
{
log.Error("字段类型错误，" + rdr.GetValue(0).ToString() + ":" + rdr.GetValue(1).ToString() + ":" + ex.Message);
}
} while (rdr.Read());
}
}
return(list);
}
public User get(string username)
{
string sql = "SELECT USER_NO,USERNAME,PASSWORD FROM \"USER\" WHERE USERNAME=@USERNAME";
iDB2Parameter[] parms = new iDB2Parameter[] {
new iDB2Parameter("@USERNAME", username)
};
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, parms))
{
if (rdr.Read())
{
User user = new User();
user.userNo = rdr.GetInt32(0);
user.username = rdr.GetString(1);
user.password = rdr.GetString(2);
return(user);
}
else
{
return(null);
}
}
}
public string nativeQuerySqlReturnString(string sql, IDictionary <string, object> parms)
{
string result = string.Empty;
iDB2Parameter[] db2Parms = null;
if (parms != null)
{
List <iDB2Parameter> parmsList = new List <iDB2Parameter>();
foreach (var item in parms)
{
Debug.Assert(item.Value != null);
parmsList.Add(new iDB2Parameter('@' + item.Key, item.Value));
}
db2Parms = parmsList.ToArray();
}
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, db2Parms))
{
if (rdr.Read())
{
result = rdr.GetString(0);
}
}
return(result);
}
public PageResult <T> nativeQuerySql(string sql, string sqlCount, IDictionary <string, object> parms, int startIndex, int pageSize)
{
Debug.Assert(startIndex >= 0);
Debug.Assert(pageSize > 0 && pageSize <= 100);
List <T> list = list = new List <T>();
int count = 0;
iDB2Parameter[] db2Parms = null;
if (parms != null)
{
List <iDB2Parameter> paramlist = new List <iDB2Parameter>();
foreach (var item in parms)
{
Debug.Assert(item.Value != null);
paramlist.Add(new iDB2Parameter('@' + item.Key, item.Value));
}
db2Parms = paramlist.ToArray();
}
try
{
count = Convert.ToInt32(DB2Helper.ExecuteScalar(DB2Helper.ConnectionString, CommandType.Text, sqlCount, db2Parms));
}
catch (Exception ex)
{
log.Error("SQL错误:" + ex.Message);
log.Error("SQL错误:" + sqlCount);
if (parms != null)
{
log.Error("SQL错误:" + parms.toJson());
}
throw ex;
}
if (count > 0)
{
try {
using (iDB2DataReader rdr = DB2Helper.ExecuteReader(DB2Helper.ConnectionString, CommandType.Text, sql, db2Parms))
{
if (rdr.Read())
{
do
{
T t = Activator.CreateInstance <T>();
PropertyInfo[] propertyInfo = t.GetType().GetProperties();
int i = 0;
foreach (PropertyInfo pi in propertyInfo)
{
try {
pi.SetValue(t, rdr.GetValue(i++));
}catch (Exception ex)
{
log.Error("字段类型错误，" + pi.Name + ":" + ex.Message);
}
}
list.Add(t);
} while (rdr.Read());
}
}
}
catch (Exception ex)
{
log.Error("SQL错误:" + ex.Message);
log.Error("SQL错误:" + sql);
if (parms != null)
{
log.Error("SQL错误:" + parms.toJson());
}
throw ex;
}
}
return(new PageResult <T>(startIndex, pageSize, count, list));
}