如何确定存储过程结果的列是否可以为空

Question

我正在编写一个代码生成器，但在确定存储过程结果集列的可为空状态时陷入困境。我可以很好地查询 DataType，但 datareader 对象和数据表列都不包含我的列的正确可为空值。

        public List<DataColumn> GetColumnInfoFromStoredProcResult(string schema, string storedProcName)
    {
        //build sql text
        var sb = new StringBuilder();
        sb.Append("SET FMTONLY OFF; SET FMTONLY ON; \n");//this is how EF4.1 did so I copied..not sure why the repeat

        sb.Append(String.Format("exec {0}.{1} ", schema, storedProcName));

        var prms = GetStoredProcedureParameters(schema: schema, sprocName: storedProcName);
        var count = 1;
        foreach (var param in prms)
        {
            sb.Append(String.Format("{0}=null", param.Name));
            if (count < prms.Count)
            {
                sb.Append(", ");
            }
            count++;
        }

        sb.Append("\n SET FMTONLY OFF; SET FMTONLY OFF;");

        var dataTable = new DataTable();
        //var list = new List<DataColumn>();

        using (var sqlConnection = this.SqlConnection)
        {
            using (var sqlAdapter = new SqlDataAdapter(sb.ToString(), sqlConnection))
            {
                if (sqlConnection.State != ConnectionState.Open) sqlConnection.Open();
                sqlAdapter.SelectCommand.ExecuteReader(CommandBehavior.KeyInfo);
                sqlConnection.Close();
                sqlAdapter.Fill(dataTable);
            }

            //using (var sqlCommand = new SqlCommand())
            //{

            //    sqlCommand.CommandText = sb.ToString();
            //    sqlCommand.CommandType = CommandType.Text;
            //    sqlCommand.Connection = sqlConnection;
            //    if (sqlConnection.State != ConnectionState.Open) sqlConnection.Open();

            //    var dr = sqlCommand.ExecuteReader(CommandBehavior.SchemaOnly);
            //    var whateva = dr.GetSchemaTable();

            //    foreach (DataColumn col in whateva.Columns)
            //    {
            //        list.Add(col);
            //    }
            //}
        }

        var list = dataTable.Columns.Cast<DataColumn>().ToList();

        return list;
    }

我试图最终得到类似于实体框架从存储过程创建复杂类型的结果。我可以劫持该功能吗？

在此示例中，Id 列.. tblJobId（不是我的命名约定）永远不会为 null。但是我选择 null 作为 ImNull，它具有所有相同的属性，那么 EF 如何确定相应的 C# 数据类型是否可以为 null ？

有没有人这样做过..

想法受到赞赏。

Answer 1

秘诀是仅使用架构并填充数据集而不是数据表。现在，数据列上的AllowDbNull 属性可以正确显示返回值的可为空状态。

