C#操作SQLite数据库
SQLite介绍
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
SQLite是⼀个开源、免费的⼩型RDBMS(关系型数据库)，能独⽴运⾏、⽆服务器、零配置、⽀持事物，⽤C实现，内存占⽤较⼩，⽀持绝⼤数的SQL92标准。SQLite数据库官⽅主页：/index.html
C#操作SQLite Database
C#下SQLite操作驱动dll下载：
C#使⽤SQLite步骤：
（1）新建⼀个project
（2）添加SQLite操作驱动dll引⽤
（3）使⽤API操作SQLite DataBase
using System;
using System.Data.SQLite;
namespace SQLiteSamples
{
class Program
{
//数据库连接
SQLiteConnection m_dbConnection;
static void Main(string[] args)
{
Program p = new Program();
}
public Program()
{
createNewDatabase();
connectToDatabase();
createTable();
fillTable();
printHighscores();
}
//创建⼀个空的数据库
void createNewDatabase()
{
SQLiteConnection.CreateFile("MyDatabase.sqlite");
}
//创建⼀个连接到指定数据库
void connectToDatabase()
{
m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
m_dbConnection.Open();
}
//在指定数据库中创建⼀个table
void createTable()
{
string sql = "create table highscores (name varchar(20), score int)";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
}
//插⼊⼀些数据
void fillTable()
{
string sql = "insert into highscores (name, score) values ('Me', 3000)";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('Myself', 6000)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('And I', 9001)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
}
//使⽤sql查询语句，并显⽰结果
void printHighscores()
{
string sql = "select * from highscores order by score desc";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
Console.ReadLine();
}
}
}
关于SQLite的connection string说明：tionstrings/sqlite/
SQLite GUI客户端列表：/cvstrac/wiki?p=ManagementTools
SQLite Administrator下载地址：bmu2k.de/files/sqliteadmin.zip
操作SQLite Database的C#帮助类SQLite Helper
将⼀些常⽤的功能封装⼀下，封装成SQLite Helper类
using System;
using System.Data;
using System.Text.RegularExpressions;
using System.Xml;
using System.IO;
using System.Collections;
using System.Data.SQLite;
namespace DBUtility.SQLite
{
///<summary>
/// SQLiteHelper is a utility class similar to "SQLHelper" in MS
/// Data Access Application Block and follows similar pattern.
///</summary>
public class SQLiteHelper
{
///<summary>
/// Creates a new <see cref="SQLiteHelper"/> instance. The ctor is marked private since all members are static.
///</summary>
private SQLiteHelper()
{
}
///<summary>
/// Creates the command.
///</summary>
///<param name="connection">Connection.</param>
///<param name="commandText">Command text.</param>
///<param name="commandParameters">Command parameters.</param>
///<returns>SQLite Command</returns>
public static SQLiteCommand CreateCommand(SQLiteConnection connection, string commandText, params SQLiteParameter[] commandParameters)        {
SQLiteCommand cmd = new SQLiteCommand(commandText, connection);
if (commandParameters.Length > 0)
{
foreach (SQLiteParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
return cmd;
}
///<summary>
/// Creates the command.
///</summary>
/
//<param name="connectionString">Connection string.</param>
///<param name="commandText">Command text.</param>
///<param name="commandParameters">Command parameters.</param>
///<returns>SQLite Command</returns>
public static SQLiteCommand CreateCommand(string connectionString, string commandText, params SQLiteParameter[] commandParameters)
{
SQLiteConnection cn = new SQLiteConnection(connectionString);
SQLiteCommand cmd = new SQLiteCommand(commandText, cn);
if (commandParameters.Length > 0)
{
foreach (SQLiteParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
return cmd;
}
///<summary>
/// Creates the parameter.
///</summary>
///<param name="parameterName">Name of the parameter.</param>
///<param name="parameterType">Parameter type.</param>
///<param name="parameterValue">Parameter value.</param>
///<returns>SQLiteParameter</returns>
public static SQLiteParameter CreateParameter(string parameterName, System.Data.DbType parameterType, object parameterValue)
{
SQLiteParameter parameter = new SQLiteParameter();
parameter.DbType = parameterType;
parameter.ParameterName = parameterName;
parameter.Value = parameterValue;
return parameter;
}
///<summary>
/// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values
///</summary>
///<param name="connectionString">SQLite Connection string</param>
///<param name="commandText">SQL Statement with embedded "@param" style parameter names</param>
///<param name="paramList">object[] array of parameter values</param>
///<returns></returns>
public static DataSet ExecuteDataSet(string connectionString, string commandText, object[] paramList)
{
SQLiteConnection cn = new SQLiteConnection(connectionString);
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText = commandText;
if (paramList != null)
{
AttachParameters(cmd,commandText, paramList);
}
DataSet ds = new DataSet();
if (cn.State == ConnectionState.Closed)
cn.Open();
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Dispose();
cn.Close();
return ds;
}
///<summary>
/// Shortcut method to execute dataset from SQL Statement and object[] arrray of  parameter values
///</summary>
///<param name="cn">Connection.</param>
///<param name="commandText">Command text.</param>
///<param name="paramList">Param list.</param>
///<returns></returns>
public static DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object[] paramList)
{
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText = commandText;
if (paramList != null)
{
AttachParameters(cmd,commandText, paramList);
}
DataSet ds = new DataSet();
if (cn.State == ConnectionState.Closed)
cn.Open();
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Dispose();
cn.Close();
return ds;
}
///<summary>
/// Executes the dataset from a populated Command object.
///</summary>
///<param name="cmd">Fully populated SQLiteCommand</param>
///<returns>DataSet</returns>
public static DataSet ExecuteDataset(SQLiteCommand cmd)
{
if (cmd.Connection.State == ConnectionState.Closed)
cmd.Connection.Open();
DataSet ds = new DataSet();
SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Connection.Close();
cmd.Dispose();
return ds;
}
///<summary>
/
// Executes the dataset in a SQLite Transaction
///</summary>
///<param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction,  /// and Command, all of which must be created prior to making this method call. </param>
///<param name="commandText">Command text.</param>
///<param name="commandParameters">Sqlite Command parameters.</param>
///<returns>DataSet</returns>
///<remarks>user must examine Transaction Object and tion .Close, etc.</remarks>
public static DataSet ExecuteDataset(SQLiteTransaction transaction, string commandText, params SQLiteParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");
IDbCommand cmd = transaction.Connection.CreateCommand();
cmd.CommandText = commandText;
foreach (SQLiteParameter parm in commandParameters)
{
cmd.Parameters.Add(parm);
}
if (transaction.Connection.State == ConnectionState.Closed)
transaction.Connection.Open();
DataSet ds = ExecuteDataset((SQLiteCommand)cmd);
return ds;
}
///<summary>
/// Executes the dataset with Transaction and object array of parameter values.
///</summary>
///<param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction,    /// and Command, all of which must be created prior to making this method call. </param>
///<param name="commandText">Command text.</param>
///<param name="commandParameters">object[] array of parameter values.</param>
///<returns>DataSet</returns>
///<remarks>user must examine Transaction Object and tion .Close, etc.</remarks>
public static DataSet ExecuteDataset(SQLiteTransaction transaction, string commandText, object[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,                                                          please provide an open transaction.", "transaction            IDbCommand cmd = transaction.Connection.CreateCommand();
cmd.CommandText = commandText;
AttachParameters((SQLiteCommand)cmd,cmd.CommandText, commandParameters);
if (transaction.Connection.State == ConnectionState.Closed)
transaction.Connection.Open();
DataSet ds = ExecuteDataset((SQLiteCommand)cmd);
return ds;
}
#region UpdateDataset
///<summary>
/// Executes the respective command for each inserted, updated, or deleted row in the DataSet.
///</summary>
///<remarks>
/// e.g.:
///  UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
///</remarks>
///<param name="insertCommand">A valid SQL statement  to insert new records into the data source</param>
///<param name="deleteCommand">A valid SQL statement to delete records from the data source</param>
///<param name="updateCommand">A valid SQL statement used to update records in the data source</param>
///<param name="dataSet">The DataSet used to update the data source</param>
///<param name="tableName">The DataTable used to update the data source.</param>
public static void UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataSet dataSet, string tableName)
{
if (insertCommand == null) throw new ArgumentNullException("insertCommand");
if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
if (updateCommand == null) throw new ArgumentNullException("updateCommand");
if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");
// Create a SQLiteDataAdapter, and dispose of it after we are done
using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter())
{
// Set the data adapter commands
dataAdapter.UpdateCommand = updateCommand;
dataAdapter.InsertCommand = insertCommand;
dataAdapter.DeleteCommand = deleteCommand;
// Update the dataset changes in the data source
dataAdapter.Update(dataSet, tableName);
// Commit all the changes made to the DataSet
dataSet.AcceptChanges();
}
}
#endregion
///<summary>
/// ShortCut method to return IDataReader
/// NOTE: You should explicitly close tion you passed in as
/// well as call Dispose on the Command  after reader is closed.
/// We do this because IDataReader has no underlying Connection Property.
///</summary>
///<param name="cmd">SQLiteCommand Object</param>
/
//<param name="commandText">SQL Statement with optional embedded "@param" style parameters</param>
///<param name="paramList">object[] array of parameter values</param>
///<returns>IDataReader</returns>
public static IDataReader ExecuteReader(SQLiteCommand cmd, string commandText, object[] paramList)
{
if (cmd.Connection == null)
throw new ArgumentException("Command must have live connection attached.", "cmd");
cmd.CommandText = commandText;
AttachParameters(cmd,commandText, paramList);
if (cmd.Connection.State == ConnectionState.Closed)
cmd.Connection.Open();
IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return rdr;
}
///<summary>
/// Shortcut to ExecuteNonQuery with SqlStatement and object[] param values
connect下载///</summary>
///<param name="connectionString">SQLite Connection String</param>
///<param name="commandText">Sql Statement with embedded "@param" style parameters</param>
///<param name="paramList">object[] array of parameter values</param>
///<returns></returns>
public static int ExecuteNonQuery(string connectionString, string commandText, params object[] paramList)
{
SQLiteConnection cn = new SQLiteConnection(connectionString);
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText = commandText;
AttachParameters(cmd,commandText, paramList);
if (cn.State == ConnectionState.Closed)
cn.Open();
int result = cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
return result;
}
public static int ExecuteNonQuery(SQLiteConnection cn, string commandText, params object[] paramList)
{
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText = commandText;
AttachParameters(cmd,commandText, paramList);
if (cn.State == ConnectionState.Closed)
cn.Open();
int result = cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
return result;
}
///<summary>
/// Executes  non-query sql Statment with Transaction
///</summary>
///<param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction,  /// and Command, all of which must be created prior to making this method call. </param>
///<param name="commandText">Command text.</param>
///<param name="paramList">Param list.</param>
///<returns>Integer</returns>
/
//<remarks>user must examine Transaction Object and tion .Close, etc.</remarks>
public static int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, params object[] paramList)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,                                                        please provide an open transaction.", "transaction            IDbCommand cmd = transaction.Connection.CreateCommand();
cmd.CommandText = commandText;
AttachParameters((SQLiteCommand)cmd,cmd.CommandText, paramList);
if (transaction.Connection.State == ConnectionState.Closed)
transaction.Connection.Open();
int result = cmd.ExecuteNonQuery();
cmd.Dispose();
return result;
}
///<summary>
/// Executes the non query.
///</summary>
///<param name="cmd">CMD.</param>
///<returns></returns>
public static int ExecuteNonQuery(IDbCommand cmd)
{
if (cmd.Connection.State == ConnectionState.Closed)
cmd.Connection.Open();
int result = cmd.ExecuteNonQuery();
cmd.Connection.Close();
cmd.Dispose();
return result;
}
///<summary>
/// Shortcut to ExecuteScalar with Sql Statement embedded params and object[] param values
///</summary>
///<param name="connectionString">SQLite Connection String</param>
/
//<param name="commandText">SQL statment with embedded "@param" style parameters</param>
///<param name="paramList">object[] array of param values</param>
///<returns></returns>
public static object ExecuteScalar(string connectionString, string commandText, params object[] paramList)
{
SQLiteConnection cn = new SQLiteConnection(connectionString);
SQLiteCommand cmd = cn.CreateCommand();
cmd.CommandText = commandText;
AttachParameters(cmd,commandText, paramList);
if (cn.State == ConnectionState.Closed)
cn.Open();
object result = cmd.ExecuteScalar();
cmd.Dispose();
cn.Close();
return result;
}
///<summary>
/// Execute XmlReader with complete Command
///</summary>
///<param name="command">SQLite Command</param>
///<returns>XmlReader</returns>
public static XmlReader ExecuteXmlReader(IDbCommand command)
{ // open the connection if necessary, but make sure we
// know to close it when we�re done.
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
// get a data adapter
SQLiteDataAdapter da = new SQLiteDataAdapter((SQLiteCommand)command);
DataSet ds = new DataSet();
// fill the data set, and return the schema information
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.Fill(ds);
// convert our dataset to XML
StringReader stream = new StringReader(ds.GetXml());
command.Connection.Close();
// convert our stream of text to an XmlReader
return new XmlTextReader(stream);
}
///<summary>
/// Parses parameter names from SQL Statement, assigns values from object array ,  /// and returns fully populated ParameterCollection.
///</summary>
///<param name="commandText">Sql Statement with "@param" style embedded parameters</param>
///<param name="paramList">object[] array of parameter values</param>
///<returns>SQLiteParameterCollection</returns>
///<remarks>Status experimental. Regex appears to be handling most issues. Note that parameter object array must be in same ///order as parameter names appear in SQL statement.</remarks> private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList)
{
if (paramList == null || paramList.Length == 0) return null;
SQLiteParameterCollection coll = cmd.Parameters;
string parmString = commandText.Substring(commandText.IndexOf("@"));
// pre-process the string so always at least 1 space after a comma.
parmString = parmString.Replace(",", " ,");
/
/ get the named parameters into a match collection
string pattern = @"(@)\S*(.*?)\b";
Regex ex = new Regex(pattern, RegexOptions.IgnoreCase);
MatchCollection mc = ex.Matches(parmString);
string[] paramNames = new string[mc.Count];
int i = 0;
foreach (Match m in mc)
{
paramNames[i] = m.Value;
i++;
}
/
/ now let's type the parameters
int j = 0;
Type t = null;
foreach (object o in paramList)
{
t = o.GetType();
SQLiteParameter parm = new SQLiteParameter();
switch (t.ToString())
{
case ("DBNull"):
case ("Char"):
case ("SByte"):
case ("UInt16"):
case ("UInt32"):
case ("UInt64"):
throw new SystemException("Invalid data type");
case ("System.String"):
parm.DbType = DbType.String;
parm.ParameterName = paramNames[j];
parm.Value = (string)paramList[j];
coll.Add(parm);
break;
case ("System.Byte[]"):
parm.DbType = DbType.Binary;
parm.ParameterName = paramNames[j];
parm.Value = (byte[])paramList[j];
coll.Add(parm);
break;
case ("System.Int32"):
parm.DbType = DbType.Int32;
parm.ParameterName = paramNames[j];
parm.Value = (int)paramList[j];
coll.Add(parm);
break;
case ("System.Boolean"):
parm.DbType = DbType.Boolean;
parm.ParameterName = paramNames[j];
parm.Value = (bool)paramList[j];
coll.Add(parm);
break;
case ("System.DateTime"):
parm.DbType = DbType.DateTime;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToDateTime(paramList[j]);
coll.Add(parm);
break;
case ("System.Double"):
parm.DbType = DbType.Double;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToDouble(paramList[j]);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。