1. Shippers 表的结构
CREATE TABLE [dbo].[Shippers] ( [ShipperID] [int] IDENTITY (1, 1) NOT 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的支持)和本文例子以及相关的工具等
上一偏 下一偏