概要:详细内容在Oracle+Ado.Net(一),这里做一下里面部分内容的注意点和思路.
先列出一些BaseMethod:
1.参数化where条件判断是否存在记录
/// <summary>
/// 参数化where条件判断
/// </summary>
/// <param name="strWhere">where关键字后的判断语句(参数化)</param>
/// <param name="listPm">(参数化)参数集合</param>
/// <returns></returns>
public virtual bool Exists(string strWhere, List<DbParam> listPm)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from " + TableName);
if (strWhere != "")
strSql.Append(" where " + strWhere);
OracleConnection conn = DbAction.getConn();
OracleCommand cmd = new OracleCommand(strSql.ToString(), conn);
if (listPm != null)
{
foreach (DbParam pm in listPm)
{
cmd.Parameters.Add(DbHelper.CreateParam(pm.ParamName, pm.ParamValue));
}
}
object obj = cmd.ExecuteScalar();
int cmdresult;
if ((Equals(obj, null)) || (Equals(obj, DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
return true;
}
2.参数化查询获得总条数
/// <summary>
/// 获得总条数
/// </summary>
/// <param name="strWhere"></param>
/// <param name="listPm"></param>
/// <returns></returns>
public virtual int GetCount(string strWhere, List<DbParam> listPm)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from " + TableName);
if (strWhere != "")
strSql.Append(" where " + strWhere);
OracleConnection conn = DbAction.getConn();
OracleCommand cmd = new OracleCommand(strSql.ToString(), conn);
if (listPm != null)
{
foreach (DbParam pm in listPm)
{
cmd.Parameters.Add(DbHelper.CreateParam(pm.ParamName, pm.ParamValue));
}
}
object obj = cmd.ExecuteScalar();
int cmdresult;
if ((Equals(obj, null)) || (Equals(obj, DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
return cmdresult;
}
接下来,说一下GetModel的实现方式:
一般通过where条件能得到一个DataReader对象,配合反射,可以将dr的数据赋值给泛型的model.
public virtual T GetModel(IDataReader dr)
{
try
{
T t = new T();
if (dr != null)
{
for (int i = 0; i < dr.FieldCount; i++)
{
PropertyInfo ps = t.GetType().GetProperty(ActionString(dr.GetName(i)));
string name = dr.GetName(i);
if (ps != null)
{
if (dr.GetValue(i) != DBNull.Value && dr.GetValue(i).ToString().Length > 0)
{
object iobject = dr.GetValue(i);
try
{
ps.SetValue(t, dr.GetValue(i), null);
}
catch (Exception)
{
//处理Decimal到Int类型转换
ps.SetValue(t, TypeConvert.ConvertToInt32((System.Decimal)dr.GetValue(i)), null);
//TypeConvert.ConvertToInt32((System.Decimal)dr.GetValue(i));
}
}
}
}
}
return t;
}
catch (Exception)
{
throw;
}
}
当然中得到个model实体远远不够,那么我们需要循环得到model,从而得到一个list;
/// <summary>
/// 使用DataReader实现
/// </summary>
/// <returns></returns>
public List<T> GetAllToList()
{
OracleConnection conn = DbAction.getConn();
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select * from {0}", TableName);
OracleCommand com = new OracleCommand(sb.ToString(), conn);
conn.Open();
OracleDataReader dr = com.ExecuteReader();
List<T> list = new List<T>();
while (dr.Read())
{
list.Add(GetModel(dr));
}
dr.Close();
conn.Close();
return list;
}
这里考虑到,很多情况会使用到DataTable(DataSet),断开式查询数据(DataAdapter适配器)得到的dt转为一个list返回,那么我这里重载了一个GetModel方法:
public virtual T GetModel(DataTable dt, DataRow row)
{
T t = new T();
if (dt != null)
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
//DataRow row = dt.Rows[0];
PropertyInfo ps = t.GetType().GetProperty(ActionString(dt.Columns[i].ColumnName));
string name = dt.Columns[i].ColumnName;
string value = string.Empty;
if (row[i] != null)
{
value = row[i].ToString();
}
if (ps != null)
{
if (row[i] != DBNull.Value && value.Length > 0)
{
try
{
ps.SetValue(t, row[i], null);
}
catch (Exception)
{
//处理Decimal到Int类型转换
ps.SetValue(t, TypeConvert.ConvertToInt32((System.Decimal)row[i]), null);
//TypeConvert.ConvertToInt32((System.Decimal)dr.GetValue(i));
}
}
}
}
}
}
return t;
}
这样可以实现从DataTable到List的转化,完整的代码长这个样子:
/// <summary>
/// 使用DataAdapter实现
/// </summary>
/// <param name="where"></param>
/// <param name="list"></param>
/// <returns></returns>
public List<T> GetListByWhere(string where, List<DbParam> list)
{
OracleConnection conn = DbAction.getConn();
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select * from {0}", TableName);
List<T> TList = new List<T>();
if (string.IsNullOrEmpty(where))
{
sb.Append(" where " + where);
}
OracleCommand com = new OracleCommand(sb.ToString(), conn);
if (list != null)
{
for (int i = 0; i < list.Count; i++)
{
com.Parameters.Add(DbHelper.CreateParam(list[i].ParamName, list[i].ParamValue));
}
}
OracleDataAdapter da = new OracleDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow item in ds.Tables[0].Rows)
{
TList.Add(GetModel(ds.Tables[0], item));
}
return TList;
}
最后,我另外定义了两个手写Sql的执行方法:
public int ToExecuteNonQuerySql(string sqlStr, List<DbParam> list)
{
OracleConnection conn = DbAction.getConn();
OracleCommand com = new OracleCommand(sqlStr, conn);
if (list != null)
{
for (int i = 0; i < list.Count; i++)
{
com.Parameters.Add(DbHelper.CreateParam(list[i].ParamName, list[i].ParamValue));
}
}
conn.Open();
OracleString outid;
int count = com.ExecuteOracleNonQuery(out outid);
conn.Close();
return count;
}
public object ToExecuteQuerySql(string strSql, List<DbParam> list)
{
OracleConnection conn = DbAction.getConn();
OracleCommand com = new OracleCommand(strSql, conn);
if (list != null)
{
for (int i = 0; i < list.Count; i++)
{
com.Parameters.Add(DbHelper.CreateParam(list[i].ParamName, list[i].ParamValue));
}
}
conn.Open();
object obj = com.ExecuteScalar();
conn.Close();
return obj;
}
现在这个框架的基本的增删查改已经齐全了.
END