Use SqlSugar to connect to OceanBase Cloud

This topic describes how to use the SqlSugar framework, OceanBase Connector/ODBC, and OceanBase Cloud to build an application that can perform basic operations such as creating tables, inserting data, and querying data.

Download the sqlsugar-oceanbase-odbc sample project Use SqlSugar to connect to OceanBase Cloud

Prerequisites

• You have registered an OceanBase Cloud account, created an instance and an Oracle-compatible tenant. For more information, see Create an instance and Create a tenant.

• You have installed Visual Studio, the NuGet package manager plug-in, and the .NET Framework components.

• You have installed the OceanBase Connector/ODBC driver.

  Note

  Download the OceanBase ODBC driver for Windows from the Middleware section on the Download Center of the OceanBase Cloud website. The OceanBase Connector/ODBC driver for Windows is a one-click installation package. You can install it by following the default instructions.

Procedure

1. Open the sqlsugar-oceanbase-odbc project.
2. Install the required packages for SqlSugar.
3. Configure the data source.
4. Modify the data source in the sqlsugar-oceanbase-odbc project.
5. Build the project.
6. Run the application.
7. View the output.

Step 1: Open the sqlsugar-oceanbase-odbc project

1. Start Visual Studio Community 2019.

2. Open an existing project.

   1. On the start page of Visual Studio Community 2019, click File > Open > Project/Solution (P). Or on the start page of Visual Studio Community 2019, click Open below Get Started.

   2. Browse to the folder of the sqlsugar-oceanbase-odbc project, select the project file (sqlsugar-oceanbase-odbc.sln or sqlsugar-oceanbase-odbc.csproj), and then click Open.

Step 2: Install the SqlSugar package

1. On the start page of Visual Studio Community 2019, select Tools > NuGet Package Manager > Manage NuGet Packages for Solution.

2. In the search box under Browse, enter sqlsugar and then download the SqlSugar package.

3. In the search box under Browse, enter sqlsugar.odbc and then download the SqlSugar.Odbc and SqlSugar.OdbcCore packages.

4. After the installation is complete, you can use the SqlSugar namespace and type in the code files of the project.

Step 3: Configure the data source

1. Log in to the OceanBase Cloud console. On the instance list page, expand the information of the target instance, go to the target tenant, and choose Connect > Get Connection String.

   For more information, see Get the connection string.

