C# SqlDataReader.GetValues方法代码示例

        private static string toJSON(SqlDataReader rdr)
        {
            StringBuilder sb = new StringBuilder();
            StringWriter sw = new StringWriter(sb);
            JsonWriter jsonWriter = new JsonTextWriter(sw);
            jsonWriter.WriteStartArray();
            while (rdr.Read())
            {
                int fieldcount = rdr.FieldCount; // count how many columns are in the row
                object[] values = new object[fieldcount]; // storage for column values
                rdr.GetValues(values); // extract the values in each column
                jsonWriter.WriteStartObject();
                for (int index = 0; index < fieldcount; index++)
                {

                    string colName = rdr.GetName(index);
                    object value = values[index];
                    jsonWriter.WritePropertyName(colName);

                    if (value == DBNull.Value)
                        value = "";
                    else if (colName.Contains("date") && !colName.Contains("by"))
                        value = String.Format("{0:MM/dd/yyyy HH:mm tt}", value);

                    jsonWriter.WriteValue(value);
                }
                jsonWriter.WriteEndObject();
            }
            jsonWriter.WriteEndArray();

            return sb.ToString();
        }

 private static void imprimirBusqueda(SqlDataReader resultado, string mensaje)
 {
     Console.WriteLine(mensaje);
     while (resultado.Read())
     {
         Object[] columnas = new Object[resultado.FieldCount];
         resultado.GetValues(columnas);
         foreach (var atributo in columnas)
         {
             Console.Write("{0} ", atributo);
         }
         Console.WriteLine();
     }
     Console.WriteLine();
 }

 /// <summary>
 /// 輸入的SqlDataReader,用ArrayList 方式取回全部資料
 /// </summary>
 /// <param name="SDR">SqlDataReader Source</param>        
 /// <returns></returns>
 public static ArrayList GetRows(SqlDataReader SDR)
 {
     ArrayList myResults = new ArrayList();
     object[] objOneRow;
     while (SDR.Read())
     {
         objOneRow = (object[])Array.CreateInstance(typeof(object), SDR.FieldCount);
         SDR.GetValues(objOneRow);
         myResults.Add(objOneRow);
     }
     return myResults;
 }

		internal void DeriveParameters ()
		{
			if (commandType != CommandType.StoredProcedure)
				throw new InvalidOperationException (String.Format ("SqlCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
			ValidateCommand ("DeriveParameters", false);

			string procName = CommandText;
			string schemaName = String.Empty;
			int dotPosition = procName.LastIndexOf ('.');

			// Procedure name can be: [database].[user].[procname]
			if (dotPosition >= 0) {
				schemaName = procName.Substring (0, dotPosition);
				procName = procName.Substring (dotPosition + 1);
				if ((dotPosition = schemaName.LastIndexOf ('.')) >= 0)
					schemaName = schemaName.Substring (dotPosition + 1);
			}
			
			procName = EscapeProcName (procName, false);
			schemaName = EscapeProcName (schemaName, true);
			
			SqlParameterCollection localParameters = new SqlParameterCollection (this);
			localParameters.Add ("@procedure_name", SqlDbType.NVarChar, procName.Length).Value = procName;
			if (schemaName.Length > 0)
				localParameters.Add ("@procedure_schema", SqlDbType.NVarChar, schemaName.Length).Value = schemaName;
			
			string sql = "sp_procedure_params_rowset";

			try {
				Connection.Tds.ExecProc (sql, localParameters.MetaParameters, 0, true);
			} catch (TdsTimeoutException ex) {
				Connection.Tds.Reset ();
				throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
			} catch (TdsInternalException ex) {
				Connection.Close ();
				throw SqlException.FromTdsInternalException ((TdsInternalException) ex);
			}

			SqlDataReader reader = new SqlDataReader (this);
			parameters.Clear ();
			object[] dbValues = new object[reader.FieldCount];

			while (reader.Read ()) {
				reader.GetValues (dbValues);
				parameters.Add (new SqlParameter (dbValues));
			}
			reader.Close ();

			if (parameters.Count == 0)
				throw new InvalidOperationException ("Stored procedure '" + procName + "' does not exist.");
		}

    private static void SendTable(SqlDataReader reader, TRowSetMap Map)
    {
        //SqlDataRecord ReadRecord = new SqlDataRecord(DataReaderFields(reader));
        DataTable LDataTable = reader.GetSchemaTable();
        SqlDataRecord WriteRecord;

        List<TFieldAlias> Fields = new List<TFieldAlias>();
        TFieldAlias Field;
        string FieldName;
        int FieldCount = reader.FieldCount, WriteFieldCount = 0;
        int i;
        SqlMetaData[] WriteFields;

        if(Map.Fields.Length > 0)
        {
          WriteFields = new SqlMetaData[0];

          foreach (string FieldMap in Map.Fields.Split(new char[] {','}))
          {
        i = FieldMap.IndexOf('=');
        if(i >= 0)
        {
          Field.Name = FieldMap.Substring(0, i);
          FieldName  = FieldMap.Substring(i + 1);
        }
        else
        {
          Field.Name = FieldMap;
          FieldName  = FieldMap;
        }

        for(i = 0; i < FieldCount; i++)
        {
          if(FieldName.ToUpper() == reader.GetName(i).ToUpper())
            break;
        }
        if((i < 0) || (i >= FieldCount))
          throw new SystemException("RowSet Field = [" + FieldName + "] not found.");
        Field.FieldIndex = i;
        Fields.Add(Field);

        Array.Resize(ref WriteFields, ++WriteFieldCount);
        //WriteFields[WriteFieldCount - 1] = SqlMetaData(LDataTable.Rows[WriteFieldCount - 1], Field.Name);
        WriteFields[WriteFieldCount - 1] = SqlMetaData(LDataTable.Rows[Field.FieldIndex], Field.Name);
          }
        }
        else
        {
          WriteFields = new SqlMetaData[FieldCount];
          for (; WriteFieldCount < reader.FieldCount; WriteFieldCount++)
        WriteFields[WriteFieldCount] = SqlMetaData(LDataTable.Rows[WriteFieldCount]);
        }
        WriteRecord = new SqlDataRecord(WriteFields);

        try
        {
          SqlContext.Pipe.SendResultsStart(WriteRecord);
          Object[] values = new Object[FieldCount];

          while (reader.Read())
          {
        reader.GetValues(values);
        if(Map.Fields.Length > 0)
        {
          for(i = 0; i < WriteFieldCount; i++)
            WriteRecord.SetValue(i, values[Fields[i].FieldIndex]);
        }
        else
        {
          WriteRecord.SetValues(values);
        }
        SqlContext.Pipe.SendResultsRow(WriteRecord);
          }
        }
        finally
        {
          SqlContext.Pipe.SendResultsEnd();
        }
    }

 private static object[] GetRowValues(SqlDataReader sqlReader)
 {
     object[] rowValues = new object[sqlReader.FieldCount];
     sqlReader.GetValues(rowValues);
     return rowValues;
 }

 private static void processData(SqlDataReader reader, FlexResult result)
 {
     try
     {
         int fieldCount = reader.FieldCount;
         var data = new List<Object[]>();
         while (reader.Read())
         {
             Object[] values = new Object[fieldCount];
             reader.GetValues(values);
             data.Add(values);
         }
         result.data = data;
     }
     catch (Exception ex)
     {
         result.exceptions.Add(ex);
     }
 }

        /// <summary> 
        /// DataReader格式转换成DataTable 
        /// </summary> 
        /// <param name="DataReader">OleDbDataReader</param> 
        private DataTable GetConvertDataReaderToDataTable(SqlDataReader reader)
        {
            DataTable objDataTable = new DataTable("TmpDataTable");
            int intCounter;

            try
            {
                //获取当前行中的列数；
                int intFieldCount = reader.FieldCount;

                for (intCounter = 0; intCounter <= intFieldCount - 1; intCounter++)
                {
                    objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
                }

                //populate   datatable   
                objDataTable.BeginLoadData();

                //object[]   objValues   =   new   object[intFieldCount   -1];   
                object[] objValues = new object[intFieldCount];

                while (reader.Read())
                {
                    reader.GetValues(objValues);
                    objDataTable.LoadDataRow(objValues, true);
                }
                reader.Close();

                objDataTable.EndLoadData();

                return objDataTable;

            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

		/// <summary>
		/// Constructs the data which was extracted from the database according to user's query.
		/// </summary>
		/// <param name="reader">SqlReader - holds the queried data.</param>
		///<returns>Queried data in DataTable.</returns>
		private static DataTable ConstructData(SqlDataReader reader)
		{
			try
			{
				if(reader.IsClosed)
					throw new InvalidOperationException("Attempt to use a closed SqlDataReader");
				
				DataTable dataTable = new DataTable();
			
				// constructs the columns data.
				for(int i=0; i<reader.FieldCount; i++)
					dataTable.Columns.Add(reader.GetName(i), reader.GetFieldType(i));

				// constructs the table's data.
				while(reader.Read())
				{	
					object[] row = new object[reader.FieldCount];
					reader.GetValues(row);
					dataTable.Rows.Add(row);
				}
				// Culture info.
				dataTable.Locale = CultureInfo.InvariantCulture;
				// Accepts changes.
				dataTable.AcceptChanges();
				
				return dataTable;
			}
			catch(Exception e)
			{
				Log.WriteErrorToLog(e.Message);
				throw;
			}
		}

 public List<List<object>> search(string query)
 {
     List<List<object>> array = new List<List<object>>();
     try
     {
         conn.Open();
     }
     catch (Exception ex)
     {
         error = "connection fail:" + ex.Message;
         return null;
     }
     SqlCommand cmd = new SqlCommand();
     cmd.Connection = conn;
     cmd.CommandText = query;
     try
     {
         sdr = cmd.ExecuteReader();
         while (sdr.Read())
         {
             List<object> tmparr = new List<object>();
             Object[] values = new Object[sdr.FieldCount];
             int fieldCount = sdr.GetValues(values);
             for (int i = 0; i < fieldCount; i++)
                 tmparr.Add(values[i]);
             array.Add(tmparr);
         }
     }
     catch (Exception ex)
     {
         error = "search fail:" + ex.Message;
     }
     conn.Close();
     return array;
 }

 public static StringBuilder ReplaceTemplate(SqlDataReader sdr, StringBuilder template, bool IsRemoveNoData)
 {
     DataTable dt = new DataTable();
     int fcnt=sdr.FieldCount;
     for (int i = 0; i < fcnt; i++)
     {
         dt.Columns.Add(new DataColumn(sdr.GetName(i),sdr.GetFieldType(i)));
     }
     DataRow row = dt.NewRow();
     object[] Values = new object[fcnt];
     sdr.GetValues(Values);
     row.ItemArray = Values;
     return ReplaceTemplate(row, template, IsRemoveNoData);
 }

		public void GetValuesTest ()
		{
			cmd.CommandText = "Select 10,20,30 from numeric_family where id=1";
			reader = cmd.ExecuteReader ();
			reader.Read ();
			object[] arr = null;
			int count = 0; 

			arr = new object[1];
			count = reader.GetValues (arr);
			Assert.AreEqual (10, (int)arr[0], "#1 Only first object shud be copied");
			Assert.AreEqual (1, count, "#1 return value shud equal objects copied");

			arr = new object[3];
			count = reader.GetValues (arr);
			Assert.AreEqual (3, count, "#2 return value shud equal objects copied");

			arr = new object [5];
			count = reader.GetValues (arr);
			Assert.AreEqual (3, count, "#3 return value shud equal objects copied");
			Assert.IsNull (arr[3], "#4 Only 3 objects shud be copied");

			reader.Close ();
		}

        /// <summary>
        /// Constructs the data which was extracted
        /// from the database according to user's query.
        /// </summary>
        /// <param name="reader">SqlReader - holds the queried data.</param>
        ///<returns>Queried data in DataTable.</returns>
        private static DataTable ConstructData(SqlDataReader reader)
        {
            try
            {
                if (reader.IsClosed)
                    throw new
                      InvalidOperationException("Attempt to" +
                               " use a closed SqlDataReader");

                DataTable dataTable = new DataTable();

                // constructs the columns data.
                for (int i = 0; i < reader.FieldCount; i++)
                    dataTable.Columns.Add(reader.GetName(i),
                                    reader.GetFieldType(i));

                // constructs the table's data.
                while (reader.Read())
                {
                    object[] row = new object[reader.FieldCount];
                    reader.GetValues(row);
                    dataTable.Rows.Add(row);
                }
                // Culture info.
                // TODO: get locale from app
                dataTable.Locale = new System.Globalization.CultureInfo("en-US");
                // Accepts changes.
                dataTable.AcceptChanges();

                return dataTable;
            }
            catch (Exception ex)
            {
                logger.Error(SQLSchemaTool.ERRORFORMAT, ex.Message, ex.Source, ex.StackTrace);
                throw ex;
            }
        }

		internal void DeriveParameters ()
		{
			if (commandType != CommandType.StoredProcedure)
				throw new InvalidOperationException (String.Format ("SqlCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
			ValidateCommand ("DeriveParameters");

			SqlParameterCollection localParameters = new SqlParameterCollection (this);
			localParameters.Add ("@procedure_name", SqlDbType.NVarChar, commandText.Length).Value = commandText;

			string sql = "sp_procedure_params_rowset";

			Connection.Tds.ExecProc (sql, localParameters.MetaParameters, 0, true);

			SqlDataReader reader = new SqlDataReader (this);
			parameters.Clear ();
			object[] dbValues = new object[reader.FieldCount];

			while (reader.Read ()) {
				reader.GetValues (dbValues);
				parameters.Add (new SqlParameter (dbValues));
			}
			reader.Close ();	

		}

 List<BD2.Conv.Frontend.Table.Row> readRowsData(SqlConnection connection, SqlCommand command, SqlDataReader reader, Table table)
 {
     Console.WriteLine ("readRowsData()");
     List<BD2.Conv.Frontend.Table.Row> rows = new List<BD2.Conv.Frontend.Table.Row> ();
     object[] values;
     //SortedDictionary<string, Column> table = tableColumns [context.Table.SqlTableID];
     string[] rowTFQNs = new string[reader.FieldCount];
     BD2.Conv.Frontend.Table.Column[] cols = new BD2.Conv.Frontend.Table.Column[reader.FieldCount];
     for (int n = 0; n != reader.FieldCount; n++) {
         cols [n] = psc.GetColumnByName (table, reader.GetName (n));
         rowTFQNs [n] = cols [n].TFQN;
     }
     int rc = 0;
     ColumnSet columnSet = new ColumnSet (cols);
     while (reader.Read ()) {
         rc++;
         values = new object[reader.FieldCount];
         reader.GetValues (values);
         BD2.Conv.Frontend.Table.Row r = new BD2.Conv.Frontend.Table.Row (columnSet, values);
         rows.Add (r);
     }
     reader.Close ();
     connection.Close ();
     return rows;
 }