C#操作SQLite数据库
·
C#操作SQLite数据库
2.3 操作SQLite Database的C#帮助类SQLite Helper
1、SQLite介绍
SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
SQLite是一个开源、免费的小型RDBMS(关系型数据库)，能独立运行、无服务器、零配置、支持事物，用C实现，内存占用较小，支持绝大数的SQL92标准。
SQLite数据库官方主页：http://www.sqlite.org/index.html
2、C#操作SQLite Database
2.1 C#下SQLite操作驱动dll下载
C#下SQLite操作驱动dll下载：System.Data.SQLite
2.2 C#使用SQLite步骤
（1）新建一个project
（2）添加SQLite操作驱动dll引用
（3）使用API操作SQLite DataBase
-
using System;
-
using System.Data.SQLite;
-
-
namespace
SQLiteSamples
-
{
-
class
Program
-
{
-
//数据库连接
-
SQLiteConnection m_dbConnection;
-
-
static void Main(string[] args)
-
{
-
Program p =
new Program();
-
}
-
-
public Program()
-
{
-
createNewDatabase();
-
connectToDatabase();
-
createTable();
-
fillTable();
-
printHighscores();
-
}
-
-
//创建一个空的数据库
-
void createNewDatabase()
-
{
-
SQLiteConnection.CreateFile(
"MyDatabase.sqlite");
-
}
-
-
//创建一个连接到指定数据库
-
void connectToDatabase()
-
{
-
m_dbConnection =
new SQLiteConnection(
"Data Source=MyDatabase.sqlite;Version=3;");
-
m_dbConnection.Open();
-
}
-
-
//在指定数据库中创建一个table
-
void createTable()
-
{
-
string sql =
"create table highscores (name varchar(20), score int)";
-
SQLiteCommand command =
new SQLiteCommand(sql, m_dbConnection);
-
command.ExecuteNonQuery();
-
}
-
-
//插入一些数据
-
void fillTable()
-
{
-
string sql =
"insert into highscores (name, score) values ('Me', 3000)";
-
SQLiteCommand command =
new SQLiteCommand(sql, m_dbConnection);
-
command.ExecuteNonQuery();
-
-
sql =
"insert into highscores (name, score) values ('Myself', 6000)";
-
command =
new SQLiteCommand(sql, m_dbConnection);
-
command.ExecuteNonQuery();
-
-
sql =
"insert into highscores (name, score) values ('And I', 9001)";
-
command =
new SQLiteCommand(sql, m_dbConnection);
-
command.ExecuteNonQuery();
-
}
-
-
//使用sql查询语句，并显示结果
-
void printHighscores()
-
{
-
string sql =
"select * from highscores order by score desc";
-
SQLiteCommand command =
new SQLiteCommand(sql, m_dbConnection);
-
SQLiteDataReader reader = command.ExecuteReader();
-
while (reader.Read())
-
Console.WriteLine(
"Name: " + reader[
"name"] +
"\tScore: " + reader[
"score"]);
-
Console.ReadLine();
-
}
-
}
-
}
关于SQLite的connection string说明：http://www.connectionstrings.com/sqlite/
SQLite GUI客户端列表：http://www.sqlite.org/cvstrac/wiki?p=ManagementTools
SQLite Administrator下载地址：http://download.orbmu2k.de/files/sqliteadmin.zip
2.3 操作SQLite Database的C#帮助类SQLite Helper
将一些常用的功能封装一下，封装成SQLite Helper类
-
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++;
-
}
-
}
-
}
-
}
-
}
Codeproject上的一个封装：http://www.codeproject.com/Articles/746191/SQLite-Helper-Csharp
作者：阿凡卢
出处：http://www.cnblogs.com/luxiaoxun/
本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)