SQLite之C#连接SQLite

SQLite是一个开源、免费的小型的Embeddable RDBMS(关系型数据库)，用C实现，内存占用较小，支持绝大数的SQL92标准，现在已变得越来越流行，它的体积很小，被广泛应用于各种不同类型的应用中。SQLite已经是世界上布署得最广泛的SQL数据库引擎，被用在无以计数的桌面电脑应用中，还有消费电子设备中，如移动电话、掌上电脑和MP3播放器等。

SQLite，是一款轻型的数据库，是遵守ACID的关联式数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它 占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多 程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源世界著名的数据库管理系统来讲，它的处理速度 比他们都快。SQLite第一个Alpha版本诞生于2000年5月. 至今已经有10个年头，SQLite也迎来了一个版本 SQLite 3已经发布。

　　官方网站：http://www.sqlite.org/

      详细简介：http://baike.baidu.com/view/19310.htm

 

要使用sqlite保存数据，则需要用到SQLite操作驱动的dll，可以在官网下载，安装完成后，引用安装目录下的System.Data.SQLite.dll文件，

可以在我百度网盘下载：sqlite-netFx40-setup-bundle-x64-2010-1.0.96.0.exe

 

 

 

你也可以直接在项目中安装sqlite，项目会自动添加引用

 

 

 

安装完成后，你会发现。app.config中会自动生成配置代码

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <configSections>
        <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
        <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
        <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=4.1.0.0, Culture=neutral, PublicKeyToken=null" />
    </configSections>
    <system.data>
        <DbProviderFactories>
            <remove invariant="System.Data.SQLite.EF6" />
            <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" 
                 type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
        </DbProviderFactories>
    </system.data>
    <entityFramework>
        <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
        <providers>
            <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
            <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
        </providers>
    </entityFramework>
    <connectionStrings>
        <add name="sqlite" connectionString="Data Source=|DataDirectory|\document.db;Pooling=true;FailIfMissing=false"
          providerName="System.Data.SQLite" />
    </connectionStrings>
</configuration>

 

 

 

这些步骤完成后，就可以操作sqlite数据库。如果你链接数据库的时候。提示：

未能加载文件或程序集“System.Data.SQLite, Version=1.0.66.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139”或它的某一个依赖项。试图加载格式不正确的程序。

这是因为目标平台不匹配， 原因是SQLite下载的平台类型不对造成的，因为你下载的sqlite驱动分x86和x64位，你可以把项目平台改成匹配的，右键项目属性：

 

 

前期准备完成，在编码前，当然少不了一个工具，来对数据库进行管理。创建库、表、执行SQL语句操作，

比如：SQLite Expert Personal 3，Navicat for SQLite等等，

 

SQLite Expert Personal 3界面，

 

我这里使用Navicat for SQLite来管理sqlite，

打开Navicat for SQLite，单击连接，如图：

 

连接成功后，创建表，添加数据。

 

我这里创建一个表：document.db，并添加简单的数据用于测试

 

 

接下来可以在vs中编码，如果用过sql server，那么sqlite就没什么难的

 使用原生态的ADO.NET访问SQLite

　　原生态的访问，就是说直接用connection和command这些对象打开数据库，然后打开连接，进行数据的操作。

在App.config中配置connectionStrings

<connectionStrings>
        <add name="sqlite" connectionString="Data Source=|DataDirectory|\document.db;Pooling=true;FailIfMissing=false"
          providerName="System.Data.SQLite" />
    </connectionStrings>

 

上面的connectionstring配置节的db就是SQLite的数据库文件，将它放在Web应用的App_Data目录，|DataDirectory|就代表这个目录的位置，后面的就是文件名。 　　剩下的就是我们使用企业库访问SQL Server是一样的了。

到这里。其实有一个盲区。就是App_Data，是web应用中才有，但winform中是没有的。在winform中DataDirectory被程序弄成了apppath/bin/debug目录，所以，此时。你需要把document.db赋值到debug目录下面。

现在来测试是否可以成功访问数据库并读取数据

   string sql = "SELECT * FROM userInfo";
            //string conStr = "D:/sqlliteDb/document.db";
            string connStr = @"Data Source=" + @"D:\sqlliteDb\document.db;Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10";
            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                //conn.Open();
                using (SQLiteDataAdapter ap = new SQLiteDataAdapter(sql, conn))
                {
                    DataSet ds = new DataSet();
                    ap.Fill(ds);

                    DataTable dt = ds.Tables[0];
                }
            }

设置一个断点，发现已经得到sqlite中的数据

 

如果想读取app.config中的数据库连接字符串

string config = System.Configuration.ConfigurationManager.ConnectionStrings["sqlite"].ConnectionString;
            using (SQLiteConnection conn = new SQLiteConnection(config))
            {
                conn.Open();
                //DbCommand comm = conn.CreateCommand();
                //comm.CommandText = "select * from userinfo";
                //comm.CommandType = CommandType.Text;

                //conn.Open();
                using (SQLiteDataAdapter ap = new SQLiteDataAdapter(sql, conn))
                {
                    DataSet ds = new DataSet();
                    ap.Fill(ds);

                    DataTable dt = ds.Tables[0];
                }
            }

 

 

 

使用SQLite.NET访问SQLite

　　SQLite.NET也是一个数据访问组件，其中的System.Data.SQLite 就好像是.NET自带的System.Data.SqlClient一样。里面包含了connection、command等数据访问的常用对象，只是他们前面都有一个前缀sqlite。

回到之前的app.config。当用NuGet程序包安装sqlite后。里面自动生成了如下代码

<system.data>
        <DbProviderFactories>
            <remove invariant="System.Data.SQLite.EF6" />
            <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" 
              type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
        </DbProviderFactories>
    </system.data>

 

即：添加一个DbProviderFactory的创建源，在代码中就可以使用DbProviderFactory类来创建SQLite的数据访问对象。

DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SQLite.EF6");
            using (DbConnection conn = fact.CreateConnection())
            {
                conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["sqlite"].ConnectionString;
                conn.Open();
                DbCommand comm = conn.CreateCommand();
                comm.CommandText = "select * from userInfo";
                comm.CommandType = CommandType.Text;
                using (IDataReader reader = comm.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string dd = reader["name"].ToString();
                    }
                }
            }

同样测试看结果：

 

 

可以用SQLiteConnection.CreateFile("D:/d.db");直接创建一个数据库文件

 

 

网上找了一个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++;
            }
        }
    }
}

 

 

其他资料：

　　http://www.cnblogs.com/virusswb/archive/2010/09/17/SQLite1.html

　　http://blog.csdn.net/heqichanggg/article/details/5784839

　　http://www.cnblogs.com/luxiaoxun/p/3784729.html

　　http://www.cnblogs.com/xugang/archive/2011/04/19/2020713.html