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