1 using System; 2 using System.Data.SQLite; 3   4 namespace SQLiteSamples 5 { 6     class Program 7     { 8         //数据库连接 9         SQLiteConnection m_dbConnection;10  11         static void Main(string[] args)12         {13             Program p = new Program();14         }15  16         public Program()17         {18             createNewDatabase();19             connectToDatabase();20             createTable();21             fillTable();22             printHighscores();23         }24  25         //创建一个空的数据库26         void createNewDatabase()27         {28             SQLiteConnection.CreateFile("MyDatabase.sqlite");29         }30  31         //创建一个连接到指定数据库32         void connectToDatabase()33         {34             m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");35             m_dbConnection.Open();36         }37  38         //在指定数据库中创建一个table39         void createTable()40         {41             string sql = "create table highscores (name varchar(20), score int)";42             SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);43             command.ExecuteNonQuery();44         }45  46         //插入一些数据47         void fillTable()48         {49             string sql = "insert into highscores (name, score) values (‘Me‘, 3000)";50             SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);51             command.ExecuteNonQuery();52  53             sql = "insert into highscores (name, score) values (‘Myself‘, 6000)";54             command = new SQLiteCommand(sql, m_dbConnection);55             command.ExecuteNonQuery();56  57             sql = "insert into highscores (name, score) values (‘And I‘, 9001)";58             command = new SQLiteCommand(sql, m_dbConnection);59             command.ExecuteNonQuery();60         }61  62         //使用sql查询语句，并显示结果63         void printHighscores()64         {65             string sql = "select * from highscores order by score desc";66             SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);67             SQLiteDataReader reader = command.ExecuteReader();68             while (reader.Read())69                 Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);70             Console.ReadLine();71         }72     }73 }

  1 using System;  2 using System.Data;  3 using System.Text.RegularExpressions;  4 using System.Xml;  5 using System.IO;  6 using System.Collections;  7 using System.Data.SQLite;  8    9 namespace DBUtility.SQLite 10 { 11     /// <summary> 12     /// SQLiteHelper is a utility class similar to "SQLHelper" in MS 13     /// Data Access Application Block and follows similar pattern. 14     /// </summary> 15     public class SQLiteHelper 16     { 17         /// <summary> 18         /// Creates a new <see cref="SQLiteHelper"/> instance. The ctor is marked private since all members are static. 19         /// </summary> 20         private SQLiteHelper() 21         { 22         } 23         /// <summary> 24         /// Creates the command. 25         /// </summary> 26         /// <param name="connection">Connection.</param> 27         /// <param name="commandText">Command text.</param> 28         /// <param name="commandParameters">Command parameters.</param> 29         /// <returns>SQLite Command</returns> 30         public static SQLiteCommand CreateCommand(SQLiteConnection connection, string commandText, params SQLiteParameter[] commandParameters) 31         { 32             SQLiteCommand cmd = new SQLiteCommand(commandText, connection); 33             if (commandParameters.Length > 0) 34             { 35                 foreach (SQLiteParameter parm in commandParameters) 36                     cmd.Parameters.Add(parm); 37             } 38             return cmd; 39         } 40   41         /// <summary> 42         /// Creates the command. 43         /// </summary> 44         /// <param name="connectionString">Connection string.</param> 45         /// <param name="commandText">Command text.</param> 46         /// <param name="commandParameters">Command parameters.</param> 47         /// <returns>SQLite Command</returns> 48         public static SQLiteCommand CreateCommand(string connectionString, string commandText, params SQLiteParameter[] commandParameters) 49         { 50             SQLiteConnection cn = new SQLiteConnection(connectionString); 51   52             SQLiteCommand cmd = new SQLiteCommand(commandText, cn); 53   54             if (commandParameters.Length > 0) 55             { 56                 foreach (SQLiteParameter parm in commandParameters) 57                     cmd.Parameters.Add(parm); 58             } 59             return cmd; 60         } 61         /// <summary> 62         /// Creates the parameter. 63         /// </summary> 64         /// <param name="parameterName">Name of the parameter.</param> 65         /// <param name="parameterType">Parameter type.</param> 66         /// <param name="parameterValue">Parameter value.</param> 67         /// <returns>SQLiteParameter</returns> 68         public static SQLiteParameter CreateParameter(string parameterName, System.Data.DbType parameterType, object parameterValue) 69         { 70             SQLiteParameter parameter = new SQLiteParameter(); 71             parameter.DbType = parameterType; 72             parameter.ParameterName = parameterName; 73             parameter.Value =http://www.mamicode.com/ parameterValue; 74             return parameter; 75         } 76   77         /// <summary> 78         /// Shortcut method to execute dataset from SQL Statement and object[] arrray of parameter values 79         /// </summary> 80         /// <param name="connectionString">SQLite Connection string</param> 81         /// <param name="commandText">SQL Statement with embedded "@param" style parameter names</param> 82         /// <param name="paramList">object[] array of parameter values</param> 83         /// <returns></returns> 84         public static DataSet ExecuteDataSet(string connectionString, string commandText, object[] paramList) 85         { 86             SQLiteConnection cn = new SQLiteConnection(connectionString); 87             SQLiteCommand cmd = cn.CreateCommand(); 88   89   90             cmd.CommandText = commandText; 91             if (paramList != null) 92             { 93                 AttachParameters(cmd,commandText, paramList); 94             } 95             DataSet ds = new DataSet(); 96             if (cn.State == ConnectionState.Closed) 97                 cn.Open(); 98             SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); 99             da.Fill(ds);100             da.Dispose();101             cmd.Dispose();102             cn.Close();103             return ds;104         }105         /// <summary>106         /// Shortcut method to execute dataset from SQL Statement and object[] arrray of  parameter values107         /// </summary>108         /// <param name="cn">Connection.</param>109         /// <param name="commandText">Command text.</param>110         /// <param name="paramList">Param list.</param>111         /// <returns></returns>112         public static DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object[] paramList)113         {114  115             SQLiteCommand cmd = cn.CreateCommand();116  117  118             cmd.CommandText = commandText;119             if (paramList != null)120             {121                 AttachParameters(cmd,commandText, paramList);122             }123             DataSet ds = new DataSet();124             if (cn.State == ConnectionState.Closed)125                 cn.Open();126             SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);127             da.Fill(ds);128             da.Dispose();129             cmd.Dispose();130             cn.Close();131             return ds;132         }133         /// <summary>134         /// Executes the dataset from a populated Command object.135         /// </summary>136         /// <param name="cmd">Fully populated SQLiteCommand</param>137         /// <returns>DataSet</returns>138         public static DataSet ExecuteDataset(SQLiteCommand cmd)139         {140             if (cmd.Connection.State == ConnectionState.Closed)141                 cmd.Connection.Open();142             DataSet ds = new DataSet();143             SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);144             da.Fill(ds);145             da.Dispose();146             cmd.Connection.Close();147             cmd.Dispose();148             return ds;149         }150  151         /// <summary>152         /// Executes the dataset in a SQLite Transaction153         /// </summary>154         /// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction,  /// and Command, all of which must be created prior to making this method call. </param>155         /// <param name="commandText">Command text.</param>156         /// <param name="commandParameters">Sqlite Command parameters.</param>157         /// <returns>DataSet</returns>158         /// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>159         public static DataSet ExecuteDataset(SQLiteTransaction transaction, string commandText, params SQLiteParameter[] commandParameters)160         {161  162             if (transaction == null) throw new ArgumentNullException("transaction");163             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", "transaction");164             IDbCommand cmd = transaction.Connection.CreateCommand();165             cmd.CommandText = commandText;166             foreach (SQLiteParameter parm in commandParameters)167             {168                 cmd.Parameters.Add(parm);169             }170             if (transaction.Connection.State == ConnectionState.Closed)171                 transaction.Connection.Open();172             DataSet ds = ExecuteDataset((SQLiteCommand)cmd);173             return ds;174         }175  176         /// <summary>177         /// Executes the dataset with Transaction and object array of parameter values.178         /// </summary>179         /// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction,    /// and Command, all of which must be created prior to making this method call. </param>180         /// <param name="commandText">Command text.</param>181         /// <param name="commandParameters">object[] array of parameter values.</param>182         /// <returns>DataSet</returns>183         /// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>184         public static DataSet ExecuteDataset(SQLiteTransaction transaction, string commandText, object[] commandParameters)185         {186  187             if (transaction == null) throw new ArgumentNullException("transaction");188             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,                                                          please provide an open transaction.", "transaction");189             IDbCommand cmd = transaction.Connection.CreateCommand();190             cmd.CommandText = commandText;191             AttachParameters((SQLiteCommand)cmd,cmd.CommandText, commandParameters);192             if (transaction.Connection.State == ConnectionState.Closed)193                 transaction.Connection.Open();194  195             DataSet ds = ExecuteDataset((SQLiteCommand)cmd);196             return ds;197         }198  199         #region UpdateDataset200         /// <summary>201         /// Executes the respective command for each inserted, updated, or deleted row in the DataSet.202         /// </summary>203         /// <remarks>204         /// e.g.: 205         ///  UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");206         /// </remarks>207         /// <param name="insertCommand">A valid SQL statement  to insert new records into the data source</param>208         /// <param name="deleteCommand">A valid SQL statement to delete records from the data source</param>209         /// <param name="updateCommand">A valid SQL statement used to update records in the data source</param>210         /// <param name="dataSet">The DataSet used to update the data source</param>211         /// <param name="tableName">The DataTable used to update the data source.</param>212         public static void UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataSet dataSet, string tableName)213         {214             if (insertCommand == null) throw new ArgumentNullException("insertCommand");215             if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");216             if (updateCommand == null) throw new ArgumentNullException("updateCommand");217             if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");218  219             // Create a SQLiteDataAdapter, and dispose of it after we are done220             using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter())221             {222                 // Set the data adapter commands223                 dataAdapter.UpdateCommand = updateCommand;224                 dataAdapter.InsertCommand = insertCommand;225                 dataAdapter.DeleteCommand = deleteCommand;226  227                 // Update the dataset changes in the data source228                 dataAdapter.Update(dataSet, tableName);229  230                 // Commit all the changes made to the DataSet231                 dataSet.AcceptChanges();232             }233         }234         #endregion235  236  237  238  239         /// <summary>240         /// ShortCut method to return IDataReader241         /// NOTE: You should explicitly close the Command.connection you passed in as242         /// well as call Dispose on the Command  after reader is closed.243         /// We do this because IDataReader has no underlying Connection Property.244         /// </summary>245         /// <param name="cmd">SQLiteCommand Object</param>246         /// <param name="commandText">SQL Statement with optional embedded "@param" style parameters</param>247         /// <param name="paramList">object[] array of parameter values</param>248         /// <returns>IDataReader</returns>249         public static IDataReader ExecuteReader(SQLiteCommand cmd, string commandText, object[] paramList)250         {251             if (cmd.Connection == null)252                 throw new ArgumentException("Command must have live connection attached.", "cmd");253             cmd.CommandText = commandText;254             AttachParameters(cmd,commandText, paramList);255             if (cmd.Connection.State == ConnectionState.Closed)256                 cmd.Connection.Open();257             IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);258             return rdr;259         }260  261         /// <summary>262         /// Shortcut to ExecuteNonQuery with SqlStatement and object[] param values263         /// </summary>264         /// <param name="connectionString">SQLite Connection String</param>265         /// <param name="commandText">Sql Statement with embedded "@param" style parameters</param>266         /// <param name="paramList">object[] array of parameter values</param>267         /// <returns></returns>268         public static int ExecuteNonQuery(string connectionString, string commandText, params object[] paramList)269         {270             SQLiteConnection cn = new SQLiteConnection(connectionString);271             SQLiteCommand cmd = cn.CreateCommand();272             cmd.CommandText = commandText;273             AttachParameters(cmd,commandText, paramList);274             if (cn.State == ConnectionState.Closed)275                 cn.Open();276             int result = cmd.ExecuteNonQuery();277             cmd.Dispose();278             cn.Close();279  280             return result;281         }282  283  284  285         public static int ExecuteNonQuery(SQLiteConnection cn, string commandText, params  object[] paramList)286         {287  288             SQLiteCommand cmd = cn.CreateCommand();289             cmd.CommandText = commandText;290             AttachParameters(cmd,commandText, paramList);291             if (cn.State == ConnectionState.Closed)292                 cn.Open();293             int result = cmd.ExecuteNonQuery();294             cmd.Dispose();295             cn.Close();296  297             return result;298         }299  300         /// <summary>301         /// Executes  non-query sql Statment with Transaction302         /// </summary>303         /// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction,   /// and Command, all of which must be created prior to making this method call. </param>304         /// <param name="commandText">Command text.</param>305         /// <param name="paramList">Param list.</param>306         /// <returns>Integer</returns>307         /// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>308         public static int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, params  object[] paramList)309         {310             if (transaction == null) throw new ArgumentNullException("transaction");311             if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,                                                        please provide an open transaction.", "transaction");312             IDbCommand cmd = transaction.Connection.CreateCommand();313             cmd.CommandText = commandText;314             AttachParameters((SQLiteCommand)cmd,cmd.CommandText, paramList);315             if (transaction.Connection.State == ConnectionState.Closed)316                 transaction.Connection.Open();317             int result = cmd.ExecuteNonQuery();318             cmd.Dispose();319             return result;320         }321  322  323         /// <summary>324         /// Executes the non query.325         /// </summary>326         /// <param name="cmd">CMD.</param>327         /// <returns></returns>328         public static int ExecuteNonQuery(IDbCommand cmd)329         {330             if (cmd.Connection.State == ConnectionState.Closed)331                 cmd.Connection.Open();332             int result = cmd.ExecuteNonQuery();333             cmd.Connection.Close();334             cmd.Dispose();335             return result;336         }337  338         /// <summary>339         /// Shortcut to ExecuteScalar with Sql Statement embedded params and object[] param values340         /// </summary>341         /// <param name="connectionString">SQLite Connection String</param>342         /// <param name="commandText">SQL statment with embedded "@param" style parameters</param>343         /// <param name="paramList">object[] array of param values</param>344         /// <returns></returns>345         public static object ExecuteScalar(string connectionString, string commandText, params  object[] paramList)346         {347             SQLiteConnection cn = new SQLiteConnection(connectionString);348             SQLiteCommand cmd = cn.CreateCommand();349             cmd.CommandText = commandText;350             AttachParameters(cmd,commandText, paramList);351             if (cn.State == ConnectionState.Closed)352                 cn.Open();353             object result = cmd.ExecuteScalar();354             cmd.Dispose();355             cn.Close();356  357             return result;358         }359  360         /// <summary>361         /// Execute XmlReader with complete Command362         /// </summary>363         /// <param name="command">SQLite Command</param>364         /// <returns>XmlReader</returns>365         public static XmlReader ExecuteXmlReader(IDbCommand command)366         { // open the connection if necessary, but make sure we367             // know to close it when we?re done.368             if (command.Connection.State != ConnectionState.Open)369             {370                 command.Connection.Open();371             }372  373             // get a data adapter 374             SQLiteDataAdapter da = new SQLiteDataAdapter((SQLiteCommand)command);375             DataSet ds = new DataSet();376             // fill the data set, and return the schema information377             da.MissingSchemaAction = MissingSchemaAction.AddWithKey;378             da.Fill(ds);379             // convert our dataset to XML380             StringReader stream = new StringReader(ds.GetXml());381             command.Connection.Close();382             // convert our stream of text to an XmlReader383             return new XmlTextReader(stream);384         }385  386  387  388         /// <summary>389         /// Parses parameter names from SQL Statement, assigns values from object array ,   /// and returns fully populated ParameterCollection.390         /// </summary>391         /// <param name="commandText">Sql Statement with "@param" style embedded parameters</param>392         /// <param name="paramList">object[] array of parameter values</param>393         /// <returns>SQLiteParameterCollection</returns>394         /// <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>395         private static SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params  object[] paramList)396         {397             if (paramList == null || paramList.Length == 0) return null;398  399             SQLiteParameterCollection coll = cmd.Parameters;400             string parmString = commandText.Substring(commandText.IndexOf("@"));401             // pre-process the string so always at least 1 space after a comma.402             parmString = parmString.Replace(",", " ,");403             // get the named parameters into a match collection404             string pattern = @"(@)\S*(.*?)\b";405             Regex ex = new Regex(pattern, RegexOptions.IgnoreCase);406             MatchCollection mc = ex.Matches(parmString);407             string[] paramNames = new string[mc.Count];408             int i = 0;409             foreach (Match m in mc)410             {411                 paramNames[i] = m.Value;412                 i++;413             }414  415             // now let‘s type the parameters416             int j = 0;417             Type t = null;418             foreach (object o in paramList)419             {420                 t = o.GetType();421  422                 SQLiteParameter parm = new SQLiteParameter();423                 switch (t.ToString())424                 {425  426                     case ("DBNull"):427                     case ("Char"):428                     case ("SByte"):429                     case ("UInt16"):430                     case ("UInt32"):431                     case ("UInt64"):432                         throw new SystemException("Invalid data type");433  434  435                     case ("System.String"):436                         parm.DbType = DbType.String;437                         parm.ParameterName = paramNames[j];438                         parm.Value = http://www.mamicode.com/(string)paramList[j];439                         coll.Add(parm);440                         break;441  442                     case ("System.Byte[]"):443                         parm.DbType = DbType.Binary;444                         parm.ParameterName = paramNames[j];445                         parm.Value = http://www.mamicode.com/(byte[])paramList[j];446                         coll.Add(parm);447                         break;448  449                     case ("System.Int32"):450                         parm.DbType = DbType.Int32;451                         parm.ParameterName = paramNames[j];452                         parm.Value = http://www.mamicode.com/(int)paramList[j];453                         coll.Add(parm);454                         break;455  456                     case ("System.Boolean"):457                         parm.DbType = DbType.Boolean;458                         parm.ParameterName = paramNames[j];459                         parm.Value = http://www.mamicode.com/(bool)paramList[j];460                         coll.Add(parm);461                         break;462  463                     case ("System.DateTime"):464                         parm.DbType = DbType.DateTime;465                         parm.ParameterName = paramNames[j];466                         parm.Value =http://www.mamicode.com/ Convert.ToDateTime(paramList[j]);467                         coll.Add(parm);468                         break;469  470                     case ("System.Double"):471                         parm.DbType = DbType.Double;472                         parm.ParameterName = paramNames[j];473                         parm.Value =http://www.mamicode.com/ Convert.ToDouble(paramList[j]);474                         coll.Add(parm);475                         break;476  477                     case ("System.Decimal"):478                         parm.DbType = DbType.Decimal;479                         parm.ParameterName = paramNames[j];480                         parm.Value =http://www.mamicode.com/ Convert.ToDecimal(paramList[j]);481                         break;482  483                     case ("System.Guid"):484                         parm.DbType = DbType.Guid;485                         parm.ParameterName = paramNames[j];486                         parm.Value =http://www.mamicode.com/ (System.Guid)(paramList[j]);487                         break;488  489                     case ("System.Object"):490  491                         parm.DbType = DbType.Object;492                         parm.ParameterName = paramNames[j];493                         parm.Value =http://www.mamicode.com/ paramList[j];494                         coll.Add(parm);495                         break;496  497                     default:498                         throw new SystemException("Value is of unknown data type");499  500                 } // end switch501  502                 j++;503             }504             return coll;505         }506  507         /// <summary>508         /// Executes non query typed params from a DataRow509         /// </summary>510         /// <param name="command">Command.</param>511         /// <param name="dataRow">Data row.</param>512         /// <returns>Integer result code</returns>513         public static int ExecuteNonQueryTypedParams(IDbCommand command, DataRow dataRow)514         {515             int retVal = 0;516  517             // If the row has values, the store procedure parameters must be initialized518             if (dataRow != null && dataRow.ItemArray.Length > 0)519             {520                 // Set the parameters values521                 AssignParameterValues(command.Parameters, dataRow);522  523                 retVal = ExecuteNonQuery(command);524             }525             else526             {527                 retVal = ExecuteNonQuery(command);528             }529  530             return retVal;531         }532  533         /// <summary>534         /// This method assigns dataRow column values to an IDataParameterCollection535         /// </summary>536         /// <param name="commandParameters">The IDataParameterCollection to be assigned values</param>537         /// <param name="dataRow">The dataRow used to hold the command‘s parameter values</param>538         /// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception>539         protected internal static void AssignParameterValues(IDataParameterCollection commandParameters, DataRow dataRow)540         {541             if (commandParameters == null || dataRow == null)542             {543                 // Do nothing if we get no data544                 return;545             }546  547             DataColumnCollection columns = dataRow.Table.Columns;548  549             int i = 0;550             // Set the parameters values551             foreach (IDataParameter commandParameter in commandParameters)552             {553                 // Check the parameter name554                 if (commandParameter.ParameterName == null ||555                  commandParameter.ParameterName.Length <= 1)556                     throw new InvalidOperationException(string.Format(557                            "Please provide a valid parameter name on the parameter #{0},                            the ParameterName property has the following value: ‘{1}‘.",558                      i, commandParameter.ParameterName));559  560                 if (columns.Contains(commandParameter.ParameterName))561                     commandParameter.Value =http://www.mamicode.com/ dataRow[commandParameter.ParameterName];562                 else if (columns.Contains(commandParameter.ParameterName.Substring(1)))563                     commandParameter.Value = http://www.mamicode.com/dataRow[commandParameter.ParameterName.Substring(1)];564  565                 i++;566             }567         }568  569         /// <summary>570         /// This method assigns dataRow column values to an array of IDataParameters571         /// </summary>572         /// <param name="commandParameters">Array of IDataParameters to be assigned values</param>573         /// <param name="dataRow">The dataRow used to hold the stored procedure‘s parameter values</param>574         /// <exception cref="System.InvalidOperationException">Thrown if any of the parameter names are invalid.</exception>575         protected void AssignParameterValues(IDataParameter[] commandParameters, DataRow dataRow)576         {577             if ((commandParameters == null) || (dataRow == null))578             {579                 // Do nothing if we get no data580                 return;581             }582  583             DataColumnCollection columns = dataRow.Table.Columns;584  585             int i = 0;586             // Set the parameters values587             foreach (IDataParameter commandParameter in commandParameters)588             {589                 // Check the parameter name590                 if (commandParameter.ParameterName == null ||591                  commandParameter.ParameterName.Length <= 1)592                     throw new InvalidOperationException(string.Format(593                      "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: ‘{1}‘.",594                      i, commandParameter.ParameterName));595  596                 if (columns.Contains(commandParameter.ParameterName))597                     commandParameter.Value =http://www.mamicode.com/ dataRow[commandParameter.ParameterName];598                 else if (columns.Contains(commandParameter.ParameterName.Substring(1)))599                     commandParameter.Value = http://www.mamicode.com/dataRow[commandParameter.ParameterName.Substring(1)];600  601                 i++;602             }603         }604  605         /// <summary>606         /// This method assigns an array of values to an array of IDataParameters607         /// </summary>608         /// <param name="commandParameters">Array of IDataParameters to be assigned values</param>609         /// <param name="parameterValues">Array of objects holding the values to be assigned</param>610         /// <exception cref="System.ArgumentException">Thrown if an incorrect number of parameters are passed.</exception>611         protected void AssignParameterValues(IDataParameter[] commandParameters, params  object[] parameterValues)612         {613             if ((commandParameters == null) || (parameterValues == null))614             {615                 // Do nothing if we get no data616                 return;617             }618  619             // We must have the same number of values as we pave parameters to put them in620             if (commandParameters.Length != parameterValues.Length)621             {622                 throw new ArgumentException("Parameter count does not match Parameter Value count.");623             }624  625             // Iterate through the IDataParameters, assigning the values from the corresponding position in the626             // value array627             for (int i = 0, j = commandParameters.Length, k = 0; i < j; i++)628             {629                 if (commandParameters[i].Direction != ParameterDirection.ReturnValue)630                 {631                     // If the current array value derives from IDataParameter, then assign its Value property632                     if (parameterValues[k] is IDataParameter)633                     {634                         IDataParameter paramInstance;635                         paramInstance = (IDataParameter)parameterValues[k];636                         if (paramInstance.Direction == ParameterDirection.ReturnValue)637                         {638                             paramInstance = (IDataParameter)parameterValues[++k];639                         }640                         if (paramInstance.Value =http://www.mamicode.com/= null)641                         {642                             commandParameters[i].Value =http://www.mamicode.com/ DBNull.Value;643                         }644                         else645                         {646                             commandParameters[i].Value =http://www.mamicode.com/ paramInstance.Value;647                         }648                     }649                     else if (parameterValues[k] == null)650                     {651                         commandParameters[i].Value =http://www.mamicode.com/ DBNull.Value;652                     }653                     else654                     {655                         commandParameters[i].Value =http://www.mamicode.com/ parameterValues[k];656                     }657                     k++;658                 }659             }660         }661     }662 }