Utilisation d'une base de données SQL Server en utilisant C#

View more categories:

1- Introduction

Dans ce document, je vais vous guider des manupulations avec la base de données  SQL Server utilisant  C#, des objectifs comprennent:
  1. Query
  2. Insert
  3. Update
  4. Delete
  5. 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 à:
  • TODO Link!
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();

// Way 1:
-----------

// Create a Command from Connection object.
SqlCommand cmd = conn.CreateCommand();

// Set Command Text
cmd.CommandText = sql;

// Way 2:
-----------

// Create a Command.
SqlCommand cmd = new SqlCommand(sql);

// Set Connection for Command.
cmd.Connection = conn;

// Way 3:
------------

// Create a Command with 2 parameter: 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)
        {
            // Get connection.
            SqlConnection conn = DBUtils.GetDBConnection();
            conn.Open();
            try
            {
                QueryEmployee(conn);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            {
                // Close connection.
                conn.Close();
                // Dispose object, freeing Resources.
                conn.Dispose();
            }       
            Console.Read();
        }

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

            // Create command.
            SqlCommand cmd = new SqlCommand();

            // Set connection for Command.
            cmd.Connection = conn;
            cmd.CommandText = sql; 

            
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    
                    while (reader.Read())
                    {
                        // Get index of Column Emp_ID in query statement.
                        int empIdIndex = reader.GetOrdinal("Emp_Id"); // 0
                        

                        long empId =  Convert.ToInt64(reader.GetValue(0));
                        
                        // Index of Emp_ID = 1
                        string empNo = reader.GetString(1);
                        int empNameIndex = reader.GetOrdinal("Emp_Name");// 2
                        string empName = reader.GetString(empNameIndex);

                        // Index of column Mng_Id.
                        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:
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.
// Use the keyword 'using' for IDispose object.
// (Is object of IDispoose interface)
using (DbDataReader reader = cmd.ExecuteReader())
{
    // ...
}

// Equivalent to:
DbDataReader reader = cmd.ExecuteReader();
try
{
    // ...
}
finally
{
    // Call the method to dispose the object
    // And freeing resources.
    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
            {    
                // Insert statement.
                string sql = "Insert into Salary_Grade (Grade, High_Salary, Low_Salary) "
                                                 + " values (@grade, @highSalary, @lowSalary) "; 

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

                // Create Parameter.
                SqlParameter gradeParam = new SqlParameter("@grade",SqlDbType.Int);
                gradeParam.Value = 3;
                cmd.Parameters.Add(gradeParam);

                // Add parameter @highSalary (Write shorter)
                SqlParameter highSalaryParam = cmd.Parameters.Add("@highSalary", SqlDbType.Float);
                highSalaryParam.Value = 20000;

                // Add parameter @lowSalary (more shorter).
                cmd.Parameters.Add("@lowSalary", SqlDbType.Float ).Value = 10000; 

                // Execute Command (for Delete,Insert or 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:

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;

                // Add and set value for parameter.
                cmd.Parameters.Add("@salary", SqlDbType.Float).Value = 850;
                cmd.Parameters.Add("@empId", SqlDbType.Decimal).Value = 7369; 

                // Execute Command (for Delete, Insert,Update).
                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:

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;  

                // Execute Command (for Delete, insert, update).
                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
            {
                // Create a Command object to call procedure Get_Employee_Info
                SqlCommand cmd = new SqlCommand("Get_Employee_Info", conn);

                // Command Type is StoredProcedure
                cmd.CommandType = CommandType.StoredProcedure;

                // Add parameter @p_Emp_Id and set value = 100
                cmd.Parameters.Add("@p_Emp_Id", SqlDbType.Int).Value =100;

                // Add parameter @v_Emp_No type of 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)); 

                // Register parameter @v_Emp_No is 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;
 
                // Execute procedure.
                cmd.ExecuteNonQuery();

                // Get output values.
                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:

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
            { 
                // Create a Command object to call Get_Emp_No function.
                SqlCommand cmd = new SqlCommand("Get_Emp_No", conn);

                // CommandType is StoredProcedure
                cmd.CommandType = CommandType.StoredProcedure;

                // Add parameter @p_Emp_Id and set value = 100.
                cmd.Parameters.Add("@p_Emp_Id", SqlDbType.Int).Value = 100;

                // Create a Parameter object, store the return value of the function.
                SqlParameter resultParam = new SqlParameter("@Result", SqlDbType.VarChar);             

                //  
                resultParam.Direction = ParameterDirection.ReturnValue; 
              
                cmd.Parameters.Add(resultParam);
                
                // Call function.
                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:

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.
-- This statement return single value.
Select count(*) from Employee;

-- Or
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;

                // ExecuteScalar method return value of first column on the first row.
                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:

View more categories: