Làm việc với cơ sở dữ liệu Oracle trong C#
1. Giới thiệu
Trong tài liệu này tôi sẽ hướng dẫn bạn thao tác với cơ sở dữ liệu Oracle từ C#, mục tiêu bao gồm:
- Query
- Insert
- Update
- Delete
- Gọi hàm, thủ tục từ C#,...
Tài liệu sử dụng SIMPLEHR, một Database Schema ví dụ được sử dụng trong nhiều hướng dẫn trên o7planning.org, bạn có thể tạo Schema này trên Oracle, MySQL hoặc SQL Server. Bạn có thể xem hướng dẫn tại:
2. Kết nối C# vào Oracle Database
Tạo project CsOracleTutorial:
Project đã được tạo ra.
Bạn cần khai báo các thư viện DLL, và cần một lớp tiện ích (DBUtils.cs) giúp kết nối vào Database. Với cơ sở dũ liệu Oracle, bạn có thể xem hướng dẫn tại:
DBOracleUtils.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;
namespace Tutorial.SqlConn
{
class DBOracleUtils
{
public static OracleConnection
GetDBConnection(string host, int port, String sid, String user, String password)
{
Console.WriteLine("Getting Connection ...");
// Connection String để kết nối trực tiếp tới Oracle.
string connString = "Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = "
+ host + ")(PORT = " + port + "))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = "
+ sid + ")));Password=" + password + ";User ID=" + user;
OracleConnection conn = new OracleConnection();
conn.ConnectionString = connString;
return conn;
}
}
}DBUtils.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;
namespace Tutorial.SqlConn
{
class DBUtils
{
public static OracleConnection GetDBConnection()
{
string host = "192.168.0.102";
int port = 1521;
string sid = "db12c";
string user = "simplehr";
string password = "12345";
return DBOracleUtils.GetDBConnection(host, port, sid, user, password);
}
}
}3. OracleCommand
Trong C# để thao tác với cơ sở dữ liệu Oracle, chẳng hạn query, insert, update, delete bạn sử dụng một đối tượng OracleCommand, OracleCommand là một class mở rộng từ DbCommand. Trong trường hợp bạn cần query, insert,update hoặc delete trong cơ sở dữ liệu MySQL bạn cần sử dụng MySqlCommand, hoặc với SQL Server là SqlCommand. Thật đáng tiếc là bạn sẽ rất khó khăn nếu muốn sử dụng một mã nguồn cho các Database khác nhau.
Bạn có thể tạo đối tượng OracleCommand để thao tác với Oracle Database:
OracleConnection conn = DBUtils.GetDBConnection();
// Cách 1:
// Tạo một Command liên hợp với Connection.
OracleCommand cmd = conn.CreateCommand();
// Sét Command Text
cmd.CommandText = sql;
// Cách 2:
// Tạo mới một Command
OracleCommand cmd = new OracleCommand(sql);
// Liên hợp Command với Connection.
cmd.Connection = conn;
// Cách 3:
// Tạo một đối tượng Command liên hợp với Connection
OracleCommand cmd = new OracleCommand(sql, conn);4. Truy vấn dữ liệu
Ví dụ truy vấn dữ liệu sử dụng 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.Common;
using Oracle.DataAccess.Client;
namespace CsOracleTutorial
{
class QueryDataExample
{
static void Main(string[] args)
{
// Lấy ra đối tượng Connection kết nối vào DB.
OracleConnection 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(OracleConnection conn)
{
string sql = "Select Emp_Id, Emp_No, Emp_Name, Mng_Id from Employee";
// Tạo một đối tượng Command.
OracleCommand cmd = new OracleCommand();
// Liên hợp Command với Connection.
cmd.Connection = conn;
cmd.CommandText = sql;
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
// Chỉ số (index) của cột Emp_ID trong câu lệnh SQL.
int empIdIndex = reader.GetOrdinal("Emp_Id"); // 0
long empId = Convert.ToInt64(reader.GetValue(0));
// Chỉ số của cột Emp_No là 1.
string empNo = reader.GetString(1);
int empNameIndex = reader.GetOrdinal("Emp_Name");// 2
string empName = reader.GetString(empNameIndex);
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);
}
}
}
}
}
}Chạy ví dụ:
--------------------
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
.....Chú ý: Câu lệnh using sử dụng để đảm bảo rằng đối tượng sẽ bị tiêu hủy (dispose) ngay sau khi nó ra khỏi phạm vi, mà không cần phải đòi hỏi phải viết code một cách trực quan.// Sử dụng từ khóa 'using' với các đối tượng kiểu IDispose. // (Là đối tượng của Interface IDispose). using (DbDataReader reader = cmd.ExecuteReader()) { // Code ... } // Tương đương với viết một cách trực quan: DbDataReader reader = cmd.ExecuteReader(); try { // Code ... } finally { // Gọi phương thức để tiêu hủy đối tượng // Giải phóng tài nguyên. reader.Dispose(); }
5. Insert dữ liệu
Ví dụ sau insert thêm một bản ghi vào bảng 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 Oracle.DataAccess.Client;
namespace CsOracleTutorial
{
class InsertDataExample
{
static void Main(string[] args)
{
OracleConnection connection = DBUtils.GetDBConnection();
connection.Open();
try
{
// Câu lệnh Insert.
string sql = "Insert into Salary_Grade (Grade, High_Salary, Low_Salary) "
+ " values (@grade, @highSalary, @lowSalary) ";
OracleCommand cmd = connection.CreateCommand();
cmd.CommandText = sql;
// Tạo một đối tượng Parameter.
OracleParameter gradeParam = new OracleParameter("@grade",SqlDbType.Int);
gradeParam.Value = 3;
cmd.Parameters.Add(gradeParam);
// Thêm tham số @highSalary (Viết ngắn hơn).
OracleParameter highSalaryParam = cmd.Parameters.Add("@highSalary", SqlDbType.Float);
highSalaryParam.Value = 20000;
// Thêm tham số @lowSalary (Viết ngắn hơn nữa).
cmd.Parameters.Add("@lowSalary", SqlDbType.Float ).Value = 10000;
// Thực thi Command (Dùng cho 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();
}
}
}Chạy ví dụ:
Row Count affected = 1
6. Update dữ liệu
Ví dụ update trong C#.
UpdateExample.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;
using Tutorial.SqlConn;
using System.Data;
namespace CsOracleTutorial
{
class UpdateExample
{
static void Main(string[] args)
{
OracleConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
string sql = "Update Employee set Salary = @salary where Emp_Id = @empId";
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.Parameters.Add("@salary", SqlDbType.Float).Value = 850;
cmd.Parameters.Add("@empId", SqlDbType.Decimal).Value = 7369;
// Thực thi Command (Dùng cho 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();
}
}
}Chạy ví dụ:
Row Count affected = 17. Xóa dữ liệu
Ví dụ sử dụng C# xóa dữ liệu trong Database.
DeleteExample.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;
using Tutorial.SqlConn;
using System.Data;
namespace CsOracleTutorial
{
class DeleteExample
{
static void Main(string[] args)
{
// Lấy ra kết nối tới cơ sở dữ liệu.
OracleConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
string sql = "Delete from Salary_Grade where Grade = @grade ";
// Tạo đối tượng Command
OracleCommand cmd = new OracleCommand();
// Liên hợp với Connection
cmd.Connection = conn;
// Command Text.
cmd.CommandText = sql;
cmd.Parameters.Add("@grade", SqlDbType.Int).Value = 3;
// Thực thi Command (Dùng cho 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. Gọi thủ tục trong C#
Bạn cần tạo ra một thủ tục đơn giản trong Oracle và gọi nó trong C#:
Get_Employee_Info
-- Thủ tục lấy ra thông tin của một nhân viên,
-- Truyền vào tham số p_Emp_ID (Integer)
-- Có 4 tham số đầu ra v_Emp_No, v_First_Name, v_Last_Name, v_Hire_Date
Create Or Replace Procedure Get_Employee_Info(p_Emp_Id Integer
,v_Emp_No Out Varchar2
,v_First_Name Out Varchar2
,v_Last_Name Out Varchar2
,v_Hire_Date Out Date) Is
Begin
v_Emp_No := 'E' || p_Emp_Id;
--
v_First_Name := 'Michael';
v_Last_Name := 'Smith';
v_Hire_Date := Sysdate;
End Get_Employee_Info;
/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 Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace CsOracleTutorial
{
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)
{
OracleConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
// Tạo một đối tượng Command gọi thủ tục Get_Employee_Info.
OracleCommand cmd = new OracleCommand("Get_Employee_Info", conn);
// Kiểu của Command là StoredProcedure
cmd.CommandType = CommandType.StoredProcedure;
// Thêm tham số @p_Emp_Id và sét giá trị của nó = 100.
cmd.Parameters.Add("@p_Emp_Id", OracleDbType.Int32).Value =100;
// Thêm tham số @v_Emp_No kiểu Varchar(20).
cmd.Parameters.Add(new OracleParameter("@v_Emp_No", OracleDbType.Varchar2, 20));
cmd.Parameters.Add(new OracleParameter("@v_First_Name", OracleDbType.Varchar2, 50));
cmd.Parameters.Add(new OracleParameter("@v_Last_Name", OracleDbType.Varchar2, 50));
cmd.Parameters.Add(new OracleParameter("@v_Hire_Date", OracleDbType.Date));
// Đăng ký tham số @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;
// Thực thi thủ tục.
cmd.ExecuteNonQuery();
// Lấy các giá trị đầu ra.
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();
object hireDateObj = cmd.Parameters["@v_Hire_Date"].Value;
Console.WriteLine("hireDateObj type: "+ hireDateObj.GetType().ToString());
OracleDate hireDate = (OracleDate)hireDateObj;
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();
}
}
}Chạy ví dụ:
Getting Connection ...
hireDateObj type: Oracle.DataAccess.Types.OracleDate
Emp No: E100
First Name: Michael
Last Name: Smith
Hire Date: 12-FEB-169. Gọi hàm trong C#
Bạn cần một hàm đơn giản và gọi nó trong C#.
Get_Emp_No
CREATE or Replace Function Get_Emp_No (p_Emp_Id Integer) Return Varchar2
As
Begin
return 'E' || p_Emp_Id;
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 Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace CsOracleTutorial
{
class CallFunctionExample
{
// Function: Get_Emp_No
// Parameter: @p_Emp_Id Integer
static void Main(string[] args)
{
OracleConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
// Tạo một đối tượng Command để gọi hàm Get_Emp_No.
OracleCommand cmd = new OracleCommand("Get_Emp_No", conn);
// Kiểu của Command là StoredProcedure
cmd.CommandType = CommandType.StoredProcedure;
// ** Chú ý: Với Oracle, tham số trả về phải được thêm vào đầu tiên.
// Tạo một đối tượng Parameter, lưu trữ kết quả trả về của hàm (Varchar2(50)).
OracleParameter resultParam = new OracleParameter("@Result", OracleDbType.Varchar2, 50);
// ReturnValue
resultParam.Direction = ParameterDirection.ReturnValue;
// Thêm vào danh sách tham số.
cmd.Parameters.Add(resultParam);
// Thêm tham số @p_Emp_Id và sét giá trị của nó = 100.
cmd.Parameters.Add("@p_Emp_Id", OracleDbType.Int32).Value = 100;
// Gọi hàm.
cmd.ExecuteNonQuery();
string empNo = null;
if (resultParam.Value != DBNull.Value)
{
Console.WriteLine("resultParam.Value: "+ resultParam.Value.GetType().ToString());
OracleString ret = (OracleString) resultParam.Value;
empNo = ret.ToString();
}
Console.WriteLine("Emp No: " + empNo);
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
Console.WriteLine(e.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
Console.Read();
}
}
}Chạy ví dụ:
Getting Connection ...
resultParam.Value: Oracle.DataAccess.Types.OracleString
Emp No: E10010. ExecuteScalar
OracleCommand.ExecuteScalar() là một phương thức sử dụng để thực thi câu lệnh SQL trả về giá trị của cột đầu tiên tại dòng đầu tiên.
-- Câu lệnh sau trả về duy nhất một giá trị.
Select count(*) from Employee;
-- Hoặc
Select Max(e.Salary) From Employee e;Ví dụ:
ExecuteScalarExample.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;
using System.Data;
using Tutorial.SqlConn;
namespace CsOracleTutorial
{
class ExecuteScalarExample
{
static void Main(string[] args)
{
OracleConnection conn = DBUtils.GetDBConnection();
conn.Open();
try
{
OracleCommand cmd = new OracleCommand("Select count(*) From Employee", conn);
cmd.CommandType = CommandType.Text;
// Phương thức ExecuteScalar trả về giá trị của cột đầu tiên trên dòng đầu tiên.
object countObj = cmd.ExecuteScalar();
int count = 0;
if (countObj != null)
{
count = Convert.ToInt32(countObj);
}
Console.WriteLine("Emp Count: " + count);
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
Console.WriteLine(e.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
Console.Read();
}
}
}Chạy ví dụ:
Emp Count: 14Các hướng dẫn lập trình C#
- Thừa kế và đa hình trong C#
- Bắt đầu với C# cần những gì?
- Học nhanh C# cho người mới bắt đầu
- Cài đặt Visual Studio 2013 trên Windows
- Abstract class và Interface trong C#
- Cài đặt Visual Studio 2015 trên Windows
- Nén và giải nén trong C#
- Hướng dẫn lập trình đa luồng trong C#
- Hướng dẫn và ví dụ C# Delegate và Event
- Cài đặt AnkhSVN trên Windows
- Lập trình C# theo nhóm sử dụng Visual Studio và SVN
- Cài đặt .Net Framework
- Access Modifier trong C#
- Hướng dẫn và ví dụ C# String và StringBuilder
- Hướng dẫn và ví dụ C# Property
- Hướng dẫn và ví dụ C# Enum
- Hướng dẫn và ví dụ C# Structure
- Hướng dẫn và ví dụ C# Generics
- Hướng dẫn xử lý ngoại lệ trong C#
- Hướng dẫn và ví dụ Date Time trong C#
- Thao tác với tập tin và thư mục trong C#
- Hướng dẫn sử dụng Stream - luồng vào ra nhị phân trong C#
- Hướng dẫn sử dụng biểu thức chính quy trong C#
- Kết nối cơ sở dữ liệu SQL Server trong C#
- Làm việc với cơ sở dữ liệu SQL Server trong C#
- Kết nối cơ sở dữ liệu MySQL trong C#
- Làm việc với cơ sở dữ liệu MySQL trong C#
- Kết nối cơ sở dữ liệu Oracle trong C# không cần Oracle Client
- Làm việc với cơ sở dữ liệu Oracle trong C#
Show More
