Generare codice SQL senza eseguirlo.

Davide Mauri 24 Gennaio 2008 Tips & Tricks 1,651 Views

Vediamo come creare codice SQL al volo (quindi senza apportare alcuna modifica "reale" alla base dati) utilizzando le potenzialità di generazione degli script fornita da SMO:

Non dimentichiamoci degli imports …

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Ci colleghiamo al server di nostro interesse …

Dim serconn As New ServerConnection("Istanza", "userName", "Password")
serconn.LoginSecure = False ‘ Usando le credenziali di SQL Server …

Creiamo l’oggetto server …

Dim _server As New Server(serconn)

Il trucco è tutto qui ! L’oggetto Server espone a sua volta un oggetto ConnectionContext tra le cui proprietà
ne troviamo una chiamata SqlExecutionModes. Come il nome stesso suggerisce, questa proprietà imposta la modalità
di esecuzione dei comandi dell’oggetto Server collegato.

SqlExecutionModes accetta un’enumerazione che prevede tre valori :

CaptureSql : Cattura (o generare) lo script, ma non lo esegue fisicamente;

ExecuteAndCaptureSql : Cattura lo script e lo esegue fisicamente;

ExecuteSql : Si limita solo ad eseguire lo script, ma non lo cattura.

Nel nostro caso dobbiamo lavorare senza che quello che facciamo venga applicato anche alla base dati, quindi dobbiamo
informare l’oggetto server che NON deve eseguire i comandi, ma solo catturarne gli script :

_server.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql

… ora possiamo lavorare tranquilli !

Puntiamo al database di nostro interesse (se non c’è, uno qualsiasi … basta che sia un db esistente …)

Dim db As Database = _server.Databases("DB_Name")

E creiamo una nuova tabella chiamata Impiegati …

Dim _nuovaTabella As New Table(db, "Impiegati")

Ora aggiungiamo delle colonne alla tabella …

Creiamo la prima colonna che è un id. Per semplicità non la impostiamo come identity e neanche come chiave primaria, ma decidiamo che
deve essere obbligatoria …

Dim _colonna As New Column(_nuovaTabella, "ID", DataType.Int)
_colonna.Nullable = False ‘ Ora è obbligatoria …
_nuovaTabella.Columns.Add(_colonna) ‘ La aggiungiamo alla tabella …

Ora creiamo una colonna che indica il codice del comunqe di nascita. Se non indicato, assume il default 0.
Aggiungiamo un costraint per il valore di default … il nome viene generato in automatico da SQL Server …

_colonna = New Column(_nuovaTabella, "IDComuneDiNascita", DataType.Int)
_colonna.AddDefaultConstraint().Text = "’0’"

… se vogliamo indicare anche il nome del costraint, dobbiamo usare questa sintassi … ma comunque la vedremo a breve …
‘ _nuovaColonna.AddDefaultConstraint("Nome_Constraint").Text = "’Valore di default’"

_nuovaTabella.Columns.Add(_colonna) ‘ Aggiungiamo la colonna alla tabella …

… e ora creiamo la terza colonna, la data di nascita …

_colonna = New Column(_nuovaTabella, "DataDiNascita", DataType.DateTime)

Ne impostiamo il valore di default
Nota. Ok, non ha molto senso impostare come default di una data di nascita la data corrente, ma è solo un esempio, dai !!!!

_colonna.AddDefaultConstraint("Test1_DataObbligatoria_CosDef").Text = "GetDate()"
_nuovaTabella.Columns.Add(_colonna)

Infine aggiungiamo il nome e il cognome che però vogliamo inserire prima della data di nascita …

_colonna = New Column(_nuovaTabella, "Nome", DataType.VarChar(50))
_nuovaTabella.Columns.Add(_colonna, 2) ‘ La inseriamo prima della data di nascita …
_colonna = New Column(_nuovaTabella, "Cognome", DataType.VarChar(50))
_nuovaTabella.Columns.Add(_colonna, 3) ‘ … e anche questa …

Ora creiamo la tabella. Dato che però abbiamo impostato _server.ConnectionContext.SqlExecutionModes
su SqlExecutionModes.CaptureSql, la tabella NON verrà creata fisicamente nel database …

_nuovaTabella.Create()

A questo punto, non ci resta che generare lo script usando sempre l’oggetto ConnectionContext …

Dim _scriptGenerato As Specialized.StringCollection = _server.ConnectionContext.CapturedSql.Text

… e leggerne il risultato …

For Each st As String In _scriptGenerato
TextBox1.Text &= st & vbCrLf
Next

Ed ecco qui il risultato :

USE [DB_Name] CREATE TABLE [dbo].[Impiegati](
    [ID] [int] NOT NULL,
    [IDComuneDiNascita] [int] CONSTRAINT [DF_Impiegati_IDComuneDiNascita] DEFAULT ‘0’,
    [Nome] [varchar](50),
    [Cognome] [varchar](50),
    [DataDiNascita] [datetime] CONSTRAINT [Test1_DataObbligatoria_CosDef] DEFAULT GetDate()
)

UGISS User Group Italiano SQL Server

Generare codice SQL senza eseguirlo.

Chi è Davide Mauri

Leggi Anche

Modalità di elaborazione query e indici columnstore