C#操作SQLiteDB.docx
- 文档编号:12326018
- 上传时间:2023-06-05
- 格式:DOCX
- 页数:28
- 大小:19.65KB
C#操作SQLiteDB.docx
《C#操作SQLiteDB.docx》由会员分享,可在线阅读,更多相关《C#操作SQLiteDB.docx(28页珍藏版)》请在冰点文库上搜索。
C#操作SQLiteDB
C#操作SQLiteDatabase
C#下SQLite操作驱动dll下载:
System.Data.SQLite
C#使用SQLite步骤:
(1)新建一个project
(2)添加SQLite操作驱动dll引用
(3)使用API操作SQLiteDataBase
usingSystem;
usingSystem.Data.SQLite;
namespaceSQLiteSamples
{
classProgram
{
//数据库连接
SQLiteConnectionm_dbConnection;
staticvoidMain(string[]args)
{
Programp=newProgram();
}
publicProgram()
{
createNewDatabase();
connectToDatabase();
createTable();
fillTable();
printHighscores();
}
//创建一个空的数据库
voidcreateNewDatabase()
{
SQLiteConnection.CreateFile("MyDatabase.sqlite");
}
//创建一个连接到指定数据库
voidconnectToDatabase()
{
m_dbConnection=newSQLiteConnection("DataSource=MyDatabase.sqlite;Version=3;");
m_dbConnection.Open();
}
//在指定数据库中创建一个table
voidcreateTable()
{
stringsql="createtablehighscores(namevarchar(20),scoreint)";
SQLiteCommandcommand=newSQLiteCommand(sql,m_dbConnection);
command.ExecuteNonQuery();
}
//插入一些数据
voidfillTable()
{
stringsql="insertintohighscores(name,score)values('Me',3000)";
SQLiteCommandcommand=newSQLiteCommand(sql,m_dbConnection);
command.ExecuteNonQuery();
sql="insertintohighscores(name,score)values('Myself',6000)";
command=newSQLiteCommand(sql,m_dbConnection);
command.ExecuteNonQuery();
sql="insertintohighscores(name,score)values('AndI',9001)";
command=newSQLiteCommand(sql,m_dbConnection);
command.ExecuteNonQuery();
}
//使用sql查询语句,并显示结果
voidprintHighscores()
{
stringsql="select*fromhighscoresorderbyscoredesc";
SQLiteCommandcommand=newSQLiteCommand(sql,m_dbConnection);
SQLiteDataReaderreader=command.ExecuteReader();
while(reader.Read())
Console.WriteLine("Name:
"+reader["name"]+"\tScore:
"+reader["score"]);
Console.ReadLine();
}
}
}
操作SQLiteDatabase的C#帮助类SQLiteHelper
将一些常用的功能封装一下,封装成SQLiteHelper类
usingSystem;
usingSystem.Data;
usingSystem.Text.RegularExpressions;
usingSystem.Xml;
usingSystem.IO;
usingSystem.Collections;
usingSystem.Data.SQLite;
namespaceDBUtility.SQLite
{
///
///SQLiteHelperisautilityclasssimilarto"SQLHelper"inMS
///DataAccessApplicationBlockandfollowssimilarpattern.
///
publicclassSQLiteHelper
{
///
///Createsanew
///
privateSQLiteHelper()
{
}
///
///Createsthecommand.
///
///
///
///
///
publicstaticSQLiteCommandCreateCommand(SQLiteConnectionconnection,stringcommandText,paramsSQLiteParameter[]commandParameters)
{
SQLiteCommandcmd=newSQLiteCommand(commandText,connection);
if(commandParameters.Length>0)
{
foreach(SQLiteParameterparmincommandParameters)
cmd.Parameters.Add(parm);
}
returncmd;
}
///
///Createsthecommand.
///
///
///
///
///
publicstaticSQLiteCommandCreateCommand(stringconnectionString,stringcommandText,paramsSQLiteParameter[]commandParameters)
{
SQLiteConnectioncn=newSQLiteConnection(connectionString);
SQLiteCommandcmd=newSQLiteCommand(commandText,cn);
if(commandParameters.Length>0)
{
foreach(SQLiteParameterparmincommandParameters)
cmd.Parameters.Add(parm);
}
returncmd;
}
///
///Createstheparameter.
///
///
///
///
///
publicstaticSQLiteParameterCreateParameter(stringparameterName,System.Data.DbTypeparameterType,objectparameterValue)
{
SQLiteParameterparameter=newSQLiteParameter();
parameter.DbType=parameterType;
parameter.ParameterName=parameterName;
parameter.Value=parameterValue;
returnparameter;
}
///
///ShortcutmethodtoexecutedatasetfromSQLStatementandobject[]arrrayofparametervalues
///
///
///
///
///
publicstaticDataSetExecuteDataSet(stringconnectionString,stringcommandText,object[]paramList)
{
SQLiteConnectioncn=newSQLiteConnection(connectionString);
SQLiteCommandcmd=cn.CreateCommand();
cmd.CommandText=commandText;
if(paramList!
=null)
{
AttachParameters(cmd,commandText,paramList);
}
DataSetds=newDataSet();
if(cn.State==ConnectionState.Closed)
cn.Open();
SQLiteDataAdapterda=newSQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Dispose();
cn.Close();
returnds;
}
///
///ShortcutmethodtoexecutedatasetfromSQLStatementandobject[]arrrayofparametervalues
///
///
///
///
///
publicstaticDataSetExecuteDataSet(SQLiteConnectioncn,stringcommandText,object[]paramList)
{
SQLiteCommandcmd=cn.CreateCommand();
cmd.CommandText=commandText;
if(paramList!
=null)
{
AttachParameters(cmd,commandText,paramList);
}
DataSetds=newDataSet();
if(cn.State==ConnectionState.Closed)
cn.Open();
SQLiteDataAdapterda=newSQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Dispose();
cn.Close();
returnds;
}
///
///ExecutesthedatasetfromapopulatedCommandobject.
///
///
///
publicstaticDataSetExecuteDataset(SQLiteCommandcmd)
{
if(cmd.Connection.State==ConnectionState.Closed)
cmd.Connection.Open();
DataSetds=newDataSet();
SQLiteDataAdapterda=newSQLiteDataAdapter(cmd);
da.Fill(ds);
da.Dispose();
cmd.Connection.Close();
cmd.Dispose();
returnds;
}
///
///ExecutesthedatasetinaSQLiteTransaction
///
///
///
///
///
///
publicstaticDataSetExecuteDataset(SQLiteTransactiontransaction,stringcommandText,paramsSQLiteParameter[]commandParameters)
{
if(transaction==null)thrownewArgumentNullException("transaction");
if(transaction!
=null&&transaction.Connection==null)thrownewArgumentException("Thetransactionwasrolledbackorcommitted,pleaseprovideanopentransaction.","transaction");
IDbCommandcmd=transaction.Connection.CreateCommand();
cmd.CommandText=commandText;
foreach(SQLiteParameterparmincommandParameters)
{
cmd.Parameters.Add(parm);
}
if(transaction.Connection.State==ConnectionState.Closed)
transaction.Connection.Open();
DataSetds=ExecuteDataset((SQLiteCommand)cmd);
returnds;
}
///
///ExecutesthedatasetwithTransactionandobjectarrayofparametervalues.
///
///
///
///
///
///
publicstaticDataSetExecuteDataset(SQLiteTransactiontransaction,stringcommandText,object[]commandParameters)
{
if(transaction==null)thrownewArgumentNullException("transaction");
if(transaction!
=null&&transaction.Connection==null)thrownewArgumentException("Thetransactionwasrolledbackorcommitted,pleaseprovideanopentransaction.","transaction");
IDbCommandcmd=transaction.Connection.CreateCommand();
cmd.CommandText=commandText;
AttachParameters((SQLiteCommand)cmd,cmd.CommandText,commandParameters);
if(transaction.Connection.State==ConnectionState.Closed)
transaction.Connection.Open();
DataSetds=ExecuteDataset((SQLiteCommand)cmd);
returnds;
}
#regionUpdateDataset
///
///Executestherespectivecommandforeachinserted,updated,ordeletedrowintheDataSet.
///
///
///e.g.:
///UpdateDataset(conn,insertCommand,deleteCommand,updateCommand,dataSet,"Order");
///
///
///
///
///
///
p
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- C# 操作 SQLiteDB