WHERE IN - potatoscript/sql GitHub Wiki

public class CommunicationErrorConfirmReposity: RepositoryBase
{
   public IEnumerable<CommunicationErrorEntity>BulkConfirmAsync(UserEntity userInfo)
   {
     using(IDbConnection dbConnection = this.Connection)
     {
         try
         {
            var builder = new SqlBuilder();
            builder.Where("confirm_flag = false");
            if(userInfo.UserTyapeId == Const.USER_TYPE_CODE_HOSPITAL_ADMIN_ID)
            {
               builder.Where("location_id IN(select location_id from m_location L"+
                  "inner join m_user_hospital_relation H on H.hospital_id = L.hospital_id WHERE H.user_id = @ConfirmUserId)"
               );
            }
            var builderTemplate = builder.AddTemplate(
              "UPDATE t_communication_error E SET confirm_flage = true,"+
              " confirm_user_id = @ConfirmUserId, update_datetime = CURRENT_TIMESTAMP /**WHERE**/ RETURNING *" 
            );
            dbConnection.Open();
            IEnumerable<CommunicationErrorEntity>communicationErrorListEntity = 
            dbConnection.Query<CommunicationErrorEntity>(builderTemplate.RawSql, 
            new {@ConfirmUserId = userInfo.UserId, @ConfirmUserName = userInfo.UserName});
            
            return communicationErrorListEntity;
         }
         catch(Exception ex)
         {
             this.logger.LogError(ex, ex.Message, null);
             return null;
         }
     }
   }
}

• RepositoryBase.cs

public abstract class RepositoryBase
{
  protected readonly string conncetionString;
  protected readonly string shemaName;
  
  protected RepositoryBase()
  {
    var connectionStrTemplate = Environment.GetEnvironmentVariable("POSTGRESQLCONNSTR_PostgreSQL");
    var dbHost = Environment.GetEnvironmentVariable("IOTRMS_RDB_HOST_NAME");
    var dbName = Environment.GetEnvironmentVariable("IOTRMS_RDB_DB_NAME");
    var dbUser = Environment.GetEnvironmentVariable("IOTRMS_RDB_USER_NAME");
    var dbPassword = Environment.GetEnvironmentVariable("IOTRMS_RDB_USER_PASSWORD");
    this.schemaName = Environment.GetEnvironmentVariable("IOTRMS_RDB_SCHEMA_NAME");
    this.connectionString = string.Format(connedtionStrTemplate, dbHost, dbName, dbName, dbUser, dbPassword);
  } 
 
  protected IDbConnection Connection
  {
     get { return new Npgsqlonnection(this.connectionString); }
  }
}

• launchSettings.json

{
  "profiles":{
     "IotRmsWeb":{
      "environmentVariables":{
          "IOTRMS_RDB_HOST_NAME": "jpe-iotrms-dev-psql",
          "IOTRMS_RDB_DB_NAME":"npriotrms",
          "IOTRMS_RDB_SCHEMA_NAME":"public",
          "IOTRMS_RDB_USER_PASSWORD":xxx,
          "IOTRMS_RDB_USER_NAME": "jpeiotrmsdevpsqladmin",
          "POSTGRESQLCONNSTR_PostgreSQL": "Server=[0].postgres.database.azure.com;Datable={1};Port=5432;User Id={2}@{0};Password={3};Ssl Mode=Require;Maximum Pool Sie= 45;", 
       } 
     }
  }
}