Connect to OceanBase Database by using SqlSugar

This topic introduces how to build an application by using the SqlSugar framework, OceanBase Connector/ODBC, and OceanBase Database. It also covers the use of the application for fundamental database operations, including table creation, data insertion, and data query.

Download the sqlsugar-oceanbase-odbc sample project

Prerequisites

• You have installed OceanBase Database and created an Oracle tenant. For more information about how to install OceanBase Database, see Deployment overview.

• You have installed Visual Studio, with the NuGet Package Manager and .NET Framework components being enabled.

• You have installed the OceanBase Connector/ODBC driver.

  Note

  You can download the installation package of OceanBase Connector/ODBC for Windows from OceanBase Download Center. To install OceanBase Connector/ODBC for Windows, follow the default instructions.

Procedure

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

Step 1: Open the sqlsugar-oceanbase-odbc project

1. Start Visual Studio Community 2019.

2. Open an existing project.

   1. In the start window of Visual Studio Community 2019, click Open a project or solution under Get started. Alternatively, click Continue without code in the lower right corner, and choose File > Open > Project/Solution in the menu bar of the page that appears.

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

Step 2: Install SqlSugar-related packages

1. On the top menu bar of Visual Studio Community 2019, choose Tools > NuGet Package Manager > Manage NuGet Packages for Solution.

2. On the Browse tab, enter sqlsugar in the search box to search for and download the SqlSugar package.

3. On Browse tab, enter sqlsugar.odbc in the search box to search for and download the SqlSugar.Odbc and SqlSugar.OdbcCore packages.

4. After installation, you can use the relevant namespaces and types of SqlSugar in the project code files.

Step 3: Configure a data source

1. Obtain the database connection information.

   Contact the deployment personnel or administrator of OceanBase Database to obtain the database connection string.

   Here is an example:

   obclient -hxxx.xxx.xxx.xxx -P2881 -usys@oracle001 -p******
   

   where

   • -h specifies the IP address for connecting to OceanBase Database. For connection through OceanBase Database Proxy (ODP), this parameter is the IP address of an ODP. For direct connection, this parameter is the IP address of an OBServer node.
   • -P specifies the port for connecting to OceanBase Database. For connection through ODP, the default value is 2883, which can be customized when ODP is deployed. For direct connection, the default value is 2881, which can be customized when OceanBase Database is deployed.
   • -u specifies the tenant account. For connection through ODP, the tenant account can be in the username@tenant name#cluster name or cluster name:tenant name:username format. For direct connection, the tenant account is in the username@tenant name format.
   • -p specifies the account password.

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

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

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

3. Create a data source.

   Choose User DSN > Add, select the driver, and click Finish.

4. In the window that appears, enter the data source name in Name and description in Description, and then click Next.

5. Enter the database connection information in the pop-up window, and click Next. You can also set the initialization statement, TLS, cursor, and result set.

   Note

   After you fill in the database connection information, you can click Test DSN to check whether the data source can be connected successfully.

6. Click Finish. The data source is added. Then, 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 that you created in Step 3: Configure a data source.

public static string ConnectionString = "DSN=test_oboracle";

Step 5: Build the project

Choose Build > Build Solution. The output of the compiler and errors or warning messages if any are displayed during the build process.

Step 6: Run the application

Choose Debug > Start Debugging or choose Debug > Start Without Debugging to run the application.

Step 7: View the output

The output is displayed in the debug console. You can determine how to handle the output based on the design logic and code of the program.

Project code introduction

Click sqlsugar-oceanbase-odbc to download the project code, which is a compressed file named sqlsugar-oceanbase-odbc.zip.

After decompressing it, 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

Here is a breakdown of the files and directories:

• Program.cs: the main program file of the project, which contains the entry to the project and the source code that defines the data table and implements data table operations.

• sqlsugar-oceanbase-odbc.csproj: the main project file in Visual Studio, which defines the configurations, dependencies, and build information of the project.

• sqlsugar-oceanbase-odbc.sln: the solution file in Visual Studio, which contains information about the project and its related projects for unified management and build of multiple projects.

