public List <string> GetNameColumnValues(string strTableName, DbInteractiveArgs args = null)
{
try
{
DataTable dt = this.ProvideTable(String.Format("select Name from {0}; ", strTableName), args);
if (dt == null)
{
return(new List <string>());
}
if (dt != null)
{
if (dt.Rows.Count == 0)
{
return(new List <String>());
}
}
List <String> lst = new List <String>();
for (int i = 0; i < dt.Rows.Count; i++)
{
lst.Add(dt.Rows[i][0].ToString());
}
return(lst);
}
catch (Exception e)
{
return(null);
}
}
public string UniqueResult(string sql, DbInteractiveArgs args = null)
{
DbCommand cmd = null;
if (args == null)
{
cmd = this.DbInteractiveArgs.ThisDbCommand;
}
else
{
cmd = args.ThisDbCommand;
}
cmd.CommandText = sql;
String strResult = null;
try
{
strResult = cmd.ExecuteScalar().ToString();
this.Status = true;
}
catch (Exception e)
{
ErrorHandler?.Invoke(cmd, e);
return(null);
}
return(strResult);
}
public void WriteArray <T>(String strTableName, String strName, T[] tArr, DbInteractiveArgs args = null)
{
for (int i = 0; i < tArr.Length; i++)
{
this.AppendItem(strTableName, strName, tArr[i].ToString(), args);
}
}
public DataTable ProvideTable(string sql, DbInteractiveArgs args = null)
{
DataTable dt = null;
DbCommand cmd = null;
if (args == null)
{
cmd = this.DbInteractiveArgs.ThisDbCommand;
}
else
{
cmd = args.ThisDbCommand;
}
try
{
dt = new DataTable();
cmd.CommandText = sql;
DbDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
reader.Close();
this.Status = true;
}
catch (Exception e)
{
ErrorHandler?.Invoke(cmd, e);
return(null);
}
return(dt);
}
public void NewRecord(string strTableName, string strValue, DbInteractiveArgs args = null)
{
DbCommand cmd = null;
try
{
if (args == null)
{
cmd = this.DbInteractiveArgs.ThisDbCommand;
}
else
{
cmd = args.ThisDbCommand;
}
cmd.CommandText = string.Format("insert into {0} values({1})", strTableName, strValue);
// Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
this.Status = true;
}
catch (Exception e)
{
ErrorHandler?.Invoke(cmd, e);
}
}
public void NewTable(string tablename, string tableDef, DbInteractiveArgs args = null)
{
DbCommand cmd = null;
try
{
if (args == null)
{
cmd = this.DbInteractiveArgs.ThisDbCommand;
}
else
{
cmd = args.ThisDbCommand;
}
cmd.CommandText = string.Format("create table {0}({1}) ;",
tablename, tableDef);
cmd.ExecuteNonQuery();
this.Status = true;
}
catch (Exception e)
{
ErrorHandler(cmd, e);
}
}
void InitDB(String strCmpactDBFilePath, DbInteractiveArgs InitSqliteArgs)
{
try
{
DbInteractiveArgs args = InitSqliteArgs;
args.Database = strCmpactDBFilePath;
args.ThisDbConnection.ConnectionString = String.Format("data source={0}", strCmpactDBFilePath);
bool b = this.Connect(args);
if (b)
{
List <String> AllTableNames = GetAllTableNames(args);
int nIndex = -1;
nIndex = (int)AllTableNames?.IndexOf(DefaultTable);
int nUpperIndex = (int)AllTableNames?.IndexOf(DefaultTable.ToUpper());
if (nIndex == -1 && nUpperIndex == -1)
{
CreateKeyValueTable(DefaultTable, args);
}
Console.WriteLine("已经成功连接数据库");
}
else
{
Console.WriteLine("未能连接数据库");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void ExecuteNonQuery(string sql, DbInteractiveArgs args = null)
{
DbCommand cmd = null;
try
{
if (args == null)
{
cmd = this.DbInteractiveArgs.ThisDbCommand;
}
else
{
cmd = args.ThisDbCommand;
}
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
this.Status = true;
}
catch (Exception e)
{
ErrorHandler(cmd, e);
}
}
public String[] FetchArray(String strTableName, string strLabelName, DbInteractiveArgs args = null)
{
String str = FetchValue(strLabelName, strTableName, false, "-89", args);
if (str == null)
{
return(null);
}
else
{
if (str.Contains(SPLITMARK.ToString()))
{
try
{
String[] str_ = str.Split('^');
return(str_);
}
catch (Exception e)
{
return(null);
}
}
else
{
return new String[] { str }
};
}
}
public void Update(string TableName, string strColumnAssignAndRequirment, DbInteractiveArgs args = null)
{
DbCommand cmd = null;
try
{
if (args == null)
{
cmd = this.DbInteractiveArgs.ThisDbCommand;
}
else
{
cmd = args.ThisDbCommand;
}
cmd.CommandText =
string.Format
("update {0} set {1}",
TableName, strColumnAssignAndRequirment);
cmd.ExecuteNonQuery();
this.Status = true;
}
catch (Exception e)
{
ErrorHandler?.Invoke(cmd, e);
}
}
public void CopyKeyValueTable(string oldTableName, string newTableName, DbInteractiveArgs args = null)
{
CreateKeyValueTable(newTableName);
this.ExecuteNonQuery(
string.Format("insert into {0} SELECT * FROM {1}"
, newTableName, oldTableName)
, args);
}
public DbInteractiveArgs Connect()
{
try
{
DbInteractiveArgs args = new DbInteractiveArgs();
String name_lower = null;
if (assembly_Sqlite_Connection != null)
{
Type[] types = assembly_Sqlite_Connection.GetTypes();
for (int i = 0; i < types.Length; i++)
{
name_lower = types[i].Name.ToLower();
if (name_lower.Equals("sqliteconnection"))
{
args.ThisDbConnection = assembly_Sqlite_Connection.CreateInstance(types[i].FullName) as DbConnection;
}
else if (name_lower.Equals("sqlitecommand"))
{
args.ThisDbCommand = assembly_Sqlite_Connection.CreateInstance(types[i].FullName) as DbCommand;
}
else if (name_lower.Equals("sqliteparameter"))
{
args.ThisDBPar = assembly_Sqlite_Connection.CreateInstance(types[i].FullName) as DbParameter;
}
}
}
else
{
return(null);
}
args.Database = SqliteDBFilePath;
String strCmpactDBFilePath = SqliteDBFilePath;
args.Database = strCmpactDBFilePath;
args.ThisDbConnection.ConnectionString = $"data source={strCmpactDBFilePath}";
if (DbInteractiveArgs == null)
{
DbInteractiveArgs = args;
}
bool b = Connect(args);
if (!b)
{
PerformErrorHandler(this, new Exception("未能连接数据库"));
args = null;
}
return(args);
}
catch (Exception exx)
{
return(null);
}
}
public void FetchIntArray(String strLabelName, out int[] arr, DbInteractiveArgs args = null)
{
String[] pArr = FetchArray(strLabelName, args);
arr = new int[pArr.Length];
for (int i = 0; i < arr.Length; i++)
{
arr[i] = Convert.ToInt32(pArr[i]);
}
}
public void FetchDoubleArray(string strLabelName, out double[] lfArr, DbInteractiveArgs args = null)
{
String[] pArr = FetchArray(strLabelName, args);
lfArr = new double[pArr.Length];
for (int i = 0; i < lfArr.Length; i++)
{
lfArr[i] = Convert.ToDouble(pArr[i]);
}
}
public void FetchFloatArray(string strLabelName, out float[] fArr, DbInteractiveArgs args = null)
{
String[] pArr = FetchArray(strLabelName, args);
fArr = new float[pArr.Length];
for (int i = 0; i < fArr.Length; i++)
{
fArr[i] = Convert.ToSingle(pArr[i]);
}
}
public String[] FetchArray(string strLabelName, DbInteractiveArgs args = null)
{
/* String str = FetchValue(strLabelName);
* if (str.Contains(SPLITMARK.ToString()))
* return str.Split(new char[] { SPLITMARK });
* else
* return null;*/
return(FetchArray("App_Extension_Data_Table", strLabelName, args));
}
public List <string> GetTableColumnTypes(string tableName, DbInteractiveArgs args = null)
{
string sql = String.Format("PRAGMA table_info ({0})", tableName);
DataTable dt = ProvideTable(sql, args);
List <String> typeList = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
String type = dt.Rows[i]["type"].ToString().ToLower();
type = TypeInfer(type);
typeList.Add(type);
}
return(typeList);
}
public void FastQueryData(string sql,
Action <Object> QueriedDataCallback,
Action EndQueryedRowCallback = null,
DbInteractiveArgs args = null)
{
DbCommand cmd = null;
try
{
if (args == null)
{
cmd = DbInteractiveArgs.ThisDbCommand;
}
else
{
cmd = args.ThisDbCommand;
}
cmd.CommandText = sql;
DbDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)//如果有数据
{
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++) //逐个字段的遍历
{
//示例
//if (NeedQuote(AllTableTypes[i]))
// sb.Append($"'{reader[i].ToString()},'");
//else
// sb.Append($"'{reader[i]}',");
QueriedDataCallback(reader[i]);
}
EndQueryedRowCallback?.Invoke();
}
}
//先关闭Reader
reader.Close();
}
catch (Exception ex)
{
ErrorHandler?.Invoke(cmd, ex);
}
}
public void Write(string strTableName, string strName, String strValue, DbInteractiveArgs args = null)
{
if (!bConnected)
{
ErrorHandler?.Invoke(this, new Exception(BADCONNECTION));
return;
}
if (IsExistRecord(strName, strTableName, args))
{
this.Update(strTableName, string.Format("Val='{0}' Where Name='{1}' ", strValue, strName), args);
}
else
{
this.NewRecord(strTableName, string.Format("'{0}','{1}'", strName, strValue), args);
}
}
public DbInteractiveArgs GetDbInteractiveArgs()
{
DbInteractiveArgs args = new DbInteractiveArgs();
args.ThisDbConnection =
this.GetReflectOject("System.Data.SQLite.dll", "System.Data.SQLite.SQLiteConnection")
as DbConnection;
args.ThisDBPar =
GetReflectOject("System.Data.SQLite.dll", "System.Data.SQLite.SQLiteParameter")
as DbParameter;
args.ThisDbCommand =
GetReflectOject("System.Data.SQLite.dll", "System.Data.SQLite.SQLiteCommand")
as DbCommand;
args.ThisDBPar.ParameterName = "Data";
args.ThisDBPar.Value = System.Data.SqlDbType.Image;
return(args);
}
public bool Connect(DbInteractiveArgs args)
{
try
{
args.
ThisDbConnection?.Open();
if (args.ThisDbConnection != null && args.ThisDbCommand != null)
{
args.ThisDbCommand.Connection = args.ThisDbConnection;
}
this.bConnected = true;
return(true);
}
catch (Exception ex)
{
bConnected = false;
return(false);
}
}
public List <string> GetAllTableNames(DbInteractiveArgs args = null)
{
DataTable dt = null; List <String> TableNames = new List <string>();
try
{
dt = this.ProvideTable("select name from sqlite_master where type='table' or type='view' order by name;", args);
for (int i = 0; i < dt.Rows.Count; i++)
{
TableNames.Add(dt.Rows[i][0].ToString());
}
return(TableNames);
}
catch (Exception e)
{
PerformErrorHandler(dt, e);
return(null);
}
}
public List <string> GetTableColumnNames(String tableName, DbInteractiveArgs args = null)
{
try
{
DataTable dt;
List <String> AllColumnNames = new List <string>();
string SQLFormat = "PRAGMA table_info({0})";
dt = this.ProvideTable(String.Format(SQLFormat, tableName), args);
for (int i = 0; i < dt.Rows.Count; i++)
{
AllColumnNames.Add(dt.Rows[i][1].ToString());
}
return(AllColumnNames);
}
catch (Exception ex)
{
return(null);
}
}
protected bool IsExistRecord(string strName, string Tablename, DbInteractiveArgs args = null)
{
string temp = this.UniqueResult(
string.Format("select count(*) from {0} where Name='{1}'"
, Tablename, strName
), args);
try
{
if (int.Parse(temp) > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
return(false);
}
}
public string FetchValue(String strTableName, String strLabelName, bool NewWhenNoFound = true,
String strDefaultValue = "0", DbInteractiveArgs args = null)
{
cc :;
if (!bConnected)
{
ErrorHandler?.Invoke(this, new Exception(BADCONNECTION));
return(null);
}
String temp = this.UniqueResult(
string.Format("select Val from {0} where Name='{1}'; "
, strTableName, strLabelName), args);
if (temp == null && NewWhenNoFound)
{
Write(strTableName, strLabelName, strDefaultValue, args);
goto cc;
}
else
{
return(temp);
}
}
public void RemoveDB(string strDBName, DbInteractiveArgs args = null)
{
DbCommand cmd = null;
try
{
if (args == null)
{
cmd = this.DbInteractiveArgs.ThisDbCommand;
}
else
{
cmd = args.ThisDbCommand;
}
cmd.CommandText = string.Format("DROP database {0} ;", strDBName);
cmd.ExecuteNonQuery();
this.Status = true;
}
catch (Exception e)
{
ErrorHandler(cmd, e);
}
}
public void RemoveRecord(string TableName, String strRequirement, DbInteractiveArgs args = null)
{
DbCommand cmd = null;
try
{
if (args == null)
{
cmd = this.DbInteractiveArgs.ThisDbCommand;
}
else
{
cmd = args.ThisDbCommand;
}
cmd.CommandText = string.Format("Delete from {0} where {1} ;", TableName, strRequirement);
cmd.ExecuteNonQuery();
this.Status = true;
}
catch (Exception e)
{
ErrorHandler?.Invoke(cmd, e);
}
}
public void CreateKeyValueTable(string strTableName, DbInteractiveArgs args = null)
{
this.NewTable(strTableName, EMBEDDEDTABLEDEF, args);
}
public void NewKeyValueView(string ViewName, string strFromTableName,
string strColumnName_Name, string strColumnName_Val, DbInteractiveArgs args = null)
{
string str = string.Format("{0} as Name,{1} as Val",
strColumnName_Name, strColumnName_Val
);
NewView(ViewName, strFromTableName, str, args);
}
// strColumnNames such as " age,sex,... "
public void NewView(string ViewName, string strTableName, string strColumnNames, DbInteractiveArgs args = null)
{
DbCommand cmd = null;
try
{
if (args == null)
{
cmd = this.DbInteractiveArgs.ThisDbCommand;
}
else
{
cmd = args.ThisDbCommand;
}
cmd.CommandText =
string.Format(CREATEVIEW, ViewName, strColumnNames, strTableName);
cmd.ExecuteNonQuery();
this.Status = true;
}
catch (Exception ex)
{
ErrorHandler(cmd, ex);
}
}
