using Oracle.ManagedDataAccess.Client; using System; using System.Data; using System.Text; using System.Collections; using UAS_LabelMachine.PublicMethod; using System.Collections.Generic; namespace UAS_LabelMachine { public class DataHelper { //吉利通外网地址 private readonly string ConnectionStrings = "Connection Timeout=0;Pooling=false;Password=select!#%*(;User ID=T_JEELYTON_WMS;Pooling=false;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=www.jeelyton.net)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));"; //吉利通ERP地址 public static readonly string ERPAddesss = "http://www.jeelyton.net:8099/WMS/"; //吉利通FTP public static readonly string FTPAdress = "ftp://www.jeelyton.net:21|JLTFTP|Admin1@#"; //Oracle端口 public static readonly string OraclePort = "1522"; //需要显示的账套 public static readonly string Masters = "T_JEELYTON_WMS,JEELYTON_WMS"; public static string DBConnectionString; static OracleConnection connection = null; static OracleCommand command = null; /// /// 重置连接的次数 /// int ReconnectTime = 0; /// /// 执行构造函数的时候打开数据库的链接 /// public DataHelper() { try { //如果选择的是默认数据则直接用配置文件的信息连接,否则选择数据库的账套信息 if (DBConnectionString == null || DBConnectionString == ConnectionStrings) { DBConnectionString = ConnectionStrings; connection = new OracleConnection(ConnectionStrings); } else { connection = new OracleConnection(DBConnectionString); } connection.Open(); } catch (Exception e) { LogManager.DoLog(e.Message); } } /// /// /// public void Dispose() { connection.Dispose(); } /// /// 获取第一行第一列的信息 /// public object getFieldDataByCondition(string TableName, string Field, string Condition) { DataTable dt = new DataTable(); string sql = "select " + Field + " from " + TableName + " where " + Condition; command = new OracleCommand(sql, connection); try { OracleDataAdapter ad = new OracleDataAdapter(); ad.SelectCommand = command; ad.Fill(dt); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; OracleDataAdapter ad = new OracleDataAdapter(command); ad.Fill(dt); } if (dt.Rows.Count > 0) { command.Dispose(); return dt.Rows[0][0]; } else { command.Dispose(); return ""; } } /// /// 获取指定表的记录的条数 ,带条件 /// /// public int getRowCount(string TableName, string Condition) { DataTable dt = new DataTable(); string sql = "select count(1) from " + TableName + " where " + Condition; command = new OracleCommand(sql, connection); try { OracleDataAdapter ad = new OracleDataAdapter(command); ad.Fill(dt); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; OracleDataAdapter ad = new OracleDataAdapter(command); ad.Fill(dt); } int count = int.Parse(dt.Rows[0][0].ToString()); command.Dispose(); return count; } /// /// 获取指定表的记录的条数 ,不带条件 /// /// /// public int getRowCount(string TableName) { DataTable dt = new DataTable(); string sql = "select count(1) from " + TableName; command = new OracleCommand(sql, connection); try { OracleDataAdapter ad = new OracleDataAdapter(command); ad.Fill(dt); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; OracleDataAdapter ad = new OracleDataAdapter(command); ad.Fill(dt); } int count = int.Parse(dt.Rows[0][0].ToString()); command.Dispose(); return count; } /// /// 通过表名和获取单行的记录 /// public DataTable getFieldsDataByCondition(string TableName, string[] Fields, string Condition) { DataTable dt = new DataTable(); string sql = "select "; sql += AddField(Fields); sql += " from " + TableName + " where " + Condition + " and rownum=1"; command = new OracleCommand(sql, connection); try { OracleDataAdapter ad = new OracleDataAdapter(command); ad.Fill(dt); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; OracleDataAdapter ad = new OracleDataAdapter(command); ad.Fill(dt); } command.Dispose(); return dt; } /// /// 通过表名和获取单行的记录 /// public DataTable getFieldsDatasByCondition(string sql) { DataTable dt = new DataTable(); command = new OracleCommand(sql, connection); try { OracleDataAdapter ad = new OracleDataAdapter(command); ad.Fill(dt); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; OracleDataAdapter ad = new OracleDataAdapter(command); ad.Fill(dt); } command.Dispose(); return dt; } /// /// 按分页获取数据 /// /// 表名 /// 查询字段 /// 当前页面 /// 页面展示条数 /// /// // SELECT * FROM (SELECT A.* FROM (SELECT* FROM datalist) A WHERE ROWNUM <= 50) WHERE ROWNUM >= 21 public DataTable getFieldsDatasByPageing(string TableName, string Fields, int CurrentPage, int PageSize, string Caller, params string[] condition) { DataTable dt = new DataTable(); StringBuilder sql = new StringBuilder(); //先查询出配置出去的列 //获取查询的列 string[] caption = GetCaptionFromField(Fields); //获取对应列的描述 string[] field = GetField(Fields); sql.Append(" select * from (select RowNum RN, A.* from (select "); sql.Append(AddField(caption)); if (condition.Length > 0) { if (condition[0].Trim() != "") { sql.Append(" from " + TableName + " where " + condition[0] + " ) A where ROWNUM <=" + CurrentPage * PageSize + ") where RN>" + (CurrentPage - 1) * PageSize); } else { sql.Append(" from " + TableName + ") A where ROWNUM <= " + CurrentPage * PageSize + ") where RN> " + (CurrentPage - 1) * PageSize); } } command = new OracleCommand(sql.ToString(), connection); OracleDataAdapter ad = new OracleDataAdapter(command); ad.Fill(dt); dt.Columns.RemoveAt(0); foreach (DataColumn dc in dt.Columns) { dc.ColumnName = field[dt.Columns.IndexOf(dc)]; dc.Caption = caption[dt.Columns.IndexOf(dc)]; } command.Dispose(); return dt; } /// /// 通过表名,字段和条件获取DataTable类型的数据 /// public DataTable getFieldsDatasByCondition(string TableName, string[] Fields, string Condition) { DataTable dt = new DataTable(); string sql = "select "; sql += AddField(Fields); sql += " from " + TableName + " where " + Condition; command = new OracleCommand(sql, connection); OracleDataAdapter ad = new OracleDataAdapter(command); try { ad.Fill(dt); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; ad = new OracleDataAdapter(command); ad.Fill(dt); ad.Dispose(); } command.Dispose(); return dt; } /// /// 根据DataTable和指定的表名更新数据,如果需要保存新增的数据则需要传递一条Insert的SQL /// /// /// /// public void UpDateTableByCondition(DataTable DataTable, string TableName, string PrimaryKey, params string[] sql) { if (DataTable == null) { return; } StringBuilder sb = new StringBuilder(); //预防插入的DataTable中存在不属于该表的列,在进行下一步操作之前全部剔除 DataTable data = (DataTable)ExecuteSql("select Column_Name,Data_Type from cols where TABLE_name=upper('" + TableName + "')", "select"); //将所有的字段拼接起来 for (int i = 0; i < data.Rows.Count; i++) { sb.Append("#" + data.Rows[i]["Column_Name"].ToString()); } //移除掉所有不属于该表的列 for (int i = DataTable.Columns.Count - 1; i >= 0; i--) { if (!sb.ToString().Contains(DataTable.Columns[i].ColumnName.ToUpper())) { DataTable.Columns.RemoveAt(i); } } sb.Clear(); //计算有多少个是新加的行,根据主键为空来进行判断 int NewRowCount = 0; for (int i = 0; i < DataTable.Rows.Count; i++) { if (DataTable.Rows[i][PrimaryKey] == null || DataTable.Rows[i][PrimaryKey].ToString() == "") { NewRowCount = NewRowCount + 1; } } if (sql.Length > 0) { if (NewRowCount > 0) { //获取参数的个数 int paramsNum = sql[0].Split(':').Length - 1; //解析参数的数据 string[] param = GetParamFromSQL(sql[0]); //新建一个二维数组去 string[][] param_array = new string[paramsNum][]; //实例化每个一维数组 for (int i = 0; i < paramsNum; i++) { param_array[i] = new string[NewRowCount]; } //设置每列参数的索引 int num = 0; //变量所有的行,如果有主键为空的则移除,不为空的进行参数的拼接 for (int i = DataTable.Rows.Count - 1; i >= 0; i--) { if (DataTable.Rows[i][PrimaryKey] == null || DataTable.Rows[i][PrimaryKey].ToString() == "") { //当为新添加行的时候才去设置参数,设置过后索引+1 for (int j = 0; j < paramsNum; j++) { param_array[j][num] = DataTable.Rows[i][param[j]].ToString(); } DataTable.Rows.RemoveAt(i); num++; } } BatchInsertDataTable(sql[0], param, param_array); } } //不是新增行的启用更新的方法 sb.Append("update " + TableName + " set "); //拼接语句,特殊处理日期 foreach (DataColumn dc in DataTable.Columns) { if (!dc.DataType.ToString().Equals("System.DateTime")) { sb.Append(dc.Caption + "=:" + dc.Caption + ","); } else { sb.Append(dc.Caption + "=:" + dc.Caption + ","); } } sb.Remove(sb.Length - 1, 1); sb.Append(" where " + PrimaryKey + "=:" + PrimaryKey); command = new OracleCommand(sb.ToString(), connection); OracleDataAdapter ad = new OracleDataAdapter(command); // 参数的长度是DataTable的行数决定的 command.ArrayBindCount = DataTable.Rows.Count; //默认全部是Varchar2类型的 OracleDbType ob = OracleDbType.Varchar2; for (int i = 0; i < DataTable.Columns.Count; i++) { object[] param = new object[DataTable.Rows.Count]; for (int j = 0; j < DataTable.Rows.Count; j++) { DateTime dt = DateTime.Now; //if (DateTime.TryParse(DataTable.Rows[j][i].ToString(), out dt)) //{ // param[j] = dt; // ob = OracleDbType.Date; //} //else //{ ob = OracleDbType.Varchar2; param[j] = DataTable.Rows[j][i]; //} } //添加批量更新的参数 command.Parameters.Add(new OracleParameter(DataTable.Columns[i].Caption, ob, param, ParameterDirection.Input)); } ad.UpdateCommand = command; ad.Update(DataTable); command.Dispose(); ad.Dispose(); } public void SaveDataTable(DataTable dt, string TableName, string ID, params string[] sql) { if (dt == null) { return; } StringBuilder sb = new StringBuilder(); //预防插入的DataTable中存在不属于该表的列,在进行下一步操作之前全部剔除 DataTable data = (DataTable)ExecuteSql("select Column_Name,Data_Type from cols where TABLE_name=upper('" + TableName + "')", "select"); //将所有的字段拼接起来 for (int i = 0; i < data.Rows.Count; i++) { sb.Append("#" + data.Rows[i]["Column_Name"].ToString()); } //移除掉所有不属于该表的列 for (int i = dt.Columns.Count - 1; i >= 0; i--) { if (!sb.ToString().Contains(dt.Columns[i].ColumnName.ToUpper())) { dt.Columns.RemoveAt(i); } } sb.Clear(); //计算有多少个是新加的行,根据主键为空来进行判断 int NewRowCount = 0; for (int i = 0; i < dt.Rows.Count; i++) { if (dt.Rows[i][ID] == null || dt.Rows[i][ID].ToString() == "") { NewRowCount = NewRowCount + 1; } } if (sql.Length > 0) { if (NewRowCount > 0) { //获取参数的个数 int paramsNum = sql[0].Split(':').Length - 1; //解析参数的数据 string[] param = GetParamFromSQL(sql[0]); //新建一个二维数组去 string[][] param_array = new string[paramsNum][]; //实例化每个一维数组 for (int i = 0; i < paramsNum; i++) { param_array[i] = new string[NewRowCount]; } //设置每列参数的索引 int num = 0; //变量所有的行,如果有主键为空的则移除,不为空的进行参数的拼接 for (int i = dt.Rows.Count - 1; i >= 0; i--) { if (dt.Rows[i][ID] == null || dt.Rows[i][ID].ToString() == "") { //当为新添加行的时候才去设置参数,设置过后索引+1 for (int j = 0; j < paramsNum; j++) { param_array[j][num] = dt.Rows[i][param[j]].ToString(); } dt.Rows.RemoveAt(i); num++; } } BatchInsertDataTable(sql[0], param, param_array); } } sb.Clear(); sb.Append("update " + TableName + " set "); int ColumnCount = dt.Columns.Count; int RowCount = dt.Rows.Count; //存数据的参数 List Parameter = new List(); //存参数名的参数 string[] ParName = new string[ColumnCount]; for (int i = 0; i < ColumnCount; i++) { ParName[i] = dt.Columns[i].ColumnName; if (i == dt.Columns.Count - 1) sb.Append(dt.Columns[i].ColumnName + "=:" + dt.Columns[i].ColumnName); else sb.Append(dt.Columns[i].ColumnName + "=:" + dt.Columns[i].ColumnName + ","); } sb.Append(" where " + ID + " =:" + ID); //先添加参数 Parameter.Add(ParName); //添加参数的具体内容 for (int i = 0; i < ColumnCount; i++) { string[] par = new string[RowCount]; for (int j = 0; j < RowCount; j++) { par[j] = dt.Rows[j][i].ToString(); } Parameter.Add(par); } BatchInsert(sb.ToString(), Parameter.ToArray()); } /// /// 检测内容是否存在 /// /// /// /// public bool CheckExist(string TableName, string Condition) { string sql = "select count(1) from " + TableName + " where " + Condition; command = new OracleCommand(sql, connection); OracleDataAdapter ad = new OracleDataAdapter(command); DataTable dt = new DataTable(); ad.Fill(dt); if (int.Parse(dt.Rows[0][0].ToString()) > 0) { command.Dispose(); ad.Dispose(); return true; } else { command.Dispose(); ad.Dispose(); return false; } } /// /// 直接执行SQL,同时传入SQL的类型 /// /// /// /// public object ExecuteSql(string SQL, string Type, params object[] names) { object result = null; command = new OracleCommand(SQL, connection); //用来拼接参数的 if (names.Length > 0) { string[] par = SQL.Split(':'); //用来存参数的数组 StringBuilder[] addpar = new StringBuilder[par.Length - 1]; for (int i = 0; i < par.Length - 1; i++) { //新建一个char类型的数组用来存储每个字节的变量 char[] c = par[i + 1].ToCharArray(); addpar[i] = new StringBuilder(); for (int j = 0; j < c.Length; j++) { if (c[j] != ' ' && c[j] != ',' && c[j] != ')') { addpar[i].Append(c[j]); } else { break; } } } for (int i = 0; i < addpar.Length; i++) { command.Parameters.Add(new OracleParameter(addpar[i].ToString(), OracleDbType.Varchar2, names[i], ParameterDirection.Input)); } } switch (Type.ToUpper()) { case "SELECT": try { result = new DataTable(); OracleDataAdapter ad = new OracleDataAdapter(command); ad.Fill((DataTable)result); ad.Dispose(); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; result = new DataTable(); OracleDataAdapter ad = new OracleDataAdapter(command); ad.Fill((DataTable)result); ad.Dispose(); } //成功执行后将重复连接数置为0 break; case "DELETE": try { result = command.ExecuteNonQuery(); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; command.ExecuteNonQuery(); } break; case "UPDATE": try { result = command.ExecuteNonQuery(); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; command.ExecuteNonQuery(); } break; case "INSERT": try { result = command.ExecuteNonQuery(); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; command.ExecuteNonQuery(); } break; } command.Dispose(); return result; } public int GetDistinctRowCount(string TableName, string Field) { DataTable dt = new DataTable(); string sql = "select distinct count('" + Field + "') from " + TableName; OracleDataAdapter ad = new OracleDataAdapter(new OracleCommand(sql, connection)); ad.Fill(dt); command.Dispose(); return int.Parse(dt.Rows[0][0].ToString()); } /// /// 根据Caller获取流水号 /// /// /// public string GetSerialNumberByCaller(string Caller) { string SerialNumber = getFieldDataByCondition("MaxNumbers", "mn_number", "mn_tablename='" + Caller + "'").ToString(); UpdateByCondition("MaxNumbers", "mn_number=mn_number+1", "mn_tablename='" + Caller + "'"); return SerialNumber; } /// /// 根据主键ID删除表的数据 /// /// 表名 /// 主键 /// 需要删除主键ID的数组 public void DeleteDataByID(string TableName, string ID, string[] DeleteID) { string sql = "delete from " + TableName + " where " + ID + " =:DeleteID"; command = new OracleCommand(sql, connection); command.ArrayBindCount = DeleteID.Length; command.Parameters.Add(new OracleParameter("DeleteID", OracleDbType.Long, DeleteID, ParameterDirection.Input)); command.ExecuteNonQuery(); command.Dispose(); } /// /// 通过序列的名称获取序列 /// /// /// public string GetSEQ(string SeqName) { DataTable dt = new DataTable(); dt = (DataTable)ExecuteSql("SELECT " + SeqName + ".NEXTVAL FROM DUAL", "select"); return dt.Rows[0][0].ToString(); } /// /// 批量通过SQL来执行插入操作 ,参数的第一个数一个string[]数组,用来传递需要添加的参数的名称 /// 之后的是名称参数数组对应的 ,所有的插入参数数据长度必须是一致的 /// /// /// public void BatchInsert(string sql, params object[][] names) { command = new OracleCommand(sql, connection); command.ArrayBindCount = names[1].Length; //因为第一个数组保存的是参数的名称,所以循环从1而不是0开始 //将第一个数组的下标固定为0作为循环添加的参数的名称 for (int i = 1; i <= names[0].Length; i++) { if (names[0][i - 1].ToString().ToUpper().Contains("DATE") && !names[0][i - 1].ToString().ToUpper().Contains("DATECODE")) { DateTime[] time = new DateTime[names[i].Length]; try { for (int j = 0; j < names[i].Length; j++) { time[j] = Convert.ToDateTime(names[i][j]); } command.Parameters.Add(new OracleParameter(names[0][i - 1].ToString(), OracleDbType.Date, time, ParameterDirection.Input)); } catch (Exception) { command.Parameters.Add(new OracleParameter(names[0][i - 1].ToString(), OracleDbType.Varchar2, names[i], ParameterDirection.Input)); } } else { command.Parameters.Add(new OracleParameter(names[0][i - 1].ToString(), OracleDbType.Varchar2, names[i], ParameterDirection.Input)); } } try { command.ExecuteNonQuery(); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; command.ExecuteNonQuery(); } command.Dispose(); } public void BatchInsertDataTable(string sql, string[] param, params object[][] param1) { command = new OracleCommand(sql, connection); command.ArrayBindCount = param1[0].Length; //因为第一个数组保存的是参数的名称,所以循环从1而不是0开始 //将第一个数组的下标固定为0作为循环添加的参数的名称 for (int i = 0; i < param.Length; i++) { command.Parameters.Add(new OracleParameter(param[i].ToString(), OracleDbType.Varchar2, param1[i], ParameterDirection.Input)); } command.ExecuteNonQuery(); command.Dispose(); } /// 取Configs表中的配置,进行该客户是否执行某个操作 /// /// /// /// public object GetConfig(string Code, string Caller) { DataTable dt = new DataTable(); string sql = "select * from configs where code='" + Code + "' and caller='" + Caller + "'"; dt = (DataTable)ExecuteSql(sql, "select"); if (dt.Rows.Count == 0) { return ""; } else { return dt.Rows[0]["Data"]; } } //将数据类型的列类型转换为DataTable public DataTable DataTypeColumnToDataTable(DataTable dt) { DataTable dt1 = new DataTable(); dt1.Rows.Add(); foreach (DataRow dr in dt.Rows) { dt1.Columns.Add(dr[0].ToString()); int index = dt.Rows.IndexOf(dr); if (dr[1].ToString() == "NUMBER") { dt1.Rows[0][index] = 0; } if (dr[1].ToString() == "VARCHAR2") { dt1.Rows[0][index] = "这是一段文字"; } if (dr[1].ToString() == "DATE") { dt1.Rows[0][index] = DateTime.Now.ToString("yyyy-MM-dd"); } if (dr[1].ToString() == "FLOAT") { dt1.Rows[0][index] = 1.0; } if (dr[1].ToString() == "CLOB") { dt1.Rows[0][index] = "一段长文字"; } } return dt1; } /// /// 通过条件更新 /// /// /// /// public void UpdateByCondition(string TableName, string update, string condition) { string sql = "update " + TableName + " set " + update + " where " + condition; command = new OracleCommand(sql, connection); command.ExecuteNonQuery(); command.Dispose(); } /// /// 调用存储过程 /// /// /// public void CallProcedure(string ProcedureName, DataTable upload, out string pibid) { StringBuilder sql = new StringBuilder(); pibid = ""; sql.Append("declare barcode BarCodeUpload_arr;begin barcode:=BarCodeUpload_arr("); for (int i = 0; i < upload.Rows.Count; i++) { //特殊语句,传递回拼接的ID给到跟新本地数据 pibid += upload.Rows[i]["pib_id"].ToString() + ","; sql.Append("BarCodeUpload("); for (int j = 0; j < upload.Columns.Count; j++) { sql.Append("'" + upload.Rows[i][j].ToString() + "',"); } sql.Remove(sql.Length - 1, 1); sql.Append("),"); } pibid += 0; sql.Remove(sql.Length - 1, 1); sql.Append(");sp_uploadbarcode(barcode);END;"); command = new OracleCommand(); command.Connection = connection; command.CommandText = sql.ToString(); try { command.ExecuteNonQuery(); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; command.ExecuteNonQuery(); } command.Dispose(); } /// /// 调用存储过程 /// /// /// public void CallProcedure(string ProcedureName, DataTable upload) { StringBuilder sql = new StringBuilder(); sql.Append("declare barcode BarCodeUpload_arr;begin barcode:=BarCodeUpload_arr("); for (int i = 0; i < upload.Rows.Count; i++) { //特殊语句,传递回拼接的ID给到跟新本地数据 sql.Append("BarCodeUpload("); for (int j = 0; j < upload.Columns.Count; j++) { sql.Append("'" + upload.Rows[i][j].ToString() + "',"); } sql.Remove(sql.Length - 1, 1); sql.Append("),"); } sql.Remove(sql.Length - 1, 1); sql.Append(");sp_uploadbarcode(barcode);END;"); command = new OracleCommand(); command.Connection = connection; command.CommandText = sql.ToString(); try { command.ExecuteNonQuery(); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; command.ExecuteNonQuery(); } command.Dispose(); } /// /// 调用存储过程 /// /// /// public void CallProcedure(string ProcedureName, ref string[] param) { command = new OracleCommand(ProcedureName); command.Connection = connection; command.CommandText = ProcedureName; command.CommandType = CommandType.StoredProcedure; for (int i = 0; i < param.Length; i++) command.Parameters.Add(new OracleParameter(param[i].ToString(), OracleDbType.Varchar2, 200, param[i], ParameterDirection.InputOutput)); try { command.ExecuteNonQuery(); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; command.ExecuteNonQuery(); } for (int i = 0; i < command.Parameters.Count; i++) param[i] = command.Parameters[i].Value.ToString(); command.Dispose(); } /// /// 调用存储过程 /// /// /// public void CallProcedure(string ProcedureName, string[] param) { command = new OracleCommand(ProcedureName); command.Connection = connection; command.CommandText = ProcedureName; command.CommandType = CommandType.StoredProcedure; for (int i = 0; i < param.Length; i++) command.Parameters.Add(new OracleParameter(param[i].ToString(), OracleDbType.Varchar2, 200, param[i], ParameterDirection.InputOutput)); try { command.ExecuteNonQuery(); } catch (Exception) { connection = new OracleConnection(DBConnectionString); connection.Open(); command.Connection = connection; command.ExecuteNonQuery(); } command.Dispose(); } /// /// 出现异常进行回滚的执行方法 /// /// public void ExecuteSQLTran(params string[] SQL) { OracleTransaction tx = connection.BeginTransaction(); command = new OracleCommand(); command.Connection = connection; command.Transaction = tx; try { foreach (string sql in SQL) { if (!String.IsNullOrEmpty(sql)) { command.CommandText = sql; command.ExecuteNonQuery(); } } tx.Commit(); } catch (OracleException E) { tx.Rollback(); throw new Exception(E.Message); } } /// /// 用于将string 的数组转换成SQL的查询内容 /// /// /// private string AddField(string[] Fields) { string sql = " "; foreach (string field in Fields) { sql += field + ","; } return sql.Substring(0, sql.Length - 1); } /// /// 通过查询的内容获取到字段的描述 /// /// /// private static string[] GetCaptionFromField(string field) { string[] caption = field.Split(','); for (int i = 0; i < caption.Length; i++) { caption[i] = caption[i].Substring(0, caption[i].LastIndexOf("as")).Trim(); } return caption; } /// /// 通过查询的语句获取查询的字段 /// /// /// private static string[] GetField(string field) { string[] fields = field.Split(','); for (int i = 0; i < fields.Length; i++) { fields[i] = fields[i].Substring(fields[i].LastIndexOf("as") + 2, fields[i].Length - fields[i].LastIndexOf("as") - 2).Trim(); } return fields; } public static string[] GetParamFromSQL(string SQL) { string[] par = SQL.Split(':'); //用来存参数的数组 StringBuilder[] addpar = new StringBuilder[par.Length - 1]; string[] param = new string[par.Length - 1]; for (int i = 0; i < par.Length - 1; i++) { //新建一个char类型的数组用来存储每个字节的变量 char[] c = par[i + 1].ToCharArray(); addpar[i] = new StringBuilder(); for (int j = 0; j < c.Length; j++) { if (c[j] != ' ' && c[j] != ',' && c[j] != ')') { addpar[i].Append(c[j]); } else { break; } } } for (int i = 0; i < par.Length - 1; i++) { param[i] = addpar[i].ToString(); } return param; } } }