22/07/11

Convertire un oggetto in istruzione SQL di Insert/update

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);
                    }
                }
            }
        }
    }
}

Convertire un DataTable in una collezione di oggetti tipizzata

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;


        }

    }

}

20/07/11

Applicare degli stili alle tabelle HTML con Jquery UI

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:

Pannello di controllo







Ed ecco il risultato:

Ora proviamo a cambiare il tema Jquery UI e senza modificare nulla avremo:

19/07/11

Form Authentication - utilizzare un custom provider sql

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.") %>
Informazioni account
<%: Html.LabelFor(m => m.UserName) %>
<%: Html.TextBoxFor(m => m.UserName) %> <%: Html.ValidationMessageFor(m => m.UserName) %>
<%: Html.LabelFor(m => m.Password) %>
<%: Html.PasswordFor(m => m.Password) %> <%: Html.ValidationMessageFor(m => m.Password) %>

<% } %>

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);
        }

08/07/11

Aruba ancora down




Dopo il fermo della server farm Aruba causato da principio di incendio ad Aprile, anche oggi si è verificato un'altro down.
Dalle indiscrezione sembrerebbe un problema causato dai nuovi UPS...

Vi consigliamo di tenere monitorato twitter per avere nuove notizie...

04/07/11

Come Eliminare SP,View e Function dinamicamente

Delle volte potrebbe essere utile eliminare tutte le Stored procedure, Viste e funzioni contenute nel database.
Una delle prime soluzioni a cui si potrebbe arrivare è ciclare sulle liste di oggetti restituite dalle varie information_schema. Ma SQL è concepito per pensare "set based" e quindi potremmo sfruttare questa sua caratteristica per riuscire a formattare con un'unica query le istruzioni di drop.
Con lo script seguente otteniamo esattamente ciò che ci siamo prefissati, un'unica query che mi restituisca tutte le istruzioni di drop necessarie.
Le istruzioni saranno poi eseguite grazie alla EXEC.



DECLARE @strSql VARCHAR(MAX) = ''
SET @strSql = (
SELECT 'DROP ' + OBJTYPE + ' [' + OBJSCHEMA + '].[' + OBJNAME + ']; ' FROM
(
SELECT ROUTINE_TYPE OBJTYPE,ROUTINE_SCHEMA OBJSCHEMA,
ROUTINE_NAME OBJNAME
FROM INFORMATION_SCHEMA.ROUTINES
UNION
SELECT TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
) OBJ
FOR XML PATH ('')
)
EXEC(@strSql)



Vi consiglio di approfondire la "FOR XML PATH('')" che potrebbe tornare molto utile....

Ciao

Luca

Come eseguire il Log delle modifiche alla struttura di un DB....

Molto spesso potrebbe tornare particolarmente utile sapere esattamente quali modifiche sono state effettuate alla struttura di un Database...
Dalla versione SQL Server 2005, grazie ai trigger di database, è possibile ottenerlo senza sforzo... (finalmente direi...)

In un database specifico per i log delle attività creiamo una tabella di log:

create table [dbo].[Log]
(
data datetime,
utente varchar(128),
comando varchar(max),
objname varchar(128),
dbname varchar(128)
)


Nel/Nei Database che desideriamo monitorare creiamo il seguente Trigger :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [Log_Modifiche_Struttura] ON DATABASE FOR
CREATE_TABLE,
ALTER_TABLE,
DROP_TABLE,
CREATE_VIEW,
ALTER_VIEW,
DROP_VIEW,
CREATE_PROCEDURE,
ALTER_PROCEDURE,
DROP_PROCEDURE,
CREATE_FUNCTION,
ALTER_FUNCTION,
DROP_FUNCTION,
CREATE_INDEX,
ALTER_INDEX,
DROP_INDEX,
CREATE_ASSEMBLY,
ALTER_ASSEMBLY,
DROP_ASSEMBLY,
CREATE_TRIGGER,
ALTER_TRIGGER,
DROP_TRIGGER,
CREATE_SCHEMA,
ALTER_SCHEMA,
DROP_SCHEMA
AS

DECLARE @command AS VARCHAR(MAX)--Comando eseguito
DECLARE @login AS VARCHAR(128)--Utente che ha eseguito il comando
DECLARE @db AS VARCHAR(128)--DB su cui ho effettuato l'operazione
DECLARE @objName AS VARCHAR(128)--Oggetto interessato

SELECT @login = SUSER_SNAME()--recupero il nome utente
SELECT @command = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')--recupero il comando di modifica struttura
SELECT @objName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')--Recupero il nome dell'oggetto modificato/creato/eliminato
SELECT @db = DB_NAME()--recupero il nome del database

--Inserisco la modifica nello storico
INSERT INTO NomeDbLog.dbo.[Log]
values
(
getdate(),
@login,
@command,
@objName,
@db
)

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [Log_Modifiche_Struttura] ON DATABASE
GO
ENABLE TRIGGER [Log_Modifiche_Struttura] ON DATABASE
GO


Ogni operazione di Create, Alter, Drop su tabelle, stored procedure, viste, function, schema, index ed assembly verrà quindi tracciata nella nostra tabella di Log.

Proviamo ora se tutto funziona.

Creo la seguente tabella nel database monitorato :

create table dbo.tabellaprova
(
campo int
)


Effettuo una select sul database di Log :








Troviamo la tabella appena creata.

Siamo quindi in grado di sapere esattamente chi ha fatto cosa e quando sul nostro DB...

Notare l'utilizzo della funzione EventData, che restituisce al chiamante le informazioni in formato Xml. Per ottenere l'informazione specifica del comando T-SQL utilizzo la X-query :

Select @command = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

Spero vi sia utile.

Ciao

Alla prossima

 
Design by Free WordPress Themes | Bloggerized by Lasantha - Premium Blogger Themes | Grants For Single Moms