C# Oracle基本操作

6rlg96r65i

#region 插入操作
/// <summary>
/// TableBaseOpt: 插入操作
/// </summary>
/// <param name="record">记录</param>
/// <param name="trans">事务</param>
/// <returns>成功插入数据库的记录数</returns>
public static string Insert(DBRecordInfo record, OracleTransaction trans)
{
string result = "";
int effectNum = 0;
string fields = "", values = "";
OracleCommand command = new OracleCommand();
OracleParameter[] paraItem = GetItemParameters(record);
string s = "";
for (int i = 0; i < paraItem.Length; i++)
{
if (record.FieldValue != null && record.FieldValue.ToString().Trim() != "")
{
fields += record.FieldName + ", ";
values += paraItem.ParameterName + ", ";
paraItem.Value = record.FieldValue;
s = s + paraItem.Value + ",";
command.Parameters.Add(paraItem);
}
}
if (fields == "" || values == "")
return "请填写记录！";
command.CommandText = "insert into " + record.TableName + "(" + fields.Substring(0, fields.Length - 2) + ") values (" +
values.Substring(0, values.Length - 2) + ")";
if (trans == null)
{
command.Connection = OracleServerDAL.GetConnection();
}
else
{
command.Connection = trans.Connection;
command.Transaction = trans;
}
try
{
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
effectNum = command.ExecuteNonQuery();
if (effectNum > 0)
{
result = "添加成功！";
}

if (trans == null)
{
command.Connection.Close();
command.Connection.Dispose();
}
}
catch (Exception e)
{
if (trans != null)
{
throw e;
}
if (e.Message.ToString().IndexOf("ORA-01400") != -1)
{
result = "带*号的文本框不能为空";
}
else
{
result = "添加失败！";
}
}
finally
{
if (trans == null)
{
if (command.Connection.State == ConnectionState.Open)
{
command.Connection.Close();
}
command.Connection.Dispose();
}
command.Dispose();
}
return result;
}
/// <summary>
/// 根据字段插入数据
/// </summary>
/// <param name="tablename"></param>
/// <param name="fields"></param>
/// <param name="values"></param>
/// <returns></returns>
public static string Insert(string tablename, string fields, string values)
{
string result = "";
int effectNum = 0;
OracleTransaction trans = null;
OracleCommand command = new OracleCommand();
if (fields == "" || values == "")
return "请填写记录！";
command.CommandText = "insert into " + tablename + "(" + fields + ") values (" +
values + ")";
if (trans == null)
{
command.Connection = OracleServerDAL.GetConnection();
}
else
{
command.Connection = trans.Connection;
command.Transaction = trans;
}
try
{
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
effectNum = command.ExecuteNonQuery();
if (effectNum > 0)
{
result = "插入成功！";
}

if (trans == null)
{
command.Connection.Close();
command.Connection.Dispose();
}
}
catch (Exception e)
{
if (trans != null)
{
throw e;
}
result = e.Message.ToString();
}
finally
{
if (trans == null)
{
if (command.Connection.State == ConnectionState.Open)
{
command.Connection.Close();
}
command.Connection.Dispose();
}
command.Dispose();
}
return result;
}
/// <summary>
/// TableBaseOpt: 插入操作
/// </summary>
/// <param name="record">记录</param>
/// <returns>成功插入数据库的记录数</returns>
public static string Insert(DBRecordInfo record)
{
return Insert(record, null);
}
/// <summary>
/// TableBaseOpt: 插入操作
/// </summary>
/// <param name="trans">事务</param>
/// <returns>成功插入数据库的记录数</returns>
public string Insert(OracleTransaction trans)
{
return Insert(this.record, trans);
}
/// <summary>
/// TableBaseOpt: 插入操作
/// </summary>
/// <returns>成功插入数据库的记录数</returns>
public string Insert()
{
return Insert(this.record, null);
}
#endregion
#region 删除操作
/// <summary>
/// TableBaseOpt: 删除操作
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="condition">条件</param>
/// <param name="trans">事务</param>
/// <returns>成功删除的记录数</returns>
public static string Delete(string tableName, string condition, OracleTransaction trans)
{
string result="";
int effectNum = 0;
string DELETE_CONTRACT_SQL = "delete from " + tableName;
OracleCommand command = new OracleCommand();
if (condition != null && condition.Trim() != "")
DELETE_CONTRACT_SQL += " where " + condition;
if (trans == null)
{
command.Connection = OracleServerDAL.GetConnection();
}
else
{
command.Connection = trans.Connection;
command.Transaction = trans;
}
command.CommandText = DELETE_CONTRACT_SQL;
try
{
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
effectNum = command.ExecuteNonQuery();
if (trans == null)
{
command.Connection.Close();
command.Connection.Dispose();
}
if (effectNum > 0)
{
result = "删除成功！";
}
//XT_OptionLogOpt.Insert("删除", tableName, command.CommandText, "操作成功");
}
catch (Exception e)
{
if (trans != null)
{
throw e;
}
result = e.Message.ToString();
}
finally
{
if (trans == null)
{
if (command.Connection.State == ConnectionState.Open)
{
command.Connection.Close();
}
command.Connection.Dispose();
}
command.Dispose();
}
return result;
}
/// <summary>
/// TableBaseOpt: 删除操作
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="condition">条件</param>
/// <returns>成功删除的记录数</returns>
public static string Delete(string tableName, string condition)
{
return Delete(tableName, condition, null);
}
/// <summary>
/// TableBaseOpt: 删除操作
/// </summary>
/// <param name="condition">条件</param>
/// <param name="trans">事务</param>
/// <returns>成功删除的记录数</returns>
public string Delete(string condition, OracleTransaction trans)
{
return Delete(this.record.TableName, condition, trans);
}
/// <summary>
/// TableBaseOpt: 删除操作
/// </summary>
/// <param name="condition">条件</param>
/// <returns>成功删除的记录数</returns>
public string Delete(string condition)
{
return Delete(this.record.TableName, condition, null);
}
#endregion
#region 修改操作
/// <summary>
/// Edit的原始操作
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="condition">条件</param>
/// <param name="changeParas">修改条件参数</param>
/// <param name="trans">事务</param>
/// <returns>受修改操作影响的记录数</returns>
private static string Edit(string tableName, string condition, OracleParameter[] changeParas, OracleTransaction trans)
{
string result="";
int effectNum = -1;
string changeStr = "";
string DELETE_CONTRACT_SQL = "update " + tableName + " set ";
OracleCommand command = new OracleCommand();
foreach (OracleParameter para in changeParas)
{
changeStr += para.ParameterName.Substring(1) + " = " + para.ParameterName + ", ";
command.Parameters.Add(para);
}
DELETE_CONTRACT_SQL += changeStr.Substring(0, changeStr.Length - 2);
if (condition != null && condition.Trim() != "")
DELETE_CONTRACT_SQL += " where " + condition;
if (trans == null)
{
command.Connection =  OracleServerDAL.GetConnection();
}
else
{
command.Connection = trans.Connection;
command.Transaction = trans;
}
command.CommandText = DELETE_CONTRACT_SQL;
try
{
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
effectNum = command.ExecuteNonQuery();
if (trans == null)
{
command.Connection.Close();
command.Connection.Dispose();
}
if (effectNum > 0)
{
result = "修改成功！";
}
//XT_OptionLogOpt.Insert("修改", tableName, command.CommandText, "操作成功");
}
catch (Exception e)
{
if (trans != null)
{
throw e;
}
result = e.Message.ToString();
}
finally
{
if (trans == null)
{
if (command.Connection.State == ConnectionState.Open)
{
command.Connection.Close();
}
command.Connection.Dispose();
}
command.Dispose();
}
return result;
}
/// <summary>
/// Edit的原始操作
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="condition">条件</param>
/// <param name="changeStr">修改字符串</param>
/// <param name="trans">事务</param>
/// <returns>受修改操作影响的记录数</returns>
private static string Edit(string tableName, string condition, string changeStr,OracleTransaction trans)
{
string result="";
int effectNum = -1;
string DELETE_CONTRACT_SQL = "update " + tableName + " set ";
OracleCommand command = new OracleCommand();
DELETE_CONTRACT_SQL += changeStr;
if (condition != null && condition.Trim() != "")
DELETE_CONTRACT_SQL += " where " + condition;
if (trans == null)
{
command.Connection = OracleServerDAL.GetConnection();
}
else
{
command.Connection = trans.Connection;
command.Transaction = trans;
}
command.CommandText = DELETE_CONTRACT_SQL;
try
{
if (command.Connection.State == ConnectionState.Closed)
{
command.Connection.Open();
}
effectNum = command.ExecuteNonQuery();
if (trans == null)
{
command.Connection.Close();
}
if (effectNum > 0)
{
result = "修改成功！";
}
//XT_OptionLogOpt.Insert("修改", tableName, command.CommandText, "操作成功");
}
catch (Exception e)
{
if (trans != null)
{
throw e;
}
result = e.Message.ToString();
}
finally
{
if (trans == null)
{
if (command.Connection.State == ConnectionState.Open)
{
command.Connection.Close();
}
command.Connection.Dispose();
}
command.Dispose();
}
return result;
}
/// <summary>
/// BaseTableOpt: 修改操作
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="condition">条件</param>
/// <param name="change">修改信息</param>
/// <param name="trans">事务</param>
/// <returns>受修改操作影响的记录数</returns>
public static string Edit(string tableName, string condition, string[][] change, OracleTransaction trans)
{
string changstr = "";
Type info = Type.GetType("AHUT.JSJ.XWLEV2.Model." + tableName + "Info", false, true);
Object infoData = null;
MethodInfo methodInfo = null;
if (info != null)
{
infoData = Activator.CreateInstance(info, null);
methodInfo = info.GetProperty("Item", new Type[] { typeof(string) }).GetGetMethod();
}
foreach (string[] chgCmpr in change)
{
if (chgCmpr[1].Replace('\'', ' ').Trim() == "" && infoData != null)
{
DBFieldInfo fieldInfo = (DBFieldInfo)methodInfo.Invoke(infoData, new object[] { chgCmpr[0] });
if (fieldInfo != null && fieldInfo.FieldType == OracleType.DateTime)
{
chgCmpr[1] = "NULL";
}
}
changstr += chgCmpr[0] + " ='" + chgCmpr[1] + "', ";
}
return Edit(tableName, condition, changstr.Substring(0, changstr.Length - 2), trans);
}
/// <summary>
/// BaseTableOpt: 修改操作
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="condition">条件</param>
/// <param name="change">修改信息</param>
/// <returns>受修改操作影响的记录数</returns>
public static string Edit(string tableName, string condition, string[][] change)
{
return Edit(tableName, condition, change, null);
}
/// <summary>
/// BaseTableOpt: 修改操作
/// change保存了所有的操作，格式为{{fieldName,fieldValue}, {fieldName,fieldValue}, ...}
/// </summary>
public string Edit(string condition, string[][] change,OracleTransaction trans)
{
return Edit(record.TableName, condition, change, trans);
}
/// <summary>
/// BaseTableOpt: 修改操作
/// </summary>
/// <param name="condition">条件</param>
/// <param name="change">修改信息</param>
/// <returns>受修改操作影响的记录数</returns>
public string Edit(string condition, string[][] change)
{
return Edit(record.TableName, condition, change, null);
}
/// <summary>
/// BaseTableOpt: 修改操作
/// </summary>
/// <param name="record">记录</param>
/// <param name="condition">条件</param>
/// <param name="trans">事务</param>
/// <returns>受修改操作影响的记录数</returns>
public static string Edit(DBRecordInfo record, string condition, OracleTransaction trans)
{
IList<OracleParameter> changParaList = new List<OracleParameter>();
for (int i = 0; i < record.Length; i++)
{
changParaList.Add(new OracleParameter(":" + record.FieldName, record.FieldValue));
}
if (changParaList.Count == 0)
{
return "您没有修改任何内容";
}
OracleParameter[] changParas = new OracleParameter[changParaList.Count];
changParaList.CopyTo(changParas, 0);
return Edit(record.TableName, condition, changParas, trans);
}
/// <summary>
/// BaseTableOpt: 修改操作
/// </summary>
/// <param name="record">记录</param>
/// <param name="condition">条件</param>
/// <returns>受修改操作影响的记录数</returns>
public static string Edit(DBRecordInfo record, string condition)
{
return Edit(record, condition, null);
}
/// <summary>
/// BaseTableOpt: 修改操作
/// </summary>
/// <param name="condition">条件</param>
/// <param name="trans">事务</param>
/// <returns>受修改操作影响的记录数</returns>
public string Edit(string condition, OracleTransaction trans)
{
return Edit(this.record, condition, trans);
}
/// <summary>
/// BaseTableOpt: 修改操作
/// </summary>
/// <param name="condition">条件</param>
/// <returns>受修改操作影响的记录数</returns>
public string Edit(string condition)
{
return Edit(condition, (OracleTransaction)null);
}
#endregion
#region 选择操作
/// <summary>
/// TableBaseOpt: 选择操作
/// </summary>
/// <param name="field">字段列表</param>
/// <param name="table">表名称列表</param>
/// <param name="condition">条件</param>
/// <param name="groupBy">分组字段</param>
/// <param name="having">分组条件</param>
/// <param name="order">排序字段</param>
/// <returns>查询的结果表格</returns>
public static DataTable Select(string field, string table, string condition, string groupBy, string having, string order)
{
DataTable dt = null;
string SELECT_CONTRACT_SQL = "Select ";
if (field != null && field.Trim() != "")
SELECT_CONTRACT_SQL += field;
else
SELECT_CONTRACT_SQL += "*";
SELECT_CONTRACT_SQL += " From " + table;
if (condition != null && condition.Trim() != "")
SELECT_CONTRACT_SQL += " Where " + condition;
if (groupBy != null && groupBy.Trim() != "")
SELECT_CONTRACT_SQL += " Group by " + groupBy;
if (having != null && having.Trim() != "")
SELECT_CONTRACT_SQL += " Having " + having;
if (order != null && order.Trim() != "")
SELECT_CONTRACT_SQL += " Order By " + order;
OracleServerDAL dctrl = new OracleServerDAL();
try
{
dctrl.connectionData();
dt = dctrl.returnRecordSet(SELECT_CONTRACT_SQL).Tables[0];
dctrl.closeConnect();
//XT_OptionLogOpt.Insert("选择", table, SELECT_CONTRACT_SQL, "操作成功");
}
catch (Exception e)
{
}
finally
{
dctrl.closeConnect();
}
return dt;
}
/// <summary>
/// TableBaseOpt: 选择操作
/// </summary>
/// <param name="field">字段列表</param>
/// <param name="condition">条件</param>
/// <param name="groupBy">分组字段</param>
/// <param name="having">分组条件</param>
/// <param name="order">排序字段</param>
/// <returns>查询的结果表格</returns>
public DataTable Select(string field, string condition, string groupBy, string having, string order)
{
return Select(field, this.record.TableName, condition, groupBy, having, order);
}
/// <summary>
/// TableBaseOpt: 选择操作
/// </summary>
/// <param name="condition">条件</param>
/// <param name="groupBy">分组字段</param>
/// <param name="having">分组条件</param>
/// <param name="order">排序字段</param>
/// <returns>查询的结果表格</returns>
public DataTable Select(string condition, string groupBy, string having, string order)
{
return Select(null, this.record.TableName, condition, groupBy, having, order);
}
#endregion
#region 获取参数信息
/// <summary>
/// TableBaseOpt: 获取记录参数信息
/// </summary>
/// <returns>参数集合</returns>
protected OracleParameter[] GetItemParameters()
{
return GetItemParameters(this.record);
}
/// <summary>
/// TableBaseOpt: 获取记录参数信息
/// </summary>
/// <param name="record">记录</param>
/// <returns>参数集合</returns>
protected static OracleParameter[] GetItemParameters(DBRecordInfo record)
{
OracleParameter[] paras = new OracleParameter[record.Length];
for (int i = 0; i < record.Length; i++)
paras = GetItemParameters(record);
return paras;
}
/// <summary>
/// TableBaseOpt: 获取记录参数信息
/// </summary>
/// <param name="field">字段</param>
/// <returns>参数</returns>
protected static OracleParameter GetItemParameters(DBFieldInfo field)
{
return new OracleParameter(":" + field.FieldName, field.FieldType, field.FieldSize);
}
/// <summary>
/// TableBaseOpt: 根据字段名称和值获取参数信息
/// </summary>
/// <param name="fieldName">字段名称</param>
/// <param name="fieldValue">字段值</param>
/// <returns>参数</returns>
protected static OracleParameter GetItemParameters(string fieldName, object fieldValue)
{
return new OracleParameter(":" + fieldName, fieldValue);
}
#endregion
　　下面是两个基础类：
　　

