C# Connection.ExecuteQuery方法代码示例

 public void ExecutePaged(Type primaryTable, IDbDataParameter[] parameters, ulong? start, ulong? recordCount)
 {
     _conn = (_conn == null ? _pool.GetConnection() : _conn);
     lock (_requiredTypes)
     {
         if (_requiredTypes.Count > 0)
         {
             while (_requiredTypes.Count > 0)
                 _pool.Updater.InitType(_requiredTypes.Dequeue(), _conn);
         }
     }
     string query = "";
     try
     {
         List<IDbDataParameter> pars = new List<IDbDataParameter>();
         if (parameters != null)
             pars.AddRange(parameters);
         query = _conn.queryBuilder.SelectPaged(_outputQuery, _pool.Mapping[primaryTable], ref pars, start, recordCount);
         List<IDbDataParameter> p = CorrectParameters(pars, ref query);
         _conn.ExecuteQuery(query, p);
     }
     catch (Exception e)
     {
         throw new Exception("An error occured executing translated query: " + query, e);
     }
 }

 public NameTranslator(ConnectionPool pool, Connection conn)
 {
     _pool = pool;
     _nameTranslations = new Dictionary<string, string>();
     conn.ExecuteQuery(conn.queryBuilder.GetAllObjectDescriptions());
     while (conn.Read())
     {
         Logger.LogLine(string.Format("Adding Description: {0} = {1}",
             conn[0].ToString(),
             conn[1].ToString()));
         if (conn[0].ToString().Trim().StartsWith(string.Format(_VIEW_DESCRIPTION,""))&&conn[0].ToString().Trim().EndsWith("]"))
             _nameTranslations.Add(conn[0].ToString().Trim().Substring(0,conn[0].ToString().Trim().IndexOf("\t")), conn[1].ToString().Trim());
         else
             _nameTranslations.Add(conn[0].ToString().Trim(), conn[1].ToString().Trim());
     }
     conn.Close();
     _createDescriptions = new List<string>();
 }

 public void Execute(IDbDataParameter[] parameters)
 {
     _conn = (_conn == null ? _pool.GetConnection() :  _conn);
     lock (_requiredTypes)
     {
         if (_requiredTypes.Count > 0)
         {
             while (_requiredTypes.Count > 0)
                 _pool.Updater.InitType(_requiredTypes.Dequeue(), _conn);
         }
     }
     try
     {
         if ((parameters != null) && (parameters.Length > 0))
         {
             List<IDbDataParameter> pars = CorrectParameters(parameters, ref _outputQuery);
             _conn.ExecuteQuery(_outputQuery, pars);
         }
         else
             _conn.ExecuteQuery(_outputQuery);
     }
     catch (Exception e)
     {
         throw new Exception("An error occured executing translated query: " + _outputQuery, e);
     }
 }

 internal override List<Index> ExtractTableIndexes(string tableName, Connection conn)
 {
     List<Index> ret = new List<Index>();
     Dictionary<string, string> objIds = new Dictionary<string, string>();
     List<string> indexIds = new List<string>();
     conn.ExecuteQuery(@"select ind.object_id,ind.index_id,ind.name,ind.is_unique from sys.indexes ind, sysobjects tbl 
         WHERE ind.object_id = tbl.id AND tbl.xtype='U' AND ind.name IS NOT NULL AND ind.is_primary_key=0 AND tbl.name = '"+tableName+"'");
     while (conn.Read())
     {
         ret.Add(new Index(conn[2].ToString(), null, conn[3].ToString() == "1", false));
         objIds.Add(conn[2].ToString(), conn[0].ToString());
         indexIds.Add(conn[1].ToString());
     }
     conn.Close();
     for (int x = 0; x < ret.Count; x++)
     {
         List<string> fields = new List<string>();
         bool asc = false;
         conn.ExecuteQuery("SELECT c.COLUMN_NAME,indcol.is_descending_key FROM sys.index_columns indcol,INFORMATION_SCHEMA.COLUMNS c " +
             "WHERE indcol.object_id = '"+objIds[ret[x].Name]+"' AND indcol.index_id = "+indexIds[x]+" AND c.table_name = '"+tableName+"' AND c.ORDINAL_POSITION = indcol.column_id ORDER BY index_id,key_ordinal");
         while (conn.Read())
         {
             fields.Add(conn[0].ToString());
             asc = conn[1].ToString() == "0";
         }
         conn.Close();
         Index ind = ret[x];
         ret.RemoveAt(x);
         ret.Insert(x, new Index(ind.Name,fields.ToArray(),ind.Unique,asc));
     }
     return ret;
 }

 internal override List<Index> ExtractTableIndexes(string tableName, Connection conn)
 {
     List<Index> ret = new List<Index>();
     conn.ExecuteQuery("SHOW INDEX FROM " + tableName + " FROM " + ((MySqlConnectionPool)conn.Pool).DbName+" WHERE Key_Name <> 'PRIMARY' AND "+
         "Key_Name NOT IN (SELECT CONSTRAINT_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = '" + ((MySqlConnectionPool)conn.Pool).DbName+"' AND TABLE_NAME='"+tableName+"')");
     string curName = null;
     bool unique=false;
     List<string> fields = new List<string>();
     while (conn.Read())
     {
         if (curName!=null){
             if (curName!=conn[2].ToString()){
                 ret.Add(new Index(curName,fields.ToArray(),unique,false));
                 curName = conn[2].ToString();
                 fields=new List<string>();
             }
         }
         fields.Add(conn[4].ToString());
         unique = conn[1].ToString()=="0";
     }
     if (curName!=null){
         ret.Add(new Index(curName,fields.ToArray(),unique,false));
     }
     conn.Close();
     return ret;
 }

 internal override List<Index> ExtractTableIndexes(string tableName, Connection conn)
 {
     List<Index> ret = new List<Index>();
     conn.ExecuteQuery("SELECT TRIM(ind.RDB$INDEX_NAME),ind.RDB$UNIQUE_FLAG,(CASE WHEN ind.RDB$INDEX_TYPE IS NULL THEN 0 ELSE 1 END) FROM RDB$INDICES ind "+
         "WHERE ind.RDB$RELATION_NAME = '" + tableName + "' AND ind.RDB$INDEX_NAME NOT IN (SELECT RDB$INDEX_NAME FROM RDB$RELATION_CONSTRAINTS WHERE RDB$RELATION_NAME = '" + tableName + "' AND RDB$INDEX_NAME IS NOT NULL) ORDER BY ind.RDB$INDEX_ID");
     while (conn.Read())
     {
         ret.Add(new Index(conn[0].ToString(), null, conn[1].ToString() == "1", conn[2].ToString() == "0"));
     }
     conn.Close();
     for (int x = 0; x < ret.Count; x++)
     {
         conn.ExecuteQuery("SELECT TRIM(ind.RDB$FIELD_NAME) FROM RDB$INDEX_SEGMENTS ind WHERE TRIM(ind.RDB$INDEX_NAME) = '" + ret[0].Name + "' ORDER BY ind.RDB$FIELD_POSITION");
         List<string> fields = new List<string>();
         while (conn.Read())
         {
             fields.Add(conn[0].ToString());
         }
         conn.Close();
         Index ind = ret[x];
         ind.Fields = fields.ToArray();
         ret.RemoveAt(x);
         ret.Insert(x, ind);
     }
     return ret;
 }

 public void Init(Connection conn)
 {
     _tables = new List<ExtractedTableMap>();
     _triggers = new List<Trigger>();
     _generators = new List<Generator>();
     _identities = new List<IdentityField>();
     _views = new List<View>();
     _procedures = new List<StoredProcedure>();
     _createdTypes = new List<Type>();
     conn.ExecuteQuery(conn.Pool.queryBuilder.SelectTriggers());
     while (conn.Read())
     {
         //patch to handle long trigger code from mssql
         if (_triggers.Count > 0)
         {
             if (_triggers[_triggers.Count - 1].Name == (string)conn[0])
                 _triggers[_triggers.Count - 1] = new Trigger((string)conn[0], (string)conn[1], _triggers[_triggers.Count - 1].Code + (string)conn[2]);
             else
                 _triggers.Add(new Trigger((string)conn[0], (string)conn[1], (string)conn[2]));
         }else
             _triggers.Add(new Trigger((string)conn[0], (string)conn[1], (string)conn[2]));
     }
     conn.Close();
     conn.ExecuteQuery(conn.Pool.queryBuilder.SelectProcedures());
     while (conn.Read())
     {
         _procedures.Add(new StoredProcedure(conn[0].ToString(), conn[1].ToString(), conn[2].ToString(), conn[3].ToString(), conn[4].ToString()));
     }
     conn.Close();
     conn.ExecuteQuery(conn.queryBuilder.SelectViews());
     while (conn.Read())
         _views.Add(new View((string)conn[0], (string)conn[1]));
     conn.Close();
     conn.ExecuteQuery(conn.queryBuilder.SelectTableNames());
     while (conn.Read())
     {
         _tables.Add(new ExtractedTableMap((string)conn[0]));
     }
     conn.Close();
     for (int x = 0; x < _tables.Count; x++)
     {
         ExtractedTableMap etm = _tables[x];
         etm.Indices = conn.queryBuilder.ExtractTableIndexes(etm.TableName, conn);
         conn.ExecuteQuery(conn.queryBuilder.SelectTableFields(etm.TableName));
         while (conn.Read())
         {
             etm.Fields.Add(new ExtractedFieldMap(conn[0].ToString(), conn[1].ToString(),
                                                  long.Parse(conn[2].ToString()), bool.Parse(conn[3].ToString()), bool.Parse(conn[4].ToString()),
                                                  bool.Parse(conn[5].ToString()),
                                                  (conn.IsDBNull(6) ? null : conn[6].ToString())));
         }
         conn.Close();
         conn.ExecuteQuery(conn.queryBuilder.SelectForeignKeys(etm.TableName));
         while (conn.Read())
         {
             etm.ForeignFields.Add(new ForeignRelationMap(conn[5].ToString(), conn[0].ToString(), conn[1].ToString(),
                                                          conn[2].ToString(), conn[3].ToString(), conn[4].ToString()));
         }
         conn.Close();
         _tables.RemoveAt(x);
         _tables.Insert(x, etm);
     }
     if (conn.UsesGenerators)
     {
         conn.ExecuteQuery(conn.queryBuilder.SelectGenerators());
         while (conn.Read())
         {
             _generators.Add(new Generator((string)conn[0]));
         }
         conn.Close();
         for (int x = 0; x < _generators.Count; x++)
         {
             Generator gen = _generators[x];
             conn.ExecuteQuery(conn.queryBuilder.GetGeneratorValue(gen.Name));
             conn.Read();
             gen.Value = long.Parse(conn[0].ToString());
             conn.Close();
             _generators.RemoveAt(x);
             _generators.Insert(x, gen);
         }
     }
     if (conn.UsesIdentities)
     {
         conn.ExecuteQuery(conn.queryBuilder.SelectIdentities());
         while (conn.Read())
         {
             _identities.Add(new IdentityField((string)conn[0], (string)conn[1], (string)conn[2], (string)conn[3]));
         }
         conn.Close();
     }
 }

 private void _CreateTablesForType(Type type, Connection conn)
 {
     List<Type> types = new List<Type>();
     types.Add(type);
     if (new List<Type>(type.GetInterfaces()).Contains(typeof(IClassView)))
     {
         ClassViewAttribute cva = conn.Pool[type];
         foreach (Type t in cva.Query.RequiredTypes)
         {
             if (!types.Contains(t))
                 types.Add(t);
             types = _RecurLoadTypesForTable(_pool.Mapping[t], types);
         }
     }
     else
         types = _RecurLoadTypesForTable(_pool.Mapping[type], types);
     for (int x = 1; x < types.Count; x++)
     {
         if (_createdTypes.Contains(types[x]))
         {
             types.RemoveAt(x);
             x--;
         }
     }
     List<ExtractedTableMap> tables;
     List<Trigger> triggers;
     List<Generator> generators;
     List<IdentityField> identities;
     List<View> views;
     List<StoredProcedure> procedures;
     ExtractExpectedStructure(ref types, out tables, out triggers, out generators, out identities, out views, out procedures, conn);
     List<string> createdTables = _UpdateStructure(tables, triggers, generators, identities, views, procedures,conn);
     foreach (Type t in types)
     {
         if (Utility.IsEnum(t))
         {
             if (createdTables.Contains(_pool.Enums[t]))
                 _pool.Enums.InsertEnumIntoTable(t, conn);
             else
                 _pool.Enums.LoadEnumsFromTable(t, conn);
         }
     }
     _createdTypes.AddRange(types);
     _UpdateAddiontalData(triggers, generators, identities, views, procedures);
     if (!_pool.DebugMode && (_translations.Count > 0))
     {
         foreach (Type t in types)
         {
             if (_translations.ContainsKey(t))
             {
                 foreach (EnumTranslationPair etp in _translations[t])
                 {
                     conn.ExecuteNonQuery(String.Format(
                         "UPDATE {0} SET {1} = '{3}' WHERE {1} = '{2}'",
                         new object[]{
                         _pool.Enums[t],
                         _pool.Translator.GetEnumValueFieldName(t,conn),
                         etp.OriginalName,
                         etp.NewName}
                     ));
                     conn.Close();
                 }
             }
             Dictionary<string, int> enumValuesMap = new Dictionary<string, int>();
             Dictionary<int, string> enumReverseValuesMap = new Dictionary<int, string>();
             List<string> enumNames = new List<string>(Enum.GetNames(t));
             List<int> deletes = new List<int>();
             conn.ExecuteQuery(String.Format("SELECT ID,{1} FROM {0}",
                 _pool.Enums[t],
                 _pool.Translator.GetEnumValueFieldName(t, conn)));
             while (conn.Read())
             {
                 if (enumNames.Contains(conn[1].ToString()))
                 {
                     enumValuesMap.Add(conn[1].ToString(), (int)conn[0]);
                     enumReverseValuesMap.Add((int)conn[0], conn[1].ToString());
                     enumNames.Remove(conn[1].ToString());
                 }
                 else
                     deletes.Add((int)conn[0]);
             }
             conn.Close();
             if (deletes.Count > 0)
             {
                 foreach (int i in deletes)
                 {
                     conn.ExecuteNonQuery(String.Format("DELETE FROM {0} WHERE ID = {1}",
                         _pool.Enums[t],
                         i));
                     conn.Close();
                 }
             }
             if (enumNames.Count > 0)
             {
                 foreach (string str in enumNames)
                 {
                     conn.ExecuteNonQuery(String.Format("INSERT INTO {0}({1}) VALUES('{2}')",
                         _pool.Enums[t],
                         _pool.Translator.GetEnumValueFieldName(t, conn),
                         str));
//.........这里部分代码省略.........

 private Dictionary<string, int> _SyncMissingValues(Dictionary<string, int> vals, Type t, Connection conn)
 {
     t = (t.IsGenericType ? t.GetGenericArguments()[0] : t);
     string[] keys = new string[vals.Count];
     vals.Keys.CopyTo(keys, 0);
     foreach (string str in Enum.GetNames(t))
     {
         if (!vals.ContainsKey(str))
         {
             conn.ExecuteNonQuery(string.Format("INSERT INTO {0}({1}) VALUES({2});",new object[]{ 
                 _enumTableMaps[t],
                 _pool.Translator.GetEnumValueFieldName(t, conn),
                 conn.CreateParameterName("value")}),
             new System.Data.IDbDataParameter[]{
                         conn.Pool.CreateParameter(conn.CreateParameterName("id"),null,Org.Reddragonit.Dbpro.Structure.Attributes.FieldType.INTEGER,4),
                         conn.CreateParameter(conn.CreateParameterName("value"),str)
                     });
             conn.ExecuteQuery("SELECT ID FROM " + _enumTableMaps[t] + " WHERE " + _pool.Translator.GetEnumValueFieldName(t, conn) + " = " + conn.CreateParameterName("value"),
                 new IDbDataParameter[]{
                     conn.CreateParameter(conn.CreateParameterName("value"),str)
                 });
             conn.Read();
             vals.Add(str, conn.GetInt32(0));
             conn.Close();
         }
     }
     return vals;
 }

 internal void LoadEnumsFromTable(Type t, Connection conn)
 {
     t = (t.IsGenericType ? t.GetGenericArguments()[0] : t);
     conn.ExecuteQuery("SELECT * FROM " + _enumTableMaps[t]);
     Dictionary<string, int> vals = new Dictionary<string, int>();
     while (conn.Read())
         vals.Add(conn[1].ToString(), conn.GetInt32(0));
     conn.Close();
     if (_enumValuesMap.ContainsKey(t))
         _enumValuesMap.Remove(t);
     if (_enumReverseValuesMap.ContainsKey(t))
         _enumReverseValuesMap.Remove(t);
     if (vals.Count == 0)
         InsertEnumIntoTable(t, conn);
     else
     {
         foreach (string str in Enum.GetNames(t))
         {
             if (!vals.ContainsKey(str))
             {
                 vals = _SyncMissingValues(vals, t, conn);
                 break;
             }
         }
         _enumValuesMap.Add(t, vals);
         Dictionary<int, string> revs = new Dictionary<int, string>();
         foreach (string str in vals.Keys)
             revs.Add(vals[str], str);
         _enumReverseValuesMap.Add(t, revs);
     }
 }