Sqlite通用类库
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms; namespace CommonLib
{
public static class DB_Sqlite
{
public static void CreateDB(string datasource,ArrayList sqlList)
{
if ((datasource == null) || (datasource == "") || (datasource.Length <= ))
{
MessageBox.Show("数据库名不能为空");
return;
}
if ((sqlList == null) || (sqlList.Count <= ))
{
MessageBox.Show("创建数据库表的语句不能为空");
return;
} try
{
if (!File.Exists(@datasource))
{
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
//connstr.Password = "";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); foreach(Object OO in sqlList)
{
cmd.CommandText = OO.ToString();
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("SQLite数据库:" + ex.ToString());
}
}
public static Int32 Fun_ExeUpdate(string strSql, string datasource)
{
Int32 nExcnt = ;
try
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
conn = new System.Data.SQLite.SQLiteConnection();
connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
//connstr.Password = "";//设置密码，SQLite ADO.NET实现了数据库密码保护 conn.ConnectionString = connstr.ToString();
conn.Open();
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn);
cmd.CommandText = strSql;
nExcnt = cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
#region 已经注释的代码
//using (SQLiteConnection connection = new SQLiteConnection(connstr.ToString ()))
//{
// connection.Open();
// using (SQLiteCommand command=new SQLiteCommand (strSql ,connection ))
// {
// command.ExecuteNonQuery();
// command.Dispose(); // } // connection.Close(); //}
#endregion
}
catch (Exception ex)
{
MessageBox.Show("SQLite数据库:" + ex.ToString());
}
return nExcnt;
} public static Object Fun_ExeScaler(string strSql, string datasource)
{
Object nExcnt = ;
try
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
conn = new System.Data.SQLite.SQLiteConnection();
connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
//connstr.Password = "";//设置密码，SQLite ADO.NET实现了数据库密码保护 conn.ConnectionString = connstr.ToString();
conn.Open();
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn);
cmd.CommandText = strSql;
nExcnt = cmd.ExecuteScalar();
cmd.Dispose();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("SQLite数据库:" + ex.ToString());
}
return nExcnt;
} public static Int32 Fun_ExeUpdate_TR(ref ArrayList strColl, string datasource)
{
Int32 nExcnt = ;
SQLiteTransaction tran = null;
try
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
//connstr.Password = "";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn);
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (object obj in strColl)
{
cmd.CommandText = obj.ToString();
cmd.ExecuteNonQuery();
}
tran.Commit();
}
catch
{
tran.Rollback();
} return nExcnt;
} public static DataTable Fun_Quary(string strsql, string datasource)
{
DataTable data = new DataTable();
try
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder(); conn = new System.Data.SQLite.SQLiteConnection();
connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
//connstr.Password = "";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn);
cmd.CommandText = strsql;
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.Fill(data);
cmd.Dispose();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("SQLite数据库:" + ex.ToString());
}
return data;
}
}
}