C#基于SQLiteHelper类似SqlHelper类实现存取Sqlite数据库的方法
本文实例讲述了C#基于SQLiteHelper类似SqlHelper类实现存取Sqlite数据库的方法。分享给大家供大家参考。具体如下：
这个类不是我实现的，英文原文地址为http://www.eggheadcafe.com/articles/20050315.asp，这里修改了原文中分析sql语句参数的方法，将方法名修改为AttachParameters，将其修饰符修改为private，并直接传递command到这个方法，直接绑定参数到comand。修改后的代码如下
usingSystem;
usingSystem.Data;
usingSystem.Text.RegularExpressions;
usingSystem.Xml;
usingSystem.IO;
usingSystem.Collections;
usingSystem.Data.SQLite;
namespaceDBUtility.SQLite
{
///<summary>
///SQLiteHelperisautilityclasssimilarto"SQLHelper"inMS
///DataAccessApplicationBlockandfollowssimilarpattern.
///</summary>
publicclassSQLiteHelper
{
///<summary>
///Createsanew<seecref="SQLiteHelper"/>instance.Thectorismarkedprivatesinceallmembersarestatic.
///</summary>
privateSQLiteHelper()
{
}
///<summary>
///Createsthecommand.
///</summary>
///<paramname="connection">Connection.</param>
///<paramname="commandText">Commandtext.</param>
///<paramname="commandParameters">Commandparameters.</param>
///<returns>SQLiteCommand</returns>
publicstaticSQLiteCommandCreateCommand(SQLiteConnectionconnection,stringcommandText,paramsSQLiteParameter[]commandParameters)
{
SQLiteCommandcmd=newSQLiteCommand(commandText,connection);
if(commandParameters.Length>0)
{
foreach(SQLiteParameterparmincommandParameters)
cmd.Parameters.Add(parm);
}
returncmd;
}
///<summary>
///Createsthecommand.
///</summary>
///<paramname="connectionString">Connectionstring.</param>
///<paramname="commandText">Commandtext.</param>
///<paramname="commandParameters">Commandparameters.</param>
///<returns>SQLiteCommand</returns>
publicstaticSQLiteCommandCreateCommand(stringconnectionString,stringcommandText,paramsSQLiteParameter[]commandParameters)
{
SQLiteConnectioncn=newSQLiteConnection(connectionString);
SQLiteCommandcmd=newSQLiteCommand(commandText,cn);
if(commandParameters.Length>0)
{
foreach(SQLiteParameterparmincommandParameters)
cmd.Parameters.Add(parm);
}
returncmd;
}
///<summary>
///Createstheparameter.
///</summary>
///<paramname="parameterName">Nameoftheparameter.</param>
///<paramname="parameterType">Parametertype.</param>
///<paramname="parameterValue">Parametervalue.</param>
///<returns>SQLiteParameter</returns>
publicstaticSQLiteParameterCreateParameter(stringparameterName,System.Data.DbTypeparameterType,objectparameterValue)
{
SQLiteParameterparameter=newSQLiteParameter();
parameter.DbType=parameterType;
parameter.ParameterName=parameterName;
parameter.Value=parameterValue;
returnparameter;
}
///<summary>
///ShortcutmethodtoexecutedatasetfromSQLStatementandobject[]arrrayofparametervalues
///</summary>
///<paramname="connectionString">SQLiteConnectionstring</param>
///<paramname="commandText">SQLStatementwithembedded"@param"styleparameternames</param>
///<paramname="paramList">object[]arrayofparametervalues</param>
///<returns></returns>
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;
}
///<summary>
///ShortcutmethodtoexecutedatasetfromSQLStatementandobject[]arrrayofparametervalues
///</summary>
///<paramname="cn">Connection.</param>
///<paramname="commandText">Commandtext.</param>
///<paramname="paramList">Paramlist.</param>
///<returns></returns>
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;
}
///<summary>
///ExecutesthedatasetfromapopulatedCommandobject.
///</summary>
///<paramname="cmd">FullypopulatedSQLiteCommand</param>
///<returns>DataSet</returns>
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;
}
///<summary>
///ExecutesthedatasetinaSQLiteTransaction
///</summary>
///<paramname="transaction">SQLiteTransaction.TransactionconsistsofConnection,Transaction,///andCommand,allofwhichmustbecreatedpriortomakingthismethodcall.</param>
///<paramname="commandText">Commandtext.</param>
///<paramname="commandParameters">SqliteCommandparameters.</param>
///<returns>DataSet</returns>
///<remarks>usermustexamineTransactionObjectandhandletransaction.connection.Close,etc.</remarks>
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;
}
///<summary>
///ExecutesthedatasetwithTransactionandobjectarrayofparametervalues.
///</summary>
///<paramname="transaction">SQLiteTransaction.TransactionconsistsofConnection,Transaction,///andCommand,allofwhichmustbecreatedpriortomakingthismethodcall.</param>
///<paramname="commandText">Commandtext.</param>
///<paramname="commandParameters">object[]arrayofparametervalues.</param>
///<returns>DataSet</returns>
///<remarks>usermustexamineTransactionObjectandhandletransaction.connection.Close,etc.</remarks>
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
///<summary>
///Executestherespectivecommandforeachinserted,updated,ordeletedrowintheDataSet.
///</summary>
///<remarks>
///e.g.:
///UpdateDataset(conn,insertCommand,deleteCommand,updateCommand,dataSet,"Order");
///</remarks>
///<paramname="insertCommand">AvalidSQLstatementtoinsertnewrecordsintothedatasource</param>
///<paramname="deleteCommand">AvalidSQLstatementtodeleterecordsfromthedatasource</param>
///<paramname="updateCommand">AvalidSQLstatementusedtoupdaterecordsinthedatasource</param>
///<paramname="dataSet">TheDataSetusedtoupdatethedatasource</param>
///<paramname="tableName">TheDataTableusedtoupdatethedatasource.</param>
publicstaticvoidUpdateDataset(SQLiteCommandinsertCommand,SQLiteCommanddeleteCommand,SQLiteCommandupdateCommand,DataSetdataSet,stringtableName)
{
if(insertCommand==null)thrownewArgumentNullException("insertCommand");
if(deleteCommand==null)thrownewArgumentNullException("deleteCommand");
if(updateCommand==null)thrownewArgumentNullException("updateCommand");
if(tableName==null||tableName.Length==0)thrownewArgumentNullException("tableName");
//CreateaSQLiteDataAdapter,anddisposeofitafterwearedone
using(SQLiteDataAdapterdataAdapter=newSQLiteDataAdapter())
{
//Setthedataadaptercommands
dataAdapter.UpdateCommand=updateCommand;
dataAdapter.InsertCommand=insertCommand;
dataAdapter.DeleteCommand=deleteCommand;
//Updatethedatasetchangesinthedatasource
dataAdapter.Update(dataSet,tableName);
//CommitallthechangesmadetotheDataSet
dataSet.AcceptChanges();
}
}
#endregion
///<summary>
///ShortCutmethodtoreturnIDataReader
///NOTE:YoushouldexplicitlyclosetheCommand.connectionyoupassedinas
///wellascallDisposeontheCommandafterreaderisclosed.
///WedothisbecauseIDataReaderhasnounderlyingConnectionProperty.
///</summary>
///<paramname="cmd">SQLiteCommandObject</param>
///<paramname="commandText">SQLStatementwithoptionalembedded"@param"styleparameters</param>
///<paramname="paramList">object[]arrayofparametervalues</param>
///<returns>IDataReader</returns>
publicstaticIDataReaderExecuteReader(SQLiteCommandcmd,stringcommandText,object[]paramList)
{
if(cmd.Connection==null)
thrownewArgumentException("Commandmusthaveliveconnectionattached.","cmd");
cmd.CommandText=commandText;
AttachParameters(cmd,commandText,paramList);
if(cmd.Connection.State==ConnectionState.Closed)
cmd.Connection.Open();
IDataReaderrdr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
returnrdr;
}
///<summary>
///ShortcuttoExecuteNonQuerywithSqlStatementandobject[]paramvalues
///</summary>
///<paramname="connectionString">SQLiteConnectionString</param>
///<paramname="commandText">SqlStatementwithembedded"@param"styleparameters</param>
///<paramname="paramList">object[]arrayofparametervalues</param>
///<returns></returns>
publicstaticintExecuteNonQuery(stringconnectionString,stringcommandText,paramsobject[]paramList)
{
SQLiteConnectioncn=newSQLiteConnection(connectionString);
SQLiteCommandcmd=cn.CreateCommand();
cmd.CommandText=commandText;
AttachParameters(cmd,commandText,paramList);
if(cn.State==ConnectionState.Closed)
cn.Open();
intresult=cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
returnresult;
}
publicstaticintExecuteNonQuery(SQLiteConnectioncn,stringcommandText,paramsobject[]paramList)
{
SQLiteCommandcmd=cn.CreateCommand();
cmd.CommandText=commandText;
AttachParameters(cmd,commandText,paramList);
if(cn.State==ConnectionState.Closed)
cn.Open();
intresult=cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
returnresult;
}
///<summary>
///Executesnon-querysqlStatmentwithTransaction
///</summary>
///<paramname="transaction">SQLiteTransaction.TransactionconsistsofConnection,Transaction,///andCommand,allofwhichmustbecreatedpriortomakingthismethodcall.</param>
///<paramname="commandText">Commandtext.</param>
///<paramname="paramList">Paramlist.</param>
///<returns>Integer</returns>
///<remarks>usermustexamineTransactionObjectandhandletransaction.connection.Close,etc.</remarks>
publicstaticintExecuteNonQuery(SQLiteTransactiontransaction,stringcommandText,paramsobject[]paramList)
{
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,paramList);
if(transaction.Connection.State==ConnectionState.Closed)
transaction.Connection.Open();
intresult=cmd.ExecuteNonQuery();
cmd.Dispose();
returnresult;
}
///<summary>
///Executesthenonquery.
///</summary>
///<paramname="cmd">CMD.</param>
///<returns></returns>
publicstaticintExecuteNonQuery(IDbCommandcmd)
{
if(cmd.Connection.State==ConnectionState.Closed)
cmd.Connection.Open();
intresult=cmd.ExecuteNonQuery();
cmd.Connection.Close();
cmd.Dispose();
returnresult;
}
///<summary>
///ShortcuttoExecuteScalarwithSqlStatementembeddedparamsandobject[]paramvalues
///</summary>
///<paramname="connectionString">SQLiteConnectionString</param>
///<paramname="commandText">SQLstatmentwithembedded"@param"styleparameters</param>
///<paramname="paramList">object[]arrayofparamvalues</param>
///<returns></returns>
publicstaticobjectExecuteScalar(stringconnectionString,stringcommandText,paramsobject[]paramList)
{
SQLiteConnectioncn=newSQLiteConnection(connectionString);
SQLiteCommandcmd=cn.CreateCommand();
cmd.CommandText=commandText;
AttachParameters(cmd,commandText,paramList);
if(cn.State==ConnectionState.Closed)
cn.Open();
objectresult=cmd.ExecuteScalar();
cmd.Dispose();
cn.Close();
returnresult;
}
///<summary>
///ExecuteXmlReaderwithcompleteCommand
///</summary>
///<paramname="command">SQLiteCommand</param>
///<returns>XmlReader</returns>
publicstaticXmlReaderExecuteXmlReader(IDbCommandcommand)
{//opentheconnectionifnecessary,butmakesurewe
//knowtocloseitwhenwe�redone.
if(command.Connection.State!=ConnectionState.Open)
{
command.Connection.Open();
}
//getadataadapter
SQLiteDataAdapterda=newSQLiteDataAdapter((SQLiteCommand)command);
DataSetds=newDataSet();
//fillthedataset,andreturntheschemainformation
da.MissingSchemaAction=MissingSchemaAction.AddWithKey;
da.Fill(ds);
//convertourdatasettoXML
StringReaderstream=newStringReader(ds.GetXml());
command.Connection.Close();
//convertourstreamoftexttoanXmlReader
returnnewXmlTextReader(stream);
}
///<summary>
///ParsesparameternamesfromSQLStatement,assignsvaluesfromobjectarray,///andreturnsfullypopulatedParameterCollection.
///</summary>
///<paramname="commandText">SqlStatementwith"@param"styleembeddedparameters</param>
///<paramname="paramList">object[]arrayofparametervalues</param>
///<returns>SQLiteParameterCollection</returns>
///<remarks>Statusexperimental.Regexappearstobehandlingmostissues.Notethatparameterobjectarraymustbeinsame///orderasparameternamesappearinSQLstatement.</remarks>
privatestaticSQLiteParameterCollectionAttachParameters(SQLiteCommandcmd,stringcommandText,paramsobject[]paramList)
{
if(paramList==null||paramList.Length==0)returnnull;
SQLiteParameterCollectioncoll=cmd.Parameters;
stringparmString=commandText.Substring(commandText.IndexOf("@"));
//pre-processthestringsoalwaysatleast1spaceafteracomma.
parmString=parmString.Replace(",",",");
//getthenamedparametersintoamatchcollection
stringpattern=@"(@)\S*(.*?)\b";
Regexex=newRegex(pattern,RegexOptions.IgnoreCase);
MatchCollectionmc=ex.Matches(parmString);
string[]paramNames=newstring[mc.Count];
inti=0;
foreach(Matchminmc)
{
paramNames[i]=m.Value;
i++;
}
//nowlet'stypetheparameters
intj=0;
Typet=null;
foreach(objectoinparamList)
{
t=o.GetType();
SQLiteParameterparm=newSQLiteParameter();
switch(t.ToString())
{
case("DBNull"):
case("Char"):
case("SByte"):
case("UInt16"):
case("UInt32"):
case("UInt64"):
thrownewSystemException("Invaliddatatype");
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]);
coll.Add(parm);
break;
case("System.Decimal"):
parm.DbType=DbType.Decimal;
parm.ParameterName=paramNames[j];
parm.Value=Convert.ToDecimal(paramList[j]);
break;
case("System.Guid"):
parm.DbType=DbType.Guid;
parm.ParameterName=paramNames[j];
parm.Value=(System.Guid)(paramList[j]);
break;
case("System.Object"):
parm.DbType=DbType.Object;
parm.ParameterName=paramNames[j];
parm.Value=paramList[j];
coll.Add(parm);
break;
default:
thrownewSystemException("Valueisofunknowndatatype");
}//endswitch
j++;
}
returncoll;
}
///<summary>
///ExecutesnonquerytypedparamsfromaDataRow
///</summary>
///<paramname="command">Command.</param>
///<paramname="dataRow">Datarow.</param>
///<returns>Integerresultcode</returns>
publicstaticintExecuteNonQueryTypedParams(IDbCommandcommand,DataRowdataRow)
{
intretVal=0;
//Iftherowhasvalues,thestoreprocedureparametersmustbeinitialized
if(dataRow!=null&&dataRow.ItemArray.Length>0)
{
//Settheparametersvalues
AssignParameterValues(command.Parameters,dataRow);
retVal=ExecuteNonQuery(command);
}
else
{
retVal=ExecuteNonQuery(command);
}
returnretVal;
}
///<summary>
///ThismethodassignsdataRowcolumnvaluestoanIDataParameterCollection
///</summary>
///<paramname="commandParameters">TheIDataParameterCollectiontobeassignedvalues</param>
///<paramname="dataRow">ThedataRowusedtoholdthecommand'sparametervalues</param>
///<exceptioncref="System.InvalidOperationException">Thrownifanyoftheparameternamesareinvalid.</exception>
protectedinternalstaticvoidAssignParameterValues(IDataParameterCollectioncommandParameters,DataRowdataRow)
{
if(commandParameters==null||dataRow==null)
{
//Donothingifwegetnodata
return;
}
DataColumnCollectioncolumns=dataRow.Table.Columns;
inti=0;
//Settheparametersvalues
foreach(IDataParametercommandParameterincommandParameters)
{
//Checktheparametername
if(commandParameter.ParameterName==null||
commandParameter.ParameterName.Length<=1)
thrownewInvalidOperationException(string.Format(
"Pleaseprovideavalidparameternameontheparameter#{0},theParameterNamepropertyhasthefollowingvalue:'{1}'.",
i,commandParameter.ParameterName));
if(columns.Contains(commandParameter.ParameterName))
commandParameter.Value=dataRow[commandParameter.ParameterName];
elseif(columns.Contains(commandParameter.ParameterName.Substring(1)))
commandParameter.Value=dataRow[commandParameter.ParameterName.Substring(1)];
i++;
}
}
///<summary>
///ThismethodassignsdataRowcolumnvaluestoanarrayofIDataParameters
///</summary>
///<paramname="commandParameters">ArrayofIDataParameterstobeassignedvalues</param>
///<paramname="dataRow">ThedataRowusedtoholdthestoredprocedure'sparametervalues</param>
///<exceptioncref="System.InvalidOperationException">Thrownifanyoftheparameternamesareinvalid.</exception>
protectedvoidAssignParameterValues(IDataParameter[]commandParameters,DataRowdataRow)
{
if((commandParameters==null)||(dataRow==null))
{
//Donothingifwegetnodata
return;
}
DataColumnCollectioncolumns=dataRow.Table.Columns;
inti=0;
//Settheparametersvalues
foreach(IDataParametercommandParameterincommandParameters)
{
//Checktheparametername
if(commandParameter.ParameterName==null||
commandParameter.ParameterName.Length<=1)
thrownewInvalidOperationException(string.Format(
"Pleaseprovideavalidparameternameontheparameter#{0},theParameterNamepropertyhasthefollowingvalue:'{1}'.",
i,commandParameter.ParameterName));
if(columns.Contains(commandParameter.ParameterName))
commandParameter.Value=dataRow[commandParameter.ParameterName];
elseif(columns.Contains(commandParameter.ParameterName.Substring(1)))
commandParameter.Value=dataRow[commandParameter.ParameterName.Substring(1)];
i++;
}
}
///<summary>
///ThismethodassignsanarrayofvaluestoanarrayofIDataParameters
///</summary>
///<paramname="commandParameters">ArrayofIDataParameterstobeassignedvalues</param>
///<paramname="parameterValues">Arrayofobjectsholdingthevaluestobeassigned</param>
///<exceptioncref="System.ArgumentException">Thrownifanincorrectnumberofparametersarepassed.</exception>
protectedvoidAssignParameterValues(IDataParameter[]commandParameters,paramsobject[]parameterValues)
{
if((commandParameters==null)||(parameterValues==null))
{
//Donothingifwegetnodata
return;
}
//Wemusthavethesamenumberofvaluesaswepaveparameterstoputthemin
if(commandParameters.Length!=parameterValues.Length)
{
thrownewArgumentException("ParametercountdoesnotmatchParameterValuecount.");
}
//IteratethroughtheIDataParameters,assigningthevaluesfromthecorrespondingpositioninthe
//valuearray
for(inti=0,j=commandParameters.Length,k=0;i<j;i++)
{
if(commandParameters[i].Direction!=ParameterDirection.ReturnValue)
{
//IfthecurrentarrayvaluederivesfromIDataParameter,thenassignitsValueproperty
if(parameterValues[k]isIDataParameter)
{
IDataParameterparamInstance;
paramInstance=(IDataParameter)parameterValues[k];
if(paramInstance.Direction==ParameterDirection.ReturnValue)
{
paramInstance=(IDataParameter)parameterValues[++k];
}
if(paramInstance.Value==null)
{
commandParameters[i].Value=DBNull.Value;
}
else
{
commandParameters[i].Value=paramInstance.Value;
}
}
elseif(parameterValues[k]==null)
{
commandParameters[i].Value=DBNull.Value;
}
else
{
commandParameters[i].Value=parameterValues[k];
}
k++;
}
}
}
}
}
希望本文所述对大家的C#程序设计有所帮助。