devstory

Travailler avec la base de données SQL Server dans C#

  1. Introduction
  2. Se connecter à C# dans SQL Server Database
  3. SqlCommand
  4. Requêter des données
  5. Insérer des données
  6. Mettre à jour des données
  7. Supprimer des données
  8. Appeler des procédures dans C#
  9. Appeler une fonction dans C#
  10. ExecuteScalar

1. Introduction

Dans ce document, je vais vous guider des manupulations avec la base de données SQL Server utilisant C#, des objectifs comprennent:
  • Query
  • Insert
  • Update
  • Delete
  • Call function, procedure in C#,...
Ce document utilise SIMPLEHR, un exemple Database Schema qui est utilisé dans différentes instructions sur o7planning.org, vous pouvez créer ce Schéma sur Oracle, MySQL ou SQL Server. Vous pouvez voir des instructions à:

2. Se connecter à C# dans SQL Server Database

Créer le projet CsSQLServerTutorial:
Le projet a été créé.
Vous avez besoin d'une classe utilitaire (DBUtils.cs) qui aide à se connecter à la base de données. Avec la base de données SQL Server, vous pouvez voir les instructions à:
DBSQLServerUtils.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;


namespace Tutorial.SqlConn
{
   class DBSQLServerUtils
   {
     
       public static SqlConnection
                GetDBConnection(string datasource, string database, string username, string password)
       {
           //
           // Data Source=TRAN-VMWARE\SQLEXPRESS;Initial Catalog=simplehr;Persist Security Info=True;User ID=sa;Password=12345
           //
           string connString = @"Data Source="+datasource+";Initial Catalog="
                       +database+";Persist Security Info=True;User ID="+username+";Password="+password;

           SqlConnection conn = new SqlConnection(connString);

           return conn;
       }
     

   }
}
DBUtils.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;


namespace Tutorial.SqlConn
{
   class DBUtils
   {
       public static SqlConnection GetDBConnection()
       {
           string datasource = @"192.168.205.135\SQLEXPRESS";
           
           string database = "simplehr";
           string username = "sa";
           string password = "1234";

           return DBSQLServerUtils.GetDBConnection(datasource,  database, username, password);
       }
   }

}

3. SqlCommand

Dans C# pour manipuler la base de données SQL Server, par exemple query, insert, update, delete, vous utilisez un objet SqlCommand, SqlCommand est une classe étendue de DbCommand. Dans le cas où vous avez besoin de query, insert,update ou delete dans Oracle Database, vous devez utiliser OracleCommand, ou dans MySQL il est MySQLCommand. Malheureusement, il sera très difficile de vouloir utiliser un code source pour les différentes bases de données.
Créez un objet SqlCommand pour travailler avec SQL Server Database:
SqlConnection conn = DBUtils.GetDBConnection();

// Manière 1:
-----------

// Créez un objet Command à partir de l'objet Connection.
SqlCommand cmd = conn.CreateCommand();

// Set Command Text
cmd.CommandText = sql;

// Manirère 2:
-----------

// Créez l'objet Command.
SqlCommand cmd = new SqlCommand(sql);

// Combinez Connection avec Command.
cmd.Connection = conn;

// Manière 3:
------------

// Créez l'objet Command avec 2 paramètres: Command Text & Connection.
SqlCommand cmd = new SqlCommand(sql, conn);

4. Requêter des données

Par exemple, la requête des données utilisant C#.
QueryDataExample.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tutorial.SqlConn;
using System.Data.SqlClient;
using System.Data.Common;

