Connect to OceanBase Cloud by using SqlSugar

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

Decompress the package to obtain 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

The files and directories are described as follows:

• 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 Cloud, 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.

Perform the following steps to configure the Program.cs file:

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 application.

   Perform the following steps:

   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 application, 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 application. It contains the main logic of the application.

      1. 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 application exits.

   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. Use 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 conveniently 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. Perform the following steps:

   1. Create an 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. Perform the following steps:

   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, and assign the value test_tbl1 to the variable.

   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 application, information about the exceptions will be output. The exceptions, once captured, will help debug and locate problems, ensuring the robustness and reliability of the application. Perform the following steps:

   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 application.

   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. Perform the following steps:

   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 the 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

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; }
    }
}