>>>// With SqlDataReader, you don't know how many records their are unless you count them. >>> recordCount = 0; >>> try >>> { >>> // StringBuilder for the entire return string and one for the records >>> StringBuilder returnValue = new StringBuilder(); >>> StringBuilder recordValues = new StringBuilder(); >>> >>> // Add the type and the Table start tags >>> returnValue.Append("<XML>"); >>> returnValue.Append("<Table>"); >>> >>> // Loop through the records and get the column names, values, and record count >>> while (sqlDataReader.Read()) >>> { >>> // There is no sqlDataReader.RecordCount property - we need to increment a counter to figure it out >>> recordCount++; >>> >>> // Add the Record tag >>> recordValues.Append("<Record>"); >>> >>> // Loop through the columns and get the name and value >>> for (Int32 i = 0; i < sqlDataReader.FieldCount; i++) >>> { >>> // Store the appropriate format of the column name based on return type >>> String columnName = sqlDataReader.GetName(i); >>> >>> // Get the column value based on the column type >>> String columnValue = GetColumnValue(sqlDataReader, i, true); >>> >>> // Set the string for the record in the appropriate format (removing any empty values from the return) >>> recordValues.AppendFormat("<{0}>{1}</{0}>", columnName, columnValue); >>> } >>> >>> // The end Record tag >>> recordValues.Append("</Record>"); >>> } >>> >>> // Add the record count for the XML return type >>> returnValue.AppendFormat("<{0}>{1}</{0}>", "RecCount", recordCount); >>> >>> // Append all of the record values (already formatted) >>> returnValue.Append(recordValues); >>> returnValue.Append("</Table>"); >>> returnValue.Append("</XML>"); >>> >>> // Return the whole string... >>> return returnValue.ToString();>>>
int fieldCount = sqlDataReader.FieldCount; string[] FieldNames = new string[fieldCount]; for (int x = 0; x < fieldCount; x++) { FieldNames[x] = sqlDataReader.GetName(x); } while (sqlDataReader.Read()) { for (int i = 0; i < fieldCount; i++) { recordValues.AppendFormat("<{0}>{1}</{0}>", FieldNames[i], GetColumnValue(sqlDataReader, i, true)); } }As I suspected the heavy lifting is in the GetColumnValue. Haven't looked to see if it can be optimized.....
> static String GetColumnValue(SqlDataReader sqlDataReader, Int32 columnIndex, Boolean allColumns = false) > { > String columnValue = ""; > > try > { > if (!sqlDataReader.IsDBNull(columnIndex)) > { > Type columnType = sqlDataReader.GetFieldType(columnIndex); > if (columnType == typeof(System.Byte[])) > { > // Get the number of bytes in the column (set the max to 100k at a time) > Int32 dataLength = (Int32)sqlDataReader.GetBytes(columnIndex, 0, null, 0, 0); > Int32 bufferSize = dataLength; > if (bufferSize > 102400) > bufferSize = 102400; > > // Allocate the buffer > Byte[] byteArray = new Byte[bufferSize]; > > // Set the index of where to start reading - in case the data length exceeds the buffer size > Int32 startIndex = 0; > > // Read bytes into byte[] and retain the number of bytes returned. > Int32 bytesRead = (Int32)sqlDataReader.GetBytes(columnIndex, startIndex, byteArray, 0, bufferSize); > Int32 totalBytesRead = bytesRead; > > // Convert what we have to a string > columnValue += Encoding.UTF7.GetString(byteArray, 0, bytesRead); > > // Continue while there are bytes beyond the size of the buffer. > while (totalBytesRead != dataLength) > { > startIndex += bufferSize; > bytesRead = (Int32)sqlDataReader.GetBytes(columnIndex, startIndex, byteArray, 0, bufferSize); > totalBytesRead += bytesRead; > columnValue += Encoding.UTF7.GetString(byteArray, 0, bytesRead); > } > } > else if (columnType == typeof(System.Boolean)) > { > if (sqlDataReader.GetBoolean(columnIndex)) > columnValue = "1"; > else if (!allColumns) > columnValue = ""; > else > columnValue = "0"; > } > else if (columnType == typeof(System.DateTime)) > { > // If we ever use SQL smalldatetime, datetime2, date, or time types, we will need to modify this > // Currently, we only use SQL datetime column types > // String sqlType = rdr.GetDataTypeName(i); > DateTime dateTime = sqlDataReader.GetDateTime(columnIndex); > columnValue = dateTime.ToString("MM/dd/yyyy hh:mm:ss tt"); > } > else if (columnType == typeof(System.DateTimeOffset)) > { > // We don't use this type, but to prevent things from crashing... > DateTimeOffset dateTimeOffset = sqlDataReader.GetDateTimeOffset(columnIndex); > dateTimeOffset.ToString(); > } > else if (!allColumns && Functions.IsTypeNumericOrBit(columnType)) > { > columnValue = sqlDataReader.GetValue(columnIndex).ToString().TrimEnd(); > if (Library.Functions.IsZero(Convert.ToDouble(columnValue))) > columnValue = ""; > } > else > { > columnValue = sqlDataReader.GetValue(columnIndex).ToString().TrimEnd(); > } > } > } > catch (Exception ex) > { > String error = ex.ToString(); > Logging.Log(error, 1); > return error; > } > > return columnValue; > }>