namespace CsSQLServerTutorial
{
    class QueryDataExample
    {
        static void Main(string[] args)
        {
            // Obtenez l'objet Connection pour se connecter à DB.
            SqlConnection conn = DBUtils.GetDBConnection();
            conn.Open();
            try
            {
                QueryEmployee(conn);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            {
                // Closez la connexion.
                conn.Close();
                // Éliminez l'objet, libérant les ressources.
                conn.Dispose();
            }       
            Console.Read();
        }

        private static void QueryEmployee(SqlConnection conn )
        { 
            string sql = "Select Emp_Id, Emp_No, Emp_Name, Mng_Id from Employee"; 

            // Créez un objet Command.
            SqlCommand cmd = new SqlCommand();

            // Combinez l'objet Command avec Connection.
            cmd.Connection = conn;
            cmd.CommandText = sql; 

            
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    
                    while (reader.Read())
                    {
                        // Récupérez l'index de Column Emp_ID dans l'instruction de requête SQL.
                        int empIdIndex = reader.GetOrdinal("Emp_Id"); // 0
                        

                        long empId =  Convert.ToInt64(reader.GetValue(0));
                        
                        // L'index de colonne Emp_No = 1.
                        string empNo = reader.GetString(1);
                        int empNameIndex = reader.GetOrdinal("Emp_Name");// 2
                        string empName = reader.GetString(empNameIndex);

                        // L'index de colonne Mng_Id trong dans l'instruction de requête SQL.
                        int mngIdIndex = reader.GetOrdinal("Mng_Id");

                        long? mngId = null;

                   
                        if (!reader.IsDBNull(mngIdIndex))
                        {
                            mngId = Convert.ToInt64(reader.GetValue(mngIdIndex)); 
                        }
                        Console.WriteLine("--------------------");
                        Console.WriteLine("empIdIndex:" + empIdIndex);
                        Console.WriteLine("EmpId:" + empId);
                        Console.WriteLine("EmpNo:" + empNo);
                        Console.WriteLine("EmpName:" + empName);
                        Console.WriteLine("MngId:" + mngId);
                    }
                }
            }

        }
    }

}
L'exécution de l'exemple:
--------------------
empIdIndex:0
EmpId:7369
EmpNo:E7369
EmpName:SMITH
MngId:7902
--------------------
empIdIndex:0
EmpId:7499
EmpNo:E7499
EmpName:ALLEN
MngId:7698
--------------------
empIdIndex:0
EmpId:7521
EmpNo:E7521
EmpName:WARD
MngId:7698
--------------------
empIdIndex:0
EmpId:7566
EmpNo:E7566
EmpName:JONES
MngId:7839
.....
Remarque: La raison de l'instruction "using" est de s'assurer que l'objet est disposé (dispose) dès sa sortie de portée et qu'il ne nécessite pas de code explicite pour s'assurer que cela se produit.
// Utilisez le mot-clé 'using' pour des objets ayant le type IDispose.
// (Est l'objet de l'Interface IDispose).
using (DbDataReader reader = cmd.ExecuteReader())
{
    // ...
}

// Équivanlent à:
DbDataReader reader = cmd.ExecuteReader();
try
{
    // ...
}
finally
{
    // Appelez la méthode pour déliminer l'objet
    // et libérant des ressources.
    reader.Dispose();
}

5. Insérer des données

Par exemple, insert un enregistrement dans la table Salary_Grade.
InsertDataExample.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tutorial.SqlConn;
using System.Data.Common; 
using System.Data;
using System.Data.SqlClient;

