public DBParameter getParameter(string table_name)
{
if (column_value == null)
{
return(null);
}
switch (StringValue)
{
case "[ID]":
var query = "select max(" + column_name + ") from " + table_name;
var data = DBQuery.query(query);
int id = 0;
try
{
id = Convert.ToInt32(data.Rows[0][0]);
}
catch { }
id++;
return(new DBParameter(column_name, id));
case "[GUID]":
return(new DBParameter(column_name, Guid.NewGuid().ToString()));
case "[DATETIME]":
return(new DBParameter(column_name, DateTime.Now));
case "[DATE]":
return(new DBParameter(column_name, DateTime.Today));
default:
return(new DBParameter(column_name, column_value));
}
}
/// <summary>
/// 从数据库中加载数据
/// </summary>
/// <param name="connection"></param>
private void load(DBConnection connection)
{
var query =
$"select {connection.left_quote}{config_info.name_column}{connection.right_quote} as {connection.left_quote}name{connection.right_quote}, " +
$" {connection.left_quote}{config_info.value_column}{connection.right_quote} as {connection.left_quote}value{connection.right_quote} " +
$"from {connection.left_quote}{config_info.table_name}{connection.right_quote} ";
if (config_info.domain_column.hasContents())
{
query += $" where {connection.left_quote}{config_info.domain_column}{connection.right_quote}={domain_id} ";
}
DataTable data = DBQuery.query(query, connection, null, out _);
for (int i = 0; i < data.Rows.Count; i++)
{
try
{
string name = data.Rows[i]["name"].ToString();
string value = data.Rows[i]["value"].ToString();
set(name, value);
}
catch { }
}
}
/// <summary>
/// 通过查询参数获取实例
/// </summary>
/// <param name="query_param">查询参数</param>
/// <returns></returns>
public static T get <T>(DBQueryParamList query_param)
{
var table = tableInfo <T>();
if (query_param == null || query_param.count == 0)
{
return(default(T));
}
var query = new StringBuilder();
query.Append($"select * from {DBUtil.connection.left_quote}{table.table_name}{DBUtil.connection.right_quote}");
var query_string = new StringBuilder();
List <DBParameter> pm = new List <DBParameter>();
foreach (DBQueryParam param in query_param.pm_list)
{
//如果有参数传递为空值, 则代表本次查询不能查询到想要的结果(会把结果集扩大化), 此时返回为查询失败
if (!param.isAvailableWithValue())
{
return(default(T));
}
if (query_string.Length > 0)
{
query_string.Append(" and ");
}
query_string.Append(param.queryString());
param.setQueryParameter(ref pm);
}
if (query_string.Length > 0)
{
query.Append($" where {query_string.ToString()}");
}
DataTable data = DBQuery.query(query.ToString(), pm);
try
{
var result = Activator.CreateInstance <T>();
foreach (DBColumn column in table.columns)
{
result.setValue(column.field_info, data.Rows[0][column.name]);
}
try
{
FieldInfo info = result.GetType().GetField("is_new");
info.SetValue(result, false);
}
catch { }
return(result);
}
catch { }
return(default(T));
}
/// <summary>
/// 获取下个Id
/// </summary>
/// <returns></returns>
public int nextId()
{
string query = $"select max({connection.left_quote}{dictionary_info.id_column}{connection.right_quote}) from {connection.left_quote}{dictionary_info.table_name}{connection.right_quote}";
int id = 0;
DataTable data = DBQuery.query(query, connection, null, out string msg);
try
{
id = Convert.ToInt32(data.Rows[0][0]);
}
catch { }
return(id + 1);
}
/// <summary>
/// 获取当前数据库版本号
/// 需要继承后实现
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
public virtual DBVersion getCurrentDBVersion(DBConnection connection)
{
var query = "select param_value from sys_configuration where param_name='current_db_version'";
DataTable data = DBQuery.query(query, connection);
string version = "";
try
{
version = data.Rows[0][0].ToString();
}
catch { }
if (version == "")
{
version = "1.0.0";
}
return(new DBVersion(version));
}
public DataTable getDictionaryInfoTable(string type, string filter)
{
var query =
$"select {connection.left_quote}{dictionary_info.id_column}{connection.right_quote} as {connection.left_quote}id{connection.right_quote}, " +
$" {connection.left_quote}{dictionary_info.content_column}{connection.right_quote} as {connection.left_quote}content{connection.right_quote} " +
$"from {connection.left_quote}{dictionary_info.table_name}{connection.right_quote} " +
$"where {connection.left_quote}{dictionary_info.type_column}{connection.right_quote}='{type}' " +
$" and ({connection.left_quote}{dictionary_info.content_column}{connection.right_quote} like '{filter}%' or {connection.left_quote}{dictionary_info.shortcut_column}{connection.right_quote} like '{filter}%') ";
if (dictionary_info.domain_column != "")
{
query += $" and {connection.left_quote}{dictionary_info.domain_column}{connection.right_quote}={domain_id} ";
}
query += $" order by {connection.left_quote}{dictionary_info.index_column}{connection.right_quote} ";
DataTable data = DBQuery.query(query, connection, null, out string msg);
return(data);
}
public DBCommonDictionaryItem queryItem(int id)
{
var query =
$"select * " +
$"from {connection.left_quote}{dictionary_info.table_name}{connection.right_quote} " +
$"where {connection.left_quote}{dictionary_info.id_column}{connection.right_quote}={id} ";
DataTable data = DBQuery.query(query, connection, null, out string msg);
DBCommonDictionaryItem item = new DBCommonDictionaryItem();
try
{
item.id = Convert.ToInt32(data.Rows[0][dictionary_info.id_column]);
item.dic_type = data.Rows[0][dictionary_info.type_column].ToString();
item.content = data.Rows[0][dictionary_info.shortcut_column].ToString();
item.index = Convert.ToInt32(data.Rows[0][dictionary_info.index_column]);
}
catch { }
return(item);
}
/// <summary>
/// 从数据库中加载数据
/// </summary>
/// <param name="connection_string"></param>
private void load(string connection_string)
{
this.connection_string = connection_string;
var query =
$"select {connection.left_quote}{dictionary_info.id_column}{connection.right_quote} as {connection.left_quote}id{connection.right_quote}, " +
$"{connection.left_quote}{dictionary_info.type_column}{connection.right_quote} as {connection.left_quote}type{connection.right_quote}, " +
$"{connection.left_quote}{dictionary_info.content_column}{connection.right_quote} as {connection.left_quote}content{connection.right_quote}, " +
$"{connection.left_quote}{dictionary_info.shortcut_column}{connection.right_quote} as {connection.left_quote}shortcut{connection.right_quote}, " +
$"{connection.left_quote}{dictionary_info.index_column}{connection.right_quote} as {connection.left_quote}index{connection.right_quote} " +
$"from {connection.left_quote}{dictionary_info.table_name}{connection.right_quote} ";
if (dictionary_info.domain_column.hasContents())
{
query += $" where {connection.left_quote}{dictionary_info.domain_column}{connection.right_quote}={domain_id} ";
}
query += $" order by {connection.left_quote}{dictionary_info.type_column}{connection.right_quote},{connection.left_quote}{dictionary_info.index_column}{connection.right_quote} ";
DataTable data = DBQuery.query(query, connection, null, out string msg);
for (int i = 0; i < data.Rows.Count; i++)
{
try
{
string type = data.Rows[i]["type"].ToString();
DBCommonDictionary dic = dictionaryWithField(type);
if (dic == null)
{
continue;
}
DBCommonDictionaryItem item = new DBCommonDictionaryItem();
item.id = Convert.ToInt32(data.Rows[i]["id"]);
item.content = data.Rows[i]["content"].ToString();
item.shortcut = data.Rows[i]["shortcut"].ToString();
try
{
item.index = Convert.ToInt32(data.Rows[i]["index"]);
}
catch { }
dic.items.Add(item);
}
catch { }
}
}
/// <summary>
/// 获取下个Index
/// </summary>
/// <param name="dictionary_type"></param>
/// <returns></returns>
public int nextIndex(string dictionary_type)
{
string query =
$"select max({connection.left_quote}{dictionary_info.index_column}{connection.right_quote}) " +
$"from {connection.left_quote}{dictionary_info.table_name}{connection.right_quote} " +
$"where {connection.left_quote}{dictionary_info.type_column}{connection.right_quote}='{dictionary_type}' ";
if (dictionary_info.domain_column != "")
{
query += $" and {connection.left_quote}{dictionary_info.domain_column}{connection.right_quote}={domain_id} ";
}
int index = 0;
DataTable data = DBQuery.query(query, connection, null, out string msg);
try
{
index = Convert.ToInt32(data.Rows[0][0]);
}
catch { }
return(index + 1);
}
/// <summary>
/// 更新数据库
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="assembly_name">资源库路径, 例如 IMC.Test.DB</param>
/// <param name="msg">错误信息</param>
/// <returns>当前更新后的数据库版本号</returns>
public DBVersion updateDB(DBConnection connection, string assembly_name, ref string msg)
{
DBVersion current_db_version = getCurrentDBVersion(connection);
if (db_version.compare(current_db_version) != 1)
{//当前最新版本号小于等于数据库版本号, 无需更新
msg = "当前数据库无需更新!";
return(db_version);
}
//获取所有数据库升级描述文件
Assembly assembly = null;
try
{
assembly = Assembly.Load(assembly_name);
}
catch
{
msg = "无法获取更新描述文件!";
return(db_version);
}
Type[] types = assembly.GetTypes();
List <DBUpdateProfile> update_profiles = new List <DBUpdateProfile>();
List <DBDataProcess> data_processes = new List <DBDataProcess>();
//获取描述文件后按照版本号先后顺序排列
foreach (Type type in types)
{
try
{
if (type.BaseType == typeof(DBUpdateProfile))
{//数据库更新
DBUpdateProfile profile = Activator.CreateInstance(type) as DBUpdateProfile;
//过滤掉之前更新过的记录
if (!profile.shouldUpdate(current_db_version))
{
continue;
}
int index = -1;
for (int i = 0; i < update_profiles.Count; i++)
{
if (update_profiles[i].start_db_version.compare(profile.start_db_version) == 1)
{
index = i;
break;
}
}
if (index >= 0)
{
update_profiles.Insert(index, profile);
}
else
{
update_profiles.Add(profile);
}
}
else if (type.BaseType == typeof(DBDataProcess))
{//数据处理操作
DBDataProcess process = Activator.CreateInstance(type) as DBDataProcess;
//过滤掉之前更新过的记录
if (!process.shouldPerformProcess(current_db_version))
{
continue;
}
data_processes.Add(process);
}
}
catch { }
}
//更新数据库
foreach (DBUpdateProfile profile in update_profiles)
{
List <string> sqls = profile.getUpdateSqlList(connection.db_type);
foreach (var query in sqls)
{
DBQuery.query(query, connection);
}
//查找是否有相关的数据更新操作, 有则进行更新
foreach (DBDataProcess process in data_processes)
{
if (process.start_db_version.compare(profile.start_db_version) == 0)
{
process.processData(connection);
}
}
}
//更新版本号到数据库相关表(需继承后实现)
msg = updateNewVersionInfoToDB(connection);
return(db_version);
}
