Faccio un Post per interposta persona, infatti questa soluzione arriva dal mio nuovo collega, Luca Del Mestre che spero di convincere presto a scrivere personalmente su DotNetWork.
Scenario:
Spesso ci troviamo ad avere la necessità di leggere da Database un singolo record di una tabella, a volte contiene dei parametri da usare nell'applicazione, a volte contiene il dettaglio di un particolare dato, è comunque comune alle applicazioni che lavorano con i DB.
Se la query che ci aspettiamo ritornare una riga non torna nulla, spesso, il codice necessario a gestire questo tipo di accadimento è lungo e decisamente poco utile, farebbe invece comodo e ci permetterebbe di risparmiare parecchio codice e parecchi grattacapi se la query ritornasse una riga in ogni caso, solamente facendo in modo che una riga non valida sia vuota.
Luca, che è un DBA Oracle, mi ha mostrato come in Oracle vi sia una sintassi nel dialetto SQL che permette di ottenere questo tipo di funzionalità, SQL Server ne ha una corrispondente però, in SQL 2005 applicandola si riceve un messaggio di errore che la indica come parte di SQL 2000 da non usarsi se non modificando il livello di compatibilità del db alla versione 2000. Ma ovviamente c'è sempre un metodo alternativo per risolvere il problema.
L'esempio che vi posto è creato su una ipotetica tabella Users che mappa gli utenti Windows che hanno accesso ad una applicazione su database.
La tabella:
USE [paperinik]
GO
CREATE TABLE [dbo].[TbUsers](
[IDUser] [uniqueidentifier] NOT NULL,
[WinUser] [nvarchar](128) NOT NULL,
[WinDomain] [nvarchar](128) NOT NULL,
[UserName] [nvarchar](255) NOT NULL,
[DataLogin] [datetime] NULL,
[IDAzienda] [nchar](3) NULL,
[InternalMail] [nvarchar](128) NULL,
[Obsoleto] [bit] NULL,
[ObsoletoDal] [datetime] NULL,
[IsAdmin] [bit] NULL,
[IsSupervisor] [bit] NULL,
[DirectConnection] [bit] NULL,
[LastUpdated] [timestamp] NULL,
CONSTRAINT [PK_TbMcpUsers] PRIMARY KEY NONCLUSTERED
(
[IDMcpUser] ASC
) ON [PRIMARY]
) ON [PRIMARY]
La classe di test:
public static class User
{
private const string SQL_CnString = "integrated security = SSPI; data source = .; initial catalog = paperinik;";
public const string FLD_IDUser = "IDUser";
public const string FLD_WinUser = "WinUser";
public const string FLD_WinDomain = "WinDomain";
public const string FLD_UserName = "UserName";
public const string FLD_IsAdmin = "IsAdmin";
private const string SQL_ValidateUser = @"
SELECT [IDUser]
,[WinUser]
,[WinDomain]
,[UserName]
,[DataLogin]
,[IDAzienda]
,[InternalMail]
,[Obsoleto]
,[ObsoletoDal]
,[IsAdmin]
,[IsSupervisor]
,[DirectConnection]
,[LastUpdated]
FROM TbUsers usr
RIGHT OUTER JOIN (SELECT @WinUser ut, @WinDomain do) fake
ON (usr.WinUser = fake.ut AND usr.WinDomain = fake.do)
";
private const string TXT_UtenteNonValido = "Utente non valido";
private static readonly string mClassName = System.Reflection.MethodBase.GetCurrentMethod().ReflectedType.Name;
private static DataRow mUserRow;
public static Guid IDUser
{
get
{
if (mUserRow[FLD_IDUser] == System.DBNull.Value)
{
return Guid.Empty;
}
else
{
return (Guid)mUserRow[FLD_IDUser];
}
}
}
public static string WinUser
{
get
{
if (mUserRow[FLD_WinUser] == System.DBNull.Value)
{
return null;
}
else
{
return (string)mUserRow[FLD_WinUser];
}
}
}
public static string WinDomain
{
get
{
if (mUserRow[FLD_WinDomain] == System.DBNull.Value)
{
return null;
}
else
{
return (string)mUserRow[FLD_WinDomain];
}
}
}
public static string UserName
{
get
{
if (mUserRow[FLD_UserName] == System.DBNull.Value)
{
return TXT_UtenteNonValido;
}
else
{
return (string)mUserRow[FLD_UserName];
}
}
}
public static bool IsAdmin
{
get
{
if (mUserRow[FLD_IsAdmin] == System.DBNull.Value)
{
return false;
}
else
{
return (bool)mUserRow[FLD_IsAdmin];
}
}
}
public static bool IsValid()
{
return( UserName != null && UserName != TXT_UtenteNonValido);
}
public static void ValidateUser(string pWinUser, string pWinDomain)
{
using (SqlConnection cn = new SqlConnection())
{
SqlParameter[] sqlParam = new SqlParameter[] {
new SqlParameter( "@WinUser", pWinUser ),
new SqlParameter( "@WinDomain", pWinDomain )
};
cn.ConnectionString = SQL_CnString;
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = SQL_ValidateUser;
cmd.Parameters.AddRange(sqlParam);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
DataTable dt = new DataTable();
dt.Load(dr);
mUserRow = dt.Rows[0];
}
cn.Close();
}
}
}
Che cosa otteniamo provando ad usare la classe in una applicazione Windows dove io ho inserito un record per il signor M.Rossi e invece non ho inserito il signor G.Rossi?:
Il signor G.Rossi non è un utente mappato.
Il signor M.Rossi invece è mappato sulla tabella.
Dov'è il trucco per far si che la query che potete notare all'inizio della classe di test ritorni sempre un record:
SELECT [IDUser]
,[WinUser]
,[WinDomain]
,[UserName]
,[DataLogin]
,[IDAzienda]
,[InternalMail]
,[Obsoleto]
,[ObsoletoDal]
,[IsAdmin]
,[IsSupervisor]
,[DirectConnection]
,[LastUpdated]
FROM TbUsers usr
RIGHT OUTER JOIN (SELECT @WinUser ut, @WinDomain do) fake
ON (usr.WinUser = fake.ut AND usr.WinDomain = fake.do)
Il segreto è la RIGHT OUTER JOIN utilizzata per costruire un filtro furbo, ove invece di fare una semplice
WHERE
WinUser = @WinUser AND WinDomain = @WinDomain
Usiamo i 2 parametri del filtro per costruire una tabella al volo che chiamiamo fake (falsa)
(SELECT @WinUser ut, @WinDomain do) fake
E facciamo una RIGHT JOIN fra questa tabella e la tabella utenti utilizzando la clausola OUTER per cui per ogni riga della tabella posta a destra della JOIN viene creata una riga nella tabella risultante, che, quando non vi fossero valori nella tabella LEFT (la nostra USER) riempie i campi con NULL.
In questo modo, otteniamo un record valido per M.Rossi e un record vuoto per G.Rossi. Come possiamo notare, la nostra classe di test mappa direttamente i campi della DataRow sulle property con la sola gestione del DBNull, senza questa soluzione, per ottenere una classe funzionante, avremmo dovuto mappare i campi su variabili a livello di classe e fare una funzione di Clear che le svuota quando non c'è una riga che possa aggiornare i dati. Avremmo inoltre dovuto aggiornare le variabili a livello di classe dalla DataRow aggiungendo codice ulteriore e OverHead al nostro sistema.
Tags: SQL, Query, CheckIdent,SqlServer