0

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 ?

Auguste
  • 2,007
  • 2
  • 17
  • 25
Vladyslav Kurkotov
  • 495
  • 8
  • 21
  • why don't you try selecting the database Table Schema. here is some examples here if it doesn't help then Google is the next best thing to find what you are looking for.[GetSchema Method](http://stackoverflow.com/questions/4834592/how-can-i-determine-in-c-sharp-whether-a-sql-server-database-column-is-autoincre) – MethodMan May 31 '16 at 14:09
  • Look at http://stackoverflow.com/questions/173834/ado-net-getting-the-schema-for-a-table – Avitus May 31 '16 at 14:12

1 Answers1

4

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
M.Ali
  • 67,945
  • 13
  • 101
  • 127