/// <summary>
/// 数据库操作帮助类，可处理CRUD 操作，和存储过程调用
/// </summary>
public class Dbutil
{
    private DbProviderFactory _factory;
    private string _connectionString;
    private string _connectioinName;

    public string ConnectionName
    {
        get { return _connectioinName; }
        set
        {
            _connectioinName = value;
            _connectionString = ConfigurationManager.ConnectionStrings[value].ConnectionString;
            _factory = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[value].ProviderName);
        }
    }

    public Dbutil(string connName)
    {
        ConnectionName = connName;
    }

    public DataSet ExecuteDataSet(string sql, List<DbParameter> list)
    {
        using (DbConnection conn = _factory.CreateConnection())
        using (DbCommand cmd = _factory.CreateCommand())
        using (DbDataAdapter da = _factory.CreateDataAdapter())
        {
            conn.ConnectionString = _connectionString;
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            da.SelectCommand = cmd;

            foreach (DbParameter i in list)
            {
                cmd.Parameters.Add(i);
            }
           
            using (DataSet ds = new DataSet())
            {
                conn.Open();
                da.Fill(ds);
                return ds;
            }
        }
    }

    public int ExecuteNonQuery(string sql)
    {
        using (DbConnection conn = _factory.CreateConnection())
        using (DbCommand cmd = _factory.CreateCommand())
        {
            conn.ConnectionString = _connectionString;
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            conn.Open();
           
            return cmd.ExecuteNonQuery();
        }

    }


    public int ExecuteNonQuery(string sql, List<DbParameter> list)
    {
        using (DbConnection conn = _factory.CreateConnection())
        using (DbCommand cmd = _factory.CreateCommand())
        {
            conn.ConnectionString = _connectionString;
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;

            foreach (DbParameter i in list)
            {
                cmd.Parameters.Add(i);
            }
            conn.Open();
            return cmd.ExecuteNonQuery();
        }

    }

    public IEnumerable<object> ExecuteProcedure(string procedureName, List<DbParameter> list)
    {
        using (DbConnection conn = _factory.CreateConnection())
        using (DbCommand cmd = _factory.CreateCommand())
        {
            conn.ConnectionString = _connectionString;
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procedureName;

            foreach (DbParameter i in list)
            {
                cmd.Parameters.Add(i);
            }
            conn.Open();
            var res = cmd.ExecuteNonQuery();
            yield return res;
            foreach (DbParameter i in list)
            {
                if (i.Direction == ParameterDirection.Output)
                    yield return i.Value;
            }

        }
    }
}