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;
}
}
}