Asp.Net View

Concatenare campi di diversi records (senza cursore)

2011-05-24T20:39:00.014+02:00

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

2011-04-03T19:51:00.015+02:00

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

Blogger!

2011-02-04T13:46:00.001+01:00

Google ha appena implementato Blogger per Android, versione ufficiale.
Era ora!!! (post scritto da Android ovviamente!)

Sql Server 2008 R2

2010-08-03T07:50:00.005+02:00

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!

Esame Microsoft 70-528

2010-06-27T11:22:00.006+02:00

Esame Microsoft 70-528

2010-03-07T15:45:00.009+01:00

Di ritorno da Mosca, mi ritrovo un pacco appena arrivato dall' Inghilterra con il manuale per l' esame Microsoft .NET Framework 2.0 - Web-based Client Development.
Questi gli argomenti principali dell' esame:
creating and programming a Web application;
integrating data in a Web application by using ADO.NET, XML, and data-bound controls;
creating custom Web controls;
tracing, configuring, and deploying applications;
customizing and personalizing a Web application;
implementing authentication and authorization;
creating ASP.NET mobile Web applications;

Come per il precedente esame, mi ci vorranno forse due mesi per studiarmelo...
Spero di riuscire nell' impresa !!!

Esame Microsoft 70-536 - Passato!!!

2010-02-25T19:04:00.015+01:00

Oggi ho finalmente fatto l'esame Microsoft .NET Framework 2.0 - Application Development Foundation (codice esame 70-536)
Passato!!! :)
La mia preparazione e' durata due mesi, lavoro e altri impegni non hanno reso possibile un tempo inferiore...
L'esame si e' svolto in questo modo:
mi sono ovviamente recato in un centro Microsoft predisposto per gli esami;
un computer con un'applicazione appositamente configurata per l'esame in questione;
collegamento con gli USA per controllare il corretto svolgimento dell'esame;
40 domande a risposta multipla (da 4 a 6 risposte possibili);
domande in inglese;
poco piu' di 2 ore di tempo.

Devo dire che una delle maggiori difficolta' e' stata quella di studiare su un manuale in inglese...
Inoltre certi argomenti nel libro e nell'esame, erano su delle Classi del Framework 2.0 con cui non ho quasi mai lavorato, quindi non semplici da ricordare.

Per qualche giorno mi riposero', poi ricomincero' a studiare per l'esame: Microsoft .NET Framework 2.0 - Web-based Client Development (codice esame 70-528)

Se passero' pure questo, otterro' la certificazione: MCTS .NET Framework 2.0 Web Applications !!!

Esame Microsoft 70-536

2009-12-23T09:20:00.015+01:00

Inizia in questi giorni di festivita' e dopo un breve (purtroppo) periodo di riposo, la mia preparazione all'esame "70-536 Microsoft .NET Framework - Application Development Foundation" !!!
Questa la descrizione ufficiale dell' esame:

"Exam 70-536 is designed to measure your knowledge of .NET development fundamentals and is not tied to a particular version of .NET. Since the exam is now applicable to both Microsoft .NET Framework 2.0 and Microsoft .NET Framework 3.5 certification tracks, we have changed the name of the exam. Formerly TS: Microsoft .NET Framework 2.0 – Application Development Foundation, Exam 70-536 is now called TS: Microsoft .NET Framework, Application Development Foundation. If you are a .NET 2.0 developer, you do not need to learn .NET 3.5 to pass Exam 70-536; conversely, if you are a .NET 3.5 developer, you do not need to review .NET 2.0 to pass the exam."

Questo e' il primo passo per ottenere la certificazione "MCTS: .NET Framework 2.0 Web Applications" MCTS
Il manuale su cui studio e' questo, circa ottocento pagine in inglese e pagato poco piu' di 30€.
Se voi lo avete gia' superato ogni consiglio sara' molto gradito :)

Twitter Updates: 19/09/2009

2009-09-19T11:00:00.004+02:00

Web

Opera Mini 5 http://tinyurl.com/mtfh6m
FileTwT : File Sharing On Twitter http://bit.ly/ZCHbC
Google Timeline http://bit.ly/12WKdJ
10 code snippets to interact with Twitter http://bit.ly/3t1O53
Low-Tech Ways To Twitter From A Cell Phone http://bit.ly/UDSZl
Gmail just got pretty. Introducing Helvetimail http://bit.ly/8dea7
lessthanabenjamin.com http://bit.ly/KATWm
Why you should upgrade your WordPress blog http://bit.ly/gwK9a
Security Threat: WordPress Under Attack http://bit.ly/LiUdA
Fantastic Google Maps mashup maps your musical memories http://bit.ly/lpbcd
7 Steps To Help You Build Your Personal Brand On LinkedIn http://bit.ly/w2Y3X

Webdesign

How to Make a Light and Sleek Web Layout in Photoshop http://bit.ly/19Obq2
65 New Examples Of Beautiful Single Page http://bit.ly/1dPceH
70 Fresh and Inspirational Blog Designs http://bit.ly/MuZgJ
Best Collection Of Free CSS Templates http://bit.ly/hBrwv
CSS cursor property http://bit.ly/3ewxaL

