/// <summary> /// 获取列 /// </summary> /// <param name="filterSchemaNameTableName">过滤模式表名,逗号分隔</param> /// <param name="databaseName">数据库名</param> /// <returns></returns> public List <ColumnVM> GetColumn(string filterSchemaNameTableName = null, string databaseName = null) { var result = new List <ColumnVM>(); if (string.IsNullOrWhiteSpace(databaseName)) { databaseName = DefaultDatabaseName(); } var listSchemaNameTableName = new List <Tuple <string, string> >(); if (!string.IsNullOrWhiteSpace(filterSchemaNameTableName)) { filterSchemaNameTableName.Replace("'", "").Split(',').ToList().ForEach(item => { var schemaName = string.Empty; var tableName = string.Empty; var listST = item.Split("."); if (listST.Length == 2) { schemaName = listST[0]; tableName = listST[1]; } else { tableName = listST[0]; } listSchemaNameTableName.Add(new Tuple <string, string>(schemaName, tableName)); }); } var sql = DataKitScript.GetColumn(tdb, databaseName, listSchemaNameTableName); var ds = db.SqlExecuteReader(sql); if (tdb == SharedEnum.TypeDB.SQLite) { ds.Item1.Tables[0].Rows.RemoveAt(0); var ds2 = ds.Item1.Tables[1].Select(); var aakey = "AUTOINCREMENT"; foreach (DataRow dr in ds.Item1.Tables[0].Rows) { var csql = ds2.FirstOrDefault(x => x["name"].ToString().ToLower() == dr["TableName"].ToString().ToLower())[1].ToString().ToUpper(); if (csql.Contains(aakey)) { var isaa = csql.Split(',').Any(x => x.Contains(aakey) && x.Contains(dr["ColumnName"].ToString().ToUpper())); if (isaa) { dr["AutoAdd"] = "YES"; } } } } result = ds.Item1.Tables[0].ToModel <ColumnVM>(); return(result); }
/// <summary> /// 设置列注释 /// </summary> /// <param name="tableName">表名</param> /// <param name="columnName">列名</param> /// <param name="columnComment">列注释</param> /// <param name="schemaName">模式名</param> /// <param name="databaseName">数据库名</param> /// <returns></returns> public bool SetColumnComment(string tableName, string columnName, string columnComment, string schemaName = null, string databaseName = null) { if (tdb != SharedEnum.TypeDB.SQLite) { if (string.IsNullOrWhiteSpace(databaseName)) { databaseName = DefaultDatabaseName(); } var sql = DataKitScript.SetColumnComment(tdb, databaseName, schemaName, tableName, columnName, columnComment); db.SqlExecuteNonQuery(sql); } return(true); }
/// <summary> /// 获取表 /// </summary> /// <param name="schemaName">模式名</param> /// <param name="databaseName">数据库名</param> /// <returns></returns> public List <TableVM> GetTable(string schemaName = null, string databaseName = null) { var result = new List <TableVM>(); if (string.IsNullOrWhiteSpace(databaseName)) { databaseName = DefaultDatabaseName(); } var sql = DataKitScript.GetTable(tdb, databaseName, schemaName); var ds = db.SqlExecuteReader(sql); result = ds.Item1.Tables[0].ToModel <TableVM>(); if (tdb == SharedEnum.TypeDB.SQLite) { //计算表行 https://stackoverflow.com/questions/4474873 var listsql = new List <string> { "SELECT '' AS TableName, 0 AS TableRows" }; result.ForEach(t => { var tableName = DbHelper.SqlQuote(SharedEnum.TypeDB.SQLite, t.TableName); var sqlrows = $"SELECT '{t.TableName}' AS TableName, max(RowId) - min(RowId) + 1 AS TableRows FROM {tableName}"; listsql.Add(sqlrows); }); var sqls = string.Join("\nUNION ALL\n", listsql); var dsrows = db.SqlExecuteReader(sqls).Item1.Tables[0].Rows.Cast <DataRow>(); result.ForEach(item => { var trow = dsrows.FirstOrDefault(x => x[0].ToString().ToLower() == item.TableName.ToLower()); if (trow != null && trow[1].ToString() != "") { item.TableRows = Convert.ToInt64(trow[1]); } }); } return(result); }
/// <summary> /// 获取库名 /// </summary> /// <returns></returns> public List <string> GetDatabaseName() { var result = new List <string>(); var sql = DataKitScript.GetDatabaseName(tdb); var dt = db.SqlExecuteReader(sql).Item1.Tables[0]; foreach (DataRow dr in dt.Rows) { if (tdb == SharedEnum.TypeDB.SQLite) { result.Add(dr["name"].ToString()); } else { result.Add(dr[0].ToString()); } } return(result); }
/// <summary> /// 获取库 /// </summary> /// <param name="filterDatabaseName">过滤数据库名,逗号分隔</param> /// <returns></returns> public List <DatabaseVM> GetDatabase(string filterDatabaseName = null) { var result = new List <DatabaseVM>(); var listDatabaseName = string.IsNullOrWhiteSpace(filterDatabaseName) ? null : filterDatabaseName.Replace("'", "").Split(','); var sql = DataKitScript.GetDatabase(tdb, listDatabaseName); var ds = db.SqlExecuteReader(sql); if (tdb == SharedEnum.TypeDB.SQLite) { var dt1 = ds.Item1.Tables[0]; var charset = ds.Item1.Tables[1].Rows[0][0].ToString(); foreach (DataRow dr in dt1.Rows) { var name = dr["name"].ToString(); var file = dr["file"].ToString(); var fi = new FileInfo(file); result.Add(new DatabaseVM { DatabaseName = name, DatabaseCharset = charset, DatabasePath = file, DatabaseDataLength = fi.Length, DatabaseCreateTime = fi.CreationTime }); } } else { result = ds.Item1.Tables[0].ToModel <DatabaseVM>(); } return(result); }
/// <summary> /// 表DDL /// </summary> /// <param name="tableName">表名</param> /// <param name="schemaName">模式名</param> /// <param name="databaseName">数据库名</param> /// <returns></returns> public string GetTableDDL(string tableName, string schemaName = null, string databaseName = null) { string result = string.Empty; if (string.IsNullOrWhiteSpace(databaseName)) { databaseName = DefaultDatabaseName(); } var sql = DataKitScript.GetTableDDL(tdb, databaseName, schemaName, tableName); Console.WriteLine(sql); switch (tdb) { case SharedEnum.TypeDB.SQLite: { var ds = db.SqlExecuteReader(sql); var rows = ds.Item1.Tables[0].Rows; var ddl = new List <string> { $"DROP TABLE IF EXISTS [{rows[0]["tbl_name"]}]" }; foreach (DataRow dr in rows) { var script = dr["sql"]?.ToString(); if (!string.IsNullOrWhiteSpace(script)) { ddl.Add(script); } } result = string.Join(";\r\n", ddl) + ";"; } break; case SharedEnum.TypeDB.MySQL: case SharedEnum.TypeDB.MariaDB: { var ds = db.SqlExecuteReader(sql); var rows = ds.Item1.Tables[0].Rows; var ddl = new List <string> { $"DROP TABLE IF EXISTS `{rows[0][0]}`" }; var script = rows[0][1].ToString(); ddl.Add(script); result = string.Join(";\r\n", ddl) + ";"; } break; case SharedEnum.TypeDB.Oracle: { var ds = db.SqlExecuteReader(sql, func: cmd => { var ocmd = (OracleCommand)cmd; //begin ... end; if (DbHelper.SqlParserBeginEnd(sql)) { //open:name for var cursors = DbHelper.SqlParserCursors(sql); foreach (var cursor in cursors) { ocmd.Parameters.Add(cursor, OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output); } } return(cmd); }); var ddlTable = ds.Item1.Tables[0].Rows[0][0].ToString().Trim(); var ddlIndex = ds.Item1.Tables[1].Rows.Cast <DataRow>().Select(x => x[0].ToString().Trim() + ";"); var ddlCheck = ds.Item1.Tables[2].Rows.Cast <DataRow>().Select(x => x[0].ToString().Trim() + ";"); var ddlTableComment = ds.Item1.Tables[3].Rows[0][0].ToString().Trim(); var ddlColumnComment = ds.Item1.Tables[4]; var fullTableName = $"\"{databaseName}\".\"{tableName}\""; var ddl = new List <string> { $"DROP TABLE {fullTableName};", $"{ddlTable};" }; if (ddlIndex.Any()) { ddl.Add(""); ddl.AddRange(ddlIndex); } if (ddlCheck.Any()) { ddl.Add(""); ddl.AddRange(ddlCheck); } ddl.Add(""); ddl.Add($"COMMENT ON TABLE {fullTableName} IS '{ddlTableComment.Replace("'", "''")}';"); ddl.Add(""); foreach (DataRow dr in ddlColumnComment.Rows) { ddl.Add($"COMMENT ON COLUMN {fullTableName}.\"{dr[0]}\" IS '{dr[1].ToString().Trim().Replace("'", "''")}';"); } result = string.Join("\r\n", ddl); } break; case SharedEnum.TypeDB.SQLServer: { var ds = db.SqlExecuteReader(sql); result = ds.Item1.Tables[0].Rows[0][1].ToString(); } break; case SharedEnum.TypeDB.PostgreSQL: { //消息 var listInfo = new List <string>(); var dbConn = (NpgsqlConnection)db.Connection; dbConn.Notice += (s, e) => { listInfo.Add(e.Notice.MessageText); }; db.SqlExecuteReader(sql); result = string.Join("\r\n", listInfo); } break; } return(result); }