public ExecutionResult ExecuteSql(string sql, ExecuteMode executeMode, Hashtable htValues)
{
ExecutionResult execRes = new ExecutionResult();
bool designMode = string.IsNullOrEmpty(CliUtils.fLoginDB);
if (designMode)
{
return ExecuteSqlDesign(sql, executeMode, htValues);
}
string database = designMode ? report.EEPAlias : CliUtils.fLoginDB;
CliUtils.fLoginDB = database;
ClientType clientType = CliUtils.GetDataBaseType(database);
DBParameter dbParameter = new DBParameter(clientType);
try
{
ArrayList parameters = new ArrayList();
if (htValues != null)
{
foreach (DictionaryEntry de in htValues)
{
IDbDataParameter dbParam = dbParameter.CreateParameter();
dbParam.ParameterName = dbParameter.ParamTag + de.Key;
dbParam.Value = de.Value;
if (de.Value != null)
{
if (de.Value.GetType().Name == "Byte[]")
{
if (dbParam is OleDbParameter)
(dbParam as OleDbParameter).OleDbType = OleDbType.LongVarBinary;
else
dbParam.DbType = DbType.Binary;
}
else
{
if (dbParam is OleDbParameter)
(dbParam as OleDbParameter).OleDbType = OleDbType.VarChar;
else
dbParam.DbType = DbType.String;
}
}
else
{
if (dbParam is OleDbParameter)
(dbParam as OleDbParameter).OleDbType = OleDbType.VarChar;
else
dbParam.DbType = DbType.String;
dbParam.Value = "";
}
parameters.Add(dbParam);
}
}
DataSet ds = CliUtils.ExecuteSql("GLModule", "cmdRefValUse", sql, database, executeMode == ExecuteMode.Select, CliUtils.fCurrentProject, null, parameters);
if (executeMode == ExecuteMode.Select)
{
execRes.Anything = ds.Tables[0];
}
//if (executeMode == ExecuteMode.Select)
//{
// iDbDataAdapter = DBUtils.CreateDbDataAdapter(iDbCommand);
// if (iDbCommand.Connection is OleDbConnection)
// {
// DataTable dt = new DataTable();
// (iDbDataAdapter as OleDbDataAdapter).Fill(dt);
// ds.Tables.Add(dt);
// }
// else
// iDbDataAdapter.Fill(ds);
// //iDbDataAdapter.Fill(ds);
// execRes.Anything = ds.Tables[0];
//}
//else
//{
// iDbCommand.ExecuteNonQuery();
//}
execRes.Status = true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (designMode)
{
CliUtils.fLoginDB = string.Empty;
}
}
return execRes;
}
private string GetInsertSysRptTableSql(Hashtable values)
{
string database = string.IsNullOrEmpty(CliUtils.fLoginDB) ? report.EEPAlias : CliUtils.fLoginDB;
ClientType clientType = CliUtils.GetDataBaseType(database);
DBParameter dbParameter = new DBParameter(clientType);
string sql = "";
sql = "insert into " + SysRptDB.TableName + " (";
foreach (DictionaryEntry de in values)
{
sql += de.Key + ",";
}
sql = sql.Remove(sql.Length - 1);
sql += ")";
sql += " values(";
foreach (DictionaryEntry de in values)
{
if (clientType == ClientType.ctOleDB || clientType == ClientType.ctInformix)
sql += dbParameter.ParamTag + ",";
else
sql += dbParameter.ParamTag + de.Key + ",";
}
sql = sql.Remove(sql.Length - 1);
sql += ")";
return sql;
}
private string GetUpdateSysRptTableSql(Hashtable values)
{
string database = string.IsNullOrEmpty(CliUtils.fLoginDB) ? report.EEPAlias : CliUtils.fLoginDB;
ClientType clientType = CliUtils.GetDataBaseType(database);
DBParameter dbParameter = new DBParameter(clientType);
string sql = "";
sql = "Update " + SysRptDB.TableName;
sql += " set ";
for (int i = 2; i < SysRptDB.sysTableColumns.Count; i++)
{
foreach (DictionaryEntry de in values)
{
if (de.Key == SysRptDB.sysTableColumns[i])
{
if (clientType == ClientType.ctOleDB || clientType == ClientType.ctInformix)
sql += SysRptDB.sysTableColumns[i] + " = " + dbParameter.ParamTag + ",";
else
sql += SysRptDB.sysTableColumns[i] + " = " + dbParameter.ParamTag + de.Key + ",";
}
}
}
sql = sql.Remove(sql.Length - 1);
sql += " where ";
for (int i = 0; i < 2; i++)
{
foreach (DictionaryEntry de in values)
{
if (de.Key == SysRptDB.sysTableColumns[i])
{
if (clientType == ClientType.ctOleDB || clientType == ClientType.ctInformix)
sql += SysRptDB.sysTableColumns[i] + " = " + dbParameter.ParamTag + " and ";
else
sql += SysRptDB.sysTableColumns[i] + " = " + dbParameter.ParamTag + de.Key + " and ";
}
}
}
sql = sql.Remove(sql.Length - 4, 4);
return sql;
}
public ExecutionResult ExecuteSqlDesign(string sql, ExecuteMode executeMode, Hashtable htValues)
{
IDbConnection iDbConn = GetConnection(report.EEPAlias);
#region Variable Definition
ExecutionResult execRes = new ExecutionResult();
//IDbConnection iDbConn = null;
IDbCommand iDbCommand = null;
IDbDataAdapter iDbDataAdapter = null;
DataSet ds = new DataSet();
IDbDataParameter dbParam = null;
#endregion
DBParameter dbParameter = new DBParameter(DBUtils.GetDatabaseType(iDbConn));
try
{
if (iDbConn.State == ConnectionState.Closed)
{
iDbConn.Open();
}
iDbCommand = iDbConn.CreateCommand();
iDbCommand.CommandText = sql;
if (htValues != null)
{
foreach (DictionaryEntry de in htValues)
{
dbParam = iDbCommand.CreateParameter();
dbParam.ParameterName = dbParameter.ParamTag + de.Key;
dbParam.Value = de.Value;
if (de.Value != null)
{
if (de.Value.GetType().Name == "Byte[]")
{
if (iDbCommand.Connection is OleDbConnection)
(dbParam as OleDbParameter).OleDbType = OleDbType.LongVarBinary;
else
dbParam.DbType = DbType.Binary;
}
else
{
if (iDbCommand.Connection is OleDbConnection)
(dbParam as OleDbParameter).OleDbType = OleDbType.VarChar;
else
dbParam.DbType = DbType.String;
}
}
else
{
if (iDbCommand.Connection is OleDbConnection)
(dbParam as OleDbParameter).OleDbType = OleDbType.VarChar;
else
dbParam.DbType = DbType.String;
dbParam.Value = "";
}
iDbCommand.Parameters.Add(dbParam);
}
}
if (executeMode == ExecuteMode.Select)
{
iDbDataAdapter = DBUtils.CreateDbDataAdapter(iDbCommand);
DataTable dt = new DataTable();
if (iDbCommand.Connection is OleDbConnection)
{
(iDbDataAdapter as OleDbDataAdapter).Fill(dt);
ds.Tables.Add(dt);
}
else
iDbDataAdapter.Fill(ds);
//iDbDataAdapter.Fill(ds);
execRes.Anything = ds.Tables[0];
}
else
{
iDbCommand.ExecuteNonQuery();
}
execRes.Status = true;
}
catch (Exception ex)
{
throw ex;
}
finally
{
iDbConn.Close();
//iDbConn.Dispose();
}
return execRes;
}