dotNet

Multi-threading in .NET http://bit.ly/13tJKu
Debugging Task-Based Parallel Applications in Visual Studio 2010 http://bit.ly/qFDrv
ASP.NET GridView with Custom Paging http://bit.ly/vhRXQ
Microsoft Dev-Labs Introduces Doloto for AJAX http://bit.ly/7chuK
Efficient Search Page Using QueryExtender control in ASP.Net 4.0 http://bit.ly/DEXaM
Custom Paging in GridView Using LINQ http://bit.ly/ERhp2
How to Launch External Applications from Visual Studio 2005/2008 http://bit.ly/1tseMP
Hosting ASP.NET sites on IIS 7.0 http://bit.ly/KSEjI
Properties vs Fields – Why Does it Matter? http://bit.ly/4itHj8

Twitter Updates: 04/09/2009

2009-09-04T18:10:00.013+02:00

Design

Weekly Web Design Inspiration http://bit.ly/OstwD
Sporty Bike http://bit.ly/TL0hp
Keep On Switchitallingaling! http://bit.ly/2nD155
Reinvention Of The Wheel? http://bit.ly/1njSes

dotNet

Auto Translate and Synchronize resources in your .NET applications http://bit.ly/4frjpH
Creating Music Video Box Using Windows Presentation Foundation(WPF) http://bit.ly/1y9TG

Webdesign

Weekly Web Design Inspiration http://bit.ly/OstwD
45 Excellent New Icon Packs For Desktop and Web http://bit.ly/2pdVsU
Create a Web Layout with 3D Elements using Photoshop http://bit.ly/mN02H
50 Websites (and More) for Your Design Inspiration http://bit.ly/Ya7gC
25 Examples of Web 2.0 and Traditional Design Rules Coming Together http://bit.ly/pXnEx
Get to Know Why CSS is Good for Google http://bit.ly/1VJzyM
How to Design and Code a Flexible Website http://bit.ly/3F4RK5
Design a creative design studio layout http://bit.ly/8rr4T

Sql

SQL Server 2008: Ole Automation http://bit.ly/1KFpCH
Videos Updated Show How to Scale SQL Server http://bit.ly/eNFlG
Ireland SQL User Group September 23rd http://bit.ly/2lxYVQ
Consume a Web Service in SQL Server Integration Services http://bit.ly/tSVxt
Speed up your Web App by 1000% with 1 Line of SQL http://bit.ly/I4bLI

Recursive function in Asp.Net

2009-04-25T20:48:00.006+02:00

An important programming technique is the Recursion.
Recursive is a procedure or subroutines that contains a statement that calls itself.

In this simple example I use a recursive function to clean all TextBox even those within a container (ascx control)


Private Function CleanAll(ByVal container As Control) As ArrayList

     Dim txt As TextBox
     For Each c As Control In container.Controls
         If c.GetType.Name = "TextBox" Then
             txt = c
             If txt.Text <> "" Then
                 txt.Text = ""
                 allControlls.Add(c)
             End If
         Else
             CleanAll(c) ' invokes recursively the method
             'to add more controls in the control children
         End If
     Next
     Return allControlls

 End Function

There are two ascx controls nested one inside the other.
The same thing works with panels and other asp.net containers.
If you want, you can download this example here (visual basic) or here (c#).

For more info try here and here.

That's all !

Add Item to DropDownList

2009-04-01T11:14:00.010+02:00

Hi!

In this post I'll explain how to add a custom-item in a DropDownList using ListItem object.

As written in MSDN:

"ListItem represents a data item in a data-bound list control"

"A ListItem control represents an individual data item within a data-bound list control"

First of all, we have to connect a Database Table with a DropDownList. (you can see how populate a DropDownList here)


Protected Sub GetContact()

   Dim obj As cl_Person = Nothing
   obj = New cl_Person(System.Configuration.ConfigurationManager.AppSettings("APPConnectionString"))

   Me.ddlPerson.DataSource = obj.GetContact()
   Me.ddlPerson.DataValueField = "ContactID"
   Me.ddlPerson.DataTextField = "FullName"
   Me.ddlPerson.DataBind()

End Sub

Then in the DataBound event of the DropDownList, write this:


Protected Sub ddlPerson_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlPerson.DataBound

   Dim myItem As New ListItem
   myItem.Text = "Choose One !!!"
   myItem.Value = 0
   myItem.Selected = True
   Me.ddlPerson.Items.Add(myItem)

End Sub

As you can see, "Choose One !!!" rapresents our custom-item.

If you want, you can download this 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")

That's all !

Hotfix for "Design view does not update HTML"

2009-03-31T09:48:00.010+02:00

Hello everybody!
In a previous post, I have described an update-problem with design-view and souce-view in Visual Studio 2008.

Recently Microsoft has released some hotfix that you can download here.

Enjoy!

Custom Filter

2009-03-18T10:39:00.020+01:00

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!

IE7 vs ThisBlog (fixed)

2009-03-13T12:40:00.006+01:00

Explorer 7(fixed):

... I have upgraded the css! :)

