下面给出示例，描述了如何通过 KingbaseES .NET Data Provider 操作 KingbaseES 数据库。
如下示例描述了如何建立与数据库的连接。
using System;
using System.Data;
using Kdbndp;
public class TestConnection
{
public static void Main(string[] args)
{
string connString = "Server=127.0.0.1;User Id=SYSTEM;
Password=MANAGER;Database=TEST;Port=54321";
KdbndpConnection conn = new KdbndpConnection(connString);
conn.Open();
conn.Close();
}
}
KdbndpCommand 示例(1)
如下示例描述了如何在 SCOTT 表中插入一条记录，再删除这条记录。
using System;
using System.Data;
using Kdbndp;
public static class TestCommand
{
public static void Main(String[] args)
{
string connString = "Server=127.0.0.1;User Id=SYSTEM;
Password=MANAGER;Database=TEST;Port=54321";
KdbndpConnection conn = new KdbndpConnection(connString);
conn.Open();
try
{
Int32 rowsaffected = 0;
/* 插入一条记录 */
KdbndpCommand cmd = new KdbndpCommand("insert into
SCOTT values(10, 100, 100)", conn);
rowsaffected = cmd.ExecuteNonQuery();
Console.WriteLine("It was added {0} lines in table
SCOTT", rowsaffected);
/* 删除一条记录 */
cmd.CommandText = "delete from
SCOTT where GRADE=10 and LOSAL=100 and HISAL=100;";
rowsaffected = cmd.ExecuteNonQuery();
Console.WriteLine("It was delete {0} lines in table
SCOTT", rowsaffected);
}
finally
{
conn.Close();
}
}
}
KdbndpCommand 示例(2)
如下示例描述了如何调用用户自定义函数，并将函数返回值显示在屏幕上。
using System;
using System.Data;
using Kdbndp;
/* 创建测试所用表和函数:
*
* CREATE OR REPLACE INTERNAL FUNCTION FUNCA()
* RETURNS int AS
* $BODY$
* DECLARE
* RET INT;
* BEGIN
* SELECT COUNT(*) INTO RET FROM SYS_CLASS;
* RETURN RET;
* END
* $BODY$
* LANGUAGE 'plsql';
*/
public static class TestFunction
{
public static void Main(String[] args)
{
string connString = "Server=127.0.0.1;User Id=SYSTEM;
Password=MANAGER;Database=TEST;Port=54321";
KdbndpConnection conn = new KdbndpConnection(connString);
try
{
conn.Open();
KdbndpCommand command = new KdbndpCommand("FUNCA", conn);
command.CommandType = CommandType.StoredProcedure;
Object result = command.ExecuteScalar();
Console.WriteLine(result);
}
catch(System.Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
conn.Close();
}
}
}
KdbndpParameter 示例(1)
如下示例描述了用户如何使用参数
using System;
using System.Data;
using Kdbndp;
public static class TestParameter
{
public static void Main(String[] args)
{
string connString = "Server=127.0.0.1;User Id=SYSTEM;
Password=MANAGER;Database=TEST;Port=54321";
KdbndpConnection conn = new KdbndpConnection(connString);
conn.Open();
/* 查询串中指定绑定参数 */
using(KdbndpCommand command = new KdbndpCommand("select *
from SCOTT where GRADE = :value1", conn))
{
/* 参数绑定指定绑定参数名和参数类型，并将参数添加到参数集合中。 */
command.Parameters.Add(new KdbndpParameter("value1",
DbType.Int32));
/* 绑定参数赋值。 */
command.Parameters[0].Value = 4;
using(KdbndpDataReader dr = command.ExecuteReader())
{
while(dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
Console.Write("{0} \t", dr[i]);
Console.WriteLine();
}
}
}
}
}
KdbndpParameter 示例(2)
如下示例描述了用户如何使用 KdbndpTypes 中的数据类型
using System;
using System.Data;
using Kdbndp;
using KdbndpTypes;
/* 注意添加该命名空间 */
/*
* 创建测试所用表和函数:
* CREATE TABLE TABLEB(C1 INT, C2 DOUBLE, C3 VARCHAR(50));
*/
public static class TestParameter
{
public static void Main(String[] args)
{
string connString = "Server=127.0.0.1;User Id=SYSTEM;
Password=MANAGER;Database=TEST;Port=54321";
KdbndpConnection conn = new KdbndpConnection(connString);
conn.Open();
KdbndpCommand command = new KdbndpCommand("INSERT INTO
TABLEB VAlUES(:COL1, :COL2, :COL3)", conn);
/* 参数绑定指定绑定参数名和参数类型，并将参数添加到参数集合中。 */
command.Parameters.Add(new KdbndpParameter("COL1",
KdbndpDbType.Integer));
command.Parameters.Add(new KdbndpParameter("COL2",
KdbndpDbType.Double));
command.Parameters.Add(new KdbndpParameter("COL3",
KdbndpDbType.Varchar));
/* 绑定参数赋值。 */
command.Parameters[0].Value = 100;
command.Parameters[1].Value = 12345.123456789;
command.Parameters[2].Value = "abcdABCD";
command.ExecuteNonQuery();
conn.Close();
}
}
如下示例查询 SCOTT 表中所有记录，并在屏幕上显示。
using System;
using System.Data;
using Kdbndp;
public static class TestDataReader
{
public static void Main(String[] args)
{
string connString = "Server=127.0.0.1;User Id=SYSTEM;
Password=MANAGER;Database=TEST;Port=54321";
KdbndpConnection conn = new KdbndpConnection(connString);
KdbndpCommand command = new KdbndpCommand();
KdbndpDataReader dr = null;
try
{
conn.Open();
command.CommandText = "select * from SCOTT";
command.Connection = conn;
dr = command.ExecuteReader();
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
Console.WriteLine(dr.GetValue(i));
}
}
catch (System.Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
dr.Close();
conn.Close();
}
}
}
如下示例描述了 Kdbndp 类库与 DataSet 类之间交互的方法。
using System;
using System.Data;
using Kdbndp;
public static class TestDataset
{
public static void Main(String[] args)
{
string connString = "Server=127.0.0.1;User Id=SYSTEM;
Password=MANAGER;Database=TEST;Port=54321";
KdbndpConnection conn = new KdbndpConnection(connString);
DataSet ds = new DataSet();
int i, j, k;
conn.Open();
KdbndpCommand command = new KdbndpCommand("select * from
sys_class", conn);
KdbndpDataAdapter da = new KdbndpDataAdapter(command);
da.Fill(ds);
for (i = 0; i < ds.Tables.Count; i++)
{
Console.WriteLine("Table " + i);
for (k = 0; k < ds.Tables[i].Columns.Count; k++)
{
if (k == ds.Tables[i].Columns.Count - 1)
Console.WriteLine(ds.Tables[i].Columns[k].
ColumnName);
else
Console.Write(ds.Tables[i].
Columns[k].ColumnName + "\t\t");
}
Console.WriteLine("------------------------------");
for (j = 0; j < ds.Tables[i].Rows.Count; j++)
{
DataRow dr = ds.Tables[i].Rows[j];
for (k = 0; k < ds.Tables[i].Columns.Count; k++)
{
if (k == ds.Tables[i].Columns.Count - 1)
Console.WriteLine(dr.ItemArray[k]);
else
Console.Write(dr.ItemArray[k] + "\t\t");
}
}
}
conn.Close();
}
}
如下示例描述了程序中的事务处理的使用方法。
using System;
using System.Data;
using Kdbndp;
/* 创建测试所用表和函数:
CREATE TABLE TABLEA(C1 INT, C2 VARCHAR(10));
INSERT INTO TABLEA VALUES(1, 'ABCD');
INSERT INTO TABLEA VALUES(2, 'abcdefgh');
CREATE OR REPLACE FUNCTION FUNC1()
RETURNS REFCURSOR
AS
DECLARE
KREF REFCURSOR;
BEGIN
OPEN KREF FOR SELECT * FROM TABLEA;
RETURN KREF;
END;
*/
public static class TestTransaction
{
public static void Main(String[] args)
{
string connString = "Server=127.0.0.1;User Id=SYSTEM;
Password=MANAGER;Database=TEST;Port=54321";
KdbndpConnection conn = new KdbndpConnection(connString);
conn.Open();
KdbndpTransaction tran = conn.BeginTransaction();
KdbndpCommand command = new KdbndpCommand("FUNC1", conn);
command.CommandType = CommandType.StoredProcedure;
KdbndpDataReader dr = command.ExecuteReader();
while(dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
Console.WriteLine(dr.GetValue(i));
}
dr.Close();
tran.Commit();
conn.Close();
}
}
如下示例描述了如何简单配置 EntityFramework6 使用。
config 文件的配置信息
C# 应用代码
public class Order
{
public int Id { get; set; }
public string Customer { get; set; }
public System.DateTime OrderDate { get; set; }
public virtual List OrderDetails { get; set; }
}
public class OrderContext : DbContext
{
public OrderContext(string connectionName)
: base(connectionName)
{
}
public DbSet Orders
{
get;
set;
}
/* 防止表名复数化 */
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
/* 全局修改默认模式搜索名字 */
modelBuilder.HasDefaultSchema("PUBLIC");
/* 单独修改某一个实例（表）的模式搜索名字 */
/* modelBuilder.Entity().ToTable("Order", "PUBLIC"); */
}
}
static void Main(string[] args)
{
using (var ctx = new OrderContext("CodeFirstDb"))
{
var o = new Order();
o.OrderDate = DateTime.Now;
ctx.Orders.Add(o);
ctx.SaveChanges();
var query = from order in ctx.Orders
select order;
foreach (var q in query)
{
Console.WriteLine("OrderId:{0},OrderDate:{1}",
q.Id, q.OrderDate);
}
ctx.Database.Delete();
Console.Read();
}
}