CODEWITHSUNDEEP
c#sql-serverado.net
Vladyslav Kurkotov
Vladyslav Kurkotov

Reputation: 505

How to Get correct DataColumn Properties from DB

I am using ADO.NET. When I want to save data to table in SQL I need to retrieve columns information in this table. By information I mean Column max size (I want to get 10 from nvarchar(10) column) and NULL or NOT NULL. I am using next code: 

var selectFromCmd = SqlCommandFactory.CreateCommand("select top 0 * from [dbo]." + destTableName, SqlConnection, SqlTransaction);
var dataAdapter = new SqlDataAdapter(selectFromCmd);
var destinationTable = new DataTable();
dataAdapter.Fill(destinationTable);

Then I get DataColumn like so:

var column = destinationTable.Columns["MyColumn"]

But AllowDBNull is always true

and MaxLength is always -1 even if this column is string

So, how can I get the correct information about column properties in ADO.NET ?

Upvotes: 0

Views: 228

Answers (1)

M.Ali
M.Ali

Reputation: 69554

I would rather use the sys cataloge views for this query. Something like this....

SELECT  c.name ColumnName
       ,t.Name Datatype
       ,c.max_length MaxLength
       ,c.is_nullable 
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = object_id('Customers') --<-- your table name

Upvotes: 4

Related Questions