Connecting to GBase 8s Database Using C# and ADO.NET on Windows (2)

In the previous article, we discussed how to install the CSDK, configure the connection, and perform basic database operations in a Windows environment. This article will further demonstrate how to manipulate the database using C# with code examples, helping you to efficiently manage and operate your database.

Link to the previous article: Connecting to GBase 8s Database via ADO.NET with C# on Windows (1)

Database Operations with C

Here, I am using Visual Studio 2022 Community Edition as the development tool.

1. Create and Configure a New Project

Open Visual Studio 2022, create a new project, and select the Console Application (.NET Framework). Click "Next".

Use .NET Framework 4.8 to create a console application, specify the project name as ConsoleApp1, and choose the location, etc.

2. Add the GBS.Data.GBasedbt.dll Reference

In the Solution Explorer, right-click on the "References" and choose "Add Reference". Browse and add the GBS.Data.GBasedbt.dll file.

In the example, the path is:

C:\Program Files\GBase Client-SDK\bin\netf40\GBS.Data.GBasedbt.dll

3. C# Code Example

using System;  
using GBS.Data.GBasedbt;  
using System.Data;  

namespace ConsoleApp1  
{  
    internal class Program  
    {  
        static void Main()  
        {  
            var constr = "database=testdb1;server=yangzai;host=192.168.137.66;service=6666;protocol=onsoctcp;uid=gbasedbt;pwd=111111;DB_LOCALE=zh_CN.utf8;CLIENT_LOCALE=zh_CN.utf8";  
            var conres = logon(constr);  

            if (conres != null)  
            {  
                execute_database(conres);  
                logoff(conres);  
            }            
        }  

        static IfxConnection logon(string constr)  
        {  
            var conn = new IfxConnection(constr);  
            try  
            {  
                conn.Open();  
                Console.WriteLine("Database connection successful!\n");  
                return conn;  
            }  
            catch (Exception ex)  
            {  
                Console.WriteLine("Database connection failed! " + ex.Message);  
                return null;  
            }  
        }  

        static void execute_database(IfxConnection conn)  
        {       
            IfxCommand ifxcmd = conn.CreateCommand();  
            ifxcmd.CommandText = "drop table if exists student";  
            ifxcmd.ExecuteNonQuery();  

            ifxcmd.CommandText = "create table student(num int,name varchar(100),class varchar(255))";  
            ifxcmd.ExecuteNonQuery();  

            ifxcmd.CommandText = "insert into student values (1001,'Xiao Zhang','Computer Science Class 3')";  
            ifxcmd.ExecuteNonQuery();  

            ifxcmd.CommandText = "insert into student values (1002,'Xiao Ming','Electronic Information Engineering Class 1')";  
            ifxcmd.ExecuteNonQuery();  

            ifxcmd.CommandText = "insert into student values (1003,'Xiao Li','Civil Engineering Class 4')";  
            ifxcmd.ExecuteNonQuery();  

            ifxcmd.CommandText = "update student set class = 'Journalism and Communication Class 2' where num = 1001";  
            ifxcmd.ExecuteNonQuery();  

            ifxcmd.CommandText = "delete from student where num = 1003";  
            ifxcmd.ExecuteNonQuery();  

            ifxcmd.CommandText = "select dbinfo('version','full') from dual";  
            IfxDataReader dr = ifxcmd.ExecuteReader();  

            if (dr.Read())  
            {  
                Console.WriteLine("Current database version: " + dr[0] + '\n');  
            }  

            IfxDataAdapter ifxadpt = new IfxDataAdapter("select * from student", conn);  
            DataSet ds = new DataSet();  
            ifxadpt.Fill(ds);  
            Console.WriteLine("Query results from the student table:\n");  
            int i = 0;  
            for (i = 0; i < ds.Tables.Count; i++)  
            {  
                var tb1 = ds.Tables[i];  
                var count = tb1.Rows.Count;  

                for (int r = 0; r < tb1.Rows.Count; r++)  
                {  
                    for (int c = 0; c < tb1.Columns.Count; c++)  
                    {  
                        var colname = tb1.Columns[c].ColumnName;  
                        var value = tb1.Rows[r].ItemArray[c];  
                        Console.WriteLine(colname + " " + value);  
                    }  
                    Console.Write("\n");  
                }  
                Console.WriteLine(count + " row(s) retrieved.\n");  
            }  
        }  

        static void logoff(IfxConnection conn)  
        {  
            try  
            {  
                conn.Close();  
                Console.WriteLine("Database connection closed successfully!\n");  
            }  
            catch (Exception ex)  
            {  
                Console.WriteLine("Failed to close database connection: " + ex.Message);  
            }  
        }  
    }  
}  

4. Program Output

Copy the above sample code into Program.cs and run the Debug test to check the database connection result.

Through this series of articles, you should have a more comprehensive understanding of how to use C# to interact with the GBase 8s database using ADO.NET. Mastering these techniques will enable you to build more efficient and reliable database applications. Thank you for reading!