ADO.NET - Aide mémoire - 1/4 : Execution, Parametres, StoredProc, DataReader

Parce que rien ne vaut quelques bons articles didactiques, voici une série de références qui peuvent se montrer bien utiles un jour ou l'autre.

Le présent article fait parti d'une suite logique de 4 articles: "ADO.NET - Aide mémoire - 1/4 : Execution, Parametres, StoredProc, DataReader",  "ADO.NET - Aide mémoire - 2/4 : DataSet", "ADO.NET - Aide mémoire - 2/4 : DataView" et "ADO.NET - Aide mémoire - 4/4 : Tutoriel"

Mettre rapidement le pied à l'étrier

Voici une série d'articles récapitulatifs en provenance de DeveloperFusion.
Ils reprennent toute la chaine d'objets Ado.Net pour SqlServeur et Access avec les options principales ainsi que des exemples en C# et VB.NET. Un excellent condensé pour aborder les DataReader, les appels aux Stored Procedure, les DataSets, Update/Delete/Insert, etc.

• Using ADO.NET with SQL Server and Access
• Executing a SQL query and SqlDataReader 
• Stored Procedures and other queries with parameter 
• Using the DataSet 
• Doing without CommandBuilder and Wrapping Up
• Tout ces articles dans un seul document PDF.

Quelques articles et sites de références

• ConnectionStrings.com site incroyable reprenant des centaines de configurations de connections strings pour les différents moteurs DB SqlServeur, Oracle, pervasive, MySql, etc. Un incontournable!
• Retrieving data using a C# .Net DataReader
  Le principal intérêt de cet article réside dans la récupération d'un dataset depuis une stored procedure avec un DataReader et remplir une ListView avec les données (réclame de passer par une structure intermédiaire).
• Configuration Settings File for providing application configuration data
  Toujours utile, ce petit article explique comment configurer des paramètre dans le fichier app.config (idéal pour stocker une ConnectionString). 
• Juste une note pour mentionner qu'il est possible de créer d'une connexion Sql depuis le fichier de config (en utilisant les Entreprise Application Blocks ).
  Il faudra faire un peu de recherche pour compléter ce point.

Data Access Application Block

Par le Data Access Application Block (DAAB pour les intimes), Microsoft propose une couche logicielle pour l'accès aux données de façon générique et se défaire du fournisseur d'accès aux bases de données. Ainsi il est supposé un gain de temps et de code pour le programmeur puisqu'il y a moins de lignes à coder.
Mais dans la réalité, ce n'est pas si simple. La généricité concerne les requêtes de base et les différences entre les fournisseurs de bases de données apparaissent rapidement. Du coup, le code se rallonge puisqu'il faut créer des instructions différentes selon le provider. (source: cet article sur developper.com)

Cet article n'aborde pas la section "Data Access Application Block" de Microsoft patterns & practice entreprise library. C'est une solution vraiment puissante mais je ne dispose pas d'assez de temps pour m'y attarder.
En attendant, vous pouvez vous renseigner ici:

• Présentation du Data Access Application Block de l'Enterprise Library
• The Enterprise Library Contrib Project.
  Hébergé sur CodePlex, ce projet vise à étendre les fonctionnalités de l'entreprise library.
  C'est ainsi que l'on retrouve des providers pour MySql, SqlLite, PostgreSql, etc.
  

Exemples Ado.Net

Voici quelques exemples (Snippet Compiler) de code rudimentaire mais démontrant néanmoins différents usages Ado.Net.

Ce premier exemple s'attarde sur:

• ExecuteNonQuery - pour des opérations tels que Insert, Delete, modification de schema, etc.
• ExecuteScalar - pour récupérer une seule valeur de retour (première ligne, première colonne).
• ExecuteReader - pour obtenir un reader permettant de passer l'ensemble des données en revue.
• Query paramétrisé
• Stored Procedure paramétrisée et avec valeur de retour.

Source file: AdoNet_Samples.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

public class MyClass
{
        public static void RunSnippet()
    {
        // Call to the various demo functions
        ExecuteNonQuery();
        ExecuteScalar();
        ExecuteReader();
        ParametrizedQuery();
        CallStoredProc(); 
    }
    
    /// <summary>
    /// Get a Sql Connection to Master Database.
    /// Always close the connection as soon as possible.
    /// </summary>
    /// <returns></returns>
    public static SqlConnection GetMasterConnection() {
        // using intégrated security to not revealing my SA password :-)
        string ConnString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;";
        return new SqlConnection( ConnString );
    }
    
