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)); }