protected IDbDataParameter BuildParam(string sParamName, object oValue)
{
IDbDataParameter oParam;
if (oValue is int ||
oValue is decimal ||
oValue is float ||
oValue is double)
{
oParam = I3DBUtil.PrepareParameter(sParamName, OracleType.Number, 22, oValue);
}
else if (oValue is DateTime)
{
oParam = I3DBUtil.PrepareParameter(sParamName, OracleType.DateTime, oValue);
}
else
{
if (opType == I3SqlOperateType.前匹配)
{
oValue = oValue + "%";
}
else if (opType == I3SqlOperateType.后匹配)
{
oValue = "%" + oValue;
}
else if (opType == I3SqlOperateType.全匹配)
{
oValue = "%" + oValue + "%";
}
oParam = I3DBUtil.PrepareParameter(sParamName, OracleType.NVarChar, 64, oValue);
}
return(oParam);
}
public static int BatchGetSequence(string sSeqName, int iSeqCount, string connection)
{
string sTableName = sSeqName.StartsWith("SEQ_") ? sSeqName.Substring(4) : sSeqName;
if (true)//trans == null)
{
object obj = I3DbHelper.ExecuteScalar(DBServerType.SqlServer, connection, CommandType.Text, "Select NEXT_VALUE From INS_SEQUENCE Where TABLE_NAME=@W_TABLE_NAME;Update INS_SEQUENCE Set NEXT_VALUE=NEXT_VALUE+:W_ITEMCOUNT Where TABLE_NAME=@W_TABLE_NAME;", new SqlParameter("@W_TABLE_NAME", sTableName), new SqlParameter("@W_ITEMCOUNT", iSeqCount));
if (obj == null || obj == DBNull.Value)
{
I3DbHelper.ExecuteNonQuery(DBServerType.SqlServer, connection, CommandType.Text, "Insert INS_SEQUENCE(TABLE_NAME,NEXT_VALUE) Values(:V_TABLE_NAME,:W_ITEMCOUNT)", new SqlParameter("@V_TABLE_NAME", sTableName), new SqlParameter("@W_ITEMCOUNT", iSeqCount));
return(1);
}
return(I3DBUtil.ToInt(obj));
}
////else
////{
//// object obj = DbHelper.ExecuteScalar(trans, CommandType.Text, "Select NEXT_VALUE From INS_SEQUENCE Where TABLE_NAME=@W_TABLE_NAME;Update INS_SEQUENCE Set NEXT_VALUE=NEXT_VALUE+1 Where TABLE_NAME=@W_TABLE_NAME;", new SqlParameter("@W_TABLE_NAME", sTableName));
//// if (obj == null || obj == DBNull.Value)
//// {
//// DbHelper.ExecuteNonQuery(trans, CommandType.Text, "Insert INS_SEQUENCE(TABLE_NAME,NEXT_VALUE) Values(:V_TABLE_NAME,1)", new SqlParameter("@V_TABLE_NAME", sTableName));
//// return 1;
//// }
//// return iId = Int32.Parse(obj + "");
////}
}
public static MySqlParameter Convert2MySqlParameter(IDbDataParameter parameter)
{
string sName = parameter.ParameterName;
if (sName[0] == ':')
{
sName = "@" + sName.Substring(1);
}
else if (sName[0] != '@')
{
sName = "@" + sName;
}
MySqlParameter newParam = new MySqlParameter();
newParam.ParameterName = sName;
if (parameter is OracleParameter)
{
newParam.MySqlDbType = I3DBUtil.Convert2MySqlType((parameter as OracleParameter).OracleType);
}
else if (parameter is SqlParameter)
{
newParam.MySqlDbType = I3DBUtil.Convert2MySqlType((parameter as SqlParameter).SqlDbType);
}
else if (parameter is MySqlParameter)
{
newParam.DbType = parameter.DbType;
}
else
{
newParam.MySqlDbType = I3DBUtil.Convert2MySqlType(parameter.DbType);
}
newParam.Value = parameter.Value;
newParam.Size = parameter.Size;
return(newParam);
}
public void AddData(params object[] data)
{
for (int i = 0; i < data.Length; i++)
{
data[i] = I3DBUtil.InsureDBParam(data[i]);
}
commandTable.Rows.Add(data);
}
public static object ExecuteScalar(DBServerType type, string connectionString, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
{
using (IDbConnection connection = I3DBUtil.CreateConnection(type, connectionString))
{
connection.Open();
return(ExecuteScalar(connection, commandType, commandText, commandParameters));
}
}
public void AddData(params object[] data)
{
for (int i = 0; i < data.Length; i++)
{
data[i] = I3DBUtil.InsureDBParam(data[i]);
}
commandTable.Rows.Add(data);
// this.parametersVals.Add(new ArrayList(data));
}
private List <string> GetTableNameList()
{
if (curItem == null)
{
return(new List <string>());
}
SaveItem();
I3DBUtil.ConnectionString = GetConnectionString();
return(I3DBUtil.GetTableNameList());
}
public static DbDataReader ExecuteReader(DBServerType type, string connectionString, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
{
DbDataReader reader;
IDbConnection connection = I3DBUtil.CreateConnection(type, connectionString);
connection.Open();
try
{
reader = ExecuteReader(connection, null, commandType, commandText, commandParameters, I3DbConnectionOwnership.Internal);
}
catch (Exception ex)
{
connection.Close();
throw;
}
return(reader);
}
public I3MsgInfo BatchExecute(string connection)
{
using (SqlConnection sqlCon = I3DBUtil.CreateAndOpenDbConnection(connection) as SqlConnection)
{
using (SqlTransaction sqlTrans = sqlCon.BeginTransaction())
{
I3MsgInfo msgInfo = BatchExecute(sqlTrans);
if (msgInfo.State)
{
sqlTrans.Commit();
}
else
{
sqlTrans.Rollback();
}
return(msgInfo);
}
}
}
private void btTestConnect_Click(object sender, EventArgs e)
{
if (curItem == null)
{
return;
}
I3DBUtil.ConnectionString = GetConnectionString();
//MessageBox.Show(I3DBUtil.ConnectionString);
try
{
using (I3DBUtil.CreateAndOpenDbConnection())
{
}
MessageBox.Show("OK！", "测试", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "测试", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// 连续增加序列计数，返回最后一个值
/// 快速处理序列的方法
/// </summary>
/// <param name="sSeqName">序列名称</param>
/// <param name="iSeqCount">系列计数</param>
/// <returns>返回最后一个值</returns>
public static int BatchGetSequence(string sSeqName, int iSeqCount, string connection)
{
if (iSeqCount == 1)
{
try
{
return(I3DBUtil.ToInt(I3OracleHelper.ExecuteScalar(connection, CommandType.Text, "Select " + sSeqName + ".NextVal From Dual")));
}
catch
{
I3OracleHelper.ExecuteScalar(connection, CommandType.Text, "create sequence " + sSeqName);
return(I3DBUtil.ToInt(I3OracleHelper.ExecuteScalar(connection, CommandType.Text, "Select " + sSeqName + ".NextVal From Dual")));
}
}
//通过存储过程增加序列值
string sSql = "select BatchUpdateSequence(:W_SEQ_NAME,:W_SEQ_COUNT) from dual";
OracleConnection sqlCon = I3DBUtil.CreateAndOpenDbConnection(connection) as OracleConnection;
sqlCon.Open();
try
{
OracleCommand sqlCom = new OracleCommand(sSql, sqlCon);
sqlCom.Parameters.Add(":W_SEQ_NAME", OracleType.VarChar);
sqlCom.Parameters.Add(":W_SEQ_COUNT", OracleType.Int32);
sqlCom.Parameters[0].Value = sSeqName;
sqlCom.Parameters[1].Value = iSeqCount;
int iLastSeq = -1;
try
{
//选择序列值
iLastSeq = I3DBUtil.ToInt(sqlCom.ExecuteScalar());
}
catch (Exception ex)
{
//添加存储过程
StringBuilder sDDLSql = new StringBuilder();
sDDLSql.Append("create or replace function BatchUpdateSequence(seqName in varchar2, iCount in integer) return integer is ").
Append("Result1 integer; ").
Append("iIndex integer; ").
Append("sSql varchar2(200); ").
Append("begin ").
Append("iIndex := 0; ").
Append("LOOP ").
Append("sSql := 'Select '||seqName||'.NextVal From Dual' ; ").
Append("execute immediate sSql ").
Append(" into Result1; ").
Append("iIndex := iIndex+1; ").
Append("EXIT When iIndex=iCount; ").
Append("End Loop; ").
Append("return (Result1); ").
Append("end BatchUpdateSequence; ");
OracleCommand sqlCom1 = new OracleCommand(sDDLSql.ToString(), sqlCon);
sqlCom1.ExecuteNonQuery();
iLastSeq = I3DBUtil.ToInt(sqlCom.ExecuteScalar());
}
return(iLastSeq - iSeqCount + 1);
}
finally
{
sqlCon.Close();
}
}
/// <summary>
/// 根据设置刷新结果
/// </summary>
public void RefreshResult()
{
switch (fieldType)
{
case I3SearchItemType.sitString:
switch (searchType)
{
case I3SearchType.stNone:
lookString = "";
searchString = "";
break;
case I3SearchType.stEqual:
lookString = fieldCaption + " 等于 " + String1;
searchString = fieldName + " = " + I3DBUtil.QuotedStr(string1);
break;
case I3SearchType.stDim:
lookString = fieldCaption + " 包含 " + String1;
searchString = fieldName + " like " + I3DBUtil.QuotedStr("%" + string1 + "%");
break;
case I3SearchType.stInterval:
lookString = "";
searchString = "";
break;
default:
lookString = "";
searchString = "";
break;
}
break;
case I3SearchItemType.sitNum:
switch (SearchType)
{
case I3SearchType.stNone:
lookString = "";
searchString = "";
break;
case I3SearchType.stEqual:
lookString = fieldCaption + " 等于 " + num1.ToString();
searchString = fieldName + " = " + num1.ToString();
break;
case I3SearchType.stDim:
lookString = "";
searchString = "";
break;
case I3SearchType.stInterval:
lookString = fieldCaption + "在" + num1.ToString() + "和" + num2.ToString() + "之间";
searchString = "(" + fieldName + " >= " + num1.ToString()
+ " and " + fieldName + " <= " + num2.ToString() + ")";
break;
default:
lookString = "";
searchString = "";
break;
}
break;
case I3SearchItemType.sitDate:
switch (SearchType)
{
case I3SearchType.stNone:
lookString = "";
searchString = "";
break;
case I3SearchType.stEqual:
lookString = fieldCaption + " 等于 " + I3DateTimeUtil.ConvertDateTimeToDateString(date1);
searchString = " Convert(char(10)," + fieldName + ",126) = "
+ I3DBUtil.QuotedStr(I3DateTimeUtil.ConvertDateTimeToDateString(date1));
break;
case I3SearchType.stDim:
lookString = "";
searchString = "";
break;
case I3SearchType.stInterval:
lookString = fieldCaption + "在" + I3DateTimeUtil.ConvertDateTimeToDateString(date1)
+ "和" + I3DateTimeUtil.ConvertDateTimeToDateString(date2) + "之间";
searchString = " Convert(char(10)," + fieldName + ",126) between "
+ I3DBUtil.QuotedStr(I3DateTimeUtil.ConvertDateTimeToDateString(date1))
+ " and "
+ I3DBUtil.QuotedStr(I3DateTimeUtil.ConvertDateTimeToDateString(date2));
break;
default:
lookString = "";
searchString = "";
break;
}
break;
default:
lookString = "";
searchString = "";
break;
}
}
private static IDbDataParameter[] DiscoverSpParameterset(string connectionString, string spName, bool includeReturnValueParameter)
{
DBServerType dbServerType = I3DBUtil.AnalysisConnectionString(ref connectionString);
DbConnection connection = null;
switch (dbServerType)
{
case DBServerType.SqlServer:
connection = new SqlConnection(connectionString);
break;
case DBServerType.Oracle:
connection = new OracleConnection(connectionString);
break;
default:
connection = new MySqlConnection(connectionString);
break;
}
IDbDataParameter[] parameterArray2;
try
{
DbCommand command = null;
switch (dbServerType)
{
case DBServerType.SqlServer:
command = new SqlCommand(spName, connection as SqlConnection);
break;
case DBServerType.Oracle:
command = new OracleCommand(spName, connection as OracleConnection);
break;
default:
command = new MySqlCommand(spName, connection as MySqlConnection);
break;
}
try
{
connection.Open();
command.CommandType = (CommandType.StoredProcedure);
switch (dbServerType)
{
case DBServerType.SqlServer:
SqlCommandBuilder.DeriveParameters(command as SqlCommand);
//if ((command as SqlCommand).Parameters[0].SqlDbType == SqlDbType.Cursor)
//{
// (command as SqlCommand).Parameters[0].Direction = (ParameterDirection.Output);
//}
break;
case DBServerType.Oracle:
OracleCommandBuilder.DeriveParameters(command as OracleCommand);
if ((command as OracleCommand).Parameters[0].OracleType == OracleType.Cursor)
{
(command as OracleCommand).Parameters[0].Direction = (ParameterDirection.Output);
}
break;
default:
MySqlCommandBuilder.DeriveParameters(command as MySqlCommand);
//if ((command as MySqlCommand).Parameters[0].SqlDbType == MySqlDbType.Cursor)
//{
// (command as MySqlCommand).Parameters[0].Direction = (ParameterDirection.Output);
//}
break;
}
IDbDataParameter[] parameterArray = new IDbDataParameter[command.Parameters.Count];
command.Parameters.CopyTo(parameterArray, 0);
parameterArray2 = parameterArray;
}
finally
{
command.Dispose();
}
}
finally
{
connection.Close();
connection.Dispose();
}
return(parameterArray2);
}
/// <summary>
/// 获取连接字符串
/// </summary>
/// <returns></returns>
public string GetConnectionString()
{
return(DBServerType + ":" + I3DBUtil.CreateConnectionString(DBServerType, Server, UserName, Password, Database));
}