C#使用存储过程查询结果

存储过程：

ALTER PROCEDURE [dbo].[pro_login]
@condition VARCHAR(255) = '',
@table_name VARCHAR(255) = '',
@int_top int = 0
AS
BEGIN
	DECLARE @sqlstr VARCHAR(1000)
  -- routine body goes here, e.g.
  -- SELECT 'Navicat for SQL Server'
	if (@int_top <> 0)
	BEGIN
	IF (@condition is not null and @condition <> '')
	BEGIN
	SET @sqlstr = 'select top ' + @int_top + ' * from ' + @table_name +' where ' + @condition
	END
	ELSE
	BEGIN
	SET @sqlstr = 'select top ' + @int_top + ' * from ' + @table_name 
	END
	END
	ELSE
	
	BEGIN
	IF (@condition is not null and @condition <> '')
	BEGIN
	SET @sqlstr = ' select * from ' + @table_name + ' where ' + @condition 
	END
	ELSE
	BEGIN
	SET @sqlstr = ' select * from ' + @table_name
	END
	END
	EXEC (@sqlstr)
END

C#方法：

//直接调用方法
 public IEnumerable<T> GetEntityList_EXEC(string where, int? top = 0)
        {
            //获取类型
            Type type = typeof(T);
            string sql = " EXEC pro_login @condition=@condition,@column_list=@column_list,@table_name=@table_name,@int_top=@int_top";
            //创建传入参数
            SqlParameter[] listParam = new SqlParameter[] {
                   new SqlParameter(){ParameterName="@condition",SqlDbType=SqlDbType.VarChar,Value=where},
                   new SqlParameter(){ParameterName="@column_list",SqlDbType=SqlDbType.VarChar,Value=SenctionHelper.GetModelSenction<T>()},
                   new SqlParameter(){ParameterName="@table_name",SqlDbType=SqlDbType.VarChar,Value=type.Name},
                   new SqlParameter(){ParameterName="@int_top",SqlDbType=SqlDbType.Int,Value=top}
            };
            //执行查询方法
            using (SqlDataReader reader = SenctionHelper.ExecuteReader(sql, listParam))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        yield return SenctionHelper.MapEntity<T>(reader);
                    }
                }
            }
        }

//SenctionHelper.ExecuteReader(string cmdText, params SqlParameter[] parameters)方法
public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteReader(cmdText, CommandType.Text, parameters);
        }

//SenctionHelper.ExecuteReader(string cmdText, CommandType type, params SqlParameter[] parameters)方法
 public static SqlDataReader ExecuteReader(string cmdText, CommandType type, params SqlParameter[] parameters)
        {
            //通过连接字符串获取数据库链接
            SqlConnection conn = new SqlConnection(ConnectionStringCustomers);
            //通过语句和链接句柄获取操作句柄
            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                //如果链接状态为关闭
                if (conn.State == ConnectionState.Closed)
                {
                    //打开链接
                    conn.Open();
                }
                //如果传入参数不为NULL
                if (parameters != null)
                {
                    //清空句柄参数
                    cmd.Parameters.Clear();
                    //添加传入参数
                    cmd.Parameters.AddRange(parameters);
                }
                //设置控制类型
                cmd.CommandType = type;
                //conn.Open();
                try
                {
                    //SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    //cmd.Parameters.Clear();
                    //return reader;
                    //返回执行结果，CommandBehavior.CloseConnection：执行后关闭链接
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    //出现异常关闭连接并且释放
                    conn.Close();
                    throw ex;
                }
            }
        }
#region 获取映射模型的字段
        ///SenctionHelper.GetModelSenction<T>()
        /// <summary>
        
        /// 获得当前对象的所有公共字段
        /// </summary>
        /// <param name="modelName"></param>
        /// <returns></returns>
        public static string GetModelSenction<T>()
        {
            Type type = typeof(T);
            string value = "";
            var oEntity = type.GetProperties();//
            foreach (var item in oEntity)
            {
                var query = item.GetCustomAttributesData();
                if (query.Count > 0 && (bool)query[0].ConstructorArguments[0].Value)
                {
                }
                else
                {
                    value = value + "[" + item.Name + "]" + ",";
                }
            }
            return value.Substring(0, value.Length - 1);
        }
        #endregion