mfcmysqlclistctrl_sqlLite接口的使用包含MFC控件CListCtrl代码示例

SqlLite是一款轻型的数据库&＃xff0c;是遵守ACID的关系型数据库管理系统&＃xff0c;它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的&＃xff0c;而且目前已经在很多嵌入式产品中使用了它&＃xff0c;它占用资源非常的低&＃xff0c;在嵌入式设备中&＃xff0c;可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统&＃xff0c;同时能够跟很多程序语言相结合&＃xff0c;比如 Tcl、C#、PHP、Java等&＃xff0c;还有ODBC接口&＃xff0c;同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲&＃xff0c;它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至今已经有14个年头&＃xff0c;SQLite也迎来了一个版本 SQLite 3已经发布。

下面利用SqlLite3 实现了简单的增、删、查、改&＃xff0c;并将查询结果显示在MFC 的CListCtrl控件中&＃xff1a;

1 //Sqlite.h

2 class_declspec (dllexport)Sqlite3 {4 public:5 Sqlite(){}6 ~Sqlite(){}7 void Open(stringname);8 voidClose();9 bool ExecDML(stringsql);10 int ExecScalar(stringsql);11 bool ExecQuery(string sql,vector &fieldName,vector &fieldValue);12 bool CreateTable(string tableName,vector &fieldName);13 bool DeleteTable(stringtableName);14 bool InsertRow(string tableName,vector &fieldName,vector &fieldValue);15 bool UpdateRow(string tableName,string key,string keyValue,vector &fieldName,vector &fieldValue);16 bool DeleteRow(string tableName,string key,stringkeyValue);17 private:18 CppSQLite3DB m_db;19 };

删表&＃xff1a;

1 bool Sqlite::DeleteTable(stringtableName)2 {3 stringsql;4 sql&＃61;string("drop table")&＃43;tableName&＃43;";";5 //删除表table1 { "drop table table1"}

6 if(!m_db.tableExists(tableName.data()))return true;7 int ret&＃61;m_db.execDML(sql.data());8 if(ret<0)return false;9 return true;10 }

建表&＃xff1a;

1 bool Sqlite::CreateTable(string tableName,vector &fieldName)2 {3 stringsql;4 if(m_db.tableExists(tableName.data()))return true;5 sql&＃61;string("create table")&＃43;tableName&＃43;"(";6 if(fieldName.size()<&＃61;0)return false;7 vector ::iterator it;8 for(it&＃61;fieldName.begin();it!&＃61;fieldName.end();it&＃43;&＃43;)9 {10 sql&＃43;&＃61;(*it&＃43;"string,");11 }12 sql[sql.size()-1]&＃61;&＃39;)&＃39;;13 sql&＃43;&＃61;";";14 //sql&＃61;" create table table1 (ID string,name string)"

15 int ret&＃61;m_db.execDML(sql.data());16 if(ret<0)return false;17 return true;18 }

向量查询&＃xff0c;取得表行数等 &＃xff1a;

1 //sql&＃61;"select count(*) from table1"

2 int Sqlite::ExecScalar(stringsql)3 {4 int ret&＃61;-1;5 try{6 ret&＃61;m_db.execScalar(sql.data());7 }catch(stringex) {8 return -1;9 }10 returnret;11 }

插入数据&＃xff1a;

1 //sql&＃61;"insert into table1 (&＃39;ID&＃39;,&＃39;name&＃39;) values(&＃39;001&＃39;,&＃39;zhang&＃39;);"

2 bool Sqlite::InsertRow(string tableName,vector &fieldName,vector &fieldValue)3 {4 //sql.Format("insert into UserTable (&＃39;domain&＃39;,&＃39;ip&＃39;,&＃39;port&＃39;,&＃39;time&＃39;) values(&＃39;%s&＃39;,&＃39;%s&＃39;,%d,&＃39;%s&＃39;);",domain,ip,port,t1);

5 stringsql;6 sql&＃61;string("insert into")&＃43;tableName&＃43;"(";7 if(fieldName.size()<&＃61;0)return false;8 vector ::iterator it;9 for(it&＃61;fieldName.begin();it!&＃61;fieldName.end();it&＃43;&＃43;)10 {11 sql&＃43;&＃61;("&＃39;"&＃43;*it&＃43;"&＃39;,");12 }13 sql[sql.size()-1]&＃61;&＃39;)&＃39;;14 sql&＃43;&＃61;"values(";15