    public static SqlConnection GetConnection( string DBName ){
        string ConnString = String.Format("Data Source=localhost;Initial Catalog={0};Integrated Security=SSPI;", DBName );
        return new SqlConnection( ConnString );    
    }

    /// <summary>
    /// Demonstrate the EXECUTE Non Query.
    /// use SqlCommand.ExecuteNonQuery only when no result is espected (like DELETE, UPDATE statements)
    /// </summary>
    public static void ExecuteNonQuery(){
        WL( "--- EXECUTE Non Query ---" );
        SqlConnection SqlConn = GetMasterConnection();
        SqlConn.Open();
        WL( "Create database BLABLA" );
        SqlCommand SqlCmd = new SqlCommand( "Create database BlaBla", SqlConn );
        SqlCmd.ExecuteNonQuery();
        // see method r.RecordsAffected when used with DELETE or UPDATE statement
        
        WL( "Drop database BLABLA" );
        SqlCommand SqlCmd2 = new SqlCommand( "Drop database BlaBla", SqlConn );
        SqlCmd2.ExecuteNonQuery();
        
        SqlConn.Close(); // Close connection Asap
    }
    
    /// <summary>
    /// Demonstrate the SCALAR READING.
    /// use SqlCommand.ExecuteScalar (scalar reading) to retreive the result of the first row, first columns.
    /// </summary>
    public static void ExecuteScalar(){
        WL( "--- SCALAR READING ---" );
        SqlConnection SqlConn = GetMasterConnection();
        SqlConn.Open();
        // Read number of SqlUsers
        SqlCommand SqlCmd = new SqlCommand( "select count(*) from sys.sysusers", SqlConn );
        int iUserCount = (int)SqlCmd.ExecuteScalar();
        // see method r.HasRows to see if there is data
        WL( String.Format( "There are {0} sql users in the database", iUserCount ));
        
        SqlConn.Close(); // Close connection ASAP    
    }
    
    /// <summary>
    /// Demonstrate the DATASET READER
    /// use SqlCommand.ExecuteReader when needed to retreive a Dataset
    /// </summary>
    public static void ExecuteReader(){
        WL( "--- EXECUTE READER ---" );
        SqlConnection SqlConn = GetMasterConnection();
        SqlConn.Open();
        // Return list of user (and a null row).
        SqlCommand SqlCmd = new SqlCommand( "SELECT UID, NAME FROM SYS.SYSUSERS union select null, null", SqlConn );
        SqlDataReader r = SqlCmd.ExecuteReader();
        // see method r.HasRows to see if there is data
        WL( "Reading SqlUser list" );
        while( r.Read() ){
            // Test nullability
            if( r["uid"] == DBNull.Value )
                continue;
            // Cast SmallInt Sql datatype to Short C# type
            WL( String.Format( "   {0} ({1})", (string)r["name"],  (short)r["uid"]  ) );
        }
        r.Close(); // close DataSet Asap
        SqlConn.Close(); // Close Connection Asap
    }
    
    /// <summary>
    /// Demonstrate how to add parameters to a query command.
    /// </summary>
    public static void ParametrizedQuery() {
        WL( "--- Parametrized Query ---" );
        SqlConnection SqlConn = GetMasterConnection();
        SqlConn.Open();
        SqlCommand SqlCmd = new SqlCommand( "SELECT id, name from sys.sysobjects where xtype = @TypeToFilter", SqlConn );
        SqlCmd.Parameters.Add( new SqlParameter( "TypeToFilter", SqlDbType.NVarChar, 5 ) );
        SqlCmd.Parameters["TypeToFilter"].Value="U"; // @ not required for parameter name
        SqlDataReader r = SqlCmd.ExecuteReader();
        while( r.Read() )
            WL( String.Format("id:{0}, name:{1}", r["id"], r["name"] ) );
        r.Close();
        
        SqlCmd.Parameters["TypeToFilter"].Value="PC";
        r = SqlCmd.ExecuteReader();
        while( r.Read() )
            WL( String.Format("id:{0}, name:{1}", r["id"], r["name"] ) );
        r.Close();
        
        SqlConn.Close();
    }
    
