Example #1
0
 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();
         }
     }
 }
Example #2
0
        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";
            }
        }
Example #3
0
        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;
        }
Example #4
0
 internal void OnBeforeExecuteSql(SqlEventArgs e)
 {
     SqlEventHandler handler = (SqlEventHandler)Events[EventBeforeExecuteSql];
     if (handler != null)
     {
         handler(this, e);
     }
 }