Работа с базой данных SQL Server с использованием C#

1- Введение

В данной статье я покажу вам как работать с базой данных SQL Server используя  C#, цели включают:
  1. Query
  2. Insert
  3. Update
  4. Delete
  5. Call function, procedure in C#,...
В данной статье используется  SIMPLEHRDatabase Schema используется во многих инструкциях на o7planning.org, вы можете создать этот Schema на  Oracle, MySQL или  SQL Server. Можете посмотреть инструкцию по ссылке:

2- Соединить C# в SQL Server Database

Создать project CsSQLServerTutorial:
Project создан.
Вам нужен утилитарный класс ( DBUtils.cs) который помогает подключиться к базе данных. С базой данных  SQL Server, вы можете посмотреть инструкцию по ссылке:
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

В C# чтобы манипулировать с базой данных  SQL Server, например  query, insert, update, delete вы используете объект  SqlCommand, SqlCommand расширенный класс из  DbCommand. В случае, когда нужен  query, insert, update или  delete в Oracle Database вам нужно использовать  OracleCommand, или с  MySQL это  MySQLCommand. К сожалению будет трудно если вы хотите использовать исходный код для разных баз данных.
Создать объект  SqlCommand для работы с  SQL Server Database:
SqlConnection conn = DBUtils.GetDBConnection();

// Way 1:

// Tạo một Command liên hợp với Connection.
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 Command text and Connection.

SqlCommand cmd = new SqlCommand(sql, conn);

4- Запрос данных

Например сделать запрос данных, используя  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();
               // 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 a column is nullable always check for DBNull...
                       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);
                   }
               }
           }

       }
   }

}
Запуск примера:
Примечание: Команда using используется, чтобы гарантировать объект будет удален (dispose) сразу после того как он вышел за рамки, и для этого не требуется написание явного кода.
// With IDispose type
// (DbDataReader implements IDispose interface).

using (DbDataReader reader = cmd.ExecuteReader())
{
  // Code using reader...
}


// Equivalent to the code:

DbDataReader reader = cmd.ExecuteReader();
try
{
  // Code using reader...
}
finally
{
   // Call Dispose() method to free resources
   reader.Dispose();
}

5- Insert (вставить) данные

В примере ниже   insert (вставляется) еще одна запись в таблицу 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)
         {

           // Get Connection
           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 (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
           {
               // Close connection
               connection.Close();
               // Freeing Resources
               connection.Dispose();
               connection = null;
           }
         

           Console.Read();

        }
   }

}
Запуск примера:

6- Update (обновление) данных

Пример  update в 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)
       {
           // Get connection.
           SqlConnection conn  = DBUtils.GetDBConnection();
           conn.Open();
           try
           {
               string sql = "Update Employee set Salary = @salary where Emp_Id = @empId";

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

               // Set connection for command.
               cmd.Connection = conn;
               // Set command text.
               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 (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
           {
               // Close connection
               conn.Close();
               // Freeing resources.
               conn.Dispose();
               conn = null;
           }


           Console.Read();

       }
   }

}
Запуск примера:

7- Удаление данных

Например использовать  C# чтобы удалить данные в  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)
       {
           // Get connection to database.
           SqlConnection conn  = DBUtils.GetDBConnection();
           conn.Open();
           try
           {

               string sql = "Delete from Salary_Grade where Grade = @grade ";

               // Create Command object.
               SqlCommand cmd = new SqlCommand();

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

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

               // Execute (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
           {
               // Close connection.
               conn.Close();
               // Freeing resources.
               conn.Dispose();
               conn = null;
           }


           Console.Read();

       }
   }

}

8- Вызов процедур в C#

Вам нужно создать простую процедуру в SQL Server и вызвать ее в  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
   {
       static void Main(string[] args)
       {
           SqlConnection conn = DBUtils.GetDBConnection();
           conn.Open();
           try
           {
               // 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

               // 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();
       }
   }


}
Запуск примера:

9- Вызов функции в C#

Вам нужно создать простую функцию и вызвать ее в  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
   {
       static void Main(string[] args)
       {
           SqlConnection conn = DBUtils.GetDBConnection();
           conn.Open();
           try
           {
               // Get_Emp_No                
               // @p_Emp_Id       Integer

               // 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 result Parameter.
               SqlParameter resultParam = new SqlParameter("@Result", SqlDbType.VarChar);            

               //  
               resultParam.Direction = ParameterDirection.ReturnValue;

               // Add to parameters
               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();
       }
   }

}
Запуск примера:

10- ExecuteScalar

SqlCommand.ExecuteScalar() это метод использования для выполнения команды  SQL, он возвращает значение первого столбца первой строки  SQL.
-- The following statement returns only one value.

Select count(*) from Employee;

-- Or

Select Max(e.Salary) From Employee e;
Пример:
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 return first column of 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();
       }
   }
}
Запуск примера: