This topic describes how to build an application using the SqlSugar framework, OceanBase Connector/ODBC, and OceanBase Cloud to perform basic operations such as creating tables, inserting data, and querying data.
Prerequisites
You have registered an OceanBase Cloud account and created a cluster instance and an OceanBase Cloud tenant in Oracle-compatible mode. For more information, see Create a cluster instance and Create a tenant.
You have installed Visual Studio, the NuGet Package Manager plugin, and the .NET Framework components.
You have installed the OceanBase Connector/ODBC driver.
Note
Download the Windows version installation package of the OceanBase ODBC driver from the Drivers and Middleware section on the Resources > Download Center > Enterprise Edition page of the OceanBase official website. The Windows version OceanBase Connector/ODBC driver installation package is one-click deployment; install it according to the default instructions.
Procedure
Note
The steps provided in this topic are for compiling and running the project using Visual Studio Community 2019 in a Windows environment. If you are using a different operating system or compiler, the steps may vary slightly.
- Open the
sqlsugar-oceanbase-odbcproject. - Install the SqlSugar-related packages.
- Configure the data source.
- Modify the data source in the
sqlsugar-oceanbase-odbcproject. - Build the project.
- Run the application.
- View the output results.
Step 1: Open the sqlsugar-oceanbase-odbc project
Start Visual Studio Community 2019.
Open an existing project.
On the start screen of Visual Studio Community 2019, click Open Project or Solution (P) below Get Started. Alternatively, on the start screen, click Continue without code (W) below Get Started; then select File > Open > Project/Solution (P) from the top menu bar.
Navigate to the folder containing the sqlsugar-oceanbase-odbc project, select the project file (
sqlsugar-oceanbase-odbc.slnorsqlsugar-oceanbase-odbc.csproj), and click Open.
Step 2: Install the SqlSugar-related packages
On the start screen of Visual Studio Community 2019, select Tools > NuGet Package Manager > Manage NuGet Packages for Solution from the top menu bar.
Enter
sqlsugarin the search box under Browse and download the SqlSugar package.Enter
sqlsugar.odbcin the search box under Browse and download the SqlSugar.Odbc and SqlSugar.OdbcCore packages.After installation is complete, you can use the relevant namespaces and types of SqlSugar in the project's code files.
Step 3: Configure the data source
Log in to the OceanBase Cloud console. On the instance list page, expand the target instance's information, and under the target tenant, select Connect > Get connection string.
For more detailed operations, see Get connection string.
Fill in the corresponding information in the URL below based on the created OceanBase Cloud database.
Example:
obclient -h t********.********.oceanbase.cloud` -P1521 -u oracle001 -p******Parameter description:
-h: The connection address of the OceanBase Cloud database, for example,t********.********.oceanbase.cloud.-P: The connection port of the OceanBase Cloud database. Default is 1521.-u: The account for accessing the database.-p: The password of the account.
For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.
Check whether the OceanBase Connector/ODBC driver is installed successfully.
Select Control Panel > System and Security > Administrative Tools > ODBC Data Sources (64-bit) > Drivers. The OceanBase Connector/ODBC driver is displayed here after successful installation.
Create a new data source.
Select User DSN > Add > Select Driver, then click Complete.
In the window that appears, enter the data source name Name and description Description, then click Next.
In the window that appears, enter the database connection information. You can also set initialization statements, TLS, cursors, and result sets by clicking Next.
Note
After entering the database connection information, you can click Test DSN to check whether the data source can be connected successfully.
After setting is complete, click Finish. The data source is added successfully. Click OK.
Step 4: Modify the data source in the sqlsugar-oceanbase-odbc project
Modify the data source in the Program.cs file based on the data source created in Step 3: Configure a data source.
Example:
Replace your_dsn with the data source test_oboracle created in Step 3: Configure a data source.
public static string ConnectionString = "DSN=test_oboracle";
Step 5: Build the project
Select the Build menu and then select Build Solution. The compiler output and any error or warning messages are displayed during the build process.
Step 6: Run the application
Select the Debug menu and then choose Start Debugging or Start Execution (No Debugging) to run the application.
Step 7: View the output results
The output results are displayed in the debug console. You can determine how to process the output based on the program's design logic and code.
Sample project overview
Click sqlsugar-oceanbase-odbc to download the project code, which is a compressed file named sqlsugar-oceanbase-odbc.zip.
After decompressing it, you will get a folder named sqlsugar-oceanbase-odbc. The directory structure is as follows:
sqlsugar-oceanbase-odbc
├─ Program.cs
├─ sqlsugar-oceanbase-odbc.csproj
└─ sqlsugar-oceanbase-odbc.sln
File description:
Program.cs: This is the main program file of the project, containing the entry point of the project, the source code for defining the data table structure, and implementing data table operations.sqlsugar-oceanbase-odbc.csproj: The main project file for Visual Studio, used to define the project's configuration, dependencies, and build information.sqlsugar-oceanbase-odbc.sln: The solution file for Visual Studio, containing information about the project and its related projects, used for unified management and building multiple projects.
Code introduction for the Program.cs file
The Program.cs code demonstrates how to use SqlSugar to operate OceanBase Cloud, and implements basic operations such as creating tables, inserting data, and querying data. Additionally, event callback functions like log recording are set up through the AopEvents object, facilitating debugging and troubleshooting.
The code in the Program.cs file mainly includes the following parts:
Namespace references.
Use the
usingkeyword to reference the namespacesSystem,SqlSugar,System.Linq,System.Data, andSystem.Data.Odbc, to access relevant data access classes and database operation classes and methods.Code:
using System; using SqlSugar; using System.Linq; using System.Data; using System.Data.Odbc;Define a namespace to place related classes and methods.
Define a namespace named
GbaseTest. Within this namespace, define two classes:ProgramandTestEntity.Code:
namespace GbaseTest { internal class Program { ... ... } public class TestEntity { ... ... } }Define a class named
Program, containing the main logic of the program.Define a class named
Program, containing the main logic of the program. The specific steps are as follows:Define a public static string variable
ConnectionStringto store the database connection string. This variable can be accessed anywhere in the current assembly. Here,DSN=your_dsnindicates using the ODBC connection method, with a data source namedyour_dsn.Note
When running the program, replace
your_dsnwith the name of the data source you created in Step 3.Main method: The entry point of the program, containing its main logic.
try-catchblock:- Use the
trykeyword to enclose code that may throw exceptions, such asusingstatements and database operations. - Use the
catchkeyword to catch and handle exceptions.
- Use the
- Finally, output
Program End.before the program terminates.
Code:
internal class Program { public static string ConnectionString = "DSN=your_dsn"; static void Main(string[] args) { try { using (...) { //Database operations } } catch (Exception ex) { ... ... } Console.WriteLine("Program End."); } }usingstatement.Use the
ConnectionConfigto configure database connection information and other options, including setting the database type to ODBC, the connection string toConnectionString, the primary key type toAttribute, enabling automatic connection closing, and configuring a log execution event callback function. You can use theSqlSugarClientobject to conveniently perform database operations, such as creating tables, inserting data, and querying data. Using theusingstatement ensures related resources are automatically released, including closing the database connection. The specific steps are as follows:Create a
SqlSugarClientobject and assign it to a variable nameddb.Create a connection configuration instance and initialize its properties using the object initializer, including
DbType,ConnectionString,InitKeyType,IsAutoCloseConnection, andAopEvents.Set the database type to ODBC.
Set the connection string to
ConnectionString.Set the primary key type to
Attribute.Enable automatic connection closing.
Configure the
AopEventslog execution event callback function. Implement the callback function's logic, wheresqlrepresents the executed SQL statement, andprepresents the parameter list in the SQL statement. In the implementation, output the SQL statement and parameter list to the console.
Code:
using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = SqlSugar.DbType.Odbc, ConnectionString = ConnectionString, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true, AopEvents = new AopEvents { OnLogExecuting = (sql, p) => { Console.WriteLine(sql); Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value))); } } }))Database operations.
Use the
SqlSugarClient.Ado.ExecuteCommandmethod to execute SQL statements for creating a table and inserting data. Use theSqlSugarClient.Ado.SqlQuerymethod to query data and iterate through the query results to output them. You can use theSqlSugarClientobject to conveniently perform database operations, such as creating tables, inserting data, and querying data. The specific steps are as follows:Output a prompt indicating the start of table creation.
Execute an SQL statement to create a table named
test_tbl1, which contains two columns:idandname.Output a prompt indicating the start of data insertion.
Define a variable
tableNameand assign it the valuetest_tbl1, which stores the table name.Execute an SQL statement to insert data into the table named
test_tbl1. Insert three records withidvalues1,2, and3, andnamevaluesJohn,Jack, andAmyrespectively.Output a prompt indicating the start of data querying.
Execute an SQL statement to query all data from the table named
test_tbl1and assign the query result to the variabledata, whereTestEntityis the entity type of the query result.Output a prompt indicating the start of iterating through the query result.
Iterate through the query result and assign each record to the variable
item. Output theidandnamefields of each record.
Code:
{ Console.WriteLine("Create Table:"); db.Ado.ExecuteCommand("CREATE TABLE test_tbl1(id NUMBER PRIMARY KEY, name VARCHAR2(50))"); Console.WriteLine("Insert Data:"); string tableName = "test_tbl1"; int intValue = db.Ado.ExecuteCommand($"INSERT INTO {tableName} (id, name) VALUES (1, 'John'), (2, 'Jack'), (3, 'Amy')"); Console.WriteLine("Query Data"); var data = db.Ado.SqlQuery<TestEntity>($"SELECT * FROM {tableName}"); Console.WriteLine("Query Results:"); foreach (var item in data) { Console.WriteLine($"id: {item.id}, name: {item.name}"); } }Use the
catchkeyword to catch and handle exceptions.When an exception occurs in the program, information related to the exception is output. By catching exceptions and outputting this information, you can help debug and locate issues, which contributes to the robustness and reliability of the program. The specific steps are as follows:
Use the
catchkeyword to catch the exception and assign the caught exception object to the variableex. This exception object is an instance of theExceptionclass or a subclass thereof.Output a prompt message indicating that an exception occurred in the program.
Convert the caught exception object into a string and output it. This string contains information such as the exception type, message, and stack trace.
Sample code:
catch (Exception ex) { Console.WriteLine("Program Exception:"); Console.WriteLine(ex.ToString()); }Define the
TestEntityclass.Define a class named
TestEntitywith two properties,idandname, representing theidandnamefields of an entity object. The specific steps are as follows:Define a public class named
TestEntity.Define a public integer property
idto represent theidfield of the entity object. You can read from and set this field using the property'sgetandsetmethods.Define a public string property
nameto represent thenamefield of the entity object. You can read from and set this field using the property'sgetandsetmethods.
Sample code:
public class TestEntity { public int id { get; set; } public string name { get; set; } }
Complete sample code
using System;
using SqlSugar;
using System.Linq;
using System.Data;
using System.Data.Odbc;
namespace GbaseTest
{
internal class Program
{
public static string ConnectionString = "DSN=your_dsn";
static void Main(string[] args)
{
try
{
using (SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
{
DbType = SqlSugar.DbType.Odbc,
ConnectionString = ConnectionString,
InitKeyType = InitKeyType.Attribute,
IsAutoCloseConnection = true,
AopEvents = new AopEvents
{
OnLogExecuting = (sql, p) =>
{
Console.WriteLine(sql);
Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
}
}
}))
{
Console.WriteLine("Create Table:");
db.Ado.ExecuteCommand("CREATE TABLE test_tbl1(id NUMBER PRIMARY KEY, name VARCHAR2(50))");
Console.WriteLine("Insert Data:");
string tableName = "test_tbl1";
int intValue = db.Ado.ExecuteCommand($"INSERT INTO {tableName} (id, name) VALUES (1, 'John'), (2, 'Jack'), (3, 'Amy')");
Console.WriteLine("Query Data");
var data = db.Ado.SqlQuery<TestEntity>($"SELECT * FROM {tableName}");
Console.WriteLine("Query Results:");
foreach (var item in data)
{
Console.WriteLine($"id: {item.id}, name: {item.name}");
}
}
}
catch (Exception ex)
{
Console.WriteLine("Program Exception:");
Console.WriteLine(ex.ToString());
}
Console.WriteLine("Program End.");
}
}
public class TestEntity
{
public int id { get; set; }
public string name { get; set; }
}
}
References
For more information about how to connect to OceanBase Cloud, see Overview.
For more information about OceanBase Connector/ODBC, see OceanBase Connector/ODBC.