using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Finisar.SQLite;//http://einboch.pixnet.net/blog/post/248187728-c#操作sqlite資料庫
namespace CS_SQLite
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
//產生SQLite的資料庫文件,副檔名為.db
CreateSQLiteDatabase(“data.db”);
//建立資料表test
string createtablestring = “create table test (speed double, dist double);”;
CreateSQLiteTable(“data.db”, createtablestring);
//插入資料到test表中
string insertstring = “insert into test (speed,dist) values (’10’,’100′);insert into test (speed,dist) values (’20’,’200′);”;
SQLiteInsertUpdateDelete(“data.db”, insertstring);
//讀取資料
DataTable dt = GetDataTable(“data.db”, “select * from test”);
dataGridView1.DataSource = dt;
}
public SQLiteConnection OpenConn(string Database)//資料庫連線程式
{
string cnstr = string.Format(“Data Source=” + Database + “;Version=3;New=False;Compress=True;”);
SQLiteConnection icn = new SQLiteConnection();
icn.ConnectionString = cnstr;
if (icn.State == ConnectionState.Open) icn.Close();
icn.Open();
return icn;
}
public void CreateSQLiteDatabase(string Database)//建立資料庫程式
{
string cnstr = string.Format(“Data Source=” + Database + “;Version=3;New=True;Compress=True;”);
SQLiteConnection icn = new SQLiteConnection();
icn.ConnectionString = cnstr;
icn.Open();
icn.Close();
}
public void CreateSQLiteTable(string Database, string CreateTableString)//建立資料表程式
{
SQLiteConnection icn = OpenConn(Database);
SQLiteCommand cmd = new SQLiteCommand(CreateTableString, icn);
SQLiteTransaction mySqlTransaction = icn.BeginTransaction();
try
{
cmd.Transaction = mySqlTransaction;
cmd.ExecuteNonQuery();
mySqlTransaction.Commit();
}
catch (Exception ex)
{
mySqlTransaction.Rollback();
throw (ex);
}
if (icn.State == ConnectionState.Open) icn.Close();
}
public void SQLiteInsertUpdateDelete(string Database, string SqlSelectString)//新增資料程式
{
SQLiteConnection icn = OpenConn(Database);
SQLiteCommand cmd = new SQLiteCommand(SqlSelectString, icn);
SQLiteTransaction mySqlTransaction = icn.BeginTransaction();
try
{
cmd.Transaction = mySqlTransaction;
cmd.ExecuteNonQuery();
mySqlTransaction.Commit();
}
catch (Exception ex)
{
mySqlTransaction.Rollback();
throw (ex);
}
if (icn.State == ConnectionState.Open) icn.Close();
}
public DataTable GetDataTable(string Database, string SQLiteString)//讀取資料程式
{
DataTable myDataTable = new DataTable();
SQLiteConnection icn = OpenConn(Database);
SQLiteDataAdapter da = new SQLiteDataAdapter(SQLiteString, icn);
DataSet ds = new DataSet();
ds.Clear();
da.Fill(ds);
myDataTable = ds.Tables[0];
if (icn.State == ConnectionState.Open) icn.Close();
return myDataTable;
}
}
}
|