public object[] GetSqlCommand(string sDSName, string strWhereOrSql, bool isSql, ArrayList paramWhere, string strOrder)
{
bool designMode = GetClientInfo(ClientInfoType.LoginUser).ToString().Length == 0;
string alias = string.Empty;
IDbCommand cmd = AllocateCommand(sDSName, ref alias, designMode);
try
{
string replaceCommandText = ReplacedCommandText;
if (!string.IsNullOrEmpty(replaceCommandText))
{
cmd.CommandText = replaceCommandText;
}
if (!isSql)
{
if (cmd is InfoCommand && (cmd as InfoCommand).MultiSetWhere)
{
(cmd as InfoCommand).ReunionInfoCommand(strWhereOrSql);
strWhereOrSql = string.Empty;//清空,下面有DealAllSqlText
}
if (paramWhere != null)
{
for (int i = 0; i < paramWhere.Count; i++)
{
cmd.Parameters.Add(paramWhere[i] as IDbDataParameter);
}
}
if (cmd is InfoCommand)
{
(cmd as InfoCommand).DealAllSqlText(strWhereOrSql, strOrder);
}
}
bool useSelectTop = false;
if (cmd is InfoCommand)
{
if ((cmd as InfoCommand).SelectPaging && StartRecord.HasValue && MaxRecords.HasValue)
{
//清除packet，下面取所有资料
(cmd as InfoCommand).DealPacketText(StartRecord.Value, MaxRecords.Value);
useSelectTop = true;
}
SqlEventArgs e = new SqlEventArgs(cmd.CommandText);
(cmd as InfoCommand).OnBeforeExecuteSql(e);
cmd.CommandText = e.Sql;
}
if (!string.IsNullOrEmpty(cmd.CommandText) && cmd.CommandText.IndexOf("SysDatabases", StringComparison.OrdinalIgnoreCase) >= 0)
{
throw new Exception("Invalid object name 'SysDatabases'.");
}
DataSet ds = new DataSet();
ds.CaseSensitive = true;
IDbDataAdapter adpater = DBUtils.CreateDbDataAdapter(cmd);
if (!useSelectTop && StartRecord.HasValue && MaxRecords.HasValue)
{
DataTable[] dts = new DataTable[1];
dts[0] = new DataTable(sDSName);
(adpater as DbDataAdapter).Fill(StartRecord.Value, MaxRecords.Value, dts);
ds.Tables.Add(dts[0]);
//(adpater as DbDataAdapter).Fill(ds, StartRecord.Value, MaxRecords.Value, sDSName);
}
else
{
DataTable dt = new DataTable(sDSName);
(adpater as DbDataAdapter).Fill(dt);
ds.Tables.Add(dt);
//(adpater as DbDataAdapter).Fill(ds, sDSName);
}
if (cmd is InfoCommand && !string.IsNullOrEmpty((cmd as InfoCommand).EncodingAfter))
{
DecodeDataTable(ds.Tables[sDSName], (cmd as InfoCommand).EncodingBefore,(cmd as InfoCommand).EncodingAfter);
}
//设置主键
if (cmd is InfoCommand && string.IsNullOrEmpty(replaceCommandText))
{
ArrayList listKey = (cmd as InfoCommand).GetKeys();
if (listKey.Count > 0)
{
DataColumn[] columnKey = new DataColumn[listKey.Count];
for (int i = 0; i < listKey.Count; i++)
{
columnKey[i] = ds.Tables[sDSName].Columns[listKey[i].ToString()];
}
ds.Tables[sDSName].PrimaryKey = columnKey;
}
}
List<string> sqls = new List<string>();
sqls.Add(cmd.CommandText);
if (cmd is Component && (cmd as Component).Container != null)
{
foreach (Component comp in (cmd as Component).Container.Components)
{
if (comp is InfoDataSource && (comp as InfoDataSource).Master == cmd)
{
List<string> detailSqls = GetDetailDataSet(comp as InfoDataSource, cmd.Connection, ds, sDSName);
sqls.AddRange(detailSqls);
}
}
}
if (!designMode && cmd.Connection.State == ConnectionState.Closed)
{
string sMess = SysMsg.GetSystemMessage(Language, ThisModuleName, ThisComponentName, "msg_RethrowException");
throw new Exception(string.Format(sMess, ThisModuleName, "GetSqlCommand","資料庫連線已中斷!"));
}
return new object[] { ds, string.Join("\r\n", sqls) };
}
catch (Exception e)
{
string sMess = SysMsg.GetSystemMessage(Language, ThisModuleName, ThisComponentName, "msg_RethrowException");
throw new Exception(string.Format(sMess, ThisModuleName, "GetSqlCommand", e.Message));
}
finally
{
if (!designMode)
{
ReleaseConnection(alias, cmd.Connection);
}
else
{
cmd.Connection.Close();
}
}
}
private void cmdDBTables_BeforeExecuteSql(object sender, SqlEventArgs e)
{
string loginDataBaseAlias = ((object[])(ClientInfo[0]))[2].ToString();
string loginDataBaseTypeNO = GetDataBaseTypeforString(loginDataBaseAlias);
string type = GetSystemDBTypeforString();
if (type != loginDataBaseTypeNO)
{
if (loginDataBaseTypeNO == "1" && !(cmdDBTables.CommandText.Contains("sysobjects")))
e.Sql = "select name from sysobjects where (xtype='U' or xtype='V') order by name";
else if (loginDataBaseTypeNO == "2")
e.Sql = "sp_help";
else if (loginDataBaseTypeNO == "3" && !(cmdDBTables.CommandText.Contains("user_objects")))
e.Sql = "select OBJECT_NAME from USER_OBJECTS where (OBJECT_TYPE = 'TABLE' or OBJECT_TYPE = 'VIEW' or OBJECT_TYPE = 'SYNONYM') order by OBJECT_NAME";
else if (loginDataBaseTypeNO == "4")
e.Sql = "select * from SYSTABLES where (TABTYPE = 'T' or TABTYPE = 'V') and TABID >= 100 order by TABNAME";
else if (loginDataBaseTypeNO == "5")
e.Sql = "show tables;";
else if (loginDataBaseTypeNO == "6")
e.Sql = "select * from SYSTABLES where (TABTYPE = 'T' or TABTYPE = 'V') and TABID >= 100 order by TABNAME";
else if (loginDataBaseTypeNO == "7")
e.Sql = "sp_help";
}
}
private List<string> GetDetailDataSet(InfoDataSource datasSource, IDbConnection conn, DataSet ds, string masterName)
{
List<string> sqls = new List<string>();
if (datasSource.MasterColumns == null || datasSource.DetailColumns == null)
{
throw new EEPException(EEPException.ExceptionType.PropertyNull, datasSource.GetType(), null, "MasterColumns", null);
}
else if (datasSource.MasterColumns.Count != datasSource.DetailColumns.Count)
{
throw new EEPException(EEPException.ExceptionType.PropertyInvalid, datasSource.GetType(), null, "MasterColumns.Count", datasSource.MasterColumns.Count.ToString());
}
else if (datasSource.MasterColumns.Count == 0)
{
throw new EEPException(EEPException.ExceptionType.PropertyInvalid, datasSource.GetType(), null, "MasterColumns.Count", "0");
}
else if (datasSource.Detail != null)
{
//反射加载,为了取到名字
FieldInfo[] fields = this.GetType().GetFields(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly);
foreach (FieldInfo field in fields)
{
object obj = field.GetValue(this);
if (obj != null && obj.GetType().GetInterface("IDbCommand") != null)
{
IDbCommand cmd = (IDbCommand)obj;
if (cmd == datasSource.Detail)
{
string detailName = field.Name;
if (detailName == masterName)
{
throw new EEPException(EEPException.ExceptionType.PropertyInvalid, datasSource.GetType(), null, "Detail", detailName);
}
cmd.Connection = conn;
if (cmd is InfoCommand)
{
(cmd as InfoCommand).DealAllSqlText(string.Empty, string.Empty);
if ((cmd as InfoCommand).DynamicTableName)
{
if (!string.IsNullOrEmpty((string)GetClientInfo(ClientInfoType.TableName)))
{
cmd.CommandText = DBUtils.ReplaceTableName(cmd.CommandText, (string)GetClientInfo(ClientInfoType.TableName));
}
}
}
if (ds.Tables[masterName].Rows.Count > 0)
{
string commandText = cmd.CommandText;//缓存Detail的CommandText
StringBuilder whereBuilder = new StringBuilder();
ClientType type = DBUtils.GetDatabaseType(conn);
OdbcDBType odbcType = DBUtils.GetOdbcDatabaseType(conn);
for (int i = 0; i < datasSource.DetailColumns.Count; i++)
{
if (whereBuilder.Length > 0)
{
whereBuilder.Append(" AND ");
}
whereBuilder.Append(DBUtils.GetTableNameForColumn(cmd.CommandText
, (datasSource.DetailColumns[i] as ColumnItem).FieldName, type));
whereBuilder.Append(" = ");
whereBuilder.Append(DBUtils.GetWhereFormat(ds.Tables[masterName].Columns[(datasSource.MasterColumns[i] as ColumnItem).FieldName].DataType
, type, odbcType, i));
}
object[] value = new object[datasSource.MasterColumns.Count];
for (int i = 0; i < ds.Tables[masterName].Rows.Count; i++)
{
DataRow row = ds.Tables[masterName].Rows[i];
if (datasSource.DynamicTableName)
{
cmd.CommandText = string.Format("SELECT * from {0}"
, DBUtils.QuoteWords(row[(datasSource.MasterColumns[i] as ColumnItem).FieldName].ToString(), type));
}
else
{
for (int j = 0; j < datasSource.MasterColumns.Count; j++)
{
ColumnItem item = datasSource.MasterColumns[j] as ColumnItem;
value[j] = DBUtils.GetWhereValue(ds.Tables[masterName].Columns[item.FieldName].DataType, row[item.FieldName]);
}
cmd.CommandText = DBUtils.InsertWhere(commandText, string.Format(whereBuilder.ToString(), value));
}
if (cmd is InfoCommand)
{
SqlEventArgs e = new SqlEventArgs(cmd.CommandText);
(cmd as InfoCommand).OnBeforeExecuteSql(e);
cmd.CommandText = e.Sql;
}
IDataAdapter adpater = DBUtils.CreateDbDataAdapter(cmd);
DataTable dt = new DataTable(detailName);
if (ds.Tables.Contains(detailName))
(adpater as DbDataAdapter).Fill(ds.Tables[detailName]);
else
{
(adpater as DbDataAdapter).Fill(dt);
ds.Tables.Add(dt);
}
if (!string.IsNullOrEmpty(cmd.CommandText) && cmd.CommandText.IndexOf("SysDatabases", StringComparison.OrdinalIgnoreCase) >= 0)
{
throw new Exception("Invalid object name 'SysDatabases'.");
}
sqls.Add(cmd.CommandText);
//(adpater as DbDataAdapter).Fill(ds, detailName);//Detail没有PacketRecords
}
}
else
{
cmd.CommandText = DBUtils.InsertWhere(cmd.CommandText, "1=0");
IDataAdapter adpater = DBUtils.CreateDbDataAdapter(cmd);
DataTable dt = new DataTable(detailName);
if (ds.Tables.Contains(detailName))
(adpater as DbDataAdapter).Fill(ds.Tables[detailName]);
else
{
(adpater as DbDataAdapter).Fill(dt);
ds.Tables.Add(dt);
}
sqls.Add(cmd.CommandText);
//(adpater as DbDataAdapter).Fill(ds, detailName);
}
if (cmd is InfoCommand && !string.IsNullOrEmpty((cmd as InfoCommand).EncodingAfter))
{
DecodeDataTable(ds.Tables[detailName], (cmd as InfoCommand).EncodingBefore, (cmd as InfoCommand).EncodingAfter);
}
if (cmd is InfoCommand)
{
//设置主键
ArrayList listKey = (cmd as InfoCommand).GetKeys();
if (listKey.Count > 0)
{
DataColumn[] columnKey = new DataColumn[listKey.Count];
for (int i = 0; i < listKey.Count; i++)
{
columnKey[i] = ds.Tables[detailName].Columns[listKey[i].ToString()];
}
ds.Tables[detailName].PrimaryKey = columnKey;
}
}
//设置Relation
DataColumn[] parentColumn = new DataColumn[datasSource.MasterColumns.Count];
DataColumn[] childColumn = new DataColumn[datasSource.DetailColumns.Count];
for (int i = 0; i < datasSource.MasterColumns.Count; i++)
{
parentColumn[i] = ds.Tables[masterName].Columns[(datasSource.MasterColumns[i] as ColumnItem).FieldName];
childColumn[i] = ds.Tables[detailName].Columns[(datasSource.DetailColumns[i] as ColumnItem).FieldName];
}
ds.Relations.Add(parentColumn, childColumn);
if (cmd is Component && (cmd as Component).Container != null)
{
foreach (Component comp in (cmd as Component).Container.Components)
{
if (comp is InfoDataSource && (comp as InfoDataSource).Master == cmd)
{
GetDetailDataSet(comp as InfoDataSource, conn, ds, detailName);
}
}
}
}
}
}
}
return sqls;
}
internal void OnBeforeExecuteSql(SqlEventArgs e)
{
SqlEventHandler handler = (SqlEventHandler)Events[EventBeforeExecuteSql];
if (handler != null)
{
handler(this, e);
}
}