/// <summary>
/// 根据表信息生成创建SQL表信息
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnGenerateSQL_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtTableName.Text) || string.IsNullOrEmpty(txtTableDesc.Text))
{
return;
}
if (dataGridView.Rows.Count > 1)
{
var tableInfo = new TableEntity()
{
TableName = txtTableName.Text.Trim(),
TableDescription = txtTableDesc.Text.Trim(),
Columns = new List <ColumnEntity>()
};
ColumnEntity column;
for (var k = 0; k < dataGridView.Rows.Count - 1; k++)
{
column = new ColumnEntity
{
ColumnName = dataGridView.Rows[k].Cells[0].Value.ToString(),
ColumnDescription = dataGridView.Rows[k].Cells[1].Value.ToString(),
IsPrimaryKey = dataGridView.Rows[k].Cells[2].Value != null && (bool)dataGridView.Rows[k].Cells[2].Value,
IsNullable = dataGridView.Rows[k].Cells[3].Value != null && (bool)dataGridView.Rows[k].Cells[3].Value,
DataType = dataGridView.Rows[k].Cells[4].Value.ToString(),
Size = dataGridView.Rows[k].Cells[5].Value == null?Utility.GetDefaultSizeForDbType(dataGridView.Rows[k].Cells[4].Value.ToString()) : Convert.ToUInt32(dataGridView.Rows[k].Cells[5].Value.ToString()),
DefaultValue = dataGridView.Rows[k].Cells[6].Value
};
//
tableInfo.Columns.Add(column);
}
//sql
var sql = tableInfo.GenerateSqlStatement(cbGenDbDescription.Checked, ConfigurationHelper.AppSetting(ConfigurationConstants.DbType));
txtGeneratedSqlText.Text = sql;
Clipboard.SetText(sql);
MessageBox.Show("生成成功,sql语句已赋值至粘贴板");
}
}
/// <summary>
/// 从 源代码 中获取表信息
/// </summary>
/// <param name="sourceFilePaths">sourceCodeFiles</param>
/// <returns></returns>
public static List <TableEntity> GeTableEntityFromSourceCode(params string[] sourceFilePaths)
{
if (sourceFilePaths == null || sourceFilePaths.Length <= 0)
{
return(null);
}
//
//Set hardcoded environment variable to set the path to the library
Environment.SetEnvironmentVariable("ROSLYN_COMPILER_LOCATION", System.IO.Directory.GetCurrentDirectory() + "\\roslyn", EnvironmentVariableTarget.Process);
var provider = new Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider();
//Clean up
Environment.SetEnvironmentVariable("ROSLYN_COMPILER_LOCATION", null, EnvironmentVariableTarget.Process);
var result = provider.CompileAssemblyFromFile(new CompilerParameters(new[] { "System.dll" }), sourceFilePaths);
if (result.Errors.HasErrors)
{
var error = new StringBuilder(result.Errors.Count * 1024);
for (var i = 0; i < result.Errors.Count; i++)
{
error.AppendLine($"{result.Errors[i].FileName}({result.Errors[i].Line},{result.Errors[i].Column}):{result.Errors[i].ErrorText}");
}
throw new ArgumentException($"所选文件编译有错误{Environment.NewLine}{error}");
}
var tables = new List <TableEntity>(2);
foreach (var type in result.CompiledAssembly.GetTypes())
{
if (type.IsClass && type.IsPublic && !type.IsAbstract)
{
var table = new TableEntity
{
TableName = type.Name.TrimModelName(),
TableDescription = type.GetCustomAttribute <DescriptionAttribute>()?.Description
};
var defaultVal = Activator.CreateInstance(type);
foreach (var property in type.GetProperties(BindingFlags.GetField | BindingFlags.Public | BindingFlags.Instance))
{
var columnInfo = new ColumnEntity
{
ColumnName = property.Name,
ColumnDescription = property.GetCustomAttribute <DescriptionAttribute>()?.Description,
IsNullable = property.PropertyType.Unwrap() != property.PropertyType
};
var val = property.GetValue(defaultVal);
columnInfo.DefaultValue =
null == val || property.PropertyType.GetDefaultValue().Equals(val) || columnInfo.IsNullable
? null : val;
columnInfo.IsPrimaryKey = columnInfo.ColumnDescription?.Contains("主键") ?? false;
columnInfo.DataType = FclType2DbType(property.PropertyType).ToString();
if (!ConfigurationHelper.AppSetting(ConfigurationConstants.DbType).EqualsIgnoreCase("SqlServer") && columnInfo.DataType.Equals("NVARCHAR"))
{
columnInfo.DataType = "VARCHAR";
}
columnInfo.Size = GetDefaultSizeForDbType(columnInfo.DataType);
table.Columns.Add(columnInfo);
}
tables.Add(table);
}
}
return(tables);
}
/// <summary>
/// 导入Excel生成创建数据库表sql与创建数据库表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnImportExcel_Click(object sender, EventArgs e)
{
var ofg = new OpenFileDialog
{
Multiselect = false,
CheckFileExists = true,
Filter = "Excel文件(*.xlsx)|*.xlsx|Excel97-2003(*.xls)|*.xls"
};
if (ofg.ShowDialog() == DialogResult.OK)
{
var path = ofg.FileName;
var isNewFileVersion = path.EndsWith(".xlsx");
var table = new TableEntity();
Stream stream = null;
try
{
stream = File.OpenRead(path);
IWorkbook workbook;
if (isNewFileVersion)
{
workbook = new XSSFWorkbook(stream);
}
else
{
workbook = new HSSFWorkbook(stream);
}
dataGridView.Rows.Clear();
var tableCount = workbook.NumberOfSheets;
if (tableCount == 1)
{
var sheet = workbook.GetSheetAt(0);
table.TableName = sheet.SheetName.Trim();
var rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
var row = (IRow)rows.Current;
if (null == row)
{
continue;
}
if (row.RowNum == 0)
{
table.TableDescription = row.Cells[0].StringCellValue;
txtTableName.Text = table.TableName;
txtTableDesc.Text = table.TableDescription;
continue;
}
if (row.RowNum > 1)
{
var column = new ColumnEntity
{
ColumnName = row.Cells[0].StringCellValue.Trim()
};
if (string.IsNullOrWhiteSpace(column.ColumnName))
{
continue;
}
column.ColumnDescription = row.Cells[1].StringCellValue;
column.IsPrimaryKey = row.Cells[2].StringCellValue.Equals("Y");
column.IsNullable = row.Cells[3].StringCellValue.Equals("Y");
column.DataType = row.Cells[4].StringCellValue.ToUpper();
if (string.IsNullOrEmpty(row.Cells[5].ToString()))
{
column.Size = Utility.GetDefaultSizeForDbType(column.DataType);
}
else
{
column.Size = row.Cells[5].GetCellValue <uint>();
}
if (row.Cells.Count > 6)
{
column.DefaultValue = row.Cells[6].ToString();
}
table.Columns.Add(column);
var rowView = new DataGridViewRow();
rowView.CreateCells(
dataGridView,
column.ColumnName,
column.ColumnDescription,
column.IsPrimaryKey,
column.IsNullable,
column.DataType,
column.Size,
column.DefaultValue
);
dataGridView.Rows.Add(rowView);
}
}
//sql
var sql = table.GenerateSqlStatement(cbGenDbDescription.Checked, ConfigurationHelper.AppSetting(ConfigurationConstants.DbType));
txtGeneratedSqlText.Text = sql;
Clipboard.SetText(sql);
MessageBox.Show("生成成功，sql语句已赋值至粘贴板");
}
else
{
var sbSqlText = new StringBuilder();
for (var i = 0; i < tableCount; i++)
{
table = new TableEntity();
var sheet = workbook.GetSheetAt(i);
table.TableName = sheet.SheetName;
sbSqlText.AppendLine();
var rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
var row = (IRow)rows.Current;
if (null == row)
{
continue;
}
if (row.RowNum == 0)
{
table.TableDescription = row.Cells[0].StringCellValue;
continue;
}
if (row.RowNum > 1)
{
var column = new ColumnEntity
{
ColumnName = row.Cells[0].StringCellValue
};
if (string.IsNullOrWhiteSpace(column.ColumnName))
{
continue;
}
column.ColumnDescription = row.Cells[1].StringCellValue;
column.IsPrimaryKey = row.Cells[2].StringCellValue.Equals("Y");
column.IsNullable = row.Cells[3].StringCellValue.Equals("Y");
column.DataType = row.Cells[4].StringCellValue;
column.Size = string.IsNullOrEmpty(row.Cells[5].ToString()) ? Utility.GetDefaultSizeForDbType(column.DataType) : Convert.ToUInt32(row.Cells[5].ToString());
if (row.Cells.Count > 6 && !string.IsNullOrWhiteSpace(row.Cells[6].ToString()))
{
column.DefaultValue = row.Cells[6].ToString();
}
table.Columns.Add(column);
}
}
sbSqlText.AppendLine(table.GenerateSqlStatement(cbGenDbDescription.Checked, ConfigurationHelper.AppSetting(ConfigurationConstants.DbType)));
}
var dialog = new FolderBrowserDialog
{
Description = "请选择要保存sql文件的文件夹",
ShowNewFolderButton = true
};
if (dialog.ShowDialog() == DialogResult.OK)
{
var dir = dialog.SelectedPath;
//获取文件名
var fileName = Path.GetFileNameWithoutExtension(path);
//
var sqlFilePath = dir + "\\" + fileName + ".sql";
File.WriteAllText(sqlFilePath, sbSqlText.ToString(), Encoding.UTF8);
MessageBox.Show("保存成功");
System.Diagnostics.Process.Start("Explorer.exe", dir);
}
else
{
Clipboard.SetText(sbSqlText.ToString());
MessageBox.Show("取消保存文件，sql语句已赋值至粘贴板");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
stream.Dispose();
}
}
}
