SQLiteHelper
标签：bst ssi int length lis tools sqlite mes pre
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did117826
SQLiteHelper
System;
using System.Data;
using System.Text.RegularExpressions;
using System.Xml;
using System.IO;
using System.Collections;
using System.Data.SQLite;
using System.Collections.Generic;
namespace Huinaozn.ASleepPC.Tools.Helper
{
static class SQLiteHelper
{
public static string ConnectionString = @" Data Source=|DataDirectory|\DataBase\ASleep.db;Pooling=true;FailIfMissing=false " ;
public static DataSet ExecuteDataset( string commandText, params IDataParameter[] paramList)
{
SQLiteParameter sQLiteParameter = new SQLiteParameter();
SQLiteCommand cmd = CreateCommand(commandText, paramList);
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;
}
public static int ExecuteNonQuery( 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 object ExecuteScalar( 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;
}
private static SQLiteCommand CreateCommand( string commandText, params IDataParameter[] paramList)
{
SQLiteConnection cn = new SQLiteConnection(ConnectionString);
SQLiteCommand cmd = new SQLiteCommand(commandText, cn);
List <SQLiteParameter> commandParameters = new List<SQLiteParameter> ();
foreach ( var parameter in paramList)
{
commandParameters.Add( new SQLiteParameter(parameter.ParameterName, parameter.DbType) { Value = parameter.Value });
}
if (commandParameters.Count > 0 )
{
foreach (SQLiteParameter parm in commandParameters)
cmd.Parameters.Add(parm);
}
return cmd;
}
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;
}
}
}
阅读：24次