public class SQLiteDBHelper
{
public static readonly string CONNECT_STRING_NODE;
private static string dbPath;
private static SQLiteConnection connection;
public static string DBPath
{
get
{
return SQLiteDBHelper.dbPath;
}
set
{
SQLiteDBHelper.dbPath = value;
SQLiteDBHelper.ConnString = "Data Source=" + SQLiteDBHelper.dbPath;
}
}
public static string ConnString
{
get;
set;
}
public static SQLiteTransaction Transaction
{
get;
private set;
}
public static SQLiteConnection Connection
{
get
{
if (SQLiteDBHelper.connection == null)
{
SQLiteDBHelper.connection = new SQLiteConnection(SQLiteDBHelper.ConnString);
}
if (SQLiteDBHelper.connection.State == ConnectionState.Closed)
{
SQLiteDBHelper.connection.Open();
}
else
{
if (SQLiteDBHelper.connection.State == ConnectionState.Broken)
{
SQLiteDBHelper.connection.Close();
SQLiteDBHelper.connection.Open();
}
}
return SQLiteDBHelper.connection;
}
}
static SQLiteDBHelper()
{
SQLiteDBHelper.CONNECT_STRING_NODE = "DBPath";
try
{
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load(Path.GetFullPath("settings.xml"));
XmlNode xmlNode = xmlDocument.SelectSingleNode(SQLiteDBHelper.CONNECT_STRING_NODE);
SQLiteDBHelper.DBPath = Path.GetFullPath(xmlNode.InnerText);
}
catch (Exception ex)
{
Console.WriteLine("Failed: Read settings.xml " + ex.Message);
}
}
public static void CloseConnection()
{
if (SQLiteDBHelper.connection != null && (SQLiteDBHelper.connection.State == ConnectionState.Open || SQLiteDBHelper.connection.State == ConnectionState.Broken))
{
SQLiteDBHelper.connection.Close();
}
}
public static object ExecuteScalar(CommandType commandType, string commandText, SQLiteParameter[] pars)
{
object result;
try
{
SQLiteCommand sQLiteCommand = new SQLiteCommand();
SQLiteDBHelper.PreCommand(sQLiteCommand, commandType, commandText, pars);
result = sQLiteCommand.ExecuteScalar();
}
finally
{
if (SQLiteDBHelper.Connection != null && SQLiteDBHelper.Transaction == null)
{
SQLiteDBHelper.Connection.Close();
}
}
return result;
}
public static SQLiteDataReader ExecuteQuery(CommandType commandType, string commandText, SQLiteParameter[] pars)
{
SQLiteDataReader result;
using (SQLiteCommand sQLiteCommand = new SQLiteCommand())
{
SQLiteDBHelper.PreCommand(sQLiteCommand, commandType, commandText, pars);
result = sQLiteCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
return result;
}
public static int ExecuteNonQuery(CommandType commandType, string commandText, SQLiteParameter[] pars)
{
int result;
using (SQLiteCommand sQLiteCommand = new SQLiteCommand())
{
SQLiteDBHelper.PreCommand(sQLiteCommand, commandType, commandText, pars);
int num = sQLiteCommand.ExecuteNonQuery();
if (SQLiteDBHelper.Transaction == null)
{
SQLiteDBHelper.CloseConnection();
}
result = num;
}
return result;
}
public static void BeginTransaction()
{
SQLiteDBHelper.Transaction = SQLiteDBHelper.Connection.BeginTransaction();
}
public static void EndTransaction()
{
if (SQLiteDBHelper.Transaction != null)
{
SQLiteDBHelper.Transaction.Dispose();
SQLiteDBHelper.Transaction = null;
SQLiteDBHelper.CloseConnection();
}
}
public static void Commit()
{
if (SQLiteDBHelper.Transaction != null)
{
SQLiteDBHelper.Transaction.Commit();
SQLiteDBHelper.Transaction = null;
SQLiteDBHelper.CloseConnection();
}
}
public static void Rollback()
{
if (SQLiteDBHelper.Transaction != null)
{
SQLiteDBHelper.Transaction.Rollback();
SQLiteDBHelper.Transaction = null;
SQLiteDBHelper.CloseConnection();
}
}
protected static void PreCommand(SQLiteCommand com, CommandType commandType, string commandText, SQLiteParameter[] pars)
{
com.CommandText = commandText;
com.Connection = SQLiteDBHelper.Connection;
com.Parameters.AddRange(pars ?? new SQLiteParameter[0]);
com.CommandType = commandType;
}
public static string[] GetFieldNames(DbDataReader reader)
{
int fieldCount = reader.FieldCount;
string[] array = new string[fieldCount];
for (int i = 0; i < fieldCount; i++)
{
array[i] = reader.GetName(i);
}
return array;
}
public static object GetValue(DbDataReader reader, string field, object defValue = null)
{
string[] fieldNames = SQLiteDBHelper.GetFieldNames(reader);
string[] array = fieldNames;
object result;
for (int i = 0; i < array.Length; i++)
{
string text = array[i];
if (string.Equals(text, field, StringComparison.CurrentCultureIgnoreCase))
{
result = reader[text];
return result;
}
}
result = defValue;
return result;
}
}
public class AppService : IApp, IDALBase<App>, IModelConvert<App>
{
public App Load(int id)
{
SQLiteParameter[] pars = new SQLiteParameter[]
{
new SQLiteParameter("@Id", id)
};
App result;
using (SQLiteDataReader sQLiteDataReader = SQLiteDBHelper.ExecuteQuery(CommandType.Text, "select * from tb_app where Id=@Id", pars))
{
if (sQLiteDataReader.Read())
{
result = this.ConvertModel(sQLiteDataReader);
return result;
}
}
result = null;
return result;
}
public IList<App> GetAll()
{
IList<App> result;
using (SQLiteDataReader sQLiteDataReader = SQLiteDBHelper.ExecuteQuery(CommandType.Text, "select * from tb_app", null))
{
result = this.ConvertModelList(sQLiteDataReader);
}
return result;
}
public bool Add(App info)
{
SQLiteParameter[] pars = new SQLiteParameter[]
{
new SQLiteParameter("@Name", info.Name),
new SQLiteParameter("@Description", info.Description),
new SQLiteParameter("@DataPath", info.DataPath),
new SQLiteParameter("@Parameters", info.Parameters),
new SQLiteParameter("@InsertTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")),
new SQLiteParameter("@UpdateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
};
return SQLiteDBHelper.ExecuteNonQuery(CommandType.Text, "insert into tb_app (Name, Description, DataPath, Parameters, InsertTime, UpdateTime)values (@Name, @Description, @DataPath, @Parameters, @InsertTime, @UpdateTime)", pars) > 0;
}
public bool Insert(App info)
{
SQLiteParameter[] pars = new SQLiteParameter[]
{
new SQLiteParameter("@Id", info.Id),
new SQLiteParameter("@Name", info.Name),
new SQLiteParameter("@Description", info.Description),
new SQLiteParameter("@DataPath", info.DataPath),
new SQLiteParameter("@Parameters", info.Parameters),
new SQLiteParameter("@InsertTime", info.InsertTime.ToString("yyyy-MM-dd HH:mm:ss")),
new SQLiteParameter("@UpdateTime", info.UpdateTime.ToString("yyyy-MM-dd HH:mm:ss"))
};
return SQLiteDBHelper.ExecuteNonQuery(CommandType.Text, "insert into tb_app (Id, Name, Description, DataPath, Parameters, InsertTime, UpdateTime)values (@Id, @Name, @Description, @DataPath, @Parameters, @InsertTime, @UpdateTime)", pars) > 0;
}
public bool Update(App info)
{
SQLiteParameter[] pars = new SQLiteParameter[]
{
new SQLiteParameter("@Id", info.Id),
new SQLiteParameter("@Name", info.Name),
new SQLiteParameter("@Description", info.Description),
new SQLiteParameter("@DataPath", info.DataPath),
new SQLiteParameter("@Parameters", info.Parameters),
new SQLiteParameter("@InsertTime", info.InsertTime.ToString("yyyy-MM-dd HH:mm:ss")),
new SQLiteParameter("@UpdateTime", info.UpdateTime.ToString("yyyy-MM-dd HH:mm:ss"))
};
return SQLiteDBHelper.ExecuteNonQuery(CommandType.Text, "update tb_app set Name=@Name, Description=@Description, DataPath=@DataPath, Parameters=@Parameters, InsertTime=@InsertTime, UpdateTime=@UpdateTime where Id=@Id", pars) > 0;
}
public bool Delete(int id)
{
SQLiteParameter[] pars = new SQLiteParameter[]
{
new SQLiteParameter("@Id", id)
};
return SQLiteDBHelper.ExecuteNonQuery(CommandType.Text, "delete from tb_app where Id=@Id", pars) > 0;
}
public void Clear()
{
SQLiteDBHelper.ExecuteNonQuery(CommandType.Text, "delete from tb_app", null);
}
public App ConvertModel(DbDataReader reader)
{
return new App
{
Id = Convert.ToInt32(reader["Id"]),
Name = Convert.ToString(reader["Name"]),
Description = Convert.ToString(reader["Description"]),
DataPath = Convert.ToString(reader["DataPath"]),
Parameters = Convert.ToString(reader["Parameters"]),
InsertTime = Convert.ToDateTime(reader["InsertTime"]),
UpdateTime = Convert.ToDateTime(reader["UpdateTime"])
};
}
public IList<App> ConvertModelList(DbDataReader reader)
{
List<App> list = new List<App>();
while (reader.Read())
{
list.Add(this.ConvertModel(reader));
}
return list;
}
}
