Работа с базой данных SQL Server на C#
View more Tutorials:
В данной статье я покажу вам как работать с базой данных SQL Server используя C#, цели включают:
- Query
- Insert
- Update
- Delete
- Call function, procedure in C#,...

В данной статье используется SIMPLEHR, Database Schema используется во многих инструкциях на o7planning.org, вы можете создать этот Schema на Oracle, MySQL или SQL Server. Можете посмотреть инструкцию по ссылке:
Создать 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); } } }

В 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);
Например сделать запрос данных, используя 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); } } } } } }
Запуск примера:

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


Пример 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(); } } }
Запуск примера:

Например использовать 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(); } } }
Вам нужно создать простую процедуру в 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(); } } }
Запуск примера:

Вам нужно создать простую функцию и вызвать ее в 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(); } } }
Запуск примера:

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(); } } }
Запуск примера:
