获取数据类型为bit的列的所有行

本文关键字：获取 bit 数据类型 | 更新日期: 2023-09-27 18:08:07

我需要传递一个参数到存储过程，以获得列的数据类型为Bit的所有行。示例:

select * from user where active = true   // get all actived users 
select * from user where active = false   // get all Not actived users

当我需要得到所有行时，我该怎么做?我将活动值作为c#

获取数据类型为bit的列的所有行

的参数传递。

您可以将active参数设置为可选参数，并执行如下操作:

ALTER PROCEDURE [dbo].[GetUsers](
    @Active BIT = NULL
) AS
BEGIN
    SELECT * 
    FROM user
    WHERE (@Active IS NULL OR active = @Active)
END

在代码中，添加一个重载的fetch方法:

var users = GetUsers(true); //pass active as true
var users = GetUsers(); //dont pass active parameter, return all users

使用?来取消你的c# bool值:

bool? showActive = SomeCode();

传递可能为空的showActive标志并更新SQL代码:

select * from user where active = @showActive or @showActive is null

如果传递的@showActive参数为空，则返回所有行

取决于活动列中的数据。如果你有真和假，我怀疑，因为sql数据库不支持这些类型的值，但你至少可以有位值类型。所以bit只能是1或者0(1或者0)

这也可以通过以下方式实现:

WHERE user.active = ISNULL(@Active, user.active)

我喜欢这样的东西。给定存储过程:

create procedure dbo.SelectAccounts
  @fExpired bit
as
  set ansi_nulls              on
  set concat_null_yields_null on
  select *
  from dbo.Account acct
  where acct.Expired = @fExpired = coalesce( @fExpired , acct.Expired )
  -- a more verbose alternative test
  --    (          acct.Expired =  @fExpired
  --      OR (     acct.Expired is null
  --           and @fExpired    is not null
  --         )
  --    )
  return 0 
go

我生成一个类，看起来像这样:

public class dbo_SelectAccounts
{
  public const string STORED_PROCEDURE_NAME = @"dbo.SelectAccounts";
  private string   ConnectString    { get ;         set ; }
  public int       ReturnCode       { get ; private set ; }
  public int       TimeoutInSeconds { get ; private set ; }
  public DataTable ResultSet        { get ; private set ; }
  public int       RowCount         { get { return this.ResultSet.Rows.Count ; } }
  public int Exec( bool? @fExpired )
  {
    using ( SqlConnection  conn = new SqlConnection( this.ConnectString ) )
    using ( SqlCommand     cmd  = conn.CreateCommand() )
    using ( SqlDataAdapter sda  = new SqlDataAdapter(cmd) )
    {
      cmd.CommandText    = STORED_PROCEDURE_NAME;
      cmd.CommandType    = CommandType.StoredProcedure;
      cmd.CommandTimeout = this.TimeoutInSeconds ;
      // 1. Format parameter to stored procedure
      SqlParameter p1 = new SqlParameter( @"@fExpired" , SqlDbType.Bit ) ;
      if ( @fExpired == null )
      {
        p1.Value = System.DBNull.Value ;
      }
      else
      {
        p1.Value = @fExpired ;
      }
      cmd.Parameters.Add( p1 );
      // add return code parameter
      SqlParameter pReturnCode = new SqlParameter();
      pReturnCode.SqlDbType    = System.Data.SqlDbType.Int;
      pReturnCode.Direction    = System.Data.ParameterDirection.ReturnValue;
      cmd.Parameters.Add( pReturnCode );
      conn.Open();
      sda.Fill( this.ResultSet ) ;
      conn.Close();
      this.ReturnCode = (int)pReturnCode.Value;
    }
    return this.ReturnCode;
  }
  #region constructors
  public dbo_SelectAccounts( string connectionString , int executionTimeoutInSeconds )
  {
    this.ConnectString    = connectionString          ;
    this.TimeoutInSeconds = executionTimeoutInSeconds ;
    this.ReturnCode       = -1                        ;
    this.ResultSet        = new DataTable()           ;
    return ;
  }
  public dbo_SelectAccounts( string connectionString )
  : this( connectionString , 30 )
  {
    this.ConnectString = connectionString;
  }
  public dbo_SelectAccounts( SqlConnectionStringBuilder csb , int executionTimeoutInSeconds )
  : this( csb.ConnectionString , executionTimeoutInSeconds )
  {
    return;
  }
  public dbo_SelectAccounts( SqlConnectionStringBuilder csb )
  : this( csb.ConnectionString )
  {
    return;
  }
  #endregion constructors
}

用法很简单:

dbo_SelectAccount sp = new dbo_SelectAccounts( myConnectString ) ;
int               rc = sp.Exec( null ) ;
相关文章：