CODEWITHSUNDEEP
c#asp.netsqlsql-server-2008
swarraj
swarraj

Reputation: 25

find variable length for sql server datatype nvarchar from c# code

Can I find allowed variable length for nvarchar type in sql server 2008 from c# code in asp.net application?

For eg:

nvarchar(?)

I want to find the maximum allowed number for "?" from c# code.

Upvotes: 1

Views: 2889

Answers (3)

Ronak Patel
Ronak Patel

Reputation: 640

Please using TableSchema method for getting all details of column .

SqlDataReader reader= command.ExecuteReader();

using (var schemaTable = reader.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, scale,ColumnSize);
        }
    }

Thanks .

Upvotes: 1

marc_s
marc_s

Reputation: 754428

You can use this T-SQL query to look at the system catalog views:

SELECT 
    [max_length]
FROM sys.columns 
WHERE [object_id] = OBJECT_ID('YourTableNameHere')
AND name = 'YourColumnNameHere'

This will return the stored, defined maximum length (in characters) for your column

Update: if you want to find out the max length of a type (not a column of any of your tables), you can use this query instead:

SELECT 
    name, max_length
FROM sys.types 
WHERE name IN ('varchar', 'nvarchar')

Be aware: this returns the max length in bytes (not in characters!) so you get 8000 for both types. For varchar, 8000 bytes is equal to 8000 characters, while for nvarchar, 8000 bytes corresponds to 4000 characters.

Upvotes: 2

SysDragon
SysDragon

Reputation: 9888

You can use the .Size property of the SqlParameter:

For output parameters with a variable length type (nvarchar, for example), the size of the parameter defines the size of the buffer holding the output parameter. The output parameter can be truncated to a size specified with Size. For character types, the size specified with Size is in characters.

See MSDN Documentation.

If you mean from a database field, you can get the data with the .GetSchemaTable() method from the DataReader object. The ColumnSize attribute has the information you need.

Read this article to see how to do it.

Upvotes: 0

Related Questions