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