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