NDO - 快速入门

      本文以sqlserver 2000 自带的Northwind数据库中的Shippers表的CRUD作为演示例子
      
      1.   Shippers 表的结构
CREATE TABLE [dbo].[Shippers] (
    
[ShipperID] [int] IDENTITY (11NOT NULL ,
    
[CompanyName] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    
[Phone] [nvarchar] (24) COLLATE Chinese_PRC_CI_AS NULL 
ON [PRIMARY]

      2.   简单的数据库约定配置(key = "ConnectionString")
<appSettings>
        
<add key="ConnectionString" value="Data Source=localhost; Database=Northwind; Integrated Security=true;"/>
</appSettings>

     3.  用IStatement - sql 语句来操作数据库(关于IStatement 和 IQrocedure 更多的使用方法,请查看API,或下载包中的类图文挡)
   
string insertSql = "insert into shippers ( CompanyName,Phone) values ( @CompanyName,@Phone);select CAST( SCOPE_IDENTITY() AS INT)";
            
string deleteSql = "delete from shippers where ShipperID = @id";
            
string updateSql = "update shippers set CompanyName = @name,Phone = @phone where ShipperID = @id";
            
string selectSql = "select * from Shippers";
            
string selectSqlByID = "select * from shippers where shipperID = @id";

            
try
            
{
                
//get NDOManager instance: nm
                INDOManager nm = NDOManager.Instance;

                
//create Statement object and set parameters
                IStatement stmt = nm.CreateStatement(insertSql)
                    .MakeInParam(
"CompanyName",DbType.String,40,"NDO Test")
                    .MakeInParam(
"Phone",DbType.String,24,"(101)  555-6666");
            
                
//insert a shipper record
                object o = stmt.GetScalar();
                Console.WriteLine(o);

                
//get a shipper record by id
                DataRow shipper = stmt.MakeInParam("id",(int)o)
                    .GetDataRow(selectSqlByID);

                Console.WriteLine(
" CompanyName = {0}\t Phone = {1} ",
                    (
string)shipper["CompanyName"],
                    (
string)shipper["Phone"]);

                
//update shipper record
                int effectRows = stmt.MakeInParam("name","update ndo test!")
                    .MakeInParam(
"phone","(101) 666-8888")
                    .MakeInParam(
"id",(int)o)
                    .Update(updateSql);

                Console.WriteLine(effectRows);

                
//check update result
                shipper = stmt.MakeInParam("id",(int)o)
                    .GetDataRow(selectSqlByID);

                Console.WriteLine(
" CompanyName = {0}\t Phone = {1} ",
                    (
string)shipper["CompanyName"],
                    (
string)shipper["Phone"]);

                
//delete a shipper
                stmt.MakeInParam("id",(int)o)
                    .Update(deleteSql);    
    
                shipper 
= stmt.MakeInParam("id",(int)o)
                    .GetDataRow(selectSqlByID);

                Console.WriteLine(shipper 
== null);

                Console.ReadLine();
            }

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

      4.  用IProcedure - 存储过程来操作数据库(存储过程的内容和上面的sql 一样)
string insertSql = "up_insert_shipper";
            
string deleteSql = "up_delete_shipper";
            
string updateSql = "up_update_shipper";
            
string selectSql = "up_selectAll_shipper";
            
string selectSqlByID = "up_selectByID_shipper";

            
try
            
{
                
//get NDOManager instance: nm
                INDOManager nm = NDOManager.Instance;

                
//create Statement object and set parameters
                IStatement stmt = nm.CreateProcedure(insertSql)
                    .MakeInParam(
"CompanyName",DbType.String,40,"NDO Test")
                    .MakeInParam(
"Phone",DbType.String,24,"(101)  555-6666");
            
                
//insert a shipper record
                object o = stmt.GetScalar();
                Console.WriteLine(o);

                
//get a shipper record by id
                DataRow shipper = stmt.MakeInParam("id",(int)o)
                    .GetDataRow(selectSqlByID);

                Console.WriteLine(
" CompanyName = {0}\t Phone = {1} ",
                    (
string)shipper["CompanyName"],
                    (
string)shipper["Phone"]);

                
//update shipper record
                int effectRows = stmt.MakeInParam("name","update ndo test!")
                    .MakeInParam(
"phone","(101) 666-8888")
                    .MakeInParam(
"id",(int)o)
                    .Update(updateSql);

                Console.WriteLine(effectRows);

                
//check update result
                shipper = stmt.MakeInParam("id",(int)o)
                    .GetDataRow(selectSqlByID);

                Console.WriteLine(
" CompanyName = {0}\t Phone = {1} ",
                    (
string)shipper["CompanyName"],
                    (
string)shipper["Phone"]);

                
//delete a shipper
                stmt.MakeInParam("id",(int)o)
                    .Update(deleteSql);    
    
                shipper 
= stmt.MakeInParam("id",(int)o)
                    .GetDataRow(selectSqlByID);

                Console.WriteLine(shipper 
== null);

                Console.ReadLine();
            }

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


         5. 用Query 对象实现
try
            
{
                
//get NDOManager instance: nm
                Query qu = NDOManager.Instance.CreateQuery("Shippers");

                
// insert a shipper record
                qu.Columns("CompanyName","Phone")
                    .Values(
"NDO Test","(101)  555-6666")
                    .Insert();

                
//get max value 
                object o = qu.Max("ShipperID").GetScalar();

                
//get a shipper record by id
                DataRow shipper = (qu.NewCondition("ShipperID"== o).GetDataRow(); 

                Console.WriteLine(
" CompanyName = {0}\t Phone = {1} ",
                    (
string)shipper["CompanyName"],
                    (
string)shipper["Phone"]);


                
//update shipper record
                int effectRows = qu
                    .Set(
"CompanyName","update ndo test!")
                    .Set(
"Phone","(101) 666-8888")
                    .Where(
"ShipperID",o)
                    .Update();

                
//check update result
                shipper = qu.Filter("ShipperID = @id")
                    .AddParam(
"id",o)
                    .GetDataRow();

                Console.WriteLine(
" CompanyName = {0}\t Phone = {1} ",
                    (
string)shipper["CompanyName"],
                    (
string)shipper["Phone"]);


                
//delete a shipper
                qu.Where("ShipperID",o).Delete();


                shipper 
= qu.NewWhere("ShipperID",o).GetDataRow();
                Console.WriteLine(shipper 
== null);

                Console.ReadLine();
            }

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

         6.  用活动记录实现,通过NDOGenerator 工具生成一个实体类
public class Shipper:ActiveRecord
    
{
        
        
public Shipper():base("Shippers")
        
{
        }

        
        
protected override ActiveRecord NewInstance()
        
{
            
return new Shipper();
        }

        

        
public int ShipperID
        
{  
            
get return this.settings.GetInt32("ShipperID"); }
            
set this["ShipperID"= value; }
        }

        
public string CompanyName
        
{  
            
get return (string)this["CompanyName"]; }
            
set this["CompanyName"= value; }
        }

        
public string Phone
        
{  
            
get return (string)this["Phone"]; }
            
set this["Phone"= value; }
        }

        
        
    }

