1- Introduction
In this document, I will guide you to work with SQL Server Database using C #, objectives include
- Query
- Insert
- Update
- Delete
- Call function, procedure in C#,…
Firstly you need to create a demo database, see the instructions at:
2- Connect to SQL Server Database with C#
Create project CsSQLServerTutorial:
Project is created:
You need a utility class ( DBUtils.cs) which help to connect to the database. With SQL Server database, you can see the instructions at:
DBSQLServerUtils.cs
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
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
In C # to manipulate SQL Server Database, such query, insert, update, delete, you use a SqlCommand object, SqlCommand is a class extended from DbCommand. In case you need query, insert, update or delete in the Oracle Database you need to use OracleCommand, or with MySQL is MySqlCommand. Unfortunately, it will be very difficult if you want to use a source code for the various Database.
Create a SqlCommand object to work with SQL Server Database:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SqlConnection conn = DBUtils.GetDBConnection();// Way 1:-----------// Create a Command from Connection object.SqlCommand cmd = conn.CreateCommand();// Set Command Textcmd.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- Query
For example, data query using C #.
QueryDataExample.cs
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
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); } } } } }} |
Running the example:
Node: The reason for the “using” statement is to ensure that the object is disposed as soon as it goes out of scope, and it doesn’t require explicit code to ensure that this happens.
// 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- Insert
For example, insert a record into Salary_Grade table.
InsertDataExample.cs
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
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(); } }} |
Running the example:
6- Update
Update example:
UpdateExample.cs
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
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(); } }} |
Running the example:
7- Delete
For example, use C# to delete SQL data.
DeleteExample.cs
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
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- Call procedures in C#
You need to create a simple procedure in SQL Server and call it in C #:
Get_Employee_Info
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
-- 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_DateCREATE 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 OUTPUTASBEGIN 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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
|
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(); } }} |
Running the example:
9- Call function in C#
You need to create a simple function in SQL Server and call it in C #:
Get_Emp_No
|
1
2
3
4
5
6
7
8
9
10
|
-- Procedure to retrieve information of an employee,-- Parameter: p_Emp_ID (Integer)-- Returns Emp_NoCREATE Function Get_Emp_No (@p_Emp_Id Integer)Returns Varchar(50)ASBEGIN return 'E'+ CAST( @p_Emp_Id as varchar);END |
CallFunctionExample.cs
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
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(); } }} |
Running the example:
10- ExecuteScalar
SqlCommand.ExecuteScalar() is a method used to execute SQL statements, it returns the value of the first column of the first row in results of SQL statements.
|
1
2
3
4
5
|
-- This statement return single value.Select count(*) from Employee;-- OrSelect Max(e.Salary) From Employee e; |
Example:
ExecuteScalarExample.cs
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
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(); } }} |
Running the example:
