Avete mai dovuto paginare i dati con SQL Server 2008 ?
bhè, è un'operazione abbastanza semplice grazie alle funzioni di ranking che il sistema ci mette a disposizione.... Tali funzioni consentono una numerazione arbitraria delle righe reperite dalla selezione e nel nostro caso uso la funzione row_number() over(order by campi da ordinare)
use TempDb
Go
--Creao una tabella contenente le righe su cui effettuare la paginazione
Create table dbo.Paginata
(
id int identity(0,1),
Nome varchar(15) not null,
Cognome varchar(20) not null
)
ALTER TABLE dbo.Paginata ADD CONSTRAINT
PK_Paginata PRIMARY KEY NONCLUSTERED
(
Nome,
Cognome
)
set nocount on
declare @i int = 0
--inserisco le righe
While @i < 100000
begin
insert into Paginata (Nome,Cognome)
values('Nome ' + cast(@i as varchar(8)), 'Cognome ' + cast(@i as varchar(8)));
set @i = @i + 1
end
--Ricerco la centesima pagina
declare @pagenumber int= 100
--Ogni pagina mi restituisce 10 righe
declare @pagesize int = 10
SELECT TOP (@pagesize) * FROM
(
SELECT row_number() over(order by Nome,Cognome) as RowNumber,
*
FROM Paginata
)as A
WHERE RowNumber > ((@pagenumber - 1) * @pagesize)
Con la nuova versione SQL Server (ora sto provando la CTP3) il giochino sarà ancora più semplice !!
--Ricerco la centesima pagina
declare @pagenumber int= 100
--Ogni pagina mi restituisce 10 righe
declare @pagesize int = 10
Select * from Paginata
order by Nome,Cognome
OFFSET ((@pagenumber - 1) * @pagesize) ROWS
FETCH NEXT @pagesize ROWS ONLY;
La sintassi dell'order by ora prevede le clausole Offset e Fetch next che consentono di implementare la paginazione senza utilizzare funzioni di ranking !!!!
Vi è mai capitato di dover cercare una stringa specifica all'interno del database senza conoscerne la struttura ? A me si e per evitare di farmi migliaia di select a mano ho creato un piccolo script che mi aiuta in modo molto efficace nella ricerca.... Utilizzo le viste information_schema che restituiscono la struttura del Db. Vediamo come ho fatto :
--non voglio ricevere informazioni sul numero di righe selezionate Set NoCount On
--Dichiarazione variabile contenente la stringa da ritrovare Declare @strToFind varchar(max) = 'Luca'
--variabile tabella contenente la le tabelle e i campi testuali Declare @Tabs table( TABLE_SCHEMA sysname ,TABLE_NAME sysname )
Insert into @Tabs Select Distinct TABLE_SCHEMA,TABLE_NAME from INFORMATION_SCHEMA.COLUMNS Where DATA_TYPE in ('char','nchar','varchar','nvarchar','text','ntext') Order by TABLE_SCHEMA,TABLE_NAME
--Fino a che ci sono righe nella variabile tabella While 1=1 Begin
--operazioni preliminari per formattare la select di ricerca Set @StrFieldsList = '' Set @StrWhereFieldsList = ''
Select Top 1 @strSchema = TABLE_SCHEMA, @strTab=TABLE_NAME from @Tabs Order by TABLE_SCHEMA,TABLE_NAME;
--Se la variabile tabella non mi ha restituito la prima riga esco dal ciclo --perchè ho esaurito le tabelle su cui implementare la ricerca IF @@ROWCOUNT=0 Break;
--Formatto la stringa per ricercare il mio dato With Fields AS ( Select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS Where DATA_TYPE in ('char','nchar','varchar','nvarchar','text','ntext') And TABLE_SCHEMA = @strSchema And TABLE_NAME = @strTab ) Select @StrFieldsList = @StrFieldsList + '' + QUOTENAME(COLUMN_NAME) + ' AS ' + QUOTENAME(COLUMN_NAME) + ',', @StrWhereFieldsList = @StrWhereFieldsList + QUOTENAME(COLUMN_NAME) + ' Like ''%' + @strToFind + '%'' OR ' From Fields
Set @strSql = 'Select ''' + QUOTENAME(@strSchema) + ''' As Table_Schema,''' + QUOTENAME(@strTab) + ''' As Table_Name, ' + Substring(@StrFieldsList,0,LEN(@StrFieldsList)) + ' from ' + QUOTENAME(@strSchema) + '.' + QUOTENAME(@strTab) + ' Where ' + Substring(@StrWhereFieldsList,0,LEN(@StrWhereFieldsList)-2)
--eseguo il comando di ricerca appena generato Exec(@strSql)
-- elimino la riga relativa alla tabella appena elaborata -- dalla tabella temporanea Delete From @Tabs Where TABLE_SCHEMA = @strSchema And TABLE_NAME = @strTab
Suppose to have two collections of objects and you want to get the objects of the first collection that are not included in the second collection, you can use Linq with the method "Except".
Now onlyInFirstSet will contains only numbers that are not present in the secondo collection.
You can get the inverse calling the method Expect on the collection numbers2 and passing the parameter numbers1.
Remember that If you want to compare sequences of objects of some custom data type, you need to implement che interface IEquatable<T> where T is your object.
This interface require to implement the methods Equals and GetHashCode.
In the method Equals you define the logic that let the program to know when two object are equals.
In the method GetHashCode you implement the hash code: it is a numerical value that is tied to a fixed input and it is a form of one-way encryption, let you uniquely identifying an object.
Nello scorso articolo abbiamo visto come convertire un DataTable in una collezione di oggetti tipizzata: Convertire un datatable in oggetti
Questa volta vediamo invece come convertire una collezione di oggetti in istruzioni di insert / update SQL.
Ribadiamo il concetto che esistono degli ORM quali NHibernate,Entity Framework che fanno già queste operazioni in modo eccellente, questo post vuole dare una soluzione semplice per quei progetti dove non è necessario (proprio per il fatto che non sono progetti complessi) oppure per progetti legacy di salvare degli oggetti tramite ADO.Net.
Diamo x scontato queste convenzioni:
- l'oggetto/tabella prevede in campo Id come chiave primaria
- le proprietà che vorremo mappare saranno di tipo Virtual, tutte le altre verranno ignorate
- il metodo eseguirà una insert (se id=0) o una update (se id!=0)
- il metodo accetta in input una IList che verrà valorizzato e vi servirà poi per eseguire il comando SQl
- il metodo ritorna la query sql che vi servirà poi per eseguire il comando di insert/update
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using MpsManager.Controllers.Helper;
namespace MpsManager.Models
{
public static class MapDataAndBusinessEntityHelper
{
public static T SaveOrUpdateBusinessEntityToSqlData(T entity, string tableName,bool isUpdate) where T : new()
{
var hashtable = new Dictionary();
PropertyInfo[] properties = typeof(T).GetProperties();
foreach (PropertyInfo info in properties)
{
if (info.GetGetMethod().IsVirtual || (isUpdate && info.Name=="Id"))
{
hashtable[info.Name.ToUpper()] = info;
}
}
IList list = new List();
SQLADOHelper.ExecuteScalarQueryCommand(isUpdate ? UpdateObject(hashtable, entity, tableName, list) : InsertObject(hashtable, entity, tableName, list), list);
return entity;
}
private static string UpdateObject(Dictionary hashtable, object entity, string tableName, IList list)
{
var isFirst = true;
var sql = "update " + tableName + " SET ";
foreach (var item in hashtable)
{
list.Add(new SqlParameter("@" + item.Key, ((PropertyInfo)item.Value).GetValue(entity, null) ?? DBNull.Value));
if (item.Key.ToString().ToUpper() != "ID")
{
sql += (isFirst ? "" : ",") + "[" + item.Key + "]=" + "@" + item.Key;
isFirst = false;
}
}
sql += " where Id =@ID";
return sql;
}
private static string InsertObject(Dictionary hashtable, object entity, string tableName,IList list )
{
var isFirst = true;
var sql = "insert into " + tableName + " (";
foreach (var item in hashtable)
{
sql += (isFirst ? "" : ",") + "[" + item.Key + "]";
isFirst = false;
}
sql += ") values (";
isFirst = true;
foreach (var item in hashtable)
{
list.Add(new SqlParameter("@" + item.Key, ((PropertyInfo)item.Value).GetValue(entity, null) ?? DBNull.Value));
sql += (isFirst ? "" : ",") + "@" + item.Key;
isFirst = false;
}
sql += ")";
return sql;
}
}
}
Questo invece è l'helper per poter effettuare operazioni Ado.net
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace MpsManager.Controllers.Helper
{
public class SQLADOHelper
{
public static string ConnectionString
{
get
{
return ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
}
}
public static object ExecuteScalarQueryCommand(string sql)
{
return ExecuteScalarQueryCommand(sql, new List());
}
public static void ExecuteNonQueryCommand(string sql)
{
using (IDbConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (IDbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
}
public static DataTable ExecuteQueryCommand(string sql)
{
return ExecuteQueryCommand(sql, new List());
}
public static DataTable ExecuteQueryCommand(string sql, IList list)
{
var dt = new DataTable();
using (var connection = new SqlConnection(ConnectionString))
{
var cmd = new SqlCommand(sql, connection);
foreach (var sqlParameter in list)
{
cmd.Parameters.Add(sqlParameter);
}
connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
connection.Close();
}
return dt;
}
public static object ExecuteScalarQueryCommand(string sql, IList list)
{
using (IDbConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (IDbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = sql;
foreach (var sqlParameter in list)
{
cmd.Parameters.Add(sqlParameter);
}
try
{
object result = cmd.ExecuteScalar();
return result;
}
catch (Exception e)
{
throw new ApplicationException("ExecuteScalarQueryCommand", e);
}
}
}
}
}
}
Premesso che attualmente si è soliti utilizzare un ORM (Object Relational Mapper) per mappare gli oggetti alle tabelle SQL, alcune volte può capitare di avere applicazioni vecchie o con codice legacy e nelle quali viene utilizzato Ado.Net per effettuare le query verso il database.
La parte più onerosa si verifica quando effettuaiamo una query e ci viene restituito il risultato in un DataTable oppure un DataReader: bisogna instanziare una collezione di oggetti e valorizzarne le proprietà (una ad una).
Per ovviare a questo problema possiamo utilizzare una classe di helper; diamo per scontato che i campi del database si chiamano nello stesso modo delle proprietà
Ecco la clase di helper, è sufficiente chiamare il metodo passando a parametro il DataTable e ci verrà restituita una collezione di oggetti
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using MpsManager.Controllers.Helper;
namespace MpsManager.Models
{
public static class MapDataAndBusinessEntityHelper
{
public static List MapDataToBusinessEntityCollection(DataTable dr)
where T : new()
{
Type businessEntityType = typeof(T);
var entitys = new List();
var hashtable = new Hashtable();
PropertyInfo[] properties = businessEntityType.GetProperties();
foreach (PropertyInfo info in properties)
{
hashtable[info.Name.ToUpper()] = info;
}
foreach (DataRow dataRow in dr.Rows)
{
T newObject = new T();
for (int index = 0; index < dr.Columns.Count; index++)
{
var info = (PropertyInfo)hashtable[dr.Columns[index].ColumnName.ToUpper()];
if ((info != null) && info.CanWrite)
{
info.SetValue(newObject, dataRow[info.Name] is DBNull ? null : dataRow[info.Name], null);
}
}
entitys.Add(newObject);
}
return entitys;
}
}
}
Generalmente le tabelle html di default non si presentano belle esteticamente e quindi si è soliti applicare degli stili CSS (Cascading Style Sheets) per renderle gradevoli.
Se non abbiamo molto tempo per effettuare delle prove per capire come potrebbero presentarsi meglio le nostre tabelle, possiamo fare affidamento ai CSS che ci vengono forniti di default con JQuery UI.
Una volta applicate le classi standard, la tabella assumerà un aspetto differente a secondo del tema di cui abbiamo fatto il download.
Ecco un esempio di tabella con le classi assegnate:
Utilizzando la form authentication in asp.net è possibile sfruttare il provider AspNetSqlProfileProvider per gestire gli accessi al sito,creare un nuovo utente, modificare la password e molto altro, senza la necessità di scrivere righe di codice.
Tramite questo provider però è necessario creare un database ad hoc utilizzando il comando Aspnet_regsql nel quale in automatico verranno create le tabelle,store procedure e tutto quanto è indispensabile per fare funzionare correttamente l'autenticazione.
Supponiamo invece di volere utilizzare una tabella utenti presente nel nostro database, creeremo quindi un provider personalizzato secondo le nostre esigenze.
Nel caso specifico abbiamo utilizzato ADO.NET per effettuare le query sul db, questo perchè la nostra esigenza era quella di realizzare un piccolo programma, ovviamente potete decidere di utilizzare un ORM come Entity Framework, NHibernate e così via...
I passi da fare sono:
- Creazione tabella utenti
CREATE TABLE [Users] (
[username] [varchar] (15) NOT NULL ,
[password] [varchar] (25) NOT NULL ,
[userRole] [tinyint] NOT NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED
(
[username]
) ON [PRIMARY]
GO
- Creazione di un provider
Il provider è una classe che deriva da MembershipProvider e nella quale andremo ad implementare i metodi che ci interessano per la validazione
Nell'esempio abbiamo implementato solo il metodo ValidateUser che permette di effettuare il login al sito.
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web.Security;
namespace MpsManager.Controllers.Helper
{
public class CustomSqlValidatorProvider : MembershipProvider
{
public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status)
{
throw new System.NotImplementedException();
}
public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
{
throw new System.NotImplementedException();
}
public override string GetPassword(string username, string answer)
{
throw new System.NotImplementedException();
}
public override bool ChangePassword(string username, string oldPassword, string newPassword)
{
throw new System.NotImplementedException();
}
public override string ResetPassword(string username, string answer)
{
throw new System.NotImplementedException();
}
public override void UpdateUser(MembershipUser user)
{
throw new System.NotImplementedException();
}
public override bool ValidateUser(string username, string password)
{
IList list = new List();
list.Add(new SqlParameter("UserName",username));
list.Add(new SqlParameter("Password", password));
var status = SQLADOHelper.ExecuteScalarQueryCommand("Select 1 from Users where userName=@UserName and password=@password",list);
return status != null;
}
public override bool UnlockUser(string userName)
{
throw new System.NotImplementedException();
}
public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
{
throw new System.NotImplementedException();
}
public override MembershipUser GetUser(string username, bool userIsOnline)
{
throw new System.NotImplementedException();
}
public override string GetUserNameByEmail(string email)
{
throw new System.NotImplementedException();
}
public override bool DeleteUser(string username, bool deleteAllRelatedData)
{
throw new System.NotImplementedException();
}
public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
{
throw new System.NotImplementedException();
}
public override int GetNumberOfUsersOnline()
{
throw new System.NotImplementedException();
}
public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
{
throw new System.NotImplementedException();
}
public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
{
throw new System.NotImplementedException();
}
public override bool EnablePasswordRetrieval
{
get { throw new System.NotImplementedException(); }
}
public override bool EnablePasswordReset
{
get { throw new System.NotImplementedException(); }
}
public override bool RequiresQuestionAndAnswer
{
get { throw new System.NotImplementedException(); }
}
public override string ApplicationName
{
get { throw new System.NotImplementedException(); }
set { throw new System.NotImplementedException(); }
}
public override int MaxInvalidPasswordAttempts
{
get { throw new System.NotImplementedException(); }
}
public override int PasswordAttemptWindow
{
get { throw new System.NotImplementedException(); }
}
public override bool RequiresUniqueEmail
{
get { throw new System.NotImplementedException(); }
}
public override MembershipPasswordFormat PasswordFormat
{
get { throw new System.NotImplementedException(); }
}
public override int MinRequiredPasswordLength
{
get { throw new System.NotImplementedException(); }
}
public override int MinRequiredNonAlphanumericCharacters
{
get { throw new System.NotImplementedException(); }
}
public override string PasswordStrengthRegularExpression
{
get { throw new System.NotImplementedException(); }
}
}
}
- modifichiamo il file web.config per aggiungere il nostro provider e attivare l'autenticazione specificando quale sarà la pagina che si occuperà del login.
- Creiamo la view con i nostri campi di accesso (se utilizzate asp.net MVC la View ed il controller conterranno il codice che ci serve)
View
Accesso al sistema
<% using (Html.BeginForm()) { %>
<%: Html.ValidationSummary(true, "Login was unsuccessful. Please correct the errors and try again.") %>
<% } %>
Controller
[HttpPost]
public ActionResult LogOn(LogOnModel model, string returnUrl)
{
if (ModelState.IsValid)
{
if (Membership.ValidateUser(model.UserName, model.Password))
{
FormsAuthentication.SetAuthCookie(model.UserName, false);
if (Url.IsLocalUrl(returnUrl) && returnUrl.Length > 1 && returnUrl.StartsWith("/")
&& !returnUrl.StartsWith("//") && !returnUrl.StartsWith("/\\"))
{
return Redirect(returnUrl);
}
else
{
return RedirectToAction("Index", "Home");
}
}
else
{
ModelState.AddModelError("", "The user name or password provided is incorrect.");
}
}
// If we got this far, something failed, redisplay form
return View(model);
}
