Update da una Select

Se ci fosse bisogno di fare un update su diversi campi copiandoli da un'altra tabella, potete usare questo script:

update
 A_Categorie
set
 A_Categorie.Descrizione_DE = B_Categorie.Descrizione_DE,
 A_Categorie.descrizione_singolare_DE = B_Categorie.descrizione_singolare_DE
from
 A_Categorie
inner join
 B_Categorie
on
 A_Categorie.idCategoria = B_Categorie.idCategoria

Nell'esempio, copio i campi "Descrizione_DE" (ecc..) dalla tabella "B_Categorie" alla tabella "A_Categorie".

Concatenare campi di diversi records (senza cursore)

Se avete la necessita' di concatenare in una stringa dei campi che sono su piu' records, normalmente viene suggerito l'uso dei cursori che eseguono un ciclo sulle righe interessate dalla clausola "where".
Per fortuna esite un modo piu' veloce per ottenere la concatenazione desiderata senza l'uso di cursori, senza stressare la cpu del server e quella del programmatore!

Come test, ho usato il db d'esempio AdventureWorks e la tabella "Production.Product". Voglio quindi concatenare e separare con una virgola, tutti i prodotti il cui colore e' "Black".


Per per poter concaternare i prodotti, ho usato il seguente codice:

SELECT STUFF ((SELECT ', ' + [Name]
FROM Production.Product WHERE Color = 'Black'
FOR XML PATH ('')),1,2,'') AS Black_Products

Usando quindi,

FOR XML PATH

ma anche la funzione (serve per separare con le virgole al meglio)

STUFF

potrete risolvere velocemente e senza cursori, la vostra concatenazione!

Maggiori info sulla funzione STUFF le trovate qui, per quanto riguarda le istruzioni FOR XML PATH, potete guardare qui.

Buon lavoro!

Automatically create all Store Procedures

I have often to write many store procedures in SQL Server!
So i decided to speed up my work, writing just 3 Store Procedures that automatically create all "Insert", "Update", "Delete" Store Procedures, of all tables, in the selected DB.

declare crs_tab cursor for
select [name] from sys.tables
where [name] not like 'sys%'

open crs_tab

As you can see, I used the "cursor" and "information_schema.columns" to get infos about all tables (and view) from the DB.

declare crs_structure cursor for
select c.[name], data_type ,ISC.character_maximum_length,ISC.character_maximum_length
from sys.columns C
join sys.tables T on t.[object_id]=c.[object_id]
join information_schema.columns ISC
on ISC.table_name=t.[name] and Column_name=c.[name]
ISC.table_name=t.[name] and Column_name=c.[name]

Download the 3 txt and create the 3 store procedures from these files.
Run the store procedures!




Then you will have all Insert, Update and Delete Store Procedures created from all Tables of your DB!

Good work and enjoy!

p.s.
These Store Procedures, are only a starting point... so sometimes, if your tables are not "standard", you have to create them by hand or customize the 3 txt :)

Sql Server 2008 R2

Se si usa Sql Server 2008 R2, e si prova a modificare una tabella (progetta), si scoprira' che Management Studio impedisce il salvataggio delle modifiche.


Sembra che questo sia il comportamento di default...
Per poter modificare le tabelle o altri oggetti basta andare in "Strumenti" e poi "Opzioni" e quindi in "Designers" e levare questa fastidiosa opzione!

Custom Filter

There are many ways to filter data in a grid, here one of them...

All you need is to create a DataSet connected with a table and a little code!
(in the example I use "AdventureWorks" as database and "Person.CountryRegion" as table)

Let's start!

As usual, we have to write an object to connect a GridView with a source like a dataset/database.

Public Function GetCountryRegion(Optional ByVal CountryRegionCode As String = "%", _
                                   Optional ByVal Name As String = "%") As ds_CountryRegion.CountryRegionDataTable

      Dim cnn As SqlConnection = Nothing
      Dim cmd As SqlCommand = Nothing
      Dim da As SqlDataAdapter = Nothing
      Dim sqlStatement As String

      Try
          sqlStatement = "SELECT * FROM Person.CountryRegion where CountryRegionCode like '" & CountryRegionCode & "%' and Name like '" & Name & "%'"

          cnn = New SqlConnection(Me._connectionString)
          cmd = New SqlCommand(sqlStatement, cnn)
          da = New SqlDataAdapter(cmd)

          cnn.Open()

          _ds.CountryRegion.Rows.Clear()
          da.Fill(_ds, "CountryRegion")
      Catch ex As Exception
          'todo: code for exceptions!
      Finally
          GetCountryRegion = _ds.CountryRegion

          If Not (cnn Is Nothing) Then
              cnn.Close()
              cnn.Dispose()
              cnn = Nothing
          End If
          If Not (da Is Nothing) Then
              da.Dispose()
              da = Nothing
          End If
          If Not (cmd Is Nothing) Then
              cmd.Dispose()
              cmd = Nothing
          End If
      End Try

  End Function

As you can see, I use the keyword "Optional" for the parameters and I use "%" as default value.
In T-Sql language "%" stands for "all". If there are no parameters passed, the code reads "all" CountryRegionCode records and "all" Name records.

Another interesting thing is the use of the keyword "like" and the use of "%" at the end of the parameters in the sqlStatement string ("like 'Name%'" and "like 'CountryRegionCode%'").

In this example I can catch all records with the Name that starts with the parameter passed: all records with "Name%" starts with "I%" (Italy, Ireland, Iceland...) (ok I know, it's not easy for me to explain in eng... I'm sorry!)

sqlStatement = "SELECT * FROM Person.CountryRegion where CountryRegionCode like '" & CountryRegionCode & "%' and Name like '" & Name & "%'"

When our object is ready and connected with a GridView through the "DataSource" property, we have to use the "Databind" property to associate the data with the GridView.

In the Page_Load event, there are no parameters passed to the DataTable object (in fact the two TextBox values are empty ---> "")

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

      If Not IsPostBack Then
          GetGwCountryRegion()
      End If

  End Sub

But each time that we write something in the TextBox Name or CountryRegionCode and click on "Filter", the DataTable recives parameters and use them for filtering.

That's all!

You can download this my little example here.
You can download and install AdventureWorksDB.msi database here.

Please remember to change in the web.config file, the two values "Data Source=pc01;" with
your sqlserver instance name. (probably "computername" with Sql2005/2008, but if you are using SqlExpress edition your instance name is "computername\SQLExpress")

Enjoy!