具体的CRUD 代码如下:
try
            
{
                Shipper shipper 
= new Shipper();
                shipper.CompanyName 
= "NDO Test";
                shipper.Phone 
= "(101)  555-6666";

                
// insert a shipper record
                shipper.Save();

                Console.WriteLine(shipper.ShipperID);


                Shipper shipper2 
= new Shipper();
                
//get a shipper record by id
                shipper2.LoadByKey(shipper.ShipperID);

                Console.WriteLine(
" CompanyName = {0}\t Phone = {1} ",
                    (
string)shipper2.CompanyName,
                    (
string)shipper2.Phone);


                
//update shipper record
                shipper2.CompanyName = "update ndo test!";
                shipper2.Phone 
= "(101) 666-8888";
                shipper2.Save();


                
//check update result
                Shipper shipper3 = new Shipper();
                shipper3.ShipperID 
= shipper2.ShipperID;
                shipper3.Load();

                Console.WriteLine(
" CompanyName = {0}\t Phone = {1} ",
                    (
string)shipper3.CompanyName,
                    (
string)shipper3.Phone);

                
//delete a shipper
                shipper3.Delete();

                
//check delete result
                Shipper shipper4 = new Shipper();
                shipper4.ShipperID 
= shipper3.ShipperID;
                shipper4.Load();

                Console.WriteLine(shipper4.CompanyName 
== null || shipper4.CompanyName == "");

                Console.ReadLine();
            }

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


      从以上例子大家可以看到用NDO 操作数据库是如何的简单!最后有一点需要声明一下NDO组件是基于.NET 1.1 的,不支持.NET 2.0的范性技术,但是.NET 2.0仍然可以使用NDO组件的.
      最后附上NDO组件(在以前的基础上添加了对ORACLE和ACCESS的支持)和本文例子以及相关的工具

      上一偏   下一偏
posted @ 2006-11-25 20:33  风云  阅读(2463)  评论(1编辑  收藏  举报