Varchar() datatype and C# SqlDataReader truncates string

Timothy Alvord 236 Reputation points
2024-06-13T22:54:05.3533333+00:00

TableDef

When I try and read into my C# program using an SQLDataReader, the Discrepency field above defined above as varchar(512) gets truncated to 255 characters.

I verified that the ColumnSize is 512 with the following C# code:

SqlDataReader = dr2 = cmd2.ExecuteReader();

using (var schemaTable = dr2.GetSchemaTable()) {

foreach (DataRow row in schemaTable.Rows) {

string ColumnName= row.Field<string>("ColumnName");

string DataTypeName=row.Field<string>("DataTypeName");

short NumericPrecision= row.Field<short>("NumericPrecision");

short NumericScale= row.Field<short>("NumericScale");

int ColumnSize= row.Field<int>("ColumnSize");

Console.WriteLine("Column: {0} Type: {1} Precision: {2} Scale: {3} ColumnSize {4}", ColumnName, DataTypeName, NumericPrecision, NumericScale, ColumnSize);

}

}

Any ideas as to why it gets truncated?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,342 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,638 questions
{count} votes

Accepted answer
  1. Jiale Xue - MSFT 43,046 Reputation points Microsoft Vendor
    2024-06-14T03:24:33.0733333+00:00

    Hi @Timothy Alvord , Welcome to Microsoft Q&A,

    Make sure you are reading data correctly from SqlDataReader. If you are using GetString, make sure the column index is correct.

      while (dr2.Read())
        {
            string discrepancy = dr2.GetString(dr2.GetOrdinal("Discrepency"));
            Console.WriteLine(discrepancy);
        }
    

    The buffer size for SqlDataReader might be limited, but typically this shouldn't be a problem with varchar fields. However, you can try to read the data in chunks.

      while (dr2.Read())
        {
            int columnIndex = dr2.GetOrdinal("Discrepency");
            char[] buffer = new char[512];
            long bytesRead = dr2.GetChars(columnIndex, 0, buffer, 0, buffer.Length);
            string discrepancy = new string(buffer, 0, (int)bytesRead);
            Console.WriteLine(discrepancy);
        }
    

    The complete code is as follows:

    using (var conn = new SqlConnection(connectionString))
    {
        var cmd = new SqlCommand("SELECT Discrepency FROM YourTable", conn);
        conn.Open();
        using (var dr2 = cmd.ExecuteReader())
        {
            using (var schemaTable = dr2.GetSchemaTable())
            {
                foreach (DataRow row in schemaTable.Rows)
                {
                    string ColumnName = row.Field<string>("ColumnName");
                    string DataTypeName = row.Field<string>("DataTypeName");
                    short NumericPrecision = row.Field<short>("NumericPrecision");
                    short NumericScale = row.Field<short>("NumericScale");
                    int ColumnSize = row.Field<int>("ColumnSize");
                    Console.WriteLine("Column: {0} Type: {1} Precision: {2} Scale: {3} ColumnSize {4}", ColumnName, DataTypeName, NumericPrecision, NumericScale, ColumnSize);
                }
            }
    
            while (dr2.Read())
            {
                int columnIndex = dr2.GetOrdinal("Discrepency");
                if (!dr2.IsDBNull(columnIndex))
                {
                    var value = dr2.GetValue(columnIndex);
                    Console.WriteLine("Discrepency Value: {0}", value);
                }
            }
        }
    }
    
    

    Best Regards,

    Jiale


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Timothy Alvord 236 Reputation points
    2024-06-14T14:06:28.6733333+00:00

    Thanks. I tried that and the ordinal is correct.

    But you did help me to scrutinize my code a little closer and I found out that it was actually my SQL Stored procedure for updating the table that had the wrong size for the varchar for that field.

    1 person found this answer helpful.
    0 comments No comments