IE7 vs ThisBlog

2009-03-13T11:31:00.012+01:00

Take a look...

Firefox:

Safari:

Explorer 7:

... ok, I'll try to fix it or maybe not! :(

GridLines style in Gridview

2009-02-19T10:05:00.011+01:00

A fast way to change the style of the gridlines in a gridview:


Protected Sub gwMyGw_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles gwMyGw.PreRender

     'PreRender event!
     Dim tblStyle As New TableItemStyle()
     tblStyle.BorderColor = Drawing.Color.White
     tblStyle.BorderWidth = "2"

     Dim row As TableRow
     For Each row In Me.gwMyGw.Rows
         Dim cel As TableCell
         For Each cel In row.Cells
             cel.ApplyStyle(tblStyle)
         Next
     Next

 End Sub

Enjoy !

Another bug in Visual Studio 2008 ?

2008-11-29T10:48:00.010+01:00

I'm using VS 2008 with SP1...
For many, VS2008 is the best IDE for a programmers, but sometimes...

I was working with a GridView (previous post) and I had to change the name of a LinkButton from "Delete" to "Eliminate" a simple operation you could say... I also thought!
Unfortunately is not maintained the new "Text" property!
This problem does not exist with normal "Button", the property "Text" seems to work with this control and with the same conditions...

To perform the change, we need to go to the html source...

How to... use an Ajax ConfirmExtender in a GridView

2008-11-28T22:10:00.014+01:00

A typical case to use an ajax-confirmbutton within a gridview is to delete a row on DB, let's take a look...

So, in our gridview, we have to create an "ItemTemplate", then add a LinkButton and the ajax ConfirmButtonExtender.
Set the property of our Extender like "TargetContolId" (= the LinkButton ID) and the "Confirm Text" (= Are You sure, continue?).
In this way we have connected the extender with the LinkButton !
Now we must connect the LinkButton with the data from the source (DataTable-method or a Control DataSource) in particular with an unic "row-ID"
Then go to Click-Event of the LinkButton to add the code to delete the row on DB...
something like:


Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
       'delete after Ajax-ConfirmButtonExtender
       Dim lb As LinkButton = sender
       Dim objDel As New clSessionTest(("ConnectionString"))
       objDel.EliTempTest(lb.CommandArgument)
       objDel = Nothing
End Sub

How to… populate a dropdownlist

2008-11-03T22:15:00.058+01:00

1. creare una vista sulla tabella da cui estrarre i dati (dal db AdventureWorks);
2. creare un dataset sulla vista;

3. creare un metodo in una classe per l'estrazione dei dati;

4. utilizzare il metodo e collegarlo alla DropDownList.

1.
Tramite Management Studio, aprire il db AdventureWorks. Visualizzare l'elenco delle "Viste" quindi crearne una di nuova sulla tabella Contact (Person).
I campi da fleggare sono: "ContactID, FirstName, LastName, Title".
Affinche' il risultato finale sia del tipo: "Mr. Gustavo Achong" suggerisco di editare il codice sql in questo modo:


SELECT ContactID, FirstName, LastName, Title, Title + N' ' + FirstName + N' ' + LastName AS [Full]
FROM Person.Contact

Salvare la Vista.

2.
Creare un DataSet e con la procedura guidata aggiungere un TableAdapter e collegare la vista appena creata con tutti i campi della vista (vw_get_persons).

Qui la procedura visualizza la vista...

3.
Creare un metodo in una classe:


Public Function GetDllPersons() As DataSet1.VW_GET_PERSONSDataTable

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

        Try
            sqlStatement = "SELECT * FROM VW_GET_PERSONS"
            cnn = New SqlConnection(Me._connectionString)
            cmd = New SqlCommand(sqlStatement, cnn)
            da = New SqlDataAdapter(cmd)

            cnn.Open()

            _ds.VW_GET_PERSONS.Rows.Clear()
            da.Fill(_ds, "VW_GET_PERSONS")
        Catch ex As Exception
            'todo: code for errors!
        Finally
            GetDllPersons = _ds.VW_GET_PERSONS
            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

4.
Nella pagina .aspx entrare nell'evento Page_Load e aggiungere questo codice:


 If Not IsPostBack Then 'essenziale per evitare di caricare sempre gli stessi valori
   Dim objGetdllPersons As Class1 = Nothing
   objGetdllPersons = New Class1("Data Source=wolfdale;Initial Catalog=AdventureWorks; Integrated Security=True")
   Me.ddl_Persons.DataSource = objGetdllPersons.GetDllPersons()
   Me.ddl_Persons.DataTextField = "Full"
   Me.ddl_Persons.DataValueField = "ContactID"
   Me.ddl_Persons.DataBind()
 End If

Quindi eseguire.

Hello World

2008-11-02T09:29:00.002+01:00


Dim Greeting As String = "Hello World"