betacode

Работа с базой данных SQL Server на C#

  1. Введение
  2. Соединить C# в SQL Server Database
  3. SqlCommand
  4. Запрос данных
  5. Insert (вставить) данные
  6. Update (обновление) данных
  7. Удаление данных
  8. Вызов процедур в C#
  9. Вызов функции в C#
  10. ExecuteScalar

1. Введение

В данной статье я покажу вам как работать с базой данных SQL Server используя C#, цели включают:
  • Query
  • Insert
  • Update
  • Delete
  • Call function, procedure in C#,...
В данной статье используется SIMPLEHR, Database 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();

// Способ 1:
-----------

// Создать объект Command из объекта Connection.
SqlCommand cmd = conn.CreateCommand();

// Set Command Text
cmd.CommandText = sql;

// Способ 2:
-----------

// Создать объект Command.
SqlCommand cmd = new SqlCommand(sql);

// Сочетать Connection с Command.
cmd.Connection = conn;

// Способ 3:
------------

// Создать объект Command с 2 параметра: Command Text & 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)
        {
            // Получить объект Connection подключенный к DB.
            SqlConnection conn = DBUtils.GetDBConnection();
            conn.Open();
            try
            {
                QueryEmployee(conn);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e);
                Console.WriteLine(e.StackTrace);
            }
            finally
            {
                // Закрыть соединение.
                conn.Close();
                // Разрушить объект, освободить ресурс.
                conn.Dispose();
            }       
            Console.Read();
        }

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

            // Создать объект Command.
            SqlCommand cmd = new SqlCommand();

            // Сочетать Command с Connection.
            cmd.Connection = conn;
            cmd.CommandText = sql; 

            
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    
                    while (reader.Read())
                    {
                        // Индекс столбца Emp_ID в команде SQL.
                        int empIdIndex = reader.GetOrdinal("Emp_Id"); // 0
                        

                        long empId =  Convert.ToInt64(reader.GetValue(0));
                        
                        // Столбец Emp_No имеет index = 1.
                        string empNo = reader.GetString(1);
                        int empNameIndex = reader.GetOrdinal("Emp_Name");// 2
                        string empName = reader.GetString(empNameIndex);

                        // Индекс столбца Mng_Id в команде 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);
                    }
                }
            }

        }
    }

}
Запуск примера:
--------------------
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
.....
Примечание: Команда using используется, чтобы гарантировать объект будет удален (dispose) сразу после того как он вышел за рамки, и для этого не требуется написание явного кода.
// Использовать ключевое слово 'using' для объектов вида IDispose.
// (Является объектом Interface IDispose).
using (DbDataReader reader = cmd.ExecuteReader())
{
    // ...
}

// Соответствует:
DbDataReader reader = cmd.ExecuteReader();
try
{
    // ...
}
finally
{
    // Вызвать метод для разрушения объекта
    // Освободить ресурс.
    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) 
          {
  
            SqlConnection connection = DBUtils.GetDBConnection();
            connection.Open();
            try
            {    
                // Команда Insert.
                string sql = "Insert into Salary_Grade (Grade, High_Salary, Low_Salary) "
                                                 + " values (@grade, @highSalary, @lowSalary) "; 

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

                // Создать объект Parameter.
                SqlParameter gradeParam = new SqlParameter("@grade",SqlDbType.Int);
                gradeParam.Value = 3;
                cmd.Parameters.Add(gradeParam);

                // Добавить параметр @highSalary (Написать короче).
                SqlParameter highSalaryParam = cmd.Parameters.Add("@highSalary", SqlDbType.Float);
                highSalaryParam.Value = 20000;

                // Добавить параметр @lowSalary (Написать короче).
                cmd.Parameters.Add("@lowSalary", SqlDbType.Float ).Value = 10000; 

                // Выполнить Command (Используется для 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
            { 
                connection.Close(); 
                connection.Dispose();
                connection = null;
            }

            Console.Read();
  
         }
    }

}
Запуск примера:
Row Count affected = 1

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)
        {
             
            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;

                // Добавить и настроить значение для параметра.
                cmd.Parameters.Add("@salary", SqlDbType.Float).Value = 850;
                cmd.Parameters.Add("@empId", SqlDbType.Decimal).Value = 7369; 

                // Выполнить Command (Используется для 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();

        }
    }

}
Запуск примера:
Row Count affected = 1

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)
        {
         
            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;  

                // Выполнить Command (Используется для 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. Вызов процедур в 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
    {
        // 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
            {
                // Создать объект Command для вызова процедуры Get_Employee_Info.
                SqlCommand cmd = new SqlCommand("Get_Employee_Info", conn);

                // Вид Command является StoredProcedure
                cmd.CommandType = CommandType.StoredProcedure;

                // Добавить параметр @p_Emp_Id и прикрепить к нему значение 100.
                cmd.Parameters.Add("@p_Emp_Id", SqlDbType.Int).Value =100;

                // Добавить параметр @v_Emp_No вида 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)); 

                // Зарегистрировать параметр @v_Emp_No là 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;
 
                // Выполнить процедуру.
                cmd.ExecuteNonQuery();

                // Получить выходные значения.
                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();
        }
    }


}
Запуск примера:
Emp No: E100
First Name: Michael
Last Name: Smith
Hire Date: 2/11/2016 12:00:00 AM

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
    {
        // Function: Get_Emp_No                
        // Parameter: @p_Emp_Id       Integer 
        static void Main(string[] args)
        {
            SqlConnection conn = DBUtils.GetDBConnection();
            conn.Open();
            try
            { 
                // Создать объект Command для вызова функции Get_Emp_No.
                SqlCommand cmd = new SqlCommand("Get_Emp_No", conn);

                // Вид Command является StoredProcedure
                cmd.CommandType = CommandType.StoredProcedure;

                // Добавить параметр @p_Emp_Id и прикрепить к нему значение 100.
                cmd.Parameters.Add("@p_Emp_Id", SqlDbType.Int).Value = 100;

                // Создать объект Parameter, сохранить возвращенное значение.
                SqlParameter resultParam = new SqlParameter("@Result", SqlDbType.VarChar);             

                //  
                resultParam.Direction = ParameterDirection.ReturnValue; 
              
                cmd.Parameters.Add(resultParam);
                
                // Вызвать функцию.
                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();
        }
    }

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

10. ExecuteScalar

SqlCommand.ExecuteScalar() это метод использования для выполнения команды SQL, он возвращает значение первого столбца первой строки SQL.
-- Следующая команда возвращает единственное значение.
Select count(*) from Employee;

-- Или
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 возвращает значение первого столбца, первой строки.
                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();
        }
    }
}
Запуск примера:
Emp Count: 14

Pуководства C#

Show More

Pуководства C#

Show More

Новейшие статьи

    Show More