    /// <summary>
    /// Demonstrate how to call a stored procedure and retreive the return value parameter
    /// </summary>
    public static void CallStoredProc(){        
        WL( "--- STORED PROCEDURE call ---" );
        // drop existing database if exists
        SqlConnection SqlConnDropDb = GetMasterConnection();
        try {
            // Drop database
            WL( "X) dropping test database" );
            SqlConnDropDb.Open();
            SqlCommand SqlDrop = new SqlCommand( "Drop database StoredProcTest", SqlConnDropDb );
            SqlDrop.ExecuteNonQuery();
            WL( "Previous StoredProcTest database droped" );
        }
        catch {
            WL( "No previous StoredProcTest database" );
        }
        finally {
            SqlConnDropDb.Close();
        }

        
        WL( "1) Create database and Stored proc" );
        SqlConnection SqlConn = GetMasterConnection();
        SqlConn.Open();
        SqlCommand SqlCmd = new SqlCommand( "Create database StoredProcTest", SqlConn );
        SqlCmd.ExecuteNonQuery();
        SqlConn.Close();
        
        SqlConnection SqlConnProc = GetConnection( "StoredProcTest" );
        SqlConnProc.Open();
        SqlCommand SqlCmdProc = new SqlCommand( "CREATE TABLE USR ( uid int not null, usrname varchar(20) not null)", SqlConnProc );
        SqlCmdProc.ExecuteNonQuery();
        SqlCmdProc.CommandText = "INSERT INTO USR VALUES ( 1, 'Dominique' )";
        SqlCmdProc.ExecuteNonQuery();
        SqlCmdProc.CommandText = "INSERT INTO USR VALUES ( 2, 'Francoise' )";
        SqlCmdProc.ExecuteNonQuery();
        // Create the stored procedure de test HAVING a RETURN PARAM
        SqlCmdProc.CommandText = 
@"create procedure UpdateUsr( @Uid as int, @NewName as varchar(20), @Result Varchar(50) OUTPUT )
as
  Update USR 
  Set UsrName = @NewName 
  where Uid = @Uid
  
  if( @@ROWCOUNT > 0 )
    SET @Result = 'c est fait'
  ELSE
    SET @Result = 'connait pas!' ";
        SqlCmdProc.ExecuteNonQuery();
        SqlConnProc.Close();
        
        // Tester la stored procedure 
        WL( "2) Dataset before stored Proc Call" );
        SqlConnProc.Open();
        SqlCommand SqlRead = new SqlCommand( "select * from USR order by uid", SqlConnProc );
        SqlDataReader r = SqlRead.ExecuteReader();
        while( r.Read() ) 
            WL( String.Format( "   UID: {0} - UserName: {1}", r["UID"], r["UsrName"] ) );
        r.Close();
        
        // Calling the stored procedure
        WL( "3) Call the stored Proc" );
        WL( "Update existing UID 2" );
        SqlCommand storedProcCall = new SqlCommand( "UpdateUsr", SqlConnProc );
        storedProcCall.CommandType = CommandType.StoredProcedure;
        storedProcCall.Parameters.AddWithValue( "@uid", 2 );
        storedProcCall.Parameters.AddWithValue( "@newname", "John Doe" );
        SqlParameter resultParam = new SqlParameter( "@result", SqlDbType.VarChar, 8000 );
        resultParam.Direction = ParameterDirection.Output;
        storedProcCall.Parameters.Add( resultParam  );
        storedProcCall.ExecuteNonQuery();
        WL( String.Format( "   The stored procedure returns: {0}", resultParam.Value ) );
        

        WL( "Update unexisting UID 100" );
        // @ is required into the parameter name BECAUSE it is identified with @
        // in the store procedure signature
        storedProcCall.Parameters["@uid"].Value = 100; 
        storedProcCall.Parameters["@newname"].Value = "Stupid User";
        storedProcCall.ExecuteNonQuery();
        WL( String.Format( "   The stored procedure returns: {0}", resultParam.Value ) );
            
        
        
        WL( "4) Dataset after stored Proc Call" );
        r = SqlRead.ExecuteReader();
        while( r.Read() ) 
            WL( String.Format( "UID: {0} - UserName: {1}", r["UID"], r["UsrName"] ) );
        r.Close();
        SqlConnProc.Close();
        SqlConnProc.Dispose(); // Force to release the connection from the Pool
        
    }
    

    
    #region Helper methods
    
    ...
    #endregion
} 

Les exemples concernant les DataSet et SqlDataAdapter sont disponibles dans l'article "ADO.NET - Aide mémoire - 2/2 : DataSet"