top
Loading...
將SQL語句轉換為符合VB.NET格式字符串

實習中時常需要將寫好的SQL語句嵌入到VB.NET的函數中,例如將下列SQL:












-- Get Mother Lot Common Infoselect c.customerlotnumber -- customer lot#,c.containername --Amkor lot#,,amk.description,inh.txndate indate--s_time,outh.txndate  outdate -- e-time,rs.resourcename resourcename,emp.fullname-- ,outh.qtyfrom container c,historymainline hml,workflowstep wfs,operation opt,movehistory outh,moveinhistory inh,RESOURCEDEF rs,amkorwwoper amk,employee empwhere c.splitfromid =(select containerid from container wherecontainername='ML6410058461')and hml.reversalstatus=1and c.containerid=hml.containeridand hml.operationid=opt.operationidand hml.workflowstepid=wfs.workflowstepidand hml.operationid=opt.operationidand hml.historymainlineid=inh.historymainlineid(+)and hml.historymainlineid=outh.historymainlineid(+)and HMl.RESOURCEID=RS.RESOURCEID(+)and (outh.historymainlineid is not null or inh.historymainlineid is not null)and opt.amkorwwoperid=amk.amkorwwoperidand hml.employeeid=emp.employeeidorder by c.containername,to_number(wfs.sequence),inh.txndate,outh.txndate轉換如下形式為:String sql in VB.NETsql = "select c.customerlotnumber -- customer lot# " & vbCrLf _    & ",c.containername --Amkor lot#, " & vbCrLf _    & ",amk.description " & vbCrLf _    & ",inh.txndate indate--s_time " & vbCrLf _    & ",outh.txndate  outdate -- e-time " & vbCrLf _    & ",rs.resourcename resourcename " & vbCrLf _    & ",emp.fullname " & vbCrLf _    & "-- ,outh.qty " & vbCrLf _    & "from container c,historymainline hml,workflowstep wfs " & vbCrLf _    & ",operation opt,movehistory outh,moveinhistory inh " & vbCrLf _    & ",RESOURCEDEF rs " & vbCrLf _    & ",amkorwwoper amk " & vbCrLf _    & ",employee emp " & vbCrLf _    & "where c.splitfromid =(select containerid from container where " & vbCrLf _    & "containername='ML6410058461') " & vbCrLf _    & "and hml.reversalstatus=1 " '..

同事Tracy告訴我一個快速方法,僅僅利用UltraEdit的查找替換功能(<>號不填,僅為引用號),

查找:< ^p >

替換:< " & vbCrLf _ ^p & " >

替換的第一個"號前不可省去空格,然后填上頭尾即可,^p代表回車符,也就是將回車符展開,是不是很有趣的技巧?

(T114)

作者:http://www.zhujiangroad.com
來源:http://www.zhujiangroad.com
北斗有巢氏 有巢氏北斗