c语言操作sqlite

前言

c语言只需要下载sqlite.dll即可操作数据库，qt sql只是对sqlite的api做了一层驱动包装而已

下载 

#include <stdio.h>
#include <assert.h>
#include <string.h>
#include "sqlite3.h"

void sqlite3_test_get_table(sqlite3 *db)
{
char **rows = NULL;
int nrow = 0, ncolumn = 0;
int res = sqlite3_get_table(db, "select id, name from stu order by id", &rows, &nrow, &ncolumn, NULL);
if (res != SQLITE_OK) {
char err[1024] = { 0 };
snprintf(err, sizeof err, sqlite3_errmsg(db));
printf("sqlite3_prepare_v2 error: %s\n", err);
return;
}
printf("获取到%d条数据，列数量为: %d\n", nrow, ncolumn);

for (int i = 0; i <= nrow; i++) {
for (int j = 0; j < ncolumn; j++) {
printf("%s\t", rows[i * ncolumn + j]);
}
printf("\n");
}
sqlite3_free_table(rows);
}

void sqlite3_test_update(sqlite3 *db)
{
sqlite3_exec(db, "begin", NULL, NULL, NULL);
sqlite3_stmt *stmt = NULL;
const char *sql = "select id,name,age from stu order by id asc";
const char *err = NULL;
int res = sqlite3_prepare_v2(db, sql, -1, &stmt, &err);
assert(res == SQLITE_OK);
printf("%s\n", sql);
while (sqlite3_step(stmt) == SQLITE_ROW) {
int id = sqlite3_column_int(stmt, 0);
const char *name = (const char *)sqlite3_column_text(stmt, 1);
int age = sqlite3_column_int(stmt, 2) + 1;
printf("%d\t%s\t%d\n", id, name, age);

char sql[1024];
snprintf(sql, sizeof sql, "update stu set age = %d where id = %d;", age, id);
res = sqlite3_exec(db, sql, NULL, NULL, NULL);
assert(res == SQLITE_OK);
}
sqlite3_exec(db, "commit", NULL, NULL, NULL);

sqlite3_finalize(stmt);
}

void sqlite3_test_insert(sqlite3 *db)
{
sqlite3_stmt *stmt;
const char *sql = "insert into stu (name, age) values (?, ?)";
const char *names[] = {"凡蕾", "幻灵", "紫安"};
int ages[] = {15, 18, 23};
int res = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
if (res != SQLITE_OK) {
printf("sql prepare error: %s\n", sql);
return;
}
sqlite3_exec(db, "begin", NULL, NULL, NULL);
for (int i = 0; i < 3; i++) {
sqlite3_bind_text(stmt, 1, names[i], -1, NULL);
sqlite3_bind_int(stmt, 2, ages[i]);

res = sqlite3_step(stmt);
if (res != SQLITE_DONE) {
printf("insert error. res = %d. name: %s age: %d\n", res, names[i], ages[i]);
break;
}
printf("insert success. name: %s age: %d\n", names[i], ages[i]);

sqlite3_reset(stmt);
}
sqlite3_exec(db, "commit", NULL, NULL, NULL);
sqlite3_finalize(stmt);
}

int main(int argc, char const *argv[])
{
sqlite3 *db = NULL;
int res = sqlite3_open("sqlite3test.db", &db);
if (res != SQLITE_OK) {
printf("open database sqlite3test.db fail!\n ERR: %s\n", sqlite3_errmsg(db));
return -1;
}
printf("open database sqlite3test.db ok!\n");

sqlite3_test_get_table(db);

sqlite3_close(db);
return 0;
}