3. 编程应用
3.1 编程环境
要求在VISUAL STUDIO .NET 2003, FRAMEWORK 1.1以上的版本。正确安装DM数据库，并将dmprovider.dll拷贝到C:\WINDOWS\System32下，并在项目中引用该dll并声明命名空间Dm。
3.2 获取连接
通过DmConnection类来连接达梦数据库服务器。
public void getConnection()
{
DmConnection cnn = new DmConnection();
cnn.ConnectionString =
"server=localhost;User ID=SYSDBA;PWD=SYSDBA;Database=SYSTEM;port=12345;";
try{
cnn.Open();
}catch(Exception ex) {
Console.WriteLine(ex.Message);
}
cnn.Close();}
{
DmConnection cnn = new DmConnection();
cnn.ConnectionString =
"server=localhost;User ID=SYSDBA;PWD=SYSDBA;Database=SYSTEM;port=12345;";
try{
cnn.Open();
}catch(Exception ex) {
Console.WriteLine(ex.Message);
}
cnn.Close();}
3.3 执行SQL语句
成功连接了达梦数据库服务器后，通过DmCommand类来提交SQL语句，对数据库服务器进行增、删、改、查等操作。
public void ExecSQL()
{
DmCommand command = new DmCommand();
command.Connection = cnn;
try
{
command.CommandText = "create table test(c1 int, c2 varchar(20));";
command.ExecuteNonQuery();
command.CommandText = "insert into test values(1, 'hi');";
command.ExecuteNonQuery();
command.CommandText = "update test set c2='hello world!' where c1=1;";
command.ExecuteNonQuery();
command.CommandText = "delete from test where c1=1;";
command.ExecuteNonQuery();
}
catch(Exception ex)
{
Console.WriteLine("发生未预期的错误导致测试失败:"+ex.Message,2);
}
}
{
DmCommand command = new DmCommand();
command.Connection = cnn;
try
{
command.CommandText = "create table test(c1 int, c2 varchar(20));";
command.ExecuteNonQuery();
command.CommandText = "insert into test values(1, 'hi');";
command.ExecuteNonQuery();
command.CommandText = "update test set c2='hello world!' where c1=1;";
command.ExecuteNonQuery();
command.CommandText = "delete from test where c1=1;";
command.ExecuteNonQuery();
}
catch(Exception ex)
{
Console.WriteLine("发生未预期的错误导致测试失败:"+ex.Message,2);
}
}
3.4 执行带参数的SQL语句
需要使用DmParameter类进行支持
public void ExecSQLWithParam(int param1, string param2)
{
DmConnection cnn = new DmConnection();
DmCommand command = new DmCommand();
try
{
command.Connection = cnn;
command.CommandText = "create table test_1 (c1 int, c2 varchar(20));";
command.ExecuteNonQuery();
command.CommandText = "insert into test_1(c1,c2) values(?, ?);";
DmParameter dp2 = new DmParameter("para2", Dm.DmDbType.VarChar,10);
DmParameter dp1 = new DmParameter("para1", Dm.DmDbType.Int32, 10);
dp1.Value = param1;
dp2.Value = param2;
command.Parameters.Add(dp1);
command.Parameters.Add(dp2);
command.ExecuteNonQuery();
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
}
{
DmConnection cnn = new DmConnection();
DmCommand command = new DmCommand();
try
{
command.Connection = cnn;
command.CommandText = "create table test_1 (c1 int, c2 varchar(20));";
command.ExecuteNonQuery();
command.CommandText = "insert into test_1(c1,c2) values(?, ?);";
DmParameter dp2 = new DmParameter("para2", Dm.DmDbType.VarChar,10);
DmParameter dp1 = new DmParameter("para1", Dm.DmDbType.Int32, 10);
dp1.Value = param1;
dp2.Value = param2;
command.Parameters.Add(dp1);
command.Parameters.Add(dp2);
command.ExecuteNonQuery();
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
}
3.5 读取结果集
读取返回的结果集，需要通过DmDataReader类来实现
public void getResultSet()
{
DmCommand command = new DmCommand();
try{
command.Connection = cnn;
command.CommandText = "select * from system.sysdba.systables;";
DmDataReader reader = command.ExecuteReader();
while(reader.Read())
{
Console.WriteLine ("结果集为:"+reader[i]);
}
}
catch(Exception ex)
{
Console.WriteLine("捕获未预期异常:"+ex.ToString(),2);
}
}
{
DmCommand command = new DmCommand();
try{
command.Connection = cnn;
command.CommandText = "select * from system.sysdba.systables;";
DmDataReader reader = command.ExecuteReader();
while(reader.Read())
{
Console.WriteLine ("结果集为:"+reader[i]);
}
}
catch(Exception ex)
{
Console.WriteLine("捕获未预期异常:"+ex.ToString(),2);
}
}
3.6 事务的支持
DmTransaction类是专门用来支持事务处理的。
public void TestTransaction(string[] args)
{
DmCommand command = cnn.CreateCommand();
DmTransaction dt;
dt = cnn.BeginTransaction();
command.Connection = cnn;
command.Transaction = dt;
try{
command.CommandText = "insert into t1_Dmcntransaction(c1,c2) values(1,'aaa')";
command.ExecuteNonQuery();
command.CommandText = "insert into t1_Dmcntransaction(c1,c2) values(2,'bbb')";
command.ExecuteNonQuery();
command.CommandText = "insert into t1_Dmcntransaction(c1,c2) values(3,'ccc')";
command.ExecuteNonQuery();
dt.Commit();
}
catch(Exception ex)
{
dt.Rollback();
Console.WriteLine(ex.Message,2);
}
}
{
DmCommand command = cnn.CreateCommand();
DmTransaction dt;
dt = cnn.BeginTransaction();
command.Connection = cnn;
command.Transaction = dt;
try{
command.CommandText = "insert into t1_Dmcntransaction(c1,c2) values(1,'aaa')";
command.ExecuteNonQuery();
command.CommandText = "insert into t1_Dmcntransaction(c1,c2) values(2,'bbb')";
command.ExecuteNonQuery();
command.CommandText = "insert into t1_Dmcntransaction(c1,c2) values(3,'ccc')";
command.ExecuteNonQuery();
dt.Commit();
}
catch(Exception ex)
{
dt.Rollback();
Console.WriteLine(ex.Message,2);
}
}
以上都是DM .NET PROVIDER的一些基本应用。并且DM .NET PROVIDER在设计时严格按照MSDN2003来实现，所以用户在具体应用时，也可以参考MSDN。
