internal TableFunctionMetadata <SqlServerObjectName, SqlDbType> GetTableFunctionInternal(SqlServerObjectName tableFunctionName)
{
const string TvfSql =
@"SELECT
s.name AS SchemaName,
o.name AS Name,
o.object_id AS ObjectId
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.type in ('TF', 'IF', 'FT') AND s.name = @Schema AND o.Name = @Name" ;
/*
* TF = SQL table-valued-function
* IF = SQL inline table-valued function
* FT = Assembly (CLR) table-valued function
*/
string actualSchema;
string actualName;
int objectId;
using (var con = new SqlConnection(m_ConnectionBuilder.ConnectionString))
{
con.Open();
using (var cmd = new SqlCommand(TvfSql, con))
{
cmd.Parameters.AddWithValue("@Schema", tableFunctionName.Schema ?? DefaultSchema);
cmd.Parameters.AddWithValue("@Name", tableFunctionName.Name);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
throw new MissingObjectException($"Could not find table valued function {tableFunctionName}");
}
actualSchema = reader.GetString("SchemaName");
actualName = reader.GetString("Name");
objectId = reader.GetInt32("ObjectId");
}
}
var objectName = new SqlServerObjectName(actualSchema, actualName);
var columns = GetColumns(objectName.ToString(), objectId);
var parameters = GetParameters(objectName.ToString(), objectId, con);
return(new TableFunctionMetadata <SqlServerObjectName, SqlDbType>(objectName, parameters, columns));
}
}
internal SqlServerTableOrViewMetadata <OleDbType> GetTableOrViewInternal(SqlServerObjectName tableName)
{
const string TableSql =
@"SELECT
s.name AS SchemaName,
t.name AS Name,
t.object_id AS ObjectId,
CONVERT(BIT, 1) AS IsTable,
(SELECT COUNT(*) FROM sys.triggers t2 WHERE t2.parent_id = t.object_id) AS Triggers
FROM SYS.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = ? AND t.Name = ?
UNION ALL
SELECT
s.name AS SchemaName,
t.name AS Name,
t.object_id AS ObjectId,
CONVERT(BIT, 0) AS IsTable,
(SELECT COUNT(*) FROM sys.triggers t2 WHERE t2.parent_id = t.object_id) AS Triggers
FROM SYS.views t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = ? AND t.Name = ?" ;
string actualSchema;
string actualName;
int objectId;
bool isTable;
bool hasTriggers;
using (var con = new OleDbConnection(m_ConnectionBuilder !.ConnectionString))
{
con.Open();
using (var cmd = new OleDbCommand(TableSql, con))
{
cmd.Parameters.AddWithValue("@Schema1", tableName.Schema ?? DefaultSchema);
cmd.Parameters.AddWithValue("@Name1", tableName.Name);
cmd.Parameters.AddWithValue("@Schema2", tableName.Schema ?? DefaultSchema);
cmd.Parameters.AddWithValue("@Name2", tableName.Name);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
throw new MissingObjectException($"Could not find table or view {tableName}");
}
actualSchema = reader.GetString("SchemaName");
actualName = reader.GetString("Name");
objectId = reader.GetInt32("ObjectId");
isTable = reader.GetBoolean("IsTable");
hasTriggers = reader.GetInt32("Triggers") > 0;
}
}
}
var columns = GetColumns(tableName.ToString(), objectId);
return(new SqlServerTableOrViewMetadata <OleDbType>(this, new SqlServerObjectName(actualSchema, actualName), isTable, columns, hasTriggers));
}
internal StoredProcedureMetadata <SqlServerObjectName, SqlDbType> GetStoredProcedureInternal(SqlServerObjectName procedureName)
{
const string StoredProcedureSql =
@"SELECT
s.name AS SchemaName,
sp.name AS Name,
sp.object_id AS ObjectId
FROM SYS.procedures sp
INNER JOIN sys.schemas s ON sp.schema_id = s.schema_id
WHERE s.name = @Schema AND sp.Name = @Name" ;
string actualSchema;
string actualName;
int objectId;
using (var con = new SqlConnection(m_ConnectionBuilder.ConnectionString))
{
con.Open();
using (var cmd = new SqlCommand(StoredProcedureSql, con))
{
cmd.Parameters.AddWithValue("@Schema", procedureName.Schema ?? DefaultSchema);
cmd.Parameters.AddWithValue("@Name", procedureName.Name);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
var sysStoredProcedure = GetSystemStoredProcedure(procedureName);
if (sysStoredProcedure != null)
{
return(sysStoredProcedure);
}
var masterStoredProcedure = GetMasterStoredProcedure(procedureName);
if (masterStoredProcedure != null)
{
return(masterStoredProcedure);
}
throw new MissingObjectException($"Could not find stored procedure {procedureName}");
}
actualSchema = reader.GetString("SchemaName");
actualName = reader.GetString("Name");
objectId = reader.GetInt32("ObjectId");
}
}
var objectName = new SqlServerObjectName(actualSchema, actualName);
var parameters = GetParameters(objectName.ToString(), objectId, con);
return(new StoredProcedureMetadata <SqlServerObjectName, SqlDbType>(objectName, parameters));
}
}
internal StoredProcedureMetadata <SqlServerObjectName, SqlDbType>?GetSystemStoredProcedure(SqlServerObjectName procedureName)
{
try
{
const string StoredProcedureSql =
@"SELECT
s.name AS SchemaName,
sp.name AS Name,
sp.object_id AS ObjectId
FROM SYS.all_objects sp
INNER JOIN sys.schemas s ON sp.schema_id = s.schema_id
WHERE s.name = @Schema AND sp.Name = @Name" ;
string actualSchema;
string actualName;
int objectId;
using (var con = new SqlConnection(m_ConnectionBuilder.ConnectionString))
{
con.Open();
using (var cmd = new SqlCommand(StoredProcedureSql, con))
{
cmd.Parameters.AddWithValue("@Schema", procedureName.Schema ?? "sys");
cmd.Parameters.AddWithValue("@Name", procedureName.Name);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
actualSchema = reader.GetString("SchemaName");
actualName = reader.GetString("Name");
objectId = reader.GetInt32("ObjectId");
}
}
var objectName = new SqlServerObjectName(actualSchema, actualName);
var parameters = GetParameters(objectName.ToString(), objectId, con, useAllParameters: true);
return(new StoredProcedureMetadata <SqlServerObjectName, SqlDbType>(objectName, parameters));
}
}
catch
{
return(null); //this will silently fail
}
}
GetUserDefinedTableTypeInternal(SqlServerObjectName typeName)
{
const string sql =
@"SELECT s.name AS SchemaName,
t.name AS Name,
tt.type_table_object_id AS ObjectId
FROM sys.types t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN sys.table_types tt ON tt.user_type_id = t.user_type_id
LEFT JOIN sys.types t2 ON t.system_type_id = t2.user_type_id
WHERE s.name = ? AND t.name = ? AND t.is_table_type = 1;";
string actualSchema;
string actualName;
int objectId;
using (var con = new OleDbConnection(m_ConnectionBuilder.ConnectionString))
{
con.Open();
using (var cmd = new OleDbCommand(sql, con))
{
cmd.Parameters.AddWithValue("@Schema", typeName.Schema ?? DefaultSchema);
cmd.Parameters.AddWithValue("@Name", typeName.Name);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
throw new MissingObjectException($"Could not find user defined type {typeName}");
}
actualSchema = reader.GetString("SchemaName");
actualName = reader.GetString("Name");
objectId = reader.GetInt32("ObjectId");
}
}
}
var columns = GetColumns(typeName.ToString(), objectId);
return(new UserDefinedTableTypeMetadata <SqlServerObjectName, OleDbType>(new SqlServerObjectName(actualSchema, actualName), columns));
}
internal ScalarFunctionMetadata <SqlServerObjectName, OleDbType> GetScalarFunctionInternal(SqlServerObjectName tableFunctionName)
{
const string sql =
@"SELECT s.name AS SchemaName,
o.name AS Name,
o.object_id AS ObjectId,
COALESCE(t.name, t2.name) AS TypeName,
p.is_nullable,
CONVERT(INT, COALESCE(p.max_length, t.max_length, t2.max_length)) AS max_length,
CONVERT(INT, COALESCE(p.precision, t.precision, t2.precision)) AS precision,
CONVERT(INT, COALESCE(p.scale, t.scale, t2.scale)) AS scale
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.parameters p ON p.object_id = o.object_id AND p.parameter_id = 0
LEFT JOIN sys.types t on p.system_type_id = t.user_type_id
LEFT JOIN sys.types t2 ON p.user_type_id = t2.user_type_id
WHERE o.type IN ('FN')
AND s.name = ?
AND o.name = ?;" ;
string actualSchema;
string actualName;
int objectId;
string fullTypeName;
string typeName;
bool isNullable;
int? maxLength;
int? precision;
int? scale;
using (var con = new OleDbConnection(m_ConnectionBuilder.ConnectionString))
{
con.Open();
using (var cmd = new OleDbCommand(sql, con))
{
cmd.Parameters.AddWithValue("@Schema", tableFunctionName.Schema ?? DefaultSchema);
cmd.Parameters.AddWithValue("@Name", tableFunctionName.Name);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
throw new MissingObjectException($"Could not find scalar function {tableFunctionName}");
}
actualSchema = reader.GetString(reader.GetOrdinal("SchemaName"));
actualName = reader.GetString(reader.GetOrdinal("Name"));
objectId = reader.GetInt32(reader.GetOrdinal("ObjectId"));
typeName = reader.IsDBNull(reader.GetOrdinal("TypeName")) ? null : reader.GetString(reader.GetOrdinal("TypeName"));
isNullable = reader.GetBoolean(reader.GetOrdinal("is_nullable"));
maxLength = reader.IsDBNull(reader.GetOrdinal("max_length")) ? (int?)null : reader.GetInt32(reader.GetOrdinal("max_length"));
precision = reader.IsDBNull(reader.GetOrdinal("precision")) ? (int?)null : reader.GetInt32(reader.GetOrdinal("precision"));
scale = reader.IsDBNull(reader.GetOrdinal("scale")) ? (int?)null : reader.GetInt32(reader.GetOrdinal("scale"));
AdjustTypeDetails(typeName, ref maxLength, ref precision, ref scale, out fullTypeName);
}
}
}
var objectName = new SqlServerObjectName(actualSchema, actualName);
var parameters = GetParameters(objectName.ToString(), objectId);
return(new ScalarFunctionMetadata <SqlServerObjectName, OleDbType>(objectName, parameters, typeName, TypeNameToSqlDbType(typeName), isNullable, maxLength, precision, scale, fullTypeName));
}
