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