/// <summary>
/// 字段类型
/// </summary>
public class DBFieldInfo
{
private String fieldName;
private OracleType fieldType;
private int fieldSize;
private object fieldValue;
private bool isChanged;
private static int index = 1;
/// <summary>
/// 默认构造函数.以字段名为fieldxxxxx格式,类型为50Byte的VarChar构造字段
/// </summary>
public DBFieldInfo()
{
InitData("field" + (index++).ToString("00000"),OracleType.VarChar, 50, null);
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="fieldName">字段名</param>
/// <param name="fieldType">字段类型</param>
public DBFieldInfo(string fieldName, OracleType fieldType)
{
InitData(fieldName, fieldType, 0, null);
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="fieldName">字段名</param>
/// <param name="fieldType">字段类型</param>
/// <param name="fieldSize">字段大小</param>
public DBFieldInfo(string fieldName, OracleType fieldType, int fieldSize)
{
InitData(fieldName, fieldType, fieldSize, null);
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="fieldName">字段名</param>
/// <param name="fieldType">字段类</param>
/// <param name="fieldValue">字段值</param>
public DBFieldInfo(string fieldName, OracleType fieldType, object fieldValue)
{
InitData(fieldName, fieldType, 0, fieldValue);
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="fieldName">字段名</param>
/// <param name="fieldType">字段类型</param>
/// <param name="fieldSize">字段大小</param>
/// <param name="fieldValue">字段值</param>
public DBFieldInfo(string fieldName, OracleType fieldType, int fieldSize, object fieldValue)
{
InitData(fieldName, fieldType, fieldSize, fieldValue);
}
/// <summary>
/// 初始化数据
/// </summary>
/// <param name="fieldName">字段名</param>
/// <param name="fieldType">字段类型</param>
/// <param name="fieldSize">字段大小</param>
/// <param name="fieldValue">字段值</param>
private void InitData(string fieldName, OracleType fieldType, int fieldSize, object fieldValue)
{
this.fieldName = fieldName;
this.fieldType = fieldType;
this.fieldSize = fieldSize;
this.fieldValue = fieldValue;
this.isChanged = false;
}
/// <summary>
/// 字段名
/// </summary>
public String FieldName
{
get { return this.fieldName; }
set { this.fieldName = value; }
}
/// <summary>
/// 字段类型
/// </summary>
public OracleType FieldType
{
get { return this.fieldType; }
set { this.fieldType = value; }
}
/// <summary>
/// 字段大小
/// </summary>
public int FieldSize
{
get { return this.fieldSize; }
set { this.fieldSize = value; }
}
/// <summary>
/// 字段值
/// </summary>
public object FieldValue
{
get { return this.fieldValue; }
set
{
this.fieldValue = value;
isChanged = true;
}
}
/// <summary>
/// 修改标识
/// </summary>
public bool IsChanged
{
get { return this.isChanged; }
set { this.isChanged = value; }
}
}
/// <summary>
/// 记录类型
/// </summary>
public class DBRecordInfo
{
private IList<DBFieldInfo> fields;
protected string tableName;
protected string[] fieldNameItem;
/// <summary>
/// 默认构造函数
/// </summary>
public DBRecordInfo()
{
this.fields = new List<DBFieldInfo>();
GetTableMsg();
}
/// <summary>
/// 拷贝构造函数
/// </summary>
/// <param name="fields">字段链表</param>
public DBRecordInfo(IList<DBFieldInfo> fields)
{
this.fields = fields;
GetTableMsg();
}
/// <summary>
/// 增加字段
/// </summary>
/// <param name="field">字段</param>
public void Add(DBFieldInfo field)
{
this[field.FieldName] = field;
}
/// <summary>
/// 删除字段
/// </summary>
/// <param name="field">字段</param>
/// <returns>是否删除成功</returns>
public bool Del(DBFieldInfo field)
{
try
{
fields.Remove(field);
}
catch
{
return false;
}
return true;
}
/// <summary>
/// 根据字段名访问字段
/// </summary>
/// <param name="fieldName">字段名称</param>
/// <returns>字段</returns>
public DBFieldInfo this[string fieldName]
{
get
{
foreach (DBFieldInfo infield in fields)
{
if (infield.FieldName == fieldName)
return infield;
}
return null;
}
set
{
for (int index = 0; index < fields.Count; index++)
{
if (fields[index].FieldName == fieldName)
{
fields[index] = value;
return;
}
}
fields.Add(value);
}
}
/// <summary>
/// 根据索引访问字段
/// </summary>
/// <param name="index">字段索引</param>
/// <returns>字段</returns>
public DBFieldInfo this[int index]
{
get
{
if (index >= fields.Count)
return null;
return fields[index];
}
set
{
if (index >= fields.Count)
return;
fields[index] = value;
}
}
/// <summary>
/// 获取字段个数
/// </summary>
public int Length
{
get
{
return fields.Count;
}
}
/// <summary>
/// 获取字段名列表
/// </summary>
public string[] FieldNameItem
{
get
{
return fieldNameItem;
}
}
/// <summary>
/// 获取表名称
/// </summary>
public string TableName
{
get
{
return tableName;
}
}
/// <summary>
/// 获取表信息,可重构
/// </summary>
virtual protected void GetTableMsg()
{
tableName = "TempTable";
fieldNameItem = null;
}
/// <summary>
/// 设置字段值
/// </summary>
/// <param name="fieldItem">字段值列表</param>
/// <returns>操作成功时返回true</returns>
public bool SetValue(IList<object> fieldItem)
{
if (fieldNameItem[0].ToUpper() == "ID")
{
if (fieldItem.Count != fieldNameItem.Length - 1)
return false;
for (int i = 0; i < fieldItem.Count; i++)
{
fields[i + 1].FieldValue = getKindValue(fieldItem, fields[i + 1].FieldType);
}
}
else
{
if (fieldItem.Count != fieldNameItem.Length)
return false;
for (int i = 0; i < fieldItem.Count; i++)
{
fields.FieldValue = getKindValue(fieldItem, fields.FieldType);
}
}
return true;
}
/// <summary>
/// 获取数据的真实类型值
/// </summary>
/// <param name="data">数据原值</param>
/// <param name="type">类型</param>
/// <returns>真实类型值</returns>
protected object getKindValue(object data, OracleType type)
{
if (type == OracleType.Int32)
return Convert.ToInt32(data);
else if (type == OracleType.Float)
return Convert.ToDouble(data);
else if (type == OracleType.Number)
return Convert.ToDecimal(data);
else if (type == OracleType.DateTime)
return Convert.ToDateTime(data);
else
return Convert.ToString(data);
}
}


所有的类都转换为基础类之后在进行增删查改的操作