本文以一则示例说明一般技术人员演示demo与实际应用开发之间的差距。
假设情况如下:一个项目当中需要用linq to sql 来作为数据访问层,如果未用过Linq to SQL 的话,一般大家会以DEMO示例 说明一张表的增、删、改、查的情况。
第一步 建好数据库表,假设建表脚本为
[ SequenceId ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ MerId ] [ nvarchar ] ( 50 ) NOT NULL ,
[ MerDate ] [ nvarchar ] ( 50 ) NOT NULL ,
[ MerSeqId ] [ nvarchar ] ( 50 ) NULL ,
[ CardNo ] [ nvarchar ] ( 50 ) NOT NULL ,
[ UsrName ] [ nvarchar ] ( 50 ) NOT NULL ,
[ OpenBank ] [ nvarchar ] ( 50 ) NOT NULL ,
[ Prov ] [ nvarchar ] ( 50 ) NOT NULL ,
[ City ] [ nvarchar ] ( 50 ) NOT NULL ,
[ TransAmt ] [ decimal ] ( 8 , 2 ) NOT NULL ,
[ Purpose ] [ nvarchar ] ( 100 ) NULL ,
[ Flag ] [ nvarchar ] ( 5 ) NULL ,
[ SubBank ] [ nvarchar ] ( 50 ) NULL ,
[ Version ] [ nvarchar ] ( 10 ) NULL ,
[ Submited ] [ bit ] NULL ,
[ SubmitedResult ] [ nvarchar ] ( 50 ) NULL ,
[ SubmitedTime ] [ datetime ] NULL ,
[ responsed ] [ bit ] NULL ,
[ responseCode ] [ nvarchar ] ( 50 ) NULL ,
[ responseTime ] [ datetime ] NULL ,
[ oracontrasted ] [ bit ] NULL ,
[ oracontrastedTime ] [ datetime ] NULL ,
[ oracontrastedResult ] [ nvarchar ] ( 50 ) NULL ,
[ EticketNo ] [ nvarchar ] ( 50 ) NULL ,
[ State ] [ nvarchar ] ( 50 ) NULL ,
CONSTRAINT [ PK_NetPay_Order ] PRIMARY KEY CLUSTERED
(
[ SequenceId ] ASC
) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
其中SequenceId是自增序列
第二步 打开Visual Studio.NET 2010,新建控制台项目TestLinqtoSQL ,然后依次点击Tool->Connect to Database 输入刚刚建好的数据库相关信息,在IDE 左侧看到数据库相关信息
第三步 新建Linq to SQL Classes ,右击项目依次点击add ->add item->linq to sql classes 并命名为DataAccess,在第二步当中看到的数据库信息找到第一步建好的表,直接拖到新建的linq to SQL Classes 上来。
第四步 前期工作完成,现在我们可以开始写代码了,我们要做的新增一条数据,我们会这样写
NetPay_Order recorder = new NetPay_Order();
recorder.MerId = " 0001 " ;
recorder.MerDate = System.DateTime.Now.ToString( " yyyyMMdd " );
recorder.MerSeqId = null ;
recorder.CardNo = " 0002 " ;
recorder.UsrName = " 赵一村 " ;
recorder.City = " 苏州 " ;
recorder.Prov = " 江苏 " ;
recorder.Purpose = " 转账 " ;
recorder.EticketNo = System.Guid.NewGuid().ToString();
recorder.OpenBank = " 招商银行 " ;
recorder.Flag = " 00 " ;
recorder.Version = " 20100906 " ;
recorder.Submited = false ;
recorder.responsed = false ;
recorder.oracontrasted = false ;
_context.NetPay_Orders.InsertOnSubmit(recorder);
_context.SubmitChanges();
这样子,新增一笔数据就搞定了,用linq to SQL新增数据就这么简单。
可是这只能算是Demo 不足以实际就应用
遇到的问题1 批量操作与事务性,这里只是新增一笔数据,实际开发当中经常会有批量新增的事情,经常修改,代码会变成如下样子
{
DataAccessDataContext _context = new DataAccessDataContext();
foreach (NetPay_Order recorder in getOrders())
{
_context.NetPay_Orders.InsertOnSubmit(recorder);
}
_context.SubmitChanges();
}
private static NetPay_Order[] getOrders()
{
NetPay_Order[] recorders = new NetPay_Order[ 2 ];
NetPay_Order recorder1,recorder2;
recorder1 = new NetPay_Order();
recorder1.MerId = " 1001 " ;
recorder1.MerDate = System.DateTime.Now.ToString( " yyyyMMdd " );
recorder1.MerSeqId = null ;
recorder1.CardNo = " 0002 " ;
recorder1.UsrName = " 赵一村 " ;
recorder1.City = " 苏州 " ;
recorder1.Prov = " 江苏 " ;
recorder1.Purpose = " 转账 " ;
recorder1.EticketNo = System.Guid.NewGuid().ToString();
recorder1.OpenBank = " 招商银行 " ;
recorder1.Flag = " 00 " ;
recorder1.Version = " 20100906 " ;
recorder1.Submited = false ;
recorder1.responsed = false ;
recorder1.oracontrasted = false ;
recorders[ 0 ] = recorder1;
recorder2 = new NetPay_Order();
// recorder2.MerId = "1002";
recorder2.MerDate = System.DateTime.Now.ToString( " yyyyMMdd " );
recorder2.MerSeqId = null ;
recorder2.CardNo = " 0002 " ;
recorder2.UsrName = " 赵一村 " ;
recorder2.City = " 苏州 " ;
recorder2.Prov = " 江苏 " ;
recorder2.Purpose = " 转账 " ;
recorder2.EticketNo = System.Guid.NewGuid().ToString();
recorder2.OpenBank = " 招商银行 " ;
recorder2.Flag = " 00 " ;
recorder2.Version = " 20100906 " ;
recorder2.Submited = false ;
recorder2.responsed = false ;
recorder2.oracontrasted = false ;
recorders[ 1 ] = recorder2;
return recorders;
}
其中DataAccessDataContext _context = new DataAccessDataContext();
foreach (NetPay_Order recorder in getOrders())
{
_context.NetPay_Orders.InsertOnSubmit(recorder);
}
_context.SubmitChanges();
这几段代码中畎认情况linq to SQL 会为你提供事务功能
遇到的问题2 假如MerSeqId字段是根据MerDate+ SequenceId 两字字段组合起来作为流水号呢,我们知道程序热行到_context.NetPay_Orders.InsertOnSubmit(recorder)这一句时并没有新增数据库,而是要_context.SubmitChanges()以后才可以更新数据库,可谓有真正更新数据库的时候SequenceId 字段才会真正有值,此时照平常想法,代码会变成如下样子
DataAccessDataContext _context = new DataAccessDataContext();
foreach (NetPay_Order recorder in getOrders())
{
_context.NetPay_Orders.InsertOnSubmit(recorder);
_context.SubmitChanges();
recorder.MerSeqId = recorder.MerDate.ToString() + recorder.SequenceId.ToString();
_context.SubmitChanges();
}
这样子_context.SubmitChanges()以后再更新MerSeqId字段值然后再SubmitChanges一次,到这里,好像问题已经解决了。
可是这只能算是DEMO,还不足以实际应用
遇到的问题3 像上面的解决方案失去了事务性了,如果对事务没有要求的话,这样子问题也不大,而如果这段保存操作是作为WCF作务客户端调用呢,问题就有了,客户端提交一批数据上来,服务要保证其事务性,为此我们又要对程序改造成如下样子
if (_context.Connection != null )
_context.Connection.Open();
DbTransaction tran = _context.Connection.BeginTransaction();
try
{
foreach (NetPay_Order recorder in getOrders())
{
_context.NetPay_Orders.InsertOnSubmit(recorder);
_context.SubmitChanges();
recorder.MerSeqId = recorder.MerDate.ToString() + recorder.SequenceId.ToString();
_context.SubmitChanges();
}
}
catch (Exception ex)
{
tran.Rollback();
}
到这里已经解决了问题2,3了