>>>Hi everybody,
>>>
>>>I am reading some code
>>>
>>>

>>>// 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();

>>>
>>>I am thinking there may be a way to write the above more efficiently with a fewer lines of code.
>>>
>>>Do you think it's possible or the above is as good as it gets?
>>>
>>>Thanks in advance.
>>
>>What's in GetColumnValue() - that's likely to be the slow bit ?
>>
>>Since the column names are fixed you might be able to cache those and avoid using 'sqlDataReader.GetName(i);' repeatedly.
>>
>>I assume that the required output format precludes using DataSet.WriteXML
>
>Do you know what format WriteXML returns? Also, this is SqlDataReader, not DataSet (although I think one can be converted to another, right)?
>
>I was also thinking we should somehow avoid calling the sqlDataReader.GetName(i) repeatedly, but do you know how then we should change the above?

Can't you figure out something as simple as that ?



> This is how GetColumnValue is coded:
>
>

>  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;
>        }

>
>I am just starting working on this project and this is all already coded, so I am just getting familiar with the code and also if something can be improved, it will be great as this code is our base code and will be used repeatedly.