Home > Tips & Tricks > Generare codice SQL senza eseguirlo.

Generare codice SQL senza eseguirlo.

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()
)

 

Chi è Davide Mauri

Microsoft Data Platform MVP dal 2007, Davide Mauri si occupa di Data Architecture e Big Data nel mondo dell'IoT. Attualmente ricopre il ruolo di "Director Software Development & Cloud Infrastructure" in Sensoria, societa specializzata nella creazione di Wearables e sensori per l'IoT applicati a tessuti ed oggetti sportivi.

Leggi Anche

String or Binary data would be truncated: replacing the infamous error 8152 … also in SQL Server 2017

Come già annunciato allo scorso Ignite, una delle nuove (tra le tante) feature di SQL …