O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

10.4. Determining the Length of Columns in a SQL Server Table

Problem

The FillSchema( ) method of the DataAdapter returns the correct length in the MaxLength property for string columns in a SQL Server database, but it returns -1 for the length of all other fields. You need to get the length of columns other than string type columns.

Solution

Use the system stored procedure sp_help.

The sample code executes a batch query to return all rows from both the Orders and Order Details tables in the Northwind sample database. The extended stored procedure sp_help is used to get the length, precision, and scale of all columns in both tables.

The C# code is shown in Example 10-4.

Example 10-4. File: ColumnSchemaForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Collections; using System.Text; using System.Data; using System.Data.SqlClient; // . . . StringBuilder schemaInfo = new StringBuilder( ); // Create a batch query to retrieve order and details. String sqlText = "select OrderID, CustomerID, EmployeeID, OrderDate, " + "RequiredDate, ShippedDate, ShipVia, Freight, ShipName, " + " ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry " + "FROM Orders;" + "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " + "FROM [Order Details];"; // Create the connection. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); // Create DataAdapter. SqlDataAdapter da = new SqlDataAdapter(sqlText, conn); ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required