/// <summary> /// sqlite 查询数据库所有表 /// </summary> /// <param name="dbConnect"></param> /// <param name="dbName"></param> /// <param name="tableName"></param> /// <returns></returns> public static List <TableInfo> SelectTableNameBySqlite(string dbConnect, string dbName, string tableKeyword) { List <TableInfo> lists = new List <TableInfo>(); try { string sqlAllTable = "SELECT name as TABLE_NAME FROM sqlite_master WHERE type='table' and name!='sqlite_sequence' "; if (!string.IsNullOrEmpty(tableKeyword)) { sqlAllTable += $" and name like '%{tableKeyword}%'"; } sqlAllTable += " ORDER BY name"; SQLiteHelper.connectionString = dbConnect; DataTable dataTable = SQLiteHelper.ExecuteDataTable(sqlAllTable, false, null); lists = DbConvert.TableToList <TableInfo>(dataTable); } catch (Exception) { throw; } finally { SQLiteHelper.connectionString = $@"data source={AppDomain.CurrentDomain.BaseDirectory}{ConfigurationManager.AppSettings["dbRootFolder"].ToString()}\{ConfigurationManager.AppSettings["dbName"].ToString()}"; } return(lists); }
/// <summary> /// 分页查询 /// </summary> /// <typeparam name="T"></typeparam> /// <typeparam name="whereCols">查询条件</typeparam> /// <param name="t"></param> /// <returns></returns> public PageInfo <T> SelectPage <T>(PageInfo <T> pageInfo, string cols, string whereCols, WhereType whereType) { SqlModel selectModel = CreateSqlHelper.SelectSqlByPage(pageInfo, cols, whereCols, whereType); DataTable dataTable = SQLiteHelper.ExecuteDataTable(selectModel.Sql, false, selectModel.SqlParameters); List <T> lists = DbConvert.TableToList <T>(dataTable); SqlModel selectCountModel = CreateSqlHelper.SelectCount(pageInfo.ParamsSearch, whereCols, whereType); object result = SQLiteHelper.ExecuteScalar(selectCountModel.Sql, false, selectCountModel.SqlParameters); int total = Convert.ToInt32(result); int totalPage = 0; if (total % pageInfo.PageSize == 0) { totalPage = total / pageInfo.PageSize; } else { totalPage = total / pageInfo.PageSize + 1; } pageInfo.TotalPage = totalPage; pageInfo.Records = lists; pageInfo.CurrentCount = total; //lists.Count; return(pageInfo); }
/// <summary> /// 查询 /// </summary> /// <typeparam name="T"></typeparam> /// <typeparam name="whereCols">查询条件</typeparam> /// <param name="t"></param> /// <returns></returns> public List <T> SelectPage <T>(T t, string cols, string whereCols, WhereType whereType) { SqlModel insertModel = CreateSqlHelper.SelectSql(t, cols, whereCols, whereType); DataTable dataTable = SQLiteHelper.ExecuteDataTable(insertModel.Sql, false, insertModel.SqlParameters); List <T> lists = DbConvert.TableToList <T>(dataTable); return(lists); }
/* /// <summary> * /// mysql 查询数据库所有表 * /// </summary> * /// <param name="connectionStr"></param> * /// <param name="tableName"></param> * /// <param name="dbName"></param> * /// <returns></returns> * public static List<TableFieldInfo> SelectByMySql(string connectionStr, string tableName, string dbName) * { * string sql = "SELECT COLUMN_NAME as ColumnName,CHARACTER_MAXIMUM_LENGTH as MaxLength,COLUMN_COMMENT as Comment,DATA_TYPE as DataType,COLUMN_KEY as ColumnKey,EXTRA as Extra FROM"; * sql += $" INFORMATION_SCHEMA.COLUMNS WHERE 1=1"; * if (!string.IsNullOrEmpty(tableName)) * { * sql+=$" and table_name in ({tableName}) "; * } * sql += $" and TABLE_SCHEMA = '{dbName}"; * sql += $" INFORMATION_SCHEMA.COLUMNS WHERE 1=1"; * MySqlHelper.connectionString = connectionStr; * DataTable dataTable = MySqlHelper.ExecuteDataTable(sql, false, null); * List<TableFieldInfo> lists = DbConvert.TableToList<TableFieldInfo>(dataTable); * return lists; * }*/ /// <summary> /// mysql 查询数据库表的字段 备注 数据库类型 长度 等数据 /// </summary> /// <param name="dbConnect"></param> /// <param name="dbName"></param> /// <param name="tableName"></param> /// <returns></returns> public static List <TableFieldInfo> SelectTableFieldsByMySql(string dbConnect, string dbName, string tableName) { string sql = "SELECT COLUMN_NAME as ColumnName,CHARACTER_MAXIMUM_LENGTH as MaxLength,COLUMN_COMMENT as Comments,DATA_TYPE as DataType,COLUMN_KEY as ColumnKey,EXTRA as Extra FROM"; sql += $" INFORMATION_SCHEMA.COLUMNS WHERE 1=1"; sql += $" and table_name ='{tableName}' and TABLE_SCHEMA = '{dbName}'"; MySqlHelper.connectionString = dbConnect; DataTable dataTable = MySqlHelper.ExecuteDataTable(sql, false, null); List <TableFieldInfo> lists = DbConvert.TableToList <TableFieldInfo>(dataTable); return(lists); }
/// <summary> /// 通过oracle 及条件 查询所有表 /// </summary> /// <param name="dbConnect"></param> /// <param name="dbName"></param> /// <param name="tableKeyword"></param> /// <returns></returns> public static List <TableInfo> SelectTableNameByOracle(string dbConnect, string dbName, string tableKeyword) { StringBuilder sbf = new StringBuilder(); sbf.Append("SELECT A.TABLE_NAME as TABLE_NAME,b.COMMENTS TABLE_COMMENT FROM user_tables A,user_tab_comments b "); sbf.Append("WHERE A.TABLE_NAME = b.TABLE_NAME "); if (!string.IsNullOrEmpty(tableKeyword)) { sbf.Append($" and A .TABLE_NAME = '{tableKeyword}'"); } sbf.Append(" ORDER BY TABLE_NAME"); OracleHelper.connectionString = dbConnect; DataTable dataTable = OracleHelper.ExecuteDataTable(sbf.ToString(), false, null); List <TableInfo> lists = DbConvert.TableToList <TableInfo>(dataTable); return(lists); }
/// <summary> /// 通过mysql 及条件 查询所有表 /// </summary> /// <param name="dbConnect"></param> /// <param name="dbName"></param> /// <param name="tableKeyword"></param> /// <returns></returns> public static List <TableInfo> SelectTableNameByMySql(string connectionStr, string dbName, string tableKeyword) { //List<string> lists = new List<string>(); string sql = $"select TABLE_NAME,TABLE_COMMENT,TABLE_COLLATION,ENGINE from information_schema.tables where table_schema='{dbName}'"; if (!string.IsNullOrEmpty(tableKeyword)) { sql += $" and TABLE_NAME like '%{tableKeyword}%'"; } MySqlHelper.connectionString = connectionStr; DataTable dataTable = MySqlHelper.ExecuteDataTable(sql, false, null); /*for (int i = 0; i < dataTable.Rows.Count; i++) * { * lists.Add(dataTable.Rows[i]["TABLE_NAME"].ToString()); * }*/ return(DbConvert.TableToList <TableInfo>(dataTable)); }
private void SaveBtn_Click(object sender, EventArgs e) { try { string temSql = $"select * from CODE_PROJECT_TEMPLATE_CONFIG_INFO where PROJECT_ID ='{codeProjectInfo.ID}'"; DataTable dataTable1 = createBLL.SelectData(temSql); List <CodeProjectTemplateConfigInfo> templateConfigInfos = DbConvert.TableToList <CodeProjectTemplateConfigInfo>(dataTable1); if (string.IsNullOrEmpty(this.tableNames.Text)) { MessageHelper.ShowWarn("表名不能为空,至少选择一个表"); return; } if (string.IsNullOrEmpty(this.TOP_LEVEL.Text)) { MessageHelper.ShowWarn("顶级包名不能为空"); return; } if (templateConfigInfos == null || templateConfigInfos.Count <= 0) { MessageHelper.ShowWarn("未检出到任何模板"); return; } string[] tableArr = this.tableNames.Text.Split(','); List <CsProjectCode> projectCodes = new List <CsProjectCode>(); List <TableFieldInfo> lists = null; CsProjectCode csProjectCode = null; CodeProjectInfo codeProjectInfoEdit = null; if (this.TopLevelControl is AutoCodeConfigForm) { codeProjectInfoEdit = ((AutoCodeConfigForm)this.TopLevelControl).GetTopTextBoxInfo(); } string outFileFolder = "", outFilePath, projectCodePath = ""; Dictionary <string, object> dicKv; StringBuilder sbf = new StringBuilder(); string projectSite = null; for (int i = 0; i < tableArr.Length; i++) { csProjectCode = new CsProjectCode(tableArr[i]) { Author = AUTHOR.Text, TopLevel = TOP_LEVEL.Text, Version = codeProjectInfo.VERSION }; lists = dbBLL.SelectTableFields(codeProjectDbConfigInfo, tableArr[i]); csProjectCode.TableFieldInfos = lists; projectCodes.Add(csProjectCode); dicKv = new Dictionary <string, object>(); dicKv.Add("tableName", csProjectCode.TableName);//表名 if (tablesDicts.ContainsKey(csProjectCode.TableName)) { dicKv.Add("tableComment", tablesDicts[csProjectCode.TableName].TableComment); //表备注 } dicKv.Add("entityName", csProjectCode.EntityName); //实体名 dicKv.Add("projectCode", csProjectCode); //项目表信息 if (codeProjectInfoEdit != null) { dicKv.Add("projectName", codeProjectInfoEdit.PRO_NAME);//项目名称 projectSite = codeProjectInfoEdit.PRO_SITE; } for (int k = 0; k < templateConfigInfos.Count; k++) { projectCodePath = projectSite ?? codeProjectInfo.PRO_SITE; //templateConfigInfos[k].TemplatePath.Replace("{tableName}", ""); outFileFolder = templateConfigInfos[k].TemplatePath.Replace("{projectSite}", projectCodePath).Replace("{tableName}", csProjectCode.EntityName); outFilePath = Path.Combine(outFileFolder, $"{csProjectCode.EntityName}{templateConfigInfos[k].OverlayName}{templateConfigInfos[k].Suffix}"); NvelocityUtil.WriteTemplateByString(outFilePath, templateConfigInfos[k].TemplateContent, dicKv); sbf.Append($"表:{csProjectCode.TableName} ,模板【{templateConfigInfos[k].TemplateName}{templateConfigInfos[k].Suffix}】生成成功\n"); } } MessageHelper.ShowSuccess(sbf.ToString()); //MessageHelper.ShowSuccess("生成完毕"); this.codeFolder.Text = projectCodePath; } catch (Exception ex) { MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error); } }