Code in Program.cs

Code in the Program.cs file shows how to use SqlSugar to operate OceanBase Database, such as creating tables, inserting data, and querying data. Besides, the AopEvents object is used to set the callback function for events such as log execution, which facilitates debugging and troubleshooting.

To configure the Program.cs file, perform the following steps:

1. Reference namespaces.

   Use the using keyword to reference the System, SqlSugar, System.Linq, System.Data, and System.Data.Odbc namespaces, so that related data access classes and database operation classes and methods can be referenced.

   The sample code is as follows:

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

2. Define the namespace for placing related classes and methods.

   Define the GbaseTest namespace, in which the Program and TestEntity classes are defined.

   The sample code is as follows:

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

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

   The steps are as follows:

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

      Notice

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

   2. Define the Main method as the entry to the program. It contains the main logic of the program.

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

   The sample code is as follows:

       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. Define the using statement.

   Use ConnectionConfig to configure the database connection information and other options, including the database type ODBC, the connection string ConnectionString, the primary key type Attribute, automatic connection closing, and the callback function for log execution events. Use the SqlSugarClient object to perform database operations, including creating tables, inserting data, and querying data. Use the using statement to automatically release related resources, including closing the database connection. The steps are as follows:

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

   2. Create a connection configuration instance and use the object initializer to initialize its attributes such as DbType, ConnectionString, InitKeyType, IsAutoCloseConnection, and AopEvents.

      1. Set the database type to ODBC.

      2. Set the connection string to ConnectionString.

      3. Set the primary key type to Attribute.

      4. Enable automatic connection closing.

      5. Use AopEvents to set the callback function for log execution events. Set the implementation logic for the callback function, in which sql indicates the executed SQL statement and p indicates the list of parameters in the SQL statement. In the implementation logic, the SQL statement and its parameter list are printed to the console.

   The sample code is as follows:

                   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. Perform database operations.

   Call the SqlSugarClient.Ado.ExecuteCommand method to execute SQL statements that create tables and insert data, and call the SqlSugarClient.Ado.SqlQuery method to query data and traverse query results. Use the SqlSugarClient object to conveniently perform database operations, including creating tables, inserting data, and querying data. The steps are as follows:

   1. Output a prompt to indicate that table creation starts.

   2. Execute an SQL statement to create the test_tbl1 table that contains two columns: id and name.

   3. Output a prompt to indicate that data insertion starts.

   4. Define the tableName variable to store a table name. Assign the test_tbl1 value to it.

   5. Execute an SQL statement to insert three records into the test_tbl1 table: 1, 'John', 2, 'Jack', and 3, 'Amy'.

   6. Output a prompt to indicate that data query starts.

   7. Execute an SQL statement to query all data in the test_tbl1 table, and assign the query results to the data variable. TestEntity is the entity class of the query result.

   8. Output a prompt to indicate that query result traversal starts.

   9. Traverse the query results and assign each record to the item variable. Output the id and name fields for each record.

   The sample code is as follows:

                   {
                       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 capture exceptions and handle them.

   When exceptions occur in the program, information about the exceptions will be output. The exceptions, once captured, will help debug and locate problems, ensuring the robustness and reliability of the program. The steps are as follows:

   1. Use the catch keyword to capture an exception and assign the exception to the ex variable, which is an instance of the Exception class or one of its subclasses.

   2. Output a prompt to indicate that an exception occurs in the program.

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

   The sample code is as follows:

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

7. Define the TestEntity class.

   Define the TestEntity class with two attributes: id and name, which represent the id and name fields of an entity object. The steps are as follows:

   1. Define a public class named TestEntity.

   2. Define a public integer attribute named id, which represents the id field of an entity object. This field can be read and set by calling the get and set methods of the attribute.

   3. Define a public string attribute named name, which represents the name field of an entity object. This field can be read and set by calling the get and set methods of the attribute.

   The sample code is as follows:

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

Complete code examples

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 Database, see Overview of connection methods.

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