namespace CsSQLServerTutorial
{
    class InsertDataExample
    {
          static void Main(string[] args) 
          {
  
            SqlConnection connection = DBUtils.GetDBConnection();
            connection.Open();
            try
            {    
                // La commande Insert.
                string sql = "Insert into Salary_Grade (Grade, High_Salary, Low_Salary) "
                                                 + " values (@grade, @highSalary, @lowSalary) "; 

                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = sql;  

                // Créez un objet Parameter.
                SqlParameter gradeParam = new SqlParameter("@grade",SqlDbType.Int);
                gradeParam.Value = 3;
                cmd.Parameters.Add(gradeParam);

                // Ajoutez le paramètre @highSalary (Écrire plus court).
                SqlParameter highSalaryParam = cmd.Parameters.Add("@highSalary", SqlDbType.Float);
                highSalaryParam.Value = 20000;

                // Ajoutez le paramètre @lowSalary (Écrire plus court).
                cmd.Parameters.Add("@lowSalary", SqlDbType.Float ).Value = 10000; 

                // Exécutez Command (Pour supprimer (delete), insérer (insert), mettre à jour (update)).
                int rowCount = cmd.ExecuteNonQuery();

                Console.WriteLine("Row Count affected = " + rowCount);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            { 
                connection.Close(); 
                connection.Dispose();
                connection = null;
            }

            Console.Read();
  
         }
    }

}
L'exécution de l'exemple:
Row Count affected = 1

6. Mettre à jour des données

Exemple de update dans C#.
UpdateExample.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Tutorial.SqlConn;
using System.Data;

namespace CsSQLServerTutorial
{
    class UpdateExample
    {
        static void Main(string[] args)
        {
             
            SqlConnection conn  = DBUtils.GetDBConnection();
            conn.Open();
            try
            { 
                string sql = "Update Employee set Salary = @salary where Emp_Id = @empId";
 
                SqlCommand cmd = new SqlCommand(); 
              
                cmd.Connection = conn; 
                cmd.CommandText = sql;

                // Ajoutez et définissez de la valeur pour le paramètre.
                cmd.Parameters.Add("@salary", SqlDbType.Float).Value = 850;
                cmd.Parameters.Add("@empId", SqlDbType.Decimal).Value = 7369; 

                // Exécutez Command (Pour supprimer, insérer, mettre à jour).
                int rowCount = cmd.ExecuteNonQuery();

                Console.WriteLine("Row Count affected = " + rowCount);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            { 
                conn.Close(); 
                conn.Dispose();
                conn = null;
            }


            Console.Read();

        }
    }

}
L'exécution de l'exemple:
Row Count affected = 1

7. Supprimer des données

Par exemple, utilisez C# pour supprimer des données SQL.
DeleteExample.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Tutorial.SqlConn;
using System.Data;

namespace CsSQLServerTutorial
{
    class DeleteExample
    {
        static void Main(string[] args)
        {
         
            SqlConnection conn  = DBUtils.GetDBConnection();
            conn.Open();
            try
            {               

                string sql = "Delete from Salary_Grade where Grade = @grade ";
           
                SqlCommand cmd = new SqlCommand();
 
                cmd.Connection = conn; 
                cmd.CommandText = sql; 

                cmd.Parameters.Add("@grade", SqlDbType.Int).Value = 3;  

                // Exécutez Command (pour supprimer, insérer, mettre à jour).
                int rowCount = cmd.ExecuteNonQuery();

                Console.WriteLine("Row Count affected = " + rowCount);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            { 
                conn.Close(); 
                conn.Dispose();
                conn = null;
            }

            Console.Read();

        }
    }

}

8. Appeler des procédures dans C#

Vous devez créer une procédure simple dans SQL Server et l'appeler en C#:
Get_Employee_Info
-- This procedure retrieves information of an employee,
-- Input parameter: p_Emp_ID (Integer)
-- There are four output parameters v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date

CREATE PROCEDURE Get_Employee_Info
    @p_Emp_Id       Integer ,
    @v_Emp_No      Varchar(50)   OUTPUT,
    @v_First_Name  Varchar(50)   OUTPUT,
    @v_Last_Name  Varchar(50)   OUTPUT,
    @v_Hire_Date    Date             OUTPUT
AS
BEGIN
    set @v_Emp_No  =   'E' + CAST( @p_Emp_Id as varchar)  ;
    --
    set @v_First_Name = 'Michael';
    set @v_Last_Name  = 'Smith';
    set @v_Hire_date  = getdate();
END
CallProcedureExample.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tutorial.SqlConn;
using System.Data;
using System.Data.SqlClient;

namespace CsSQLServerTutorial
{
    class CallProcedureExample
    {
        // Get_Employee_Info                
        // @p_Emp_Id       Integer ,
        // @v_Emp_No      Varchar(50)   OUTPUT
        // @v_First_Name  Varchar(50)   OUTPUT
        // @v_Last_Name  Varchar(50)    OUTPUT
        // @v_Hire_Date    Date         OUTPUT
        static void Main(string[] args)
        {
            SqlConnection conn = DBUtils.GetDBConnection();
            conn.Open();
            try
            {
                // Créez un objet Command pour appeler la procédure Get_Employee_Info
                SqlCommand cmd = new SqlCommand("Get_Employee_Info", conn);

                // Le type de Command est StoredProcedure
                cmd.CommandType = CommandType.StoredProcedure;

                // Ajoutez le paramètre @p_Emp_Id et définissez la valeur = 100.
                cmd.Parameters.Add("@p_Emp_Id", SqlDbType.Int).Value =100;

                // Ajoutez le paramètre @v_Emp_No type de Varchar (20).
                cmd.Parameters.Add(new SqlParameter("@v_Emp_No", SqlDbType.VarChar, 20));
                cmd.Parameters.Add(new SqlParameter("@v_First_Name", SqlDbType.VarChar, 50));
                cmd.Parameters.Add(new SqlParameter("@v_Last_Name", SqlDbType.VarChar, 50));
                cmd.Parameters.Add(new SqlParameter("@v_Hire_Date", SqlDbType.Date)); 

                // Le paramètre de registre @v_Emp_No est OUTPUT.
                cmd.Parameters["@v_Emp_No"].Direction = ParameterDirection.Output;
                cmd.Parameters["@v_First_Name"].Direction = ParameterDirection.Output;
                cmd.Parameters["@v_Last_Name"].Direction = ParameterDirection.Output;
                cmd.Parameters["@v_Hire_Date"].Direction = ParameterDirection.Output;
 
                // Exécutez la procédure.
                cmd.ExecuteNonQuery();

                // Obtenez des valeurs de sortie.
                string empNo = cmd.Parameters["@v_Emp_No"].Value.ToString();
                string firstName = cmd.Parameters["@v_First_Name"].Value.ToString();
                string lastName = cmd.Parameters["@v_Last_Name"].Value.ToString();
                DateTime hireDate = (DateTime)cmd.Parameters["@v_Hire_Date"].Value;


                Console.WriteLine("Emp No: " + empNo);
                Console.WriteLine("First Name: " + firstName);
                Console.WriteLine("Last Name: " + lastName);
                Console.WriteLine("Hire Date: " + hireDate);

            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            Console.Read();
        }
    }


}
L'exécution de l'exemple:
Emp No: E100
First Name: Michael
Last Name: Smith
Hire Date: 2/11/2016 12:00:00 AM

9. Appeler une fonction dans C#

Vous devez créer une fonction simple dans SQL Server et l'appeler en C#:
Get_Emp_No
-- Procedure to retrieve information of an employee,
-- Parameter: p_Emp_ID (Integer)
-- Returns Emp_No

CREATE Function Get_Emp_No (@p_Emp_Id  Integer)
Returns Varchar(50)
AS
BEGIN    
   return 'E'+  CAST( @p_Emp_Id as varchar);
END
CallFunctionExample.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tutorial.SqlConn;
using System.Data;
using System.Data.SqlClient;


namespace CsSQLServerTutorial
{
    class CallFunctionExample
    {
        // Function: Get_Emp_No                
        // Parameter: @p_Emp_Id       Integer 
        static void Main(string[] args)
        {
            SqlConnection conn = DBUtils.GetDBConnection();
            conn.Open();
            try
            { 
                // Créez un objet Command pour appeler la fonction Get_Emp_No.
                SqlCommand cmd = new SqlCommand("Get_Emp_No", conn);

                // Le type de Command est StoredProcedure
                cmd.CommandType = CommandType.StoredProcedure;

                // Ajoutez le paramètre @p_Emp_Id et définissez la valeur = 100.
                cmd.Parameters.Add("@p_Emp_Id", SqlDbType.Int).Value = 100;

                // Créez un objet Parameter, stockez la valeur de retour de la fonction.
                SqlParameter resultParam = new SqlParameter("@Result", SqlDbType.VarChar);             

                //  
                resultParam.Direction = ParameterDirection.ReturnValue; 
              
                cmd.Parameters.Add(resultParam);
                
                // Appelez la fonction.
                cmd.ExecuteNonQuery();

                string empNo = null;
                if (resultParam.Value != DBNull.Value)
                {
                    empNo = (string)resultParam.Value;
                }                 
                Console.WriteLine("Emp No: " + empNo); 

            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            Console.Read();
        }
    }

}
L'exécution de l'exemple:
Emp No: E100

10. ExecuteScalar

SqlCommand.ExecuteScalar() est une méthode utilisée pour exécuter des instructions SQL, elle renvoie la valeur de la première colonne de la première ligne dans les résultats des instructions SQL.
-- Cette requête renvoie une seule valeur
Select count(*) from Employee;

-- Ou
Select Max(e.Salary) From Employee e;
Exemple:
ExecuteScalarExample.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Tutorial.SqlConn;


namespace CsSQLServerTutorial
{
    class ExecuteScalarExample
    {
        static void Main(string[] args)
        {
            SqlConnection conn = DBUtils.GetDBConnection();
            conn.Open();
            try
            { 
                SqlCommand cmd = new SqlCommand("Select count(*) From Employee", conn);
                 
                cmd.CommandType = CommandType.Text;

                // La méthode ExecuteScalar renvoie la valeur de la première colonne, sur la première ligne.
                int count = (int) cmd.ExecuteScalar();

                Console.WriteLine("Emp Count: " + count);

            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            Console.Read();
        }
    }
}
L'exécution de l'exemple:
Emp Count: 14