/// <summary>
/// This method gets the metadata about the result shape of the input query.
/// </summary>
/// <param name="query">
/// The query for which to return shape information.
/// </param>
/// <param name="minVar">
/// The variable name used in the query to denote the minimum end of an integer range.
/// </param>
/// <param name="maxVar">
/// The variable name used in the query to denote the maximum end of an integer range.
/// </param>
/// <param name="targetConnectionString">
/// Used to override the connection on which the query is tested. If not used, the loopback connection
/// will be used.
/// </param>
/// <returns>
/// A collection of SqlMetaData objects, corresponding to each column returned by the query.
/// </returns>
public static SqlMetaData[] GetQueryMetadata(
string query,
string minVar,
string maxVar,
string targetConnectionString)
{
SqlClientPermission sqlPerm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
sqlPerm.Assert();
DataTable dt = null;
using (SqlConnection conn = new SqlConnection((targetConnectionString == null) ? ConnectionBuilder.LoopbackConnectionString : targetConnectionString))
{
using (SqlCommand comm = conn.CreateCommand())
{
comm.CommandText = "SET FMTONLY ON;";
conn.Open();
comm.ExecuteNonQuery();
comm.CommandText = query;
comm.Parameters.Add(minVar, SqlDbType.Int).Value = int.MinValue;
comm.Parameters.Add(maxVar, SqlDbType.Int).Value = int.MaxValue;
using (SqlDataReader r = comm.ExecuteReader())
{
dt = r.GetSchemaTable();
if (dt == null)
{
throw new Exception("Metadata Generator found no columns in the input query");
}
}
}
}
SqlMetaData[] cols = new SqlMetaData[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
//Different SqlMetaData overloads are required
//depending on the data type of the column
switch ((SqlDbType)(dt.Rows[i]["ProviderType"]))
{
case SqlDbType.Decimal:
cols[i] =
new SqlMetaData(
(string)(dt.Rows[i]["ColumnName"]),
(SqlDbType)(dt.Rows[i]["ProviderType"]),
(byte)(Int16)(dt.Rows[i]["NumericPrecision"]),
(byte)(Int16)(dt.Rows[i]["NumericScale"]));
break;
case SqlDbType.Binary:
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.NVarChar:
case SqlDbType.VarBinary:
case SqlDbType.VarChar:
cols[i] =
new SqlMetaData(
(string)(dt.Rows[i]["ColumnName"]),
(SqlDbType)(dt.Rows[i]["ProviderType"]),
((long)((int)(dt.Rows[i]["ColumnSize"])) == 2147483647) ? (long)-1 : (long)((int)(dt.Rows[i]["ColumnSize"])));
break;
default:
cols[i] =
new SqlMetaData(
(string)(dt.Rows[i]["ColumnName"]),
(SqlDbType)(dt.Rows[i]["ProviderType"]));
break;
}
}
return(cols);
}