DM8达梦数据库.Net Data Provider

.NET Data Provider 是 .NET Framework 编程环境下的数据库用户访问数据库的编程接口，用于连接到数据库、执行命令和检索结果。在数据源和代码之间创建了一个最小层，以便在不以功能为代价的前提下提高性能。

开发环境搭建

软件	版本
DM 数据库	DM 8.0 及以上版本
VS	2012

加载驱动

在 c# 项目中，通过添加引用的方式，加载 DM provider 驱动，如下图所示：

驱动加载

数据库连接

DM provider 接口登录登出示例程序 provider_conn.c 如下：

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

namespace providertest
{
    class conn
    {
        static void Main(string[] args)
        {
            try
            {
                // 连接测试
                DmConnection conn = new DmConnection();
                conn.ConnectionString = "Server=192.168.104.21:51236; UserId=SYSDBA; PWD=SYSDBA;";
                conn.Open();
                Console.WriteLine("dmprovider: connect success!");
                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

执行结果如下：

开发示例

基本操作示例

DM provider 接口增、删、改、查四个基本操作，示例程序 provider_dml.c 如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dm;

namespace ConsoleApplication1
{
    class dml
    {
        static void Main(string[] args)
        {
            try
            {
                DmConnection conn = new DmConnection();
                conn.ConnectionString = "Server=192.168.104.21:51236; UserId=SYSDBA; PWD=SYSDBA;";
                conn.Open();

                //清理测试环境
                DmCommand cmd = new DmCommand();
                cmd.Connection = conn;
                cmd.CommandText = "delete from PRODUCTION.PRODUCT_CATEGORY";
                cmd.ExecuteNonQuery();

                //插入数据
                cmd.CommandText = "insert into PRODUCTION.PRODUCT_CATEGORY(NAME) values('语文'), ('数学'), ('英语'), ('体育')";
                cmd.ExecuteNonQuery();
                Console.WriteLine("dmprovider: insert success!");

                //更新数据
                cmd.CommandText = "delete from PRODUCTION.PRODUCT_CATEGORY where name='数学'";
                cmd.ExecuteNonQuery();
                Console.WriteLine("dmprovider: update success!");

                //删除数据
                cmd.CommandText = "update PRODUCTION.PRODUCT_CATEGORY set name = '英语-新课标' where name='英语'";
                cmd.ExecuteNonQuery();
                Console.WriteLine("dmprovider: delete success!");

                //查询数据
                cmd.CommandText = "select name from PRODUCTION.PRODUCT_CATEGORY";
                DmDataReader reader =(DmDataReader)cmd.ExecuteReader();
                while (reader.Read())
                {
                    string name = reader.GetString(0);
                    Console.WriteLine("name：" + name);
                }
                reader.Close();
                Console.WriteLine("dmprovider: select success!");

                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

执行结果如下：

绑定变量示例

DM provider 接口绑定变量示例程序 provider_bind.c 如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dm;

namespace provider_bind
{
    class bind
    {
        static void Main(string[] args)
        {
            try
            {
                DmConnection conn = new DmConnection();
                conn.ConnectionString = "Server=192.168.104.21:51236; UserId=SYSDBA; PWD=SYSDBA;";
                conn.Open();

                //清理测试环境
                DmCommand cmd = new DmCommand();
                cmd.Connection = conn;
                cmd.CommandText = "delete from PRODUCTION.PRODUCT_CATEGORY";
                cmd.ExecuteNonQuery();

                //插入数据
                cmd.CommandText = "insert into PRODUCTION.PRODUCT_CATEGORY(name) values(:s_name)";
                var para1 = new DmParameter(":s_name", DmDbType.VarChar, 50);
                cmd.Parameters.Add(para1);
                para1.Value = "物理";
                cmd.ExecuteNonQuery();
                Console.WriteLine("dmprovider: insert with bind success!");

                //查询数据
                cmd.Parameters.Clear();
                cmd.CommandText = "select name from PRODUCTION.PRODUCT_CATEGORY";
                DmDataReader reader = (DmDataReader)cmd.ExecuteReader();
                while (reader.Read())
                {
                    string name = reader.GetString(0);
                    Console.WriteLine("name：" + name);
                }
                reader.Close();

                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

执行结果如下：

大字段操作示例

DM provider 接口大字段操作包括大字段的插入，查询等。示例程序 provider_lob.c 如下：

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dm;

namespace provider_lob
{
    class lob
    {
        static void Main(string[] args)
        {
            try
            {
                DmConnection conn = new DmConnection();
                conn.ConnectionString = "Server=192.168.104.21:51236; UserId=SYSDBA; PWD=SYSDBA;";
                conn.Open();

                //清理测试环境
                DmCommand cmd = new DmCommand();
                cmd.Connection = conn;
                cmd.CommandText = "drop table if exists provider_demo";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "create table provider_demo(c1 int, c2 blob)";
                cmd.ExecuteNonQuery();

                // 读取文件数据，写入 lob 列
                FileInfo fi = new FileInfo(@"..\..\file\DM8_SQL.pdf");
                FileStream fs = fi.OpenRead();
                int nBytes = (int)fs.Length;
                byte[] dataArray = new byte[nBytes];
                fs.Read(dataArray, 0, nBytes);
                fs.Close();

                cmd.CommandText = "insert into provider_demo values(1, :blob)";
                DmParameter param1 = new DmParameter(":blob", DmDbType.Binary);
                cmd.Parameters.Add(param1);
                param1.Value = dataArray;
                cmd.ExecuteNonQuery();
                Console.WriteLine("dmprovider: write to col of lob success!");

                //读取 lob 列数据，写入到文件中
                DmBlob buffer;
                cmd.Parameters.Clear();
                cmd.CommandText = "select c2 from provider_demo";
                DmDataReader reader = (DmDataReader)cmd.ExecuteReader();
                reader.Read();
                buffer = reader.GetBlob(0);                   
                int length = buffer.Length();

                FileStream fs1 = new FileStream(@"..\..\file\DM8_SQL1.pdf", FileMode.Create);
                fs1.Write(buffer.GetBytes(1, length), 0, length);
                fs1.Flush();
                fs1.Close();
                Console.WriteLine("dmprovider: get data from col of lob success!");

                reader.Close();



                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

执行结果如下：

DM8达梦数据库.Net Data Provider

开发环境搭建

加载驱动

数据库连接

开发示例

基本操作示例

绑定变量示例

大字段操作示例

评论