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; } } }

 

SQLiteHelper

标签：bst   ssi   int   length   lis   tools   sqlite   mes   pre