1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
|
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void AnnuaireFind(SqlInt32 intBuId, SqlInt32 intSSId, SqlInt32 intREId, SqlInt32 intCOId, SqlString strLangueId, SqlInt32 intJobId, SqlString strSearch)
{
strLangueId = (SqlString)strLangueId.ToString().ToLower();
strSearch = (SqlString)Regex.Replace((string)strSearch, @"(\')", "''");
StringBuilder oSBSQL = new StringBuilder();
oSBSQL.Append("SELECT DISTINCT ");
oSBSQL.Append("VAnnu.AnnuaireId, ");
oSBSQL.Append("VAnnu.AnnuaireLastName, ");
oSBSQL.Append("VAnnu.AnnuaireFirstName, ");
oSBSQL.Append("VAnnu.AnnuaireMail, ");
oSBSQL.Append("VAnnu.AnnuairePhone, ");
oSBSQL.Append("Vannu.JobTitle, ");
oSBSQL.Append("VAnnu.LocationName ");
oSBSQL.Append("FROM ");
oSBSQL.Append("SchemaWeb.ViewAnnuaireUserList AS VAnnu ");
StringBuilder oSBClause = new StringBuilder(" WHERE ");
oSBClause.Append("(VAnnu.AnnuaireId = VAnnu.AnnuaireId) ");
if (intBuId > 0)
{
oSBClause.Append(" AND (VAnnu.BuId = " + intBuId.ToString() + ") ");
}
if (intSSId > 0)
{
oSBClause.Append(" AND (VAnnu.FunctionId = " + intSSId.ToString() + ") ");
}
if (intREId > 0)
{
oSBClause.Append(" AND (VAnnu.RegionId = " + intREId.ToString() + ") ");
}
if (intCOId > 0)
{
oSBClause.Append(" AND (VAnnu.ContractId = " + intCOId.ToString() + ") ");
}
if (intJobId > 0)
{
oSBClause.Append(" AND (VAnnu.JobId = " + intJobId.ToString() + ") ");
}
if (strSearch != "")
{
oSBClause.Append(" AND LOWER(");
oSBClause.Append(" isnull(VAnnu.AnnuaireLastName + ' ','') +");
oSBClause.Append(" isnull(VAnnu.AnnuaireFirstName + ' ','') +");
oSBClause.Append(" isnull(VAnnu.AnnuaireMail + ' ','') +");
oSBClause.Append(" isnull(VAnnu.JobTitle + ' ','') +");
oSBClause.Append(" isnull(VAnnu.LocationName + ' ','')");
oSBClause.Append(" ) like lower('%");
oSBClause.Append((string)strSearch);
oSBClause.Append("%') ");
}
oSBSQL.Append(oSBClause.ToString());
using (SqlConnection oConn = new SqlConnection("context connection=true"))
{
SqlCommand oCmd;
oConn.Open();
oCmd = new SqlCommand(oSBSQL.ToString(), oConn);
SqlContext.Pipe.ExecuteAndSend(oCmd);
oConn.Close();
}
}
}; |
Partager