C#基于SQLiteHelper类似SqlHelper类实现存取Sqlite数据库的方法
程序员文章站
2023-11-12 23:53:40
本文实例讲述了c#基于sqlitehelper类似sqlhelper类实现存取sqlite数据库的方法。分享给大家供大家参考。具体如下:
这个类不是我实现的,英文原文地址...
本文实例讲述了c#基于sqlitehelper类似sqlhelper类实现存取sqlite数据库的方法。分享给大家供大家参考。具体如下:
这个类不是我实现的,英文原文地址为http://www.eggheadcafe.com/articles/20050315.asp,这里修改了原文中分析sql语句参数的方法,将方法名修改为attachparameters,将其修饰符修改为private,并直接传递command到这个方法,直接绑定参数到comand。修改后的代码如下
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 handle transaction.connection .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 handle transaction.connection .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 the command.connection 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 /// </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 handle transaction.connection .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]); 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: throw new systemexception("value is of unknown data type"); } // end switch j++; } return coll; } /// <summary> /// executes non query typed params from a datarow /// </summary> /// <param name="command">command.</param> /// <param name="datarow">data row.</param> /// <returns>integer result code</returns> public static int executenonquerytypedparams(idbcommand command, datarow datarow) { int retval = 0; // if the row has values, the store procedure parameters must be initialized if (datarow != null && datarow.itemarray.length > 0) { // set the parameters values assignparametervalues(command.parameters, datarow); retval = executenonquery(command); } else { retval = executenonquery(command); } return retval; } /// <summary> /// this method assigns datarow column values to an idataparametercollection /// </summary> /// <param name="commandparameters">the idataparametercollection to be assigned values</param> /// <param name="datarow">the datarow used to hold the command's parameter values</param> /// <exception cref="system.invalidoperationexception">thrown if any of the parameter names are invalid.</exception> protected internal static void assignparametervalues(idataparametercollection commandparameters, datarow datarow) { if (commandparameters == null || datarow == null) { // do nothing if we get no data return; } datacolumncollection columns = datarow.table.columns; int i = 0; // set the parameters values foreach (idataparameter commandparameter in commandparameters) { // check the parameter name if (commandparameter.parametername == null || commandparameter.parametername.length <= 1) throw new invalidoperationexception(string.format( "please provide a valid parameter name on the parameter #{0}, the parametername property has the following value: '{1}'.", i, commandparameter.parametername)); if (columns.contains(commandparameter.parametername)) commandparameter.value = datarow[commandparameter.parametername]; else if (columns.contains(commandparameter.parametername.substring(1))) commandparameter.value = datarow[commandparameter.parametername.substring(1)]; i++; } } /// <summary> /// this method assigns datarow column values to an array of idataparameters /// </summary> /// <param name="commandparameters">array of idataparameters to be assigned values</param> /// <param name="datarow">the datarow used to hold the stored procedure's parameter values</param> /// <exception cref="system.invalidoperationexception">thrown if any of the parameter names are invalid.</exception> protected void assignparametervalues(idataparameter[] commandparameters, datarow datarow) { if ((commandparameters == null) || (datarow == null)) { // do nothing if we get no data return; } datacolumncollection columns = datarow.table.columns; int i = 0; // set the parameters values foreach (idataparameter commandparameter in commandparameters) { // check the parameter name if (commandparameter.parametername == null || commandparameter.parametername.length <= 1) throw new invalidoperationexception(string.format( "please provide a valid parameter name on the parameter #{0}, the parametername property has the following value: '{1}'.", i, commandparameter.parametername)); if (columns.contains(commandparameter.parametername)) commandparameter.value = datarow[commandparameter.parametername]; else if (columns.contains(commandparameter.parametername.substring(1))) commandparameter.value = datarow[commandparameter.parametername.substring(1)]; i++; } } /// <summary> /// this method assigns an array of values to an array of idataparameters /// </summary> /// <param name="commandparameters">array of idataparameters to be assigned values</param> /// <param name="parametervalues">array of objects holding the values to be assigned</param> /// <exception cref="system.argumentexception">thrown if an incorrect number of parameters are passed.</exception> protected void assignparametervalues(idataparameter[] commandparameters, params object[] parametervalues) { if ((commandparameters == null) || (parametervalues == null)) { // do nothing if we get no data return; } // we must have the same number of values as we pave parameters to put them in if (commandparameters.length != parametervalues.length) { throw new argumentexception("parameter count does not match parameter value count."); } // iterate through the idataparameters, assigning the values from the corresponding position in the // value array for (int i = 0, j = commandparameters.length, k = 0; i < j; i++) { if (commandparameters[i].direction != parameterdirection.returnvalue) { // if the current array value derives from idataparameter, then assign its value property if (parametervalues[k] is idataparameter) { idataparameter paraminstance; 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; } } else if (parametervalues[k] == null) { commandparameters[i].value = dbnull.value; } else { commandparameters[i].value = parametervalues[k]; } k++; } } } } }
希望本文所述对大家的c#程序设计有所帮助。