/// <summary>
/// 获取数据库表的信息.
/// </summary>
/// <returns></returns>
public List <TableInfoModel> GetTables()
{
string commandText = global::Wunion.DataAdapter.EntityGenerator.Properties.Resources.PostgreSQLDbSchema;
List <TableInfoModel> Result = null;
using (NpgsqlCommand Command = (NpgsqlCommand)DbEngine.DBA.CreateDbCommand())
{
Command.Connection = (NpgsqlConnection)DbEngine.DBA.Connect();
Command.CommandText = commandText;
Command.CommandType = CommandType.Text;
Command.Connection.Open();
NpgsqlDataReader Rd = Command.ExecuteReader(CommandBehavior.CloseConnection);
Result = new List <TableInfoModel>();
int i;
TableInfoModel tableInfo;
while (Rd.Read())
{
tableInfo = new TableInfoModel();
tableInfo.SetValue("tableName", Rd["tablename"], true);
tableInfo.SetValue("paramName", Rd["paramname"], true);
tableInfo.SetValue("allowNull", Rd["allownull"], true);
tableInfo.SetValue("dbType", Rd["dbtype"], true);
tableInfo.SetValue("isPrimary", Rd["isprimary"], true);
tableInfo.SetValue("isIdentity", Rd["isidentity"], true);
Result.Add(tableInfo);
}
Rd.Close();
}
return(Result);
}
/// <summary>
/// 获取数据库表的信息.
/// </summary>
/// <returns></returns>
public List <TableInfoModel> GetTables()
{
List <TableInfoModel> Result = null;
using (MySqlCommand Command = (MySqlCommand)DbEngine.DBA.CreateDbCommand())
{
Command.Connection = (MySqlConnection)DbEngine.DBA.Connect();
Command.CommandText = global::Wunion.DataAdapter.EntityGenerator.Properties.Resources.MySQLDbSchema;
Command.CommandType = CommandType.Text;
Command.Parameters.Add((MySqlParameter)(DbEngine.DBA.CreateParameter("dbName", GetDbName())));
Command.Connection.Open();
MySqlDataReader Rd = Command.ExecuteReader(CommandBehavior.CloseConnection);
Result = new List <TableInfoModel>();
int i;
TableInfoModel tableInfo;
while (Rd.Read())
{
tableInfo = new TableInfoModel();
for (i = 0; i < Rd.FieldCount; ++i)
{
tableInfo.SetValue(Rd.GetName(i), Rd.GetValue(i), true);
}
Result.Add(tableInfo);
}
Rd.Close();
}
return(Result);
}
private void Detalha()
{
if (lstTabela.SelectedItems.Count == 1)
{
tableInfo = new TableInfoModel();
TabelasInfoService.DetalhaTabela(tableInfo, lstTabela.SelectedValue.ToString());
txtDesTabela.Text = tableInfo.descricaoTabela;
gbTabela.Text = tableInfo.nomeTabela;
txtChaves.Text = tableInfo.chavesPrimarias;
bsDetalheColunas.DataSource = tableInfo.dtDetalheColunas;
gdvFK.DataSource = tableInfo.dtReferenciasFK;
gdvGrant.DataSource = tableInfo.dtPermissoes;
if (tableInfo.dtRegras == null)
{
tableInfo.dtRegras = new List <string>();
}
gdvRegras.DataSource = tableInfo.dtRegras.Select(x => new { Value = x }).ToList();
txtBuscaDetalheColuna.ADDRecursos(gdvDetalheColuna, bsDetalheColunas);
txtBuscaDetalheColuna.filtro.Add("COLUMN_NAME");
txtBuscaDetalheColuna.filtro.Add("COLUMN_DESCRIPTION");
txtBuscaDetalheColuna.filtro.Add("DATA_TYPE");
txtRegra.Enabled = true;
btnRegra.Enabled = true;
}
}
/// <summary>
/// 获取数据库表的信息.
/// </summary>
/// <returns></returns>
public List <TableInfoModel> GetTables()
{
string commandText = global::Wunion.DataAdapter.EntityGenerator.Properties.Resources.SQLServerDbSchema;
//command = command.Replace("{TABLE_NAME}", TableName);
List <TableInfoModel> Result = null;
using (SqlCommand Command = (SqlCommand)DbEngine.DBA.CreateDbCommand())
{
Command.Connection = (SqlConnection)DbEngine.DBA.Connect();
Command.CommandText = commandText;
Command.CommandType = CommandType.Text;
Command.Connection.Open();
SqlDataReader Rd = Command.ExecuteReader(CommandBehavior.CloseConnection);
Result = new List <TableInfoModel>();
int i;
string field;
TableInfoModel tableInfo;
while (Rd.Read())
{
tableInfo = new TableInfoModel();
for (i = 0; i < Rd.FieldCount; ++i)
{
field = Rd.GetName(i);
tableInfo.SetValue(field, Rd.GetValue(i), true);
}
Result.Add(tableInfo);
}
Rd.Close();
}
return(Result);
}
public TableColumnsForm(List <TableInfoModel> tables, TableInfoModel tableInfo)
{
InitializeComponent();
AllTableInfo = tables;
currentTable = tableInfo;
ColumnsDlgTableName.Text = string.Format("{0} 表", tableInfo.tableName);
}
public static void GerarDocumentoTabela(string caminhoArquivo, TableInfoModel tableInfo)
{
string arquivoXML = caminhoArquivo;
XmlTextWriter xmlWriter = new XmlTextWriter(arquivoXML, Encoding.UTF8);
GerarCabecalhoDocumento(xmlWriter);
AdicionarEstilos(xmlWriter);
GerarConteudoDocumentoTabela(xmlWriter, tableInfo);
FinalizarArquivo(xmlWriter);
}
public List <TableInfoModel> GetTableNumList()
{
var ds = dao.GetTableNumList();
List <TableInfoModel> tableNums = new List <TableInfoModel>();
if (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
var item = new TableInfoModel();
item.TableNum = dr[0].ToString();
tableNums.Add(item);
}
}
return(tableNums);
}
public static void GerarDocumentoTabela(TableInfoModel tableInfo)
{
try
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Planilha Excel xml|*.xml";
saveFileDialog.Title = "Salvar Excel xml";
saveFileDialog.FileName = tableInfo.nomeTabela;
saveFileDialog.ShowDialog();
if (saveFileDialog.FileName != "")
{
DAO.GeradorDocumentos.GerarDocumentoTabela(saveFileDialog.FileName, tableInfo);
MessageBox.Show("Documento gerado com sucesso");
}
}
catch (Exception ex)
{
MessageBox.Show("Erro au gerar arquivo: " + ex.Message);
}
}
private void smi_viewTable_Click(object sender, EventArgs e)
{
if (codeService == null)
{
return;
}
if (dataGridView1.SelectedRows.Count < 1)
{
return;
}
int currentIndex = dataGridView1.SelectedRows[0].Index;
List <TableInfoModel> tables = (List <TableInfoModel>)dataGridView1.DataSource;
TableInfoModel currentTable = tables[currentIndex];
using (TableColumnsForm columnsView = new TableColumnsForm(codeService.AllTables, currentTable))
{
columnsView.Language = Language;
columnsView.StartPosition = FormStartPosition.CenterParent;
columnsView.ShowDialog(this);
}
}
/// <summary>
/// 获取指定表的信息.
/// </summary>
/// <param name="Command">用于执行命令的对象.</param>
/// <param name="tbl">表名.</param>
/// <param name="list">将表的列信息输出到此集合.</param>
private void GetTableInfo(SqliteCommand Command, dynamic tbl, List <TableInfoModel> list)
{
Command.CommandText = string.Format("PRAGMA table_info([{0}])", tbl.tbl_name);
if (Command.Connection.State != ConnectionState.Open)
{
Command.Connection.Open();
}
SqliteDataReader Rd = Command.ExecuteReader();
TableInfoModel tableInfo;
while (Rd.Read())
{
tableInfo = new TableInfoModel();
tableInfo.tableName = tbl.tbl_name;
tableInfo.paramName = Rd["name"] as string;
tableInfo.dbType = FormatDbType(Rd["type"] as string);
tableInfo.allowNull = !Convert.ToBoolean(Rd["notnull"]);
tableInfo.defaultValue = (Rd["dflt_value"] == null || Rd["dflt_value"] == DBNull.Value) ? null : Rd["dflt_value"];
tableInfo.isPrimary = Convert.ToBoolean(Rd["pk"]);
tableInfo.isIdentity = IsIdentity(tableInfo.paramName, tbl.sql);
list.Add(tableInfo);
}
Rd.Close();
}
/// <summary>
///
/// </summary>
/// <param name="model"></param>
/// <param name="table"></param>
/// <returns></returns>
private async Task <bool> BeginSyncAsync(TaskListModel model, TableInfoModel table)
{
try
{
var repository = _repositoryFactory.Create(table.SystemInfo.DbConnection);
var columns = table.Columns;
var addFields = new List <string>();
var atFields = new List <string>();
foreach (var column in columns)
{
addFields.Add($"{column.SelfColumn}");
atFields.Add($"@{column.ModelColumn}");
}
var sql = $"insert into {table.TableName}({string.Join(", ", addFields)}) values({string.Join(", ", atFields)});";
var bRet = await repository.ExecuteSqlAsync(sql, model);
return(bRet);
}
catch (Exception ex)
{
_logger.LogError("BeginSyncAsync", ex);
return(false);
}
}
internal static void DetalhaTabela(TableInfoModel tableInfo, string nomeTabela)
{
tableInfo.nomeTabela = nomeTabela;
tableInfo.dtDetalheColunas = ListaDestalhesTabelas(tableInfo.nomeTabela);
tableInfo.dtReferenciasFK = DAO.TabelasInfoSQLDao.DependenciasFK(tableInfo.nomeTabela);
tableInfo.schema = DAO.TabelasInfoSQLDao.SchemaTabela(tableInfo.nomeTabela);
tableInfo.dtPermissoes = DAO.TabelasInfoSQLDao.Permissoes(tableInfo.nomeTabela);
//Chaves Primarias
DataTable chavesPrimarias = DAO.TabelasInfoSQLDao.ChavesPrimarias(tableInfo.nomeTabela);
for (int row = 0; row < chavesPrimarias.Rows.Count; row++)
{
tableInfo.chavesPrimarias += chavesPrimarias.Rows[row][0].ToString();
if (row != chavesPrimarias.Rows.Count - 1)
{
tableInfo.chavesPrimarias += ", ";
}
}
XMLModel.ListaDescricoesModel lsDescricoes = DAO.DescricaoXMLDAO.Ler();
XMLModel.DescricaoTabelaModel tableDescription = (from ts in lsDescricoes.listaDescricaoTabelas
where String.Equals(ts.NomeTabela, nomeTabela, StringComparison.OrdinalIgnoreCase)
select ts).DefaultIfEmpty().First();
if (tableDescription != null)
{
tableInfo.descricaoTabela = tableDescription.Descricao;
tableInfo.dtRegras = tableDescription.Regras;
}
else
{
tableInfo.descricaoTabela = "";
}
}
public IEnumerable <TableInfoWithColumnsModel> GetTableInfos(DatabaseSettingModel settingModel)
{
var resultTable = new DataTable();
NpgsqlConnection connection = null;
try
{
connection = new NpgsqlConnection(
settingModel.HasConnectionString ? settingModel.ConnectionString :
$"Host={settingModel.Host};Port={settingModel.Port};Database={settingModel.DatabaseName};Username={settingModel.UserName};Password={settingModel.Password};");
connection.Open();
#region SqlCommandString
var sqlCommandString = @"SELECT obj_description(g.OID) as table_comment, col.table_catalog, col.table_schema,col.table_name, col.column_name, col.column_default, col.is_nullable,
CASE WHEN col.data_type = 'USER-DEFINED' THEN col.udt_name ELSE col.data_type END,
col.character_maximum_length, g.description
FROM information_schema.columns AS col
LEFT JOIN (SELECT c.OID, c.relname AS table_name, a.attname As column_name, d.description
FROM pg_class As c
INNER JOIN pg_attribute As a ON c.oid = a.attrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
LEFT JOIN pg_description As d ON (d.objoid = c.oid AND d.objsubid = a.attnum)
WHERE c.relkind IN('r', 'v') AND n.nspname = 'public'
ORDER BY n.nspname, c.relname, a.attname) AS g ON col.table_name = g.table_name AND col.column_name = g.column_name
WHERE table_schema NOT IN ('information_schema' , 'pg_catalog' ,'topology')
AND table_catalog = @dbname
AND col.table_name NOT IN ('raster_overviews','raster_columns','spatial_ref_sys')
ORDER BY col.table_schema ASC, col.table_name ASC, col.ordinal_position ASC";
#endregion
var command = connection.CreateCommand();
command.CommandTimeout = 120;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sqlCommandString;
command.Parameters.AddWithValue("@dbname", settingModel.DatabaseName);
var adapter = new NpgsqlDataAdapter(command);
adapter.Fill(resultTable);
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
var result = new List <TableInfoWithColumnsModel>();
TableInfoModel tableModel = null;
var columnInfos = new List <ColumnInfoModel>();
foreach (DataRow row in resultTable.Rows)
{
var schema = Convert.ToString(row["table_schema"]);
var table = schema == "public" ? Convert.ToString(row["table_name"]) : $"{schema}.{Convert.ToString(row["table_name"])}";
var tableComment = Convert.ToString(row["table_comment"]);
var column = Convert.ToString(row["column_name"]);
var dataType = Convert.ToString(row["data_type"]);
var lengthX = row["character_maximum_length"] == DBNull.Value ? 0 : Convert.ToInt32(row["character_maximum_length"]);
var length = lengthX == -1 ? "(MAX)" : lengthX == 0 ? "" : $"({lengthX})";
var defaultValue = Convert.ToString(row["column_default"]);
var isNull = Convert.ToString(row["is_nullable"]) != "NO";
var description = Convert.ToString(row["description"]);
if (tableModel == null || tableModel.Name != table)
{
if (tableModel != null)
{
result.Add(new TableInfoWithColumnsModel(tableModel, columnInfos));
}
tableModel = new TableInfoModel {
Name = table, Comment = tableComment
};
columnInfos = new List <ColumnInfoModel>();
}
columnInfos.Add(new ColumnInfoModel
{
Name = column,
Type = $"{dataType}{length}",
DefaultValue = defaultValue,
IsCanNull = isNull,
Comment = description,
});
}
result.Add(new TableInfoWithColumnsModel(tableModel, columnInfos));
return(result);
}
/// <summary>
/// 创建数据库表
/// </summary>
/// <param name="table">表名</param>
/// <param name="columns">列信息</param>
/// <returns>是否成功</returns>
public override bool CreateTable(TableInfoModel table, List <ColumnInfoModel> columns)
{
if (string.IsNullOrEmpty(table.Name))
{
throw new ArgumentNullException("table");
}
if (columns == null)
{
throw new ArgumentNullException("columns");
}
if (columns.Count == 0)
{
return(false);
}
int count = 0;
StringBuilder createTableSql = new StringBuilder();
List <ColumnInfoModel> keyColumns = columns.Where(q => q.IsKey).ToList();
List <IndexModel> indexs = new List <IndexModel>();
createTableSql.AppendFormat("CREATE TABLE \"{0}\"(", table.Name);
foreach (var column in columns)
{
createTableSql.AppendFormat("\"{0}\" {1} {2} NULL, ", column.Name, column.DataType, column.IsNullable ? "" : "NOT");
if (column.Indexs != null && column.Indexs.Count > 0)
{
indexs.AddRange(column.Indexs);
}
}
createTableSql.Remove(createTableSql.Length - 2, 2);
if (keyColumns.Count > 0)
{
createTableSql.AppendFormat(", CONSTRAINT \"PK_{0}\" PRIMARY KEY (", table.Name);
foreach (var column in keyColumns)
{
createTableSql.AppendFormat("\"{0}\", ", column.Name);
}
createTableSql.Remove(createTableSql.Length - 2, 2);
createTableSql.Append(")");
}
createTableSql.Append(")");
using (this.db.BeginTransaction())
{
var sql = createTableSql.ToString();
count = this.db.ExecuteNonQuery(sql);
foreach (var column in columns.Where(q => q.IsAutoIncrement))
{
this.AddAutoIncrement(table.Name, column);
}
if (indexs.Count > 0)
{
this.AddIndex(table.Name, indexs);
}
this.db.Commit();
}
return(count != 0);
}
/// <summary>
/// 创建数据库表
/// </summary>
/// <param name="table">表名</param>
/// <param name="columns">列信息</param>
/// <returns>是否成功</returns>
public override bool CreateTable(TableInfoModel table, List <ColumnInfoModel> columns)
{
if (string.IsNullOrEmpty(table.Name) || columns == null && columns.Count == 0)
{
return(true);
}
int count = 0;
StringBuilder createTableSql = new StringBuilder();
List <ColumnInfoModel> keyColumns = columns.Where(q => q.IsKey).ToList();
List <IndexModel> indexs = new List <IndexModel>();
createTableSql.AppendFormat("CREATE TABLE [{0}](", table.Name);
foreach (var column in columns)
{
createTableSql.AppendFormat("[{0}] {1} {2} NULL", column.Name, column.DataType, column.IsNullable ? "" : "NOT");
if (column.IsKey && keyColumns.Count == 1)
{
createTableSql.Append(" PRIMARY KEY");
if (column.IsAutoIncrement)
{
createTableSql.Append(" AUTOINCREMENT");
}
}
createTableSql.Append(",");
if (column.Indexs != null && column.Indexs.Count > 0)
{
indexs.AddRange(column.Indexs);
}
}
createTableSql.Remove(createTableSql.Length - 1, 1);
if (keyColumns.Count > 1)
{
createTableSql.AppendFormat(", CONSTRAINT PK_{0} PRIMARY KEY (", table.Name);
foreach (var column in keyColumns)
{
createTableSql.AppendFormat("[{0}],", column.Name);
}
createTableSql.Remove(createTableSql.Length - 1, 1);
createTableSql.Append(")");
}
createTableSql.Append(")");
this.db.ClearParameters();
using (var tx = this.db.BeginTransaction())
{
this.db.CommandText = createTableSql.ToString();
count = this.db.ExecuteNonQuery();
if (indexs.Count > 0)
{
this.AddIndex(table.Name, indexs);
}
tx.Commit();
}
return(count > 0);
}
private static void GerarConteudoDocumentoTabela(XmlTextWriter xmlWriter, TableInfoModel tableInfo)
{
//Planilha
xmlWriter.WriteStartElement("Worksheet");
xmlWriter.WriteAttributeString("ss:Name", "Tabelas");
//Tabela
xmlWriter.WriteStartElement("Table");
xmlWriter.WriteAttributeString("ss:DefaultRowHeight", "13");
//Cabeçalho tabelas
xmlWriter.WriteStartElement("Column");
xmlWriter.WriteAttributeString("ss:Width", "162.72");
xmlWriter.WriteEndElement();//Column
xmlWriter.WriteStartElement("Column");
xmlWriter.WriteAttributeString("ss:Width", "115.92");
xmlWriter.WriteEndElement();//Column
xmlWriter.WriteStartElement("Column");
xmlWriter.WriteAttributeString("ss:Width", "115.92");
xmlWriter.WriteEndElement();//Column
xmlWriter.WriteStartElement("Column");
xmlWriter.WriteAttributeString("ss:Width", "115.92");
xmlWriter.WriteEndElement();//Column
//Linha cabeçalho tabelas
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.colunaEntidade);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "2");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.descricao);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
//Linha detalhes tabela
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(tableInfo.nomeTabela);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "2");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(tableInfo.descricaoTabela);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
//----Linha titulos banco
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.BaseDeDados);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "2");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.Esquema);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement();//Row
//----Linha detalhes banco
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.banco);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "2");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(tableInfo.schema);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
//----Linha titulo atributos
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.Coluna);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.descricaoColuna);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.tipoDados);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.aceitaNulo);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
foreach (DataRow row in tableInfo.dtDetalheColunas.Rows)
{
//----Linha descricao atributos
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(row["COLUMN_NAME"].ToString());
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(row["COLUMN_DESCRIPTION"].ToString());
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(row["DATA_TYPE"].ToString());
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.getIsNullable(row["IS_NULLABLE"].ToString()));
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
}
//--Linha em branco
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "3");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
//----Linha chaves primarias
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.chavePrimaria);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "2");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(tableInfo.chavesPrimarias);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
//--Linha em branco
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "3");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
//----Titulo Regras negocio
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "3");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.regrasNegocio);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
//--Regras
foreach (string regra in tableInfo.dtRegras)
{
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "3");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(regra);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
}
//--Linha em branco
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "3");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
//----Linha Grant
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "3");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.grant);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
//----Tilulo Permissoes
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.usuario);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "2");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.permissao);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
foreach (DataRow row in tableInfo.dtPermissoes.Rows)
{
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(row["GRANTEE"].ToString());
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "2");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(row["PERMISSION"].ToString());
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
}
//--Linha em branco
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "3");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
//----Linha FK
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "3");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.chaveEstrangeira);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
//----Linha titulo FKs
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.Coluna);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.origemTabelaFk);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Titulo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "1");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(Global.origemColunaFk);
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
foreach (DataRow row in tableInfo.dtReferenciasFK.Rows)
{
//----Linha descricao FKs
xmlWriter.WriteStartElement("Row");
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(row["Coluna"].ToString());
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(row["TabelaOrigem"].ToString());
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteStartElement("Cell");
xmlWriter.WriteAttributeString("ss:StyleID", "Conteudo");
xmlWriter.WriteAttributeString("ss:MergeAcross", "1");
xmlWriter.WriteStartElement("Data");
xmlWriter.WriteAttributeString("ss:Type", "String");
xmlWriter.WriteRaw(row["CampoOrigem"].ToString());
xmlWriter.WriteEndElement(); //Data
xmlWriter.WriteEndElement(); //Cell
xmlWriter.WriteEndElement(); //Row
}
xmlWriter.WriteEndElement(); //Table
}
/// <summary>
/// 创建数据库表
/// </summary>
/// <param name="table">表名</param>
/// <param name="columns">列信息</param>
/// <returns>是否成功</returns>
public override bool CreateTable(TableInfoModel table, List <ColumnInfoModel> columns)
{
if (table == null || string.IsNullOrEmpty(table.Name))
{
throw new ArgumentNullException("table");
}
if (columns == null)
{
throw new ArgumentNullException("columns");
}
if (columns.Count == 0)
{
return(false);
}
int count = 0;
StringBuilder createTableSql = new StringBuilder();
List <ColumnInfoModel> keyColumns = columns.Where(q => q.IsKey).ToList();
List <IndexModel> indexs = new List <IndexModel>();
createTableSql.AppendFormat("CREATE TABLE `{0}`(", table.Name);
foreach (var column in columns)
{
createTableSql.AppendFormat("`{0}` {1} {2} NULL", column.Name, column.DataType, column.IsNullable ? "" : "NOT");
if (column.IsAutoIncrement)
{
createTableSql.Append(" AUTO_INCREMENT");
}
if (!string.IsNullOrEmpty(column.Comment))
{
createTableSql.AppendFormat(" COMMENT '{0}'", column.Comment.Replace("'", ""));
}
createTableSql.Append(",");
if (column.Indexs != null && column.Indexs.Count > 0)
{
indexs.AddRange(column.Indexs);
}
}
createTableSql.Remove(createTableSql.Length - 1, 1);
if (keyColumns.Count > 0)
{
createTableSql.Append(", PRIMARY KEY (");
foreach (var column in keyColumns)
{
createTableSql.AppendFormat("`{0}`,", column.Name);
}
createTableSql.Remove(createTableSql.Length - 1, 1);
createTableSql.Append(")");
}
createTableSql.Append(") ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci");
if (!string.IsNullOrEmpty(table.Comment))
{
createTableSql.AppendFormat(" COMMENT '{0}'", table.Comment.Replace("'", ""));
}
createTableSql.Append(";");
this.db.ClearParameters();
using (var tx = this.db.BeginTransaction())
{
this.db.CommandText = createTableSql.ToString();
count = this.db.ExecuteNonQuery();
if (indexs.Count > 0)
{
this.AddIndex(table.Name, indexs);
}
tx.Commit();
}
return(count > 0);
}
public static bool ParseCreateTableCommnad(string sql, out TableInfoModel tableInfoData)
{
tableInfoData = new TableInfoModel()
{
Columns = new Dictionary <string, ParseSqlData>(),
};
BaseSqlReader reader = new MySqlReader(sql);
while (reader.NextLine(out string line))
{
if (line.ToUpper().StartsWith("CREATE TABLE"))
{
var openIndex = line.IndexOf("(", 0);
var closeIndex = line.LastIndexOf(')');
if (openIndex == -1)
{
throw new Exception($"CreateTable Parse Error: {sql}");
}
var tableNameIndex = line.ToUpper().IndexOf("CREATE TABLE") + "CREATE TABLE".Length;
var tableName = line.Substring(tableNameIndex, openIndex - tableNameIndex).Replace("`", "").Trim();
var body = line.Substring(openIndex + 1, closeIndex - openIndex - 1).Trim();
tableInfoData.TableName = tableName;
var findIndex = 0;
while (findIndex <= body.Length)
{
int beforeIndex = findIndex;
findIndex = body.IndexOf(",", findIndex);
if (findIndex == -1)
{
findIndex = body.Length;
}
line = body.Substring(beforeIndex, findIndex - beforeIndex).Trim();
bool isReservedKeyword = false;
var queue = new Queue <string>();
foreach (var key in Consts.MySqlReservedKeyword.Keys)
{
isReservedKeyword = line.ToUpper().StartsWith(key);
if (isReservedKeyword == false)
{
continue;
}
queue.Enqueue(key);
break;
}
if (isReservedKeyword == false)//기본 컬럼
{
var datas = line.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries).ToList();
var option = "";
for (int ii = 2; ii < datas.Count; ii++)
{
if (datas[ii].StartsWith("(") && datas[ii].EndsWith(")"))
{
datas[1] += datas[ii];
}
else
{
option += $"{datas[ii]} ";
}
}
if (Consts.BaseMySqlDataType.ContainsKey(datas[1]))
{
datas[1] = Consts.BaseMySqlDataType[datas[1]];
}
var column = new ParseSqlData()
{
ColumnName = datas[0].Replace("`", ""),
ColumnType = datas[1],
ColumnOptions = option,
TableName = tableName
};
tableInfoData.Columns.Add(column.ColumnName, column);
}
else
{
int maxIndex = 0;
int minIndex = beforeIndex;
while (queue.Count > 0)
{
var item = queue.Dequeue();
var index = body.IndexOf(item, minIndex);
if (index == -1)
{
maxIndex = body.Length;
}
if (maxIndex <= index)
{
maxIndex = index + item.Length;
}
findIndex = maxIndex;
//InputManager.Instance.Write(body.Substring(beforeIndex, findIndex - beforeIndex).Trim());
minIndex = findIndex;
if (!Consts.MySqlReservedKeyword.ContainsKey(item))
{
continue;
}
foreach (var key in Consts.MySqlReservedKeyword[item])
{
queue.Enqueue(key);
}
}
}
findIndex++;
}
}
}
return(true);
}
public IEnumerable <TableInfoWithColumnsModel> GetTableInfos(DatabaseSettingModel settingModel)
{
var resultTable = new DataTable();
SqlConnection connection = null;
try
{
connection = new SqlConnection(
settingModel.HasConnectionString ? settingModel.ConnectionString :
$"Data Source={settingModel.Host},{settingModel.Port};Initial Catalog={settingModel.DatabaseName};Persist Security Info=True;User ID={settingModel.UserName};Password={settingModel.Password}");
connection.Open();
#region SqlCommandString
var sqlCommandString = @"
SELECT a.Table_schema +'.'+a.Table_name as [Table]
,b.COLUMN_NAME as [Column]
,b.DATA_TYPE as [DataType]
,isnull(b.CHARACTER_MAXIMUM_LENGTH,'') as [Length]
,isnull(b.COLUMN_DEFAULT,'') as [DefaultValue]
,b.IS_NULLABLE as [IsNull]
,( SELECT value
FROM fn_listextendedproperty (NULL, 'schema', a.Table_schema, 'table', a.TABLE_NAME, 'column', default)
WHERE name='MS_Description' and objtype='COLUMN'
and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME
) as [Description]
FROM INFORMATION_SCHEMA.TABLES a
LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_NAME = b.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY a.TABLE_NAME , b.ORDINAL_POSITION
";
#endregion
var command = connection.CreateCommand();
command.CommandTimeout = 120;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sqlCommandString;
var adapter = new SqlDataAdapter(command);
adapter.Fill(resultTable);
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
var result = new List <TableInfoWithColumnsModel>();
TableInfoModel tableModel = null;
var columnInfos = new List <ColumnInfoModel>();
foreach (DataRow row in resultTable.Rows)
{
var table = Convert.ToString(row["Table"]);
var column = Convert.ToString(row["Column"]);
var dataType = Convert.ToString(row["DataType"]);
var lengthX = Convert.ToInt32(row["Length"]);
var length = lengthX == -1 ? "(MAX)" : lengthX == 0 ? "" : $"({lengthX})";
var defaultValue = Convert.ToString(row["DefaultValue"]);
var isNull = Convert.ToString(row["IsNull"]) != "NO";
var description = Convert.ToString(row["Description"]);
if ("dbo.sysdiagrams" == table)
{
continue;
}
if (tableModel == null || tableModel.Name != table)
{
if (tableModel != null)
{
result.Add(new TableInfoWithColumnsModel(tableModel, columnInfos));
}
tableModel = new TableInfoModel {
Name = table
};
columnInfos = new List <ColumnInfoModel>();
}
columnInfos.Add(new ColumnInfoModel
{
Name = column,
Type = $"{dataType}{length}",
DefaultValue = defaultValue,
IsCanNull = isNull,
Comment = description,
});
}
result.Add(new TableInfoWithColumnsModel(tableModel, columnInfos));
return(result);
}
/// <summary>
/// 创建数据库表
/// </summary>
/// <param name="table">表名</param>
/// <param name="columns">列信息</param>
/// <returns>是否成功</returns>
public override bool CreateTable(TableInfoModel table, List <ColumnInfoModel> columns)
{
if (table == null || string.IsNullOrEmpty(table.Name))
{
throw new ArgumentNullException("table");
}
if (columns == null)
{
throw new ArgumentNullException("columns");
}
if (columns.Count == 0)
{
return(false);
}
int count = 0;
StringBuilder createTableSql = new StringBuilder();
StringBuilder createKeySql = new StringBuilder();
List <ColumnInfoModel> keyColumns = columns.Where(q => q.IsKey).ToList();
List <IndexModel> indexs = new List <IndexModel>();
createTableSql.AppendFormat("CREATE TABLE [{0}](", table.Name);
foreach (var column in columns)
{
createTableSql.AppendFormat("[{0}] {1} {2} NULL", column.Name, column.DataType, column.IsNullable ? "" : "NOT");
if (column.IsAutoIncrement)
{
createTableSql.Append(" IDENTITY (1, 1)");
}
createTableSql.Append(",");
if (column.Indexs != null && column.Indexs.Count > 0)
{
indexs.AddRange(column.Indexs);
}
}
createTableSql.Remove(createTableSql.Length - 1, 1);
createTableSql.Append(")");
if (keyColumns.Count > 0)
{
createTableSql.Append(" ON [PRIMARY]");
string s = keyColumns.Count(q => q.IsNonClustered == true) > 0 ? "NONCLUSTERED" : "CLUSTERED";
createKeySql.AppendFormat("ALTER TABLE [{0}] ADD CONSTRAINT PK_{0} PRIMARY KEY {1}(", table.Name, s);
foreach (var column in keyColumns)
{
createKeySql.AppendFormat("[{0}],", column.Name);
}
createKeySql.Remove(createKeySql.Length - 1, 1);
createKeySql.Append(") WITH(STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]");
}
using (this.db.BeginTransaction())
{
count = this.db.ExecuteNonQuery(createTableSql.ToString());
createTableSql.Clear();
if (createKeySql.Length > 0)
{
this.db.ExecuteNonQuery(createKeySql.ToString());
}
if (!string.IsNullOrEmpty(table.Comment))
{
this.db.ExecuteNonQuery("EXEC sys.sp_addextendedproperty", new {
name = "MS_Description",
value = table.Comment,
level0type = "SCHEMA",
level0name = "dbo",
level1type = "TABLE",
level1name = table.Name
}, CommandType.StoredProcedure);
}
foreach (var column in columns)
{
if (!string.IsNullOrEmpty(column.Comment))
{
this.db.ExecuteNonQuery("EXEC sys.sp_addextendedproperty", new
{
name = "MS_Description",
value = column.Comment,
level0type = "SCHEMA",
level0name = "dbo",
level1type = "TABLE",
level1name = table.Name,
level2type = "COLUMN",
level2name = column.Comment
}, CommandType.StoredProcedure);
}
}
if (indexs.Count > 0)
{
this.AddIndex(table.Name, indexs);
}
db.Commit();
}
return(count > 0);
}
public IEnumerable <TableInfoWithColumnsModel> GetTableInfos(DatabaseSettingModel settingModel)
{
var resultTable = new DataTable();
MySqlConnection connection = null;
try
{
connection = new MySqlConnection(
settingModel.HasConnectionString ? settingModel.ConnectionString :
$"server={settingModel.Host};port={settingModel.Port};database={settingModel.DatabaseName};user id={settingModel.UserName};password={settingModel.Password};charset=utf8;");
connection.Open();
#region SqlCommandString
var sqlCommandString = @"
SELECT
TABLE_NAME AS ""Table"",
COLUMN_NAME AS ""Column"",
DATA_TYPE AS ""DataType"",
CHARACTER_MAXIMUM_LENGTH AS ""Length"",
COLUMN_DEFAULT AS ""DefaultValue"",
IS_NULLABLE AS ""IsNull"",
COLUMN_COMMENT AS ""Description""
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA like @dbname
ORDER BY TABLE_NAME , ORDINAL_POSITION
";
#endregion
var command = connection.CreateCommand();
command.CommandTimeout = 120;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sqlCommandString;
command.Parameters.AddWithValue("@dbname", settingModel.DatabaseName);
var adapter = new MySqlDataAdapter(command);
adapter.Fill(resultTable);
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
var result = new List <TableInfoWithColumnsModel>();
TableInfoModel tableModel = null;
var columnInfos = new List <ColumnInfoModel>();
foreach (DataRow row in resultTable.Rows)
{
var table = Convert.ToString(row["Table"]);
var column = Convert.ToString(row["Column"]);
var dataType = Convert.ToString(row["DataType"]);
var lengthX = Convert.ToString(row["Length"]);
var length = string.IsNullOrWhiteSpace(lengthX) ? string.Empty : $"({lengthX})";
var defaultValue = Convert.ToString(row["DefaultValue"]);
var isNull = Convert.ToString(row["IsNull"]) != "NO";
var description = Convert.ToString(row["Description"]);
if (tableModel == null || tableModel.Name != table)
{
if (tableModel != null)
{
result.Add(new TableInfoWithColumnsModel(tableModel, columnInfos));
}
tableModel = new TableInfoModel {
Name = table
};
columnInfos = new List <ColumnInfoModel>();
}
columnInfos.Add(new ColumnInfoModel
{
Name = column,
Type = $"{dataType}{length}",
DefaultValue = defaultValue,
IsCanNull = isNull,
Comment = description,
});
}
result.Add(new TableInfoWithColumnsModel(tableModel, columnInfos));
return(result);
}
