基于LabVIEW和LabWindows／CVI的數(shù)據(jù)庫(kù)編程
? ? ? ? LabVIEW是一種程序開發(fā)環(huán)境，由美國(guó)國(guó)家儀器（NI）公司研制開發(fā)的，類似于C和BASIC開發(fā)環(huán)境，但是LabVIEW與其他計(jì)算機(jī)語(yǔ)言的顯著區(qū)別是：其他計(jì)算機(jī)語(yǔ)言都是采用基于文本的語(yǔ)言產(chǎn)生代碼，而LabVIEW使用的是圖形化編輯語(yǔ)言G編寫程序，產(chǎn)生的程序是框圖的形式。
? ? ? ? LabWindows/CVI是NI公司推出的交互式C語(yǔ)言開發(fā)平臺(tái)。它的集成化開發(fā)環(huán)境、交互式編程方法、函數(shù)面板和豐富的庫(kù)函數(shù)大大增強(qiáng)了語(yǔ)言的功能。它是一種32位的面向計(jì)算機(jī)測(cè)控領(lǐng)域的虛擬儀器軟件開發(fā)工具，可在多操作系統(tǒng)下運(yùn)行。是以ANIS C為核心的交互式虛擬儀器開發(fā)環(huán)境，將功能強(qiáng)大的C語(yǔ)言與測(cè)控技術(shù)有機(jī)結(jié)合。
? ? ? ? SQL Server由微軟開發(fā)的數(shù)據(jù)庫(kù)管理系統(tǒng)，是Web上比較流行的用于存儲(chǔ)數(shù)據(jù)的數(shù)據(jù)庫(kù)，它廣泛用于電子商務(wù)、銀行、保險(xiǎn)、電力等與數(shù)據(jù)庫(kù)有關(guān)的行業(yè)。 SQL Server通過(guò)Web對(duì)數(shù)據(jù)進(jìn)行輕松安全的訪問(wèn)，具有強(qiáng)大的、靈活的、基于Web的和安全的應(yīng)用程序管理等，操作簡(jiǎn)潔，界面友好。
? ? ? ? 在用LabVIEW或者LabWindows/CVI進(jìn)行數(shù)據(jù)采集分析過(guò)程中，一般會(huì)用到數(shù)據(jù)庫(kù)。在數(shù)據(jù)量很大的場(chǎng)合，可考慮使用SQL SERVER。下面就分別介紹LabVIEW和LabWindows/CVI與SQL SERVER的連接方法。
基于配置ODBC數(shù)據(jù)源的方法訪問(wèn)SQL Server數(shù)據(jù)庫(kù)
1.? 在Windows10 ?32bit下，可以通過(guò)“控制面板/Control Panel”->“管理工具/Administrative Tools”->“數(shù)據(jù)源(ODBC) /Data Sources (ODBC)”打開(如果是64bit,可以通過(guò)如下路徑打開C:\Windows\SysWOW64\ odbcad32)，將ODBC數(shù)據(jù)源管理器切換到“用戶DSN”選項(xiàng)卡，然后單擊“Add”按鈕；
? 2.? 選擇SQL server項(xiàng)并單擊“完成”按鈕；
?3.? 此時(shí)將會(huì)彈出“創(chuàng)建到SQL server的新數(shù)據(jù)源”對(duì)話框。給數(shù)據(jù)源起個(gè)名字為zhangxing, 在“服務(wù)器”下拉列表中選擇要做為數(shù)據(jù)源的服務(wù)器JINLING（服務(wù)器的名稱可以從SQL Server Management Studio登陸界面獲得），或者直接輸入數(shù)據(jù)庫(kù)服務(wù)器的IP地址，然后點(diǎn)擊下一步；
4.? 根據(jù)數(shù)據(jù)庫(kù)服務(wù)器的登錄驗(yàn)證方式（Windows驗(yàn)證、SQL Server驗(yàn)證）做適當(dāng)選擇，如果是SQL Server驗(yàn)證，輸入用戶名和密碼（賬號(hào)和密碼可以從SQL Server Management Studio登陸界面獲得），然后點(diǎn)擊下一步；
5.? 更改數(shù)據(jù)庫(kù)： 選擇您想要連接的數(shù)據(jù)庫(kù)zhangxing；其余的按默認(rèn)設(shè)置，如下圖所示，然后單擊下一步按鈕；
?6.? 選擇語(yǔ)言，再點(diǎn)擊“完成”按鈕；
? 7.? 最后點(diǎn)擊“測(cè)試數(shù)據(jù)源”；
8.? 接著會(huì)彈出一個(gè)窗口，測(cè)試成功：
?9.? 最后成功，我們的新建的數(shù)據(jù)源列在了列表中；
常用的SQL語(yǔ)言
1.SELECT 語(yǔ)句用于從表中選取數(shù)據(jù)；
SELECT 列1名稱，列2名稱，...列n名稱 FROM 表名稱；
SELECT * FROM 表名稱（其中*帶表全部）；
2. INSERT INTO 語(yǔ)句用于向表格中插入新的行;
用于插入表的全部列值，INSERT INTO 表名稱 VALUES (值1, 值2,....值n)；
也可以指定所要插入數(shù)據(jù)的列，INSERT INTO table_name (列1名稱，列2名稱，...列n名稱) VALUES (值1, 值2,....值n)
3. Update 語(yǔ)句用于修改表中的數(shù)據(jù)。
UPDATE 表名稱 SET 列1名稱= 新值，列2名稱= 新值，...列n名稱= 新值 WHERE 列名稱 = 某值；
其中WHERE是當(dāng)某列滿足一定的條件時(shí)候才更新
4. DELETE 語(yǔ)句用于刪除表中的行。
DELETE FROM 表名稱 WHERE 列名稱 = 值；
其中WHERE是當(dāng)某列滿足一定的條件時(shí)候才刪除；
Labview連接SQL Server數(shù)據(jù)庫(kù)
? ? ? ?由于LabVIEW本身并不具備數(shù)據(jù)庫(kù)訪問(wèn)功能，因此以LabVIEW編制的虛擬儀器系統(tǒng)需要其它輔助的方法來(lái)進(jìn)行數(shù)據(jù)庫(kù)訪問(wèn)。在LabVIEW編程環(huán)境下，LabVIEW對(duì)SQL Server數(shù)據(jù)的訪問(wèn)主要由NI公司的附加工具包LabVIEW SQL Toolkit進(jìn)行數(shù)據(jù)庫(kù)訪問(wèn)：本程序利用Database Connectivity中5個(gè)VI構(gòu)成（DB Tools Open Connection，DB Tools Execute Query，DB Tools Fetch Recordset Data，DB Tools Free Object，DB Tools Close Connection）,開發(fā)人員只需要在DB Tools Open Connection中字符串常量控件“connection information”處輸入DSN= zhangxing即可（其中zhangxing是ODBC配置時(shí)候指定的名稱），然后在SQL指令處讀寫操作需要輸入SQL語(yǔ)言即可。
工具包介紹
1.? DB Tools Open Connection.vi：打開數(shù)據(jù)庫(kù)連接
2.? DB Tools Close Connection.vi：關(guān)閉數(shù)據(jù)庫(kù)連接
3.? DB Tools Execute Query.vi：執(zhí)行輸入的SQL語(yǔ)句，并返回一個(gè)數(shù)據(jù)庫(kù)引用
4.? DB Tools Fetch Recordset Data.vi：根據(jù)記錄引用從文件中獲取數(shù)據(jù)
5.? DB Tools Free Object.vi：通過(guò)釋放引用來(lái)關(guān)閉目標(biāo)
Labview連接SQL Server數(shù)據(jù)庫(kù)實(shí)例
1.主界面
2.數(shù)據(jù)庫(kù)讀取數(shù)據(jù)程序
3. 數(shù)據(jù)庫(kù)數(shù)據(jù)查詢程序
4. 數(shù)據(jù)庫(kù)修改數(shù)據(jù)程序
5. 數(shù)據(jù)庫(kù)刪除選定數(shù)據(jù)程序
6. 數(shù)據(jù)庫(kù)添加數(shù)據(jù)程序
LabWindows/CVI連接SQL Server數(shù)據(jù)庫(kù)
? ? ? ? LabWindows／CVI SQL Toolkit是用于CVI數(shù)據(jù)庫(kù)訪問(wèn)的附加工具包，它集成了一系列的高級(jí)功能模塊，這些模塊封裝了大部分的數(shù)據(jù)庫(kù)操作和一些高級(jí)數(shù)據(jù)庫(kù)訪問(wèn)功能。LabWindows／CVI SQL Toolkit支持主流的數(shù)據(jù)庫(kù)驅(qū)動(dòng)，對(duì)于數(shù)據(jù)庫(kù)開發(fā)商及第三方開發(fā)者提供的驅(qū)動(dòng)，LabWindows／CVI SQL Toolkit也一樣采用標(biāo)準(zhǔn)的ODBC編譯方式，通過(guò)這種擴(kuò)展方式，可以靈活支持任何符合ODBC開發(fā)標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)系統(tǒng)。由于其包含了豐富的數(shù)據(jù)庫(kù)操作函數(shù)，給開發(fā)帶來(lái)了方便和快捷。
基本函數(shù)介紹
1. DBConnect函數(shù)：創(chuàng)建一個(gè)與數(shù)據(jù)庫(kù)系統(tǒng)的連接。
例如：hdbc = DBConnect ("DSN=zhangxing");
2. DBDisconnect函數(shù)：關(guān)閉數(shù)據(jù)庫(kù)連接。
例如：DBDisconnect (hdbc);
3. DBActivateSQL函數(shù)：激活一個(gè)SQL語(yǔ)句。
4. DBPrepareSQL函數(shù)：準(zhǔn)備執(zhí)行一條SQL語(yǔ)句。
5. DBExecutePreparedSQL函數(shù)：執(zhí)行一條SQL語(yǔ)句。
例如：DBExecutePreparedSQL (hstat);
6.DBCreateParamChar函數(shù)：為存儲(chǔ)過(guò)程或SQL語(yǔ)句創(chuàng)建一個(gè)字符型參數(shù)變量。
7.DBClosePreparedSQL函數(shù)：關(guān)閉SQL語(yǔ)句。
8. DBBindColChar函數(shù)：將字符型字段綁定到變量中。
9. DBDeactivateSQL函數(shù)：釋放SQL語(yǔ)句所占用的系統(tǒng)資源。
LabWindows/CVI連接SQL Server數(shù)據(jù)庫(kù)實(shí)例
1.主界面
2.數(shù)據(jù)庫(kù)讀取數(shù)據(jù)部分代碼
hstat = DBActivateSQL (hdbc, "SELECT *FROM zhangxing.dbo.equipment");//選取表中所有的數(shù)據(jù)? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
resultcode = DBBindColChar (hstat, 1, 50, equid, &equidstatus, "");?????????????
resultcode = DBBindColChar (hstat, 2, 50, equipmentname, &equipmentnamestatus, "");??
resultcode = DBBindColChar (hstat, 3, 50, equipmenttype, &equipmenttypestatus, "");??
resultcode = DBBindColChar (hstat, 4, 50, equipmentaddress, &equipmentaddressstatus, "");
resultcode = DBBindColChar (hstat, 5, 50, equipmentfactory, &equipmentfactorystatus, "");? ? ? ??
total = DBNumberOfRecords (hstat);//獲得總的記錄數(shù)
?int data[total];
DeleteTableRows (equpanel, EQUPANEL_EQUTABLE, 1, -1);//刪除現(xiàn)在CVI中的設(shè)備信息表中的內(nèi)容?
InsertTableRows (equpanel, EQUPANEL_EQUTABLE, 1, total, VAL_USE_MASTER_CELL_TYPE);//將數(shù)據(jù)插入CVI中的設(shè)備信息表中
SetCtrlAttribute (equpanel, EQUPANEL_EQUTABLE, ATTR_VISIBLE, 0);? ? ?
????????? if(total>0)
???????????? {
???????????? while (DBFetchNext(hstat) != DB_EOF)
???????????????? {
SetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(1,i), equid);??? ?//將綁定的信息在CVI中的設(shè)備信息表中顯示出來(lái)
SetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(2,i), equipmentname);?
SetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(3,i), equipmenttype);?
SetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(4,i), equipmentaddress);?
SetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(5,i), equipmentfactory);?? ? ? ? ? ??
??????????????????? i++;
???????????????? }
?SetCtrlAttribute (equpanel, EQUPANEL_EQUTABLE, ATTR_VISIBLE, 1);
???????????? ??? DBDeactivateSQL (hstat);
???????????? }
3.數(shù)據(jù)庫(kù)數(shù)據(jù)查詢部分代碼
hmap = DBBeginMap (hdbc); //利用SQL TOOKIT映射數(shù)據(jù)表
? ? ? ? ? ? ? ??
??DBMapColumnToChar (hmap, "equid", 50, equid, &equidstatus, "");
? DBMapColumnToChar (hmap, "equipmentname", 50, equipmentname, &equipmentnamestatus, "");
??DBMapColumnToChar (hmap, "equipmenttype", 50, equipmenttype, &equipmenttypestatus, "");
??DBMapColumnToChar (hmap, "equipmentaddress", 50, equipmentaddress, &equipmentaddressstatus, "");
? DBMapColumnToChar (hmap, "equipmentfactory", 50, equipmentfactory, &equipmentfactorystatus, "");
????????????????????
?????????hstmt = DBActivateMap (hmap, "equipment");//激活數(shù)據(jù)表
? ? ? ? ? ?GetCtrlVal (equpanel, EQUPANEL_EQUFINDCONDITION, &equfindcondition);? ??
???????????????????? while(DBFetchNext (hstmt)!=DB_EOF)
???????????????????? {
??????????????????????????? switch(equfindcondition)
?????????????????????????????????? {??
????????????????????????????????????????? //以設(shè)備ID為查詢條件
????????????????????????????????????????? case 1:? ? ? ? ?
????????????? GetCtrlVal (equpanel, EQUPANEL_EQUID, equidbuffer);
???????????????????????????????????????????????? strncpy(equid1,equid,strlen(equidbuffer));
??????????????????if(strcmp(equidbuffer,equid1)==0)//當(dāng)存在與查詢的用戶名一致的記錄時(shí)標(biāo)定flag=1
??????????????????????????????????????????????????????? {
?????????????????????????????????????????????????????????????? flag=1;
?????????????????????????????????????????????????????????????? break;
??????????????????????????????????????????????????????? }
?????????????????????????????????????????????? break;
……
case 3://清除設(shè)備信息表中的所有內(nèi)容后，重新插入符合查詢條件的記錄
sprintf(sqlcommand,"SELECT *FROM zhangxing.dbo.equipment WHERE equipmentfactory='%s'",factorybuffer);? ? ? ? ? ? ? ?
?hstat = DBActivateSQL (hdbc, sqlcommand);//選取表中所有的數(shù)據(jù)
??????????// 分別對(duì)數(shù)據(jù)庫(kù)字段對(duì)應(yīng)的數(shù)據(jù)進(jìn)行綁定?????????????????
resultcode = DBBindColChar (hstat, 1, 50, equid, &equidstatus, "");
resultcode = DBBindColChar (hstat, 2, 50, equipmentname, &equipmentnamestatus, "");//
resultcode = DBBindColChar (hstat, 3, 50, equipmenttype, &equipmenttypestatus, "");
resultcode = DBBindColChar (hstat, 4, 50, equipmentaddress, &equipmentaddressstatus, "");
resultcode = DBBindColChar (hstat, 5, 50, equipmentfactory, &equipmentfactorystatus, "");
?????????????????????????????????????????
?total = DBNumberOfRecords (hstat);//獲得總的記錄數(shù)
DeleteTableRows (equpanel, EQUPANEL_EQUTABLE, 1, -1);//刪除現(xiàn)在CVI中的設(shè)備信息表中的內(nèi)容? ? ? ? ? ? ?
?InsertTableRows (equpanel, EQUPANEL_EQUTABLE, 1, total, VAL_USE_MASTER_CELL_TYPE);//將數(shù)據(jù)插入CVI中的設(shè)備信息表中
????????????????????
????????????????????????????????????????? if(total>0)
???????????????????????????????????????????????? {
??????????????????????????????????????????????????????? while (DBFetchNext(hstat) != DB_EOF)
?????????????????????????????????????????????????????????????? {
SetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(1,i), equid);?????????? ?//將綁定的信息在CVI中的設(shè)備信息表中顯示出來(lái)
SetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(2,i), equipmentname);? //
SetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(3,i), equipmenttype);? //
SetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(4,i), equipmentaddress); //
SetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(5,i), equipmentfactory);??
????????????????????????????????????????????????????????????????????? i++;
?????????????????????????????????????????????????????????????? }
?????????????
???????????????????????????????????????????????? }
???????????????????????????????????????????????? else
???????????????????????????????????????????????? {
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?MessagePopup ("提示", "你所查詢的設(shè)備未找到!");????
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?}??
4.數(shù)據(jù)庫(kù)修改數(shù)據(jù)部分代碼
SetCtrlAttribute (equpanel, EQUPANEL_EQUTABLE, ATTR_CTRL_MODE, VAL_HOT);//激活CVI的設(shè)備信息表???????????
GetActiveTableCell (equpanel, EQUPANEL_EQUTABLE, &colrow);//獲得活動(dòng)表格當(dāng)前的行與列
GetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(1, colrow.y), equid);?? ?? // 獲得本行第1列中的設(shè)備ID
GetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(2, colrow.y), equipmentname);// 獲得本行第2列中的設(shè)備名稱
GetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(3, colrow.y), equipmenttype);// 獲得本行第3列中的設(shè)備型號(hào)
GetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(4, colrow.y), equipmentaddress);// 獲得本行第4列中的設(shè)備地址
GetTableCellVal (equpanel, EQUPANEL_EQUTABLE, MakePoint(5, colrow.y), equipmentfactory); //獲得本行第5列中的設(shè)備生產(chǎn)廠家
?hstat = DBPrepareSQL (hdbc, "UPDATE zhangxing.dbo.equipment SET equipmentname = ?, equipmenttype = ?, equipmentaddress = ?, equipmentfactory = ? WHERE equid = ?");
resultcode = DBCreateParamChar (hstat, "", DB_PARAM_INPUT, equipmentname, strlen(equipmentname)+1);
?resultcode = DBCreateParamChar (hstat, "", DB_PARAM_INPUT, equipmenttype, strlen(equipmenttype)+1);
resultcode = DBCreateParamChar (hstat, "", DB_PARAM_INPUT, equipmentaddress, strlen(equipmentaddress)+1);
resultcode = DBCreateParamChar (hstat, "", DB_PARAM_INPUT, equipmentfactory, strlen(equipmentfactory)+1);
resultcode = DBCreateParamChar (hstat, "", DB_PARAM_INPUT, equid, strlen(equid)+1);
resultcode = DBExecutePreparedSQL (hstat);
DBClosePreparedSQL (hstat);
?ReadEQUTab(equpanel,EQUPANEL_READEQUTAB,EVENT_COMMIT,NULL,0,0);
5.數(shù)據(jù)庫(kù)刪除選定數(shù)據(jù)部分代碼
hstat = DBPrepareSQL (hdbc, "DELETE FROM zhangxing.dbo.equipment WHERE equid = ?");
resultcode = DBCreateParamChar (hstat, "", DB_PARAM_INPUT, equid,50);? ? ? ??
resultcode = DBExecutePreparedSQL (hstat);
DBClosePreparedSQL (hstat);
6.???? 數(shù)據(jù)庫(kù)添加數(shù)據(jù)部分代碼
hstat = DBPrepareSQL (hdbc, "INSERT INTO zhangxing.dbo.equipment VALUES (?,?,?,?,?)");? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
esultcode = DBCreateParamChar (hstat, "equid", DB_PARAM_INPUT, equid, 50);//一切同用戶管理事件
resultcode = DBCreateParamChar (hstat, "equipmentname", DB_PARAM_INPUT, equipmentname, 50);?
resultcode = DBCreateParamChar (hstat, "equipmenttype", DB_PARAM_INPUT, equipmenttype, 50);?
resultcode = DBCreateParamChar (hstat, "equipmentaddress", DB_PARAM_INPUT, equipmentaddress, 50);
resultcode = DBCreateParamChar (hstat, "equipmentfactory", DB_PARAM_INPUT, equipmentfactory, 50);
resultcode = DBExecutePreparedSQL (hstat);
DBClosePreparedSQL (hstat);
? ? ? ? ? ? ? ? ?ReadEQUTab(equpanel,EQUPANEL_READEQUTAB,EVENT_COMMIT,NULL,0,0);
? ? ? 實(shí)踐證明，LabVIEW和LabWindows/CVI利用自身的數(shù)據(jù)庫(kù)訪問(wèn)工具包實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)，操作更簡(jiǎn)潔，容易理解，不用精通ActiveX技術(shù)和SQL語(yǔ)言（只需要熟悉基本的SQL語(yǔ)言），充分利用了現(xiàn)有的資源，就可以對(duì)數(shù)據(jù)庫(kù)進(jìn)行實(shí)時(shí)操作。