2. Fill in the following URL with the information of the created OceanBase Cloud database.

   Here is an example:

   obclient -h t********.********.oceanbase.cloud` -P1521 -u oracle001 -p******
   

   Parameter description:

   • -h: the endpoint of the OceanBase Cloud database, for example, t********.********.oceanbase.cloud.
   • -P: the port of the OceanBase Cloud database, which is 1521 by default.
   • -u: the username for accessing the database.
   • -p: the password.

   For more information, see Connect to an OceanBase tenant by using OBClient.

3. Check whether the OceanBase Connector/ODBC driver is installed.

   Choose Control Panel > System and Security > Administrative Tools > ODBC Data Sources (64-bit) > Drivers. If the OceanBase Connector/ODBC driver is installed, it is displayed here.

4. Create a new data source.

   Choose User DSN > Add > Select Driver, and then click Complete.

5. In the dialog box that appears, specify the data source name Name and the description Description, and then click Next.

   

6. In the dialog box that appears, specify the database connection information. You can also click Next to set the initialization statement, TLS, cursor, and result set.

   

   Note

   After you specify the database connection information, you can click Test DSN to check whether the data source is connected.

7. Click Finish after the settings are completed. The data source is added. 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 the data source.

Here is an example:

Replace your_dsn with the data source test_oboracle created in Step 3: Configure the data source.

public static string ConnectionString = "DSN=test_oboracle";

Step 5: Build the project

Choose Build > Build Solution. The compiler output and any error or warning information are displayed during the build.

Step 6: Run the application

Choose Debug > Start Debugging or Start Execution (No Debugging) to run the application.

Step 7: View the output

The output will be displayed in the Debug console. You can determine how to handle the output based on the program's design logic and code.

Project code

Click sqlsugar-oceanbase-odbc to download the project code. The file is named sqlsugar-oceanbase-odbc.zip.

After decompressing the file, you will find 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 and source code files that define the structure of the data table and implement data table operations.

• sqlsugar-oceanbase-odbc.csproj: This is the main project file for Visual Studio, used to define the project's configuration, dependencies, and build information.

• sqlsugar-oceanbase-odbc.sln: This is the solution file for Visual Studio, containing information about the project and its related projects. It is used for unified management and building of multiple projects.

Program.cs file code

The Program.cs file shows how to use SqlSugar to operate on the database of OceanBase Cloud, and implements basic operations such as creating tables, inserting data, and querying data. It also sets up event callback functions for logging through the AopEvents object, which facilitates debugging and troubleshooting.

The Program.cs file in this topic contains the following parts:

1. Namespace references.

   The using keyword is used to reference the System, SqlSugar, System.Linq, System.Data, and System.Data.Odbc namespaces, which are used to reference related data access classes and methods for operating on the database.

   Code:

   using System;
   using SqlSugar;
   using System.Linq;
   using System.Data;
   using System.Data.Odbc;
   

2. Define a namespace to store related classes and methods.

   A namespace named GbaseTest is defined. In this namespace, the Program and TestEntity classes are defined.

   Code:

   namespace GbaseTest
   {
       internal class Program
       {
           ...
           ...
       }
   
       public class TestEntity
       {
           ...
           ...
       }
   }
   

3. Define a class named Program that contains the main logic of the program.

   A class named Program is defined, which contains the main logic of the program. The steps are as follows:

   1. Define a public static string variable named ConnectionString to store the database connection string. This variable can be accessed anywhere in the current assembly. DSN=your_dsn indicates that the ODBC connection method is used, and the data source named your_dsn is used.

      Notice

      When you run the program, replace your_dsn with the data source name that you created in Step 3.

   2. Main method: The entry point of the program, which contains the main logic.

      1. try-catch block:
         1. Use the try keyword to wrap a block of code that may throw an exception, including using statements and database operations.
         2. Use the catch keyword to catch and handle exceptions.
      2. Finally, output Program End. before the program ends.

   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.");
           }
       }
   

4. using statement.

   The ConnectionConfig statement is used to configure the database connection information and other options, including the database type as ODBC, the connection string as ConnectionString, the primary key type as Attribute, automatic connection closure, and the log execution event callback function. The SqlSugarClient object can be used to conveniently execute database operations, including creating tables, inserting data, and querying data. The using statement automatically releases related resources, including closing the database connection. The steps are as follows:

   1. Create a SqlSugarClient object and assign it to the db variable.

   2. Create a connection configuration instance and use an object initializer to initialize the DbType, ConnectionString, InitKeyType, IsAutoCloseConnection, and AopEvents properties.

      1. Set the database type to ODBC.

      2. Set the connection string to ConnectionString.

      3. Set the primary key type to Attribute.

      4. Set automatic connection closure.

      5. Set the log execution event callback function of AopEvents. The implementation logic of the log execution event callback function is set, where sql indicates the executed SQL statement and p indicates the parameter list of the SQL statement. In the implementation logic, the SQL statement and parameter list are output 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)));
                           }
                       }
                   }))
   

5. Database operations.

   The SqlSugarClient.Ado.ExecuteCommand method is used to execute SQL statements to create tables and insert data. The SqlSugarClient.Ado.SqlQuery method is used to query data, and the query results are traversed and output. The SqlSugarClient object can be used to conveniently execute database operations, including creating tables, inserting data, and querying data. The steps are as follows:

   1. Output a prompt message indicating the start of table creation.

   2. Execute an SQL statement to create a table named test_tbl1 with two columns, id and name.

   3. Output a prompt message indicating the start of data insertion.

   4. Define a variable named tableName and assign it the value test_tbl1, which is used to store the table name.

   5. Execute an SQL statement to insert data into the test_tbl1 table. Three records are inserted, with id values of 1, 2, and 3, and name values of John, Jack, and Amy.

   6. Output a prompt message indicating the start of data query.

   7. Execute an SQL statement to query all data from the test_tbl1 table and assign the query results to the data variable. TestEntity is the entity type of the query results.

   8. Output a prompt message indicating the start of traversal of the query results.

   9. Traverse the query results and assign each record to the item variable. Output the id and name fields 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}");
                       }
                   }
   

6. Use the catch keyword to catch and handle exceptions.

   When an exception occurs in the program, the related information of the exception is output. By catching and outputting the exception information, you can debug and locate the problem, which ensures the robustness and reliability of the program. The steps are as follows:

   1. Use the catch keyword to catch the exception and assign the captured exception object to the ex variable. The exception object is an instance of the Exception class or a subclass thereof.

   2. Output a prompt message indicating that an exception occurs in the program.

   3. Convert the captured exception object to a string and output it. The string contains information such as the type, message, and stack trace of the exception.

   Code:

               catch (Exception ex)
               {
                   Console.WriteLine("Program Exception:");
                   Console.WriteLine(ex.ToString());
               }
   

7. Define a class named TestEntity.

   Define a public class named TestEntity with two properties, id and name, which represent the id and name fields of the entity object. The steps are as follows:

   1. Define a public class named TestEntity.

   2. Define a public integer property named id to represent the id field of the entity object. The get and set methods of the property can be used to read and set the field.

   3. A public string type attribute name is defined to represent the name field of the entity object. You can read and set the field by using the get and set methods of the attribute.

   Sample code:

       public class TestEntity
       {
           public int id { get; set; }
           public string name { get; set; }
       }
   

Full 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 of connection methods.

• For more information about OceanBase Connector/ODBC, see OceanBase Connector/ODBC.