Oracle基本操作代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】 Oracle基本操作
作者:东昌 / 发布于2015/11/13/ 668

	#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[i].FieldValue != null && record[i].FieldValue.ToString().Trim() != "")
	 {
	 fields += record[i].FieldName + ", ";
	 values += paraItem[i].ParameterName + ", ";
	 paraItem[i].Value = record[i].FieldValue;
	 s = s + paraItem[i].Value + ",";
	 command.Parameters.Add(paraItem[i]);
	 }
	 }
	 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[i].FieldName, record[i].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[i] = GetItemParameters(record[i]);

	 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
试试其它关键字
　Oracle　　基本操作　
同语言下
可能有用的
东昌贡献的其它代码(28)