就是这个...

 public List<DataColumn> GetColumnInfoFromStoredProcResult(string schema, string storedProcName)
    {
        //build sql text
        var sb = new StringBuilder();
        sb.Append("SET FMTONLY OFF; SET FMTONLY ON; \n");//this is how EF4.1 did so I copied..not sure why the repeat

        sb.Append(String.Format("exec {0}.{1} ", schema, storedProcName));

        var prms = GetStoredProcedureParameters(schema: schema, sprocName: storedProcName);
        var count = 1;
        foreach (var param in prms)
        {
            sb.Append(String.Format("{0}=null", param.Name));
            if (count < prms.Count)
            {
                sb.Append(", ");
            }
            count++;
        }

        sb.Append("\n SET FMTONLY OFF; SET FMTONLY OFF;");

        var ds = new DataSet();
        using (var sqlConnection = this.SqlConnection)
        {
            using (var sqlAdapter = new SqlDataAdapter(sb.ToString(), sqlConnection))
            {
                if (sqlConnection.State != ConnectionState.Open) sqlConnection.Open();
                sqlAdapter.SelectCommand.ExecuteReader(CommandBehavior.SchemaOnly);
                sqlConnection.Close();
                sqlAdapter.FillSchema(ds, SchemaType.Source, "MyTable");
            }
        }

        var list = ds.Tables[0].Columns.Cast<DataColumn>().ToList();

        return list;
    }

    public List<SqlParamInfo> GetStoredProcedureParameters(string schema, string sprocName)
    {
        var sqlText = String.Format(
            @"SELECT
                [Name] = N'@RETURN_VALUE',
                [ID] = 0,
                [Direction] = 6,
                [UserType] = NULL,
                [SystemType] = N'int',
                [Size] = 4,
                [Precision] = 10,
                [Scale] = 0
            WHERE
                OBJECTPROPERTY(OBJECT_ID(N'{0}.{1}'), 'IsProcedure') = 1
            UNION
            SELECT
                [Name] = CASE WHEN p.name <> '' THEN p.name ELSE '@RETURN_VALUE' END,
                [ID] = p.parameter_id,
                [Direction] = CASE WHEN p.is_output = 0 THEN 1 WHEN p.parameter_id > 0 AND p.is_output = 1 THEN 3 ELSE 6 END,
                [UserType] = CASE WHEN ut.is_assembly_type = 1 THEN SCHEMA_NAME(ut.schema_id) + '.' + ut.name ELSE NULL END,
                [SystemType] = CASE WHEN ut.is_assembly_type = 0 AND ut.user_type_id = ut.system_type_id THEN ut.name WHEN ut.is_user_defined = 1 OR ut.is_assembly_type = 0 THEN st.name WHEN ut.is_table_type =1 Then 'STRUCTURED' ELSE 'UDT' END,
                [Size] = CONVERT(int, CASE WHEN st.name IN (N'text', N'ntext', N'image') AND p.max_length = 16 THEN -1 WHEN st.name IN (N'nchar', N'nvarchar', N'sysname') AND p.max_length >= 0 THEN p.max_length/2 ELSE p.max_length END),
                [Precision] = p.precision,
                [Scale] = p.scale
            FROM
                sys.all_parameters p
                INNER JOIN sys.types ut ON p.user_type_id = ut.user_type_id
                LEFT OUTER JOIN sys.types st ON ut.system_type_id = st.user_type_id AND ut.system_type_id = st.system_type_id
            WHERE
                object_id = OBJECT_ID(N'{0}.{1}') 
            ORDER BY 2", schema, sprocName);


        using (var sqlConnection = this.SqlConnection)
        {
            using (var sqlCommand = new SqlCommand())
            {
                if (sqlConnection.State != ConnectionState.Open) sqlConnection.Open();

                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = sqlText;

                var dr = sqlCommand.ExecuteReader();

                var result = new List<SqlParamInfo>();

                while (dr.Read())
                {
                    if (Convert.ToString(dr["Name"]) != "@RETURN_VALUE")
                    {
                        result.Add(new SqlParamInfo(dr));
                    }
                }

                return result;
            }
        }
    }

Answer 2

假设来自 SP 的每一列都可以为空 - 这是一个有效的假设，因为存储过程 - 它是一种数据抽象层，因此它的代码可以更改，但仍然会产生有效的结果。

如果昨天列不可为空，那么今天就没有任何意义。因此，来自 SP 结果集的所有列在设计上都可以为空。

更新。

假设表 t1 有列 Id INT IDENTITY PRIMARY KEY

您的存储过程如下所示：

CREATE PROC p1
AS
BEGIN
  SELECT Id FROM t1
END

因此它永远不会返回 Id = NULL，但这是 SP -数据的抽象，所以 - 明天我将像这样修改它：

CREATE PROC p1
AS
BEGIN
  SELECT Id FROM t1
  UNION
  SELECT NULL
END

所以，现在它返回 NULL - 想想这个。对数据抽象理解的差异