CODEWITHSUNDEEP
c#sqldatasetsmo
Tanielian V. Barreira
Tanielian V. Barreira

Reputation: 305

How to get the MaxLength of a Column in a DataSet returned by ExecuteWithResults in C# SMO?

I need to get the max length of any column returned in any SELECT executed in the ExecuteWithResults method.

Example:

The Table TbCli has the column NmCli (varchar NOT NULL)

using System;
using System.Data;
using Microsoft.SqlServer.Management.Smo;

...

Server server = new Server();
server.ConnectionContext.ServerInstance = "ServerInstance";
server.ConnectionContext.LoginSecure = true;
server.ConnectionContext.DatabaseName = "DatabaseName";
server.ConnectionContext.Connect();

DataSet lQR = server.ConnectionContext.ExecuteWithResults("Select NmCli From TbCli");

var max = lQR.Tables[0].Columns[0].MaxLength;

Console.WriteLine("\n- End!"); Console.ReadKey();

In this example the max variable is assigned the value -1. I need something that give me the number 50. (Because in this example the column is varchar(50))

Remember:

This is an Example. If someone send this query to my method:

SELECT a.ID, a.Name, b.ID, b.Name From TableA as a inner join TableB as b on a.BID = b.ID

I need to be able to get the correct length of all the columns.

Thanks in advance,

Upvotes: 0

Views: 652

Answers (1)

Hozikimaru
Hozikimaru

Reputation: 1156

Use exec sp_help 'tablename'. It will return multiple result sets and the second one will give you the length of the columns.

Edit: The following code block should give you what you want. I am not sure what is the complication based on your comment but I was able to create a dictionary with the column names, and the length of the columns.

            Server server = new Server();
            server.ConnectionContext.ConnectionString = @"ConnectionStringHere";
            server.ConnectionContext.Connect();

            var ColumnDictionary = new Dictionary<string, int>();
            using (SqlDataReader lQR = server.ConnectionContext.ExecuteReader("sp_help 'dbo.WhicheverTable'"))
            {
                lQR.NextResult();
                while (lQR.Read())
                {
                    ColumnDictionary.Add((String)lQR.GetValue(0),(Int32)lQR.GetValue(3));
                }

            }


            //Do anything with the dictionary here
            Console.WriteLine("\n- End!"); Console.ReadKey();

Upvotes: 1

Related Questions