public CommandResultSchema(CommandSchema command, string name, CommandResultColumnSchema[] columns)
{
this.database = command.Database;
this.command = command;
this.name = name;
this.columns = new List<CommandResultColumnSchema>();
this.columns.AddRange(columns);
}
public CommandResultColumnSchema(CommandSchema command, string name, DbType dataType, string nativeType, int size, byte precision, int scale, bool isAllowDBNull)
{
this.database = command.Database;
this.command = command;
this.name = name;
this.dataType = dataType;
this.nativeType = nativeType;
this.size = size;
this.precision = precision;
this.scale = scale;
this.isAllowDBNull = isAllowDBNull;
}
public ParameterSchema(CommandSchema command, string name, ParameterDirection direction, DbType dataType, string nativeType, int size, byte precision, int scale, bool allowDBNull)
{
this.database = command.Database;
this.command = command;
this.name = name;
this.direction = direction;
this.dataType = dataType;
this.nativeType = nativeType;
this.size = size;
this.precision = precision;
this.scale = scale;
this.isAllowDBNull = allowDBNull;
}
public ParameterSchema(CommandSchema command, string name, ParameterDirection direction, DbType dataType, string nativeType, int size, byte precision, int scale, bool allowDBNull, ExtendedProperty[] extendedProperties)
: this(command, name, direction, dataType, nativeType, size, precision, scale, allowDBNull)
{
this.extendedProperties = new List<ExtendedProperty>();
this.extendedProperties.AddRange(extendedProperties);
}
public CommandResultSchema(CommandSchema command, string name, CommandResultColumnSchema[] columns, ExtendedProperty[] extendedProperties)
: this(command, name, columns)
{
this.extendedProperties = new List<ExtendedProperty>();
this.extendedProperties.AddRange(extendedProperties);
}
public CommandResultColumnSchema(CommandSchema command, string name, DbType dataType, string nativeType, int size, byte precision, int scale, bool isAllowDBNull, ExtendedProperty[] extendedProperties)
: this(command, name, dataType, nativeType, size, precision, scale, isAllowDBNull)
{
this.extendedProperties = new List<ExtendedProperty>();
this.extendedProperties.AddRange(extendedProperties);
}
public CommandResultSchema[] GetCommandResultSchemas(string connectionString, CommandSchema command)
{
throw new NotSupportedException("GetCommandResultSchemas() is not supported in this release.");
}
public ParameterSchema[] GetCommandParameters(string connectionString, CommandSchema command)
{
string sqlstring = string.Empty;
List<ParameterSchema> parameters = new List<ParameterSchema>();
sqlstring = string.Format("\r\n declare @SchemaName nvarchar(200)\r\n declare @CommandName nvarchar(200)\r\n select @CommandName='{0}'\r\n select @SchemaName='{1}' {2}", command.Name, "dbo", "\r\n SELECT\r\n\t DB_NAME() AS [PROCEDURE_CATALOG],\r\n\t @SchemaName AS [PROCEDURE_SCHEMA],\r\n\t NULL AS [PROCEDURE_NAME],\r\n\t '@RETURN_VALUE' AS [PARAMETER_NAME],\r\n\t 0 AS [ORDINAL_POSITION],\r\n\t CAST(4 AS smallint) AS [PARAMETER_TYPE],\r\n\t 0 AS [PARAMETER_HASDEFAULT],\r\n\t NULL AS [PARAMETER_DEFAULT],\r\n\t CAST(0 AS bit) AS [IS_NULLABLE],\r\n\t 0 AS [DATA_TYPE],\r\n NULL AS [CHARACTER_MAXIMUM_LENGTH],\r\n NULL AS [CHARACTER_OCTET_LENGTH],\r\n CAST(10 AS smallint) AS [NUMERIC_PRECISION],\r\n CAST(NULL AS smallint) AS [NUMERIC_SCALE],\r\n NULL AS [DESCRIPTION],\r\n 'int' AS [TYPE_NAME],\r\n 'int' AS [LOCAL_TYPE_NAME]\r\n UNION ALL\r\n SELECT\r\n\t DB_NAME() AS [PROCEDURE_CATALOG],\r\n\t SCHEMA_NAME(sp.schema_id) AS [PROCEDURE_SCHEMA],\r\n\t NULL AS [PROCEDURE_NAME],\r\n\t param.name AS [PARAMETER_NAME],\r\n\t param.parameter_id AS [ORDINAL_POSITION],\r\n\t CAST(CASE WHEN param.is_output = 1 THEN 2 ELSE 1 END AS smallint) AS [PARAMETER_TYPE],\r\n\t 0 AS [PARAMETER_HASDEFAULT],\r\n\t NULL AS [PARAMETER_DEFAULT],\r\n\t CAST(1 AS bit) AS [IS_NULLABLE],\r\n\t 0 AS [DATA_TYPE],\r\n CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [CHARACTER_MAXIMUM_LENGTH],\r\n NULL AS [CHARACTER_OCTET_LENGTH],\r\n CAST(param.precision AS smallint) AS [NUMERIC_PRECISION],\r\n CAST(param.scale AS smallint) AS [NUMERIC_SCALE],\r\n NULL AS [DESCRIPTION],\r\n ISNULL(baset.name, N'') AS [TYPE_NAME],\r\n ISNULL(baset.name, N'') AS [LOCAL_TYPE_NAME]\r\n FROM\r\n\t sys.all_objects AS sp\r\n\t INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id\r\n\t LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id\r\n WHERE\r\n\t (sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')and(sp.name=@CommandName and SCHEMA_NAME(sp.schema_id)=@SchemaName)\r\n ORDER BY\r\n\t 5 ASC");
using (DbConnection dbConnection = MsSqlSchemaProvider.CreateConnection(connectionString))
{
dbConnection.Open();
DbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = sqlstring;
dbCommand.Connection = dbConnection;
using (IDataReader reader = dbCommand.ExecuteReader())
{
while (reader.Read())
{
string name = reader.GetString(3);
ParameterDirection direction = this.GetParameterDirection(reader.GetInt16(5));
string nativeType = reader.IsDBNull(15) ? string.Empty : reader.GetString(15);
DbType dataType = reader.IsDBNull(15) ? DbType.Object : this.GetDbType(nativeType);
int size = reader.IsDBNull(10) ? 0 : reader.GetInt32(10);
int precision = reader.IsDBNull(12) ? 0 : reader.GetInt16(12);
int scale = (int)(reader.IsDBNull(13) ? 0 : reader.GetInt16(13));
bool allowDBNull = reader.GetBoolean(8);
string defaultValue = reader.IsDBNull(7) ? string.Empty : reader.GetString(7);
List<ExtendedProperty> extendedProperties = new List<ExtendedProperty>();
extendedProperties.Add(new ExtendedProperty("CS_Default", defaultValue, DbType.String, PropertyStateEnum.ReadOnly));
parameters.Add(new ParameterSchema(command, name, direction, dataType, nativeType, size, Convert.ToByte(precision), scale, allowDBNull, extendedProperties.ToArray()));
}
}
}
return parameters.ToArray();
}
public string GetCommandText(string connectionString, CommandSchema commandSchema)
{
StringBuilder stringBuilder = new StringBuilder();
string commandText = string.Format("sp_helptext '{0}'", commandSchema.Name);
using (DbConnection dbConnection = MsSqlSchemaProvider.CreateConnection(connectionString))
{
dbConnection.Open();
DbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = commandText;
dbCommand.Connection = dbConnection;
using (IDataReader dataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dataReader.Read())
{
stringBuilder.Append(dataReader.GetString(0));
}
if (!dataReader.IsClosed)
{
dataReader.Close();
}
}
if (dbConnection.State != ConnectionState.Closed)
{
dbConnection.Close();
}
}
return stringBuilder.ToString();
}
