数据库测试

连接数据库

连接串参数配置

本次新增了连接串参数DBCompatibility，用来控制返回数据库对象字段名是否为大写，目前只支持“Oracle”和“PostgreSQL”两个配置项，默认为“PostgreSQL”。例如，需将返回结果中字段名转换成大写，则在连接串中配置：DBCompatibility=Oracle；无需转换成大写，则无需配置该参数或在连接串中配置：DBCompatibility=PostgreSQL。

连接示例

以下C#代码显示如何连接到现有的数据库postgres。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Text;
using Npgsql;

namespace Vastba、se
{
    class TestConnenct
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Connect Vastbase G100 test!");
            string connString = "Host=172.16.12.65;Port=25432;Username=dmuser;Password=Aa123456;Database=postgres;DBCompatibiity=PostgreSQL";
            var conn = new NpgsqlConnection(connString);
            // 打开数据库连接
            conn.Open();
            Console.WriteLine("Connect successful.");
            // 关闭数据库连接
            conn.Close();
        }
    }
}

执行结果为：

创建表

以下C#程序用之前建立的连接在数据库postgres中创建一张表：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Text;
using Npgsql;

namespace Vastbase
{
      class TestConnenct
      {
          static void Main(string[] args)
          {
              Console.WriteLine("Connect Vastbase G100 test!");
              string connString = "Host=172.16.12.65;Port=25432;Username=dmuser;Password=Aa123456;Database=postgres";
              var conn = new NpgsqlConnection(connString);
              // 打开数据库连接
              conn.Open();
              Console.WriteLine("Connect successful.");
              string creatSQL = "create table test_npgsql(id int, name text)";
              ExecNonQuery(creatSQL, conn);
              // 关闭数据库连接
              conn.Close();
          }
          static int ExecNonQuery(string _SQLCommand, NpgsqlConnection _conn)
          {
              int res = 0;
              NpgsqlCommand cmd = new NpgsqlCommand(_SQLCommand, _conn);
              cmd.CommandType = CommandType.Text;
              res = cmd.ExecuteNonQuery();
              cmd.Dispose();
              return res;
          }
      }
}

执行上述程序后，它将在postgress中创建一张test_npgsql表：

INSERT操作

以下C#程序显示了如何在上述示例中创建的test_npgsql表中创建/插入记录：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Text;
using Npgsql;

namespace Vastbase
{
    class TestConnenct
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Connect Vastbase G100 test!");
            string connString = "Host=172.16.12.65;Port=25432;Username=dmuser;Password=Aa123456;Database=postgres";
            var conn = new NpgsqlConnection(connString);
            // 打开数据库连接
            conn.Open();
            Console.WriteLine("Connect successful.");
            string insertSQL = "insert into test_npgsql(id, name) values (1, 'Amy'), (2, 'Mike'), (3, 'Sophia'), (4, 'Jack')";
            ExecNonQuery(insertSQL, conn);
            // 关闭数据库连接
            conn.Close();
        }
        static int ExecNonQuery(string _SQLCommand, NpgsqlConnection _conn)
        {
            int res = 0;
            NpgsqlCommand cmd = new NpgsqlCommand(_SQLCommand, _conn);
            cmd.CommandType = CommandType.Text;
            res = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return res;
        }
    }
}

执行以上程序后，它将在test_npgsql表中插入给定的记录：

SELECT操作

以下C#程序显示了如何从上述示例中创建的test_npgsql表中获取和显示记录：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Text;
using Npgsql;

namespace Vastbase
{
    class TestConnenct
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Connect Vastbase G100 test!");
            string connString = "Host=172.16.12.65;Port=25432;Username=dmuser;Password=Aa123456;Database=postgres";
            var conn = new NpgsqlConnection(connString);
            // 打开数据库连接
            conn.Open();
            Console.WriteLine("Connect successful.");
            var queryCmd = new NpgsqlCommand("select * from test_npgsql", conn);
            var reader = queryCmd.ExecuteReader();
            while (reader.Read())
                Console.WriteLine("id: " + reader.GetInt32(0) + ", name: " + reader.GetString(1));
            // 关闭数据库连接
            conn.Close();
        }
    }
}

程序成功执行输出如下：

UPDATE操作

以下C#代码显示了如何使用UPDATE语句来更新指定记录，然后从test_npgsql表中获取和显示更新的记录：

using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Text;
using Npgsql;

namespace Vastbase
{
    class TestConnenct
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Connect Vastbase G100 test!");
            string connString = "Host=172.16.12.65;Port=25432;Username=dmuser;Password=Aa123456;Database=postgres";
            var conn = new NpgsqlConnection(connString);
            // 打开数据库连接
            conn.Open();
            Console.WriteLine("Connect successful.");
            string updateSQL = "update test_npgsql set name = 'Emma' where id = 2";
            ExecNonQuery(updateSQL, conn);
            var queryCmd = new NpgsqlCommand("select * from test_npgsql", conn);
            var reader = queryCmd.ExecuteReader();
            while (reader.Read())
                Console.WriteLine("id: " + reader.GetInt32(0) + ", name: " + reader.GetString(1));

            // 关闭数据库连接
            conn.Close();
        }
        static int ExecNonQuery(string _SQLCommand, NpgsqlConnection _conn)
        {
            int res = 0;
            NpgsqlCommand cmd = new NpgsqlCommand(_SQLCommand, _conn);
            cmd.CommandType = CommandType.Text;
            res = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return res;
        }
    }
}

更新后的表数据如下：

DELETE操作

以下C#代码显示了如何使用DELETE语句删除指定记录，然后从test_npgsql表中获取并显示剩余的记录：

using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Text;
using Npgsql;

namespace Vastbase
{
    class TestConnenct
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Connect Vastbase G100 test!");
            string connString = "Host=172.16.12.65;Port=25432;Username=dmuser;Password=Aa123456;Database=postgres";
            var conn = new NpgsqlConnection(connString);
            // 打开数据库连接
            conn.Open();
            Console.WriteLine("Connect successful.");
            string deleteSQL = "delete from test_npgsql where id = 2";
            ExecNonQuery(deleteSQL, conn);
            var queryCmd = new NpgsqlCommand("select * from test_npgsql", conn);
            var reader = queryCmd.ExecuteReader();
            while (reader.Read())
                Console.WriteLine("id: " + reader.GetInt32(0) + ", name: " + reader.GetString(1));

            // 关闭数据库连接
            conn.Close();
        }
        static int ExecNonQuery(string _SQLCommand, NpgsqlConnection _conn)
        {
            int res = 0;
            NpgsqlCommand cmd = new NpgsqlCommand(_SQLCommand, _conn);
            cmd.CommandType = CommandType.Text;
            res = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return res;
        }
    }
}

删除后的表数据如下：