16 for(it&＃61;fieldValue.begin();it!&＃61;fieldValue.end();it&＃43;&＃43;)17 {18 sql&＃43;&＃61;("&＃39;"&＃43;*it&＃43;"&＃39;,");19 }20 sql[sql.size()-1]&＃61;&＃39;)&＃39;;21 sql&＃43;&＃61;";";22 int ret&＃61;m_db.execDML(sql.data());23 if(ret<0)return false;24 return true;25 }

更改数据&＃xff1a;

1 //sql&＃61;"update table1 set ID &＃61; &＃39;004&＃39;,name &＃61; &＃39;zhao1&＃39; where ID &＃61; &＃39;004&＃39;;"

2 bool Sqlite::UpdateRow(string tableName,string key,string keyValue,vector &fieldName,vector &fieldValue)3 {4 stringsql;5 if(fieldName.size()<&＃61;0)return false;6 if(fieldName.size()!&＃61;fieldValue.size())return false;7 sql&＃61;string("update")&＃43;tableName&＃43;"set";8 vector ::iterator it;9 vector ::iterator it1;10 it1&＃61;fieldValue.begin();11 for(it&＃61;fieldName.begin();it!&＃61;fieldName.end();it&＃43;&＃43;)12 {13 sql&＃43;&＃61;(*it&＃43;"&＃61; &＃39;"&＃43;*it1&＃43;"&＃39;,");14 it1&＃43;&＃43;;15 }16 sql[sql.size()-1]&＃61;&＃39; &＃39;;17 if(key.data()&&keyValue.data())18 {19 sql&＃43;&＃61;(string("where")&＃43;key&＃43;"&＃61; &＃39;"&＃43;keyValue&＃43;"&＃39;");20 }21 sql&＃43;&＃61;";";22 int ret&＃61;m_db.execDML(sql.data());23 if(ret<0)return false;24 return true;25 }

删除一行数据&＃xff1a;

1 //sql&＃61;"delete from table1 where ID &＃61; &＃39;004&＃39;;";

2 bool Sqlite::DeleteRow(string tableName,string key,stringkeyValue)3 {4 stringsql;5 sql&＃61;string("delete from")&＃43;tableName&＃43;" ";6 if(key.data()&&keyValue.data())7 {8 sql&＃43;&＃61;(string("where")&＃43;key&＃43;"&＃61; &＃39;"&＃43;keyValue&＃43;"&＃39;");9 }10 sql&＃43;&＃61;";";11 int ret&＃61;m_db.execDML(sql.data());12 if(ret<0)return false;13 return true;14 }

查询&＃xff0c;后文有更详细的使用代码&＃xff1a;

1 //sql&＃61;"select * from table1";

2 bool Sqlite::ExecQuery(string sql,vector &fieldName,vector &fieldValue)3 {4 CppSQLite3Query q&＃61;m_db.execQuery(sql.data());5 fieldName.clear();6 fieldValue.clear();7 for(int i&＃61;0;i

demo code,how to use:

1 //插入了4条数据&＃xff0c;删除一条&＃xff0c;最后查询出所有数据

2 voidCmy_SqlLite_tDlg::OnBnClickedBnTest()3 {4

5 //TODO: 在此添加控件通知处理程序代码

6 int ret &＃61; 0;7 Sqlite m;8 m.Open("test.db");9

10 ret &＃61; m.DeleteTable("table1");11 TRACE("%d\n", ret);12 vectorfields;13 fields.push_back("ID");14 fields.push_back("name");15 ret &＃61; m.CreateTable("table1", fields);16 TRACE("%d\n", ret);17 ret &＃61; m.ExecScalar("select count(*) from table1");18 TRACE("%d\n", ret);19 vectorValues;20 Values.clear();21 Values.push_back("001");22 Values.push_back("zhang");23 ret &＃61; m.InsertRow("table1", fields, Values);24 TRACE("%d\n", ret);25 ret &＃61; m.ExecScalar("select count(*) from table1");26 TRACE("%d\n", ret);27 Values.clear();28 Values.push_back("002");29 Values.push_back("wang");30 ret &＃61; m.InsertRow("table1", fields, Values);31 TRACE("%d\n", ret);32 ret &＃61; m.ExecScalar("select count(*) from table1");33 TRACE("%d\n", ret);34 Values.clear();35 Values.push_back("003");36 Values.push_back("li");37 ret &＃61; m.InsertRow("table1", fields, Values);38 TRACE("%d\n", ret);39 ret &＃61; m.ExecScalar("select count(*) from table1");40 TRACE("%d\n", ret);41 Values.clear();42 Values.push_back("004");43 Values.push_back("zhao");44 ret &＃61; m.InsertRow("table1", fields, Values);45 TRACE("%d\n", ret);46 ret &＃61; m.ExecScalar("select count(*) from table1");47 TRACE("%d\n", ret);48 Values.clear();49 Values.push_back("004");50 Values.push_back("zhao1");51 ret &＃61; m.UpdateRow("table1", (char *)fields[0].data(), "004", fields, Values);52 TRACE("%d\n", ret);53 ret &＃61; m.DeleteRow("table1", (char *)fields[0].data(), "004");54 TRACE("%d\n", ret);55 ret &＃61; m.ExecScalar("select count(*) from table1");56 TRACE("%d\n", ret);57

58

59 vectorfieldName;60 vectorfieldValue;61 ret &＃61; m.ExecQuery("select * from table1", fieldName, fieldValue);62 vector ::iterator it;63 for (it &＃61; fieldValue.begin(); it !&＃61; fieldValue.end(); it&＃43;&＃43;)64 {65 MessageBox(*it);66

67 }68

69 m.Close();70

71 }

下面讨论下MFC CList控件显示查询结果&＃xff1a;

先看看效果图&＃xff1a;

代码&＃xff1a;

1 voidCmy_SqlLite_tDlg::OnBnClickedBnselect()2 {3 //TODO: 在此添加控件通知处理程序代码4 //CStringArray fieldName;CStringArray rowValue;

5 CppSQLite3DB m_db;6 m_db.open("test.db");7

8

9 std::string sql &＃61; "select * from table1;";10 CppSQLite3Query q &＃61;m_db.execQuery(sql.data());11

12

13 //b14 //删除所用行和列&＃xff0c;reset CListCtrl

15 while (m_LsData.DeleteColumn(0)) //因为你删除了第一列后&＃xff0c;后面的列会依次向上移动。

16 m_LsData.DeleteAllItems();17

18

19 m_LsData.ModifyStyle(0L, LVS_REPORT);20 m_LsData.ModifyStyle(0L, LVS_SINGLESEL);21 m_LsData.ModifyStyle(0L, LVS_SHOWSELALWAYS);22 m_LsData.ModifyStyle(0L, LVS_NOSORTHEADER);23 m_LsData.SetExtendedStyle(LVS_EX_GRIDLINES);24 //e

25

26

27 for (int i &＃61; 0; i

30 m_LsData.InsertColumn(i, q.fieldName(i), LVCFMT_LEFT);31 m_LsData.SetColumnWidth(i, strlen(q.fieldName(i)) &＃43; 100);32 }33 int nRow &＃61; 0;//数据太大分页&＃xff0c;或做限制

34 while (!q.eof())35 {36 int nColumn &＃61;q.numFields();37 m_LsData.InsertItem(nRow, "");38 for (int i &＃61; 0; i

41 m_LsData.SetItemText(nRow, i, q.fieldValue(i));42

43 }44 nRow&＃43;&＃43;;45 q.nextRow();46 }47 m_db.close();48

49 }

下面是使用SQLite Expert Professional 3查看数据看”test.db“&＃xff1a;(注&＃xff1a;不免费&＃xff0c;我下载的这个只有30天试用期)

整个工程&＃xff0c;我分享在百度网盘&＃xff0c;感谢支持&＃xff0c;boyang987 ,all copyright reserved.