Oracle+Ado.Net(二)
概要:更多详细的BaseDal请看Oracle+Ado.Net(一),这里只是对(一)的封装的东西进行简要介绍.
功能添加:
在BaseDal中添加了公共的新增,更新功能;
在Model层中添加以下代码:
/// <summary>
/// 标识自增长
/// </summary>
private bool isAutoId = true;
public bool IsAutoId
{
get { return isAutoId; }
set { isAutoId = value; }
}
private string columns = "*";
public string Columns
{
get { return columns; }
set { columns = value; }
}
private string where;
public string Where
{
get { return where; }
set { where = value; }
}
在namespace myOracle.Dal下添加一个参数化查询类:
using System.Data;
using System.Data.OracleClient;
/// <summary>
/// 参数化查询类
/// </summary>
public class DbParam
{
/// <summary>
/// 参数键
/// </summary>
private string _ParamName = "";
/// <summary>
/// 参数类型
/// </summary>
private OracleType _ParamDbType;
/// <summary>
/// 参数值
/// </summary>
private object _ParamValue = null;
public string ParamName
{
get { return _ParamName; }
set { _ParamName = value; }
}
public OracleType ParamDbType
{
get { return _ParamDbType; }
set { _ParamDbType = value; }
}
public object ParamValue
{
get { return _ParamValue; }
set { _ParamValue = value; }
}
}
在BaseDal层的具体代码(使用反射技术):
StringBuilder sb = new StringBuilder();
StringBuilder ParamStr = new StringBuilder();
sb.AppendFormat("insert into {0} (",TableName);
List<DbParam> list = new List<DbParam>();
PropertyInfo[] propertys = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
for (int i = 0; i < propertys.Length; i++)
{
if(t.IsAutoId)
{
if (propertys[i].Name == PrimaryKey)
continue;
}
//DateTime类型处理(日期最小不能小于1900.1.1)
if (propertys[i].PropertyType == typeof(DateTime) && ((DateTime)propertys[i].GetValue(model, null)) < new DateTime(1900, 1, 1))
{
propertys[i].SetValue(model, new DateTime(1900, 1, 1), null);
}
sb.Append(propertys[i].Name+",");
ParamStr.Append(":"+propertys[i].Name+",");
DbParam param = new DbParam() {
ParamName=":"+propertys[i].Name,
ParamDbType=TypeConvert.GetOracleDbType(propertys[i].PropertyType),
ParamValue=propertys[i].GetValue(model,null)
};
list.Add(param);
}
sb.Replace(",",")",sb.Length-1,1);
ParamStr.Replace(",",")",ParamStr.Length-1,1);
sb.Append(" values(");
sb.Append(ParamStr);//在plsql虽然可以加上分号";",但是在这里不能加上分号";"
if(t.IsAutoId)
{
/*
* 先取得一个序列的下一个值：
select myseq.nextval from dual;
然后再把这个值当成主键值插入数据表：
insert into mytable (id, ...) values (id_val, ...)
* */
}
OracleConnection conn=DbAction.getConn();
OracleCommand com = new OracleCommand(sb.ToString(), conn);
foreach (DbParam item in list)
{
com.Parameters.Add(DbHelper.CreateParam(item.ParamName,item.ParamValue));
//com.Parameters.Add(p);
}
OracleString rowid;
conn.Open();
com.ExecuteOracleNonQuery(out rowid);
conn.Close();
至于在更新Update方法中,我们需要先封装一个方法:
/// <summary>
/// 是否字段值是否更新由BaseModel的columns定义===>推断出反射出来的属性是否需要更新
/// </summary>
/// <param name="model">columns定义的列</param>
/// <param name="val">反射的属性</param>
/// <returns>是否更新</returns>
private bool IsUpdateProperty(T model,string val)
{
bool result = false;
string strs=model.Columns;
if(strs=="*")
{
return true;
}
string[] cols=strs.Split(',');
for (int i = 0; i < cols.Length; i++)
{
if(val.Equals(cols[i],StringComparison.OrdinalIgnoreCase))
{
result = true;
//跳出循环
break;
}
}
return result;
}
在update方法中:需要进行判断一下是否
if(this.IsUpdateProperty(model,ps[i].Name))
{
sb.Append(ps[i].Name+"="+":"+ps[i].Name+",");
list.Add( DbHelper.CreateParam(ps[i].Name, ps[i].GetValue(model, null)));
}
Update于Insert大致一样;
总结:在拼接sql语句的时候需要非常细心,中英文输入法,还有sql语句最后不要加";"
END
