.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);
}
}
}
}
执行结果如下: