private void btnYes_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow row in dgFields.SelectedRows)
{
SelectedField info = row.DataBoundItem as SelectedField;
selectedFields.Add(info);
}
if (selectedFields.Count > 0)
{
this.DialogResult = DialogResult.Yes;
this.Close();
}
}
private void dgSelectedField_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
{
foreach (DataGridViewRow row in dgSelectedField.Rows)
{
SelectedField t = row.DataBoundItem as SelectedField;
if (t.keyFieldFlag)
{
foreach (DataGridViewCell cell in row.Cells)
{
cell.Style.BackColor = Color.ForestGreen;
cell.Style.ForeColor = Color.White;
}
}
else
{
foreach (DataGridViewCell cell in row.Cells)
{
cell.Style.BackColor = Color.White;
cell.Style.ForeColor = Color.Black;
}
}
}
}
private void btnDelField_Click(object sender, EventArgs e)
{
if (dgTables.SelectedRows.Count == 0)
{
return;
}
if (MessageBox.Show(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n您确认要删除所选的内容吗?"
, "提示"
, MessageBoxButtons.YesNoCancel
, MessageBoxIcon.Question
, MessageBoxDefaultButton.Button1) != DialogResult.Yes)
{
return;
}
foreach (DataGridViewRow row in dgSelectedField.SelectedRows)
{
SelectedField t = row.DataBoundItem as SelectedField;
for (int i = 0; i < selectedFields.Count; i++)
{
if (selectedFields[i].tableName != t.tableName)
{
continue;
}
if (selectedFields[i].fieldName != t.fieldName)
{
continue;
}
selectedFields.Remove(selectedFields[i]);
break;
}
}
dgSelectedField.DataSource = null;
dgSelectedField.DataSource = selectedFields;
dgSelectedField.Refresh();
dgSelectedField.ClearSelection();
}
private void DialogFields_Load(object sender, EventArgs e)
{
lblTableName.Text = tableName;
dgFields.MultiSelect = !keyFlag;
string getFieldsMethod = AppSettings.GetConfigString("GetFieldsMethod");
string sql = "SELECT c.column_id AS DB_ID"
+ ",c.name AS FIELD_NAME"
+ ",TYPE_NAME(c.user_type_id) AS DATA_TYPE"
+ ",c.max_length AS LENGTH"
+ ",COLUMNPROPERTY( c.OBJECT_ID , c.Name ,'PRECISION') AS PRECISION"
+ ",p.Value AS [TITLE_CN]"
+ ",c.name AS [TITLE_EN] "
+ "FROM sys.columns c "
+ "LEFT JOIN sys.table_types t ON t.type_table_object_id = c.object_ID "
+ "LEFT JOIN sys.extended_properties p ON (p.major_id = c.OBJECT_ID OR p.major_id = t.user_type_id) AND p.minor_id = c.column_id AND p.name = 'MS_Description' "
+ "WHERE COALESCE( t.name, OBJECT_NAME(c.OBJECT_ID)) =@TABLE_NAME "
///+ "AND c.name not in ('ID','NID','FID','PARENT_FID','VALID_FLAG','CREATE_USER','CREATE_DATE','MODIFY_USER','MODIFY_DATE') "
+ "ORDER BY c.column_id;";
if (getFieldsMethod.ToLower() == "kt")
{
sql = @"select
DB_ID = a.colorder,
FIELD_NAME = a.name,
DATA_TYPE = k.name,
--Tab_index = case when exists(SELECT name FROM sysindexes WHERE id = (select id from sysobjects where name=d.name) and indid<>'0' and name=a.name) then '1' else '0' end,
LENGTH = COL_LENGTH(d.name,a.name),
PRECISION = COLUMNPROPERTY( OBJECT_ID(d.name),a.name,'PRECISION'),
--Col_scale = isnull(COLUMNPROPERTY( OBJECT_ID(d.name),a.name,'Scale'),0),
TITLE_CN = left(dbo.f_get_des_sql(d.name,a.name,'MS_Description'),case when CHARINDEX(',',dbo.f_get_des_sql(d.name,a.name,'MS_Description'))>0 then CHARINDEX(',',dbo.f_get_des_sql(d.name,a.name,'MS_Description'))-1 else 0 end ),
--Note = dbo.f_get_des_sql(d.name,a.name,'MS_Description'),
TITLE_EN = left(dbo.f_get_des_sql(d.name,a.name,'US_Description'),case when CHARINDEX(',',dbo.f_get_des_sql(d.name,a.name,'US_Description'))>0 then CHARINDEX(',',dbo.f_get_des_sql(d.name,a.name,'US_Description'))-1 else 0 end )
--,US_Des = dbo.f_get_des_sql(d.name,a.name,'US_Description'),
--Empty = COLUMNPROPERTY( OBJECT_ID(d.name),a.name,'AllowsNull'),
---Col_Deflt = replace(replace(convert(varchar(200),isnull(e.text,'')),'CREATE DEFAULT con_empty AS ',''),'CREATE DEFAULT con_zero AS ',''),
---Endspace = case when A.status & 16 = 0 then '0' else '1' end,
---tab_Key = case when a.name='nid'and exists(select id from dbo.sysobjects where id = object_id('PK_'+d.name)) then '1' else '0' end
FROM syscolumns a
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' --U = 用户表 排出dtproperties
left join syscomments e on a.cdefault=e.id
inner join systypes k on k.xtype = a.xtype
WHERE d.name =@TABLE_NAME
---AND a.name not in ('ID','NID','FID','PARENT_FID','VALID_FLAG','CREATE_USER','CREATE_DATE','MODIFY_USER','MODIFY_DATE')
order by DB_ID";
}
Database db = DatabaseFactory.CreateDatabase();
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@TABLE_NAME", DbType.AnsiString, tableName);
DataSet ds = db.ExecuteDataSet(cmd);
DataTable dtFields = ds.Tables[0];
foreach (DataRow dr in dtFields.Rows)
{
SelectedField info = new SelectedField();
info.fieldName = dr["FIELD_NAME"].ToString();
info.dataType = dr["DATA_TYPE"].ToString();
info.maxLength = int.Parse(dr["LENGTH"].ToString());
info.precision = int.Parse(dr["PRECISION"].ToString());
info.descCn = dr["TITLE_CN"].ToString();
info.descEn = dr["TITLE_EN"].ToString();
info.keyFieldFlag = keyFlag;
info.tableName = tableName;
loadedFields.Add(info);
}
dgFields.DataSource = null;
dgFields.DataSource = loadedFields;
dgFields.Refresh();
dgFields.ClearSelection();
}
private void btnCreateEntity_Click(object sender, EventArgs e)
{
string spaceName = cbNameSpace.Text.ToUpper();
string entityName = txtEntityName.Text.Trim();
if (string.IsNullOrEmpty(entityName))
{
DialogTextbox dialog = new DialogTextbox("请输入数据模型名称");
if (dialog.ShowDialog(this) == DialogResult.Yes)
{
entityName = dialog.content;
txtEntityName.Text = entityName;
}
dialog.Dispose();
}
if (string.IsNullOrEmpty(entityName))
{
return;
}
EntityInfo entityinfo = new EntityBLL().GetInfo(entityName, GetTableNameByEntityName(entityName));
if (entityinfo != null)
{
ErrorMessage(entityName + "已存在");
return;
}
entityinfo = new EntityInfo();
entityinfo.Fid = Guid.NewGuid();
entityinfo.EntityName = entityName;
entityinfo.TableNames = GetTableNameByEntityName(entityName);
SelectedField keyField = selectedFields.FirstOrDefault(d => d.keyFieldFlag);
if (keyField == null)
{
return;
}
entityinfo.DefaultSort = "dbo.[" + keyField.tableName + "].[" + keyField.fieldName + "]-desc";///KT默认为NID倒排序
entityinfo.Comments = string.Empty;
entityinfo.ValidFlag = true;
entityinfo.CreateUser = string.Empty;
entityinfo.CreateDate = DateTime.Now;
entityinfo.EntityType = 1;
List <EntityFieldInfo> fieldlist = new List <EntityFieldInfo>();
int cnt = 1;
foreach (var field in selectedFields.Where(d => !d.keyFieldFlag))
{
EntityFieldInfo fieldinfo = new EntityFieldInfo();
fieldinfo.Fid = Guid.NewGuid();
fieldinfo.EntityFid = entityinfo.Fid;
fieldinfo.FieldName = GetEntityNameByTableName(field.tableName) + "_" + field.fieldName; ///对象的属性
fieldinfo.TableFieldName = field.fieldName; ///数据库字段
fieldinfo.DisplayNameCn = field.descCn;
fieldinfo.DisplayNameEn = field.descEn;
fieldinfo.DisplayOrder = cnt * 10;
fieldinfo.DataType = 10; ///string
fieldinfo.ControlType = 10; ///textbox
fieldinfo.DataLength = field.maxLength;
fieldinfo.Nullenable = true;
fieldinfo.Editable = true;
fieldinfo.EditDisplayWidth = "200";
fieldinfo.Listable = true;
fieldinfo.ListDisplayWidth = "100";
fieldinfo.Regex = string.Empty;
fieldinfo.ValidFlag = true;
fieldinfo.CreateDate = DateTime.Now;
fieldinfo.CreateUser = string.Empty;
fieldlist.Add(fieldinfo);
cnt++;
}
using (var trans = new TransactionScope())
{
if (new EntityBLL().InsertInfo(entityinfo) == 0)
{
ErrorMessage(entityName + "已经生成实体类失败");
return;
}
foreach (var fieldinfo in fieldlist)
{
if (new EntityFieldBLL().InsertInfo(fieldinfo) == 0)
{
ErrorMessage("插入字段数据失败");
return;
}
}
trans.Complete();
}
ShowMessage("数据模型添加成功");
btnClear_Click(null, null);
}
private void btnCreateDal_Click(object sender, EventArgs e)
{
string spaceName = cbNameSpace.Text.ToUpper();
string entityName = txtEntityName.Text.Trim();
if (string.IsNullOrEmpty(entityName))
{
DialogTextbox dialog = new DialogTextbox("请输入数据模型名称");
if (dialog.ShowDialog(this) == DialogResult.Yes)
{
entityName = dialog.content;
txtEntityName.Text = entityName;
}
dialog.Dispose();
}
if (string.IsNullOrEmpty(entityName))
{
return;
}
string joinStr = string.Empty;
DialogMultextbox mdialog = new DialogMultextbox("请输入表关系语句");
if (mdialog.ShowDialog(this) == DialogResult.Yes)
{
joinStr = mdialog.content;
}
mdialog.Dispose();
if (MessageBox.Show(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n您确认要创建" + entityName + "的DAL类文件吗?"
, "提示"
, MessageBoxButtons.YesNoCancel
, MessageBoxIcon.Question
, MessageBoxDefaultButton.Button1) != DialogResult.Yes)
{
return;
}
StringBuilder sb = new StringBuilder();
///using
sb.AppendLine("using DM." + spaceName + ";");
sb.AppendLine("using Infrustructure.Utilities;");
sb.AppendLine("using Microsoft.Practices.EnterpriseLibrary.Data;");
sb.AppendLine("using System;");
sb.AppendLine("using System.Collections.Generic;");
sb.AppendLine("using System.Data;");
sb.AppendLine("using System.Data.Common;");
sb.AppendLine("using System.Linq;");
sb.AppendLine("using System.Text;");
sb.AppendLine("");
///namespace
sb.AppendLine("namespace DAL." + spaceName + "");
sb.AppendLine("{");
sb.AppendLine(" public class " + entityName + "DAL");
sb.AppendLine(" {");
///GetListByPage
sb.AppendLine(" public List<" + entityName + "Info> GetListByPage(string textWhere, string textOrder, int pageIndex, int pageRow)");
sb.AppendLine(" {");
sb.AppendLine(" if (pageIndex <= 0) pageIndex = 1;");
sb.AppendLine(" if (pageRow <= 0) pageRow = 10;");
sb.AppendLine(" string whereText = string.Empty;");
sb.AppendLine(" if (!string.IsNullOrEmpty(textWhere))");
sb.AppendLine(" {");
sb.AppendLine(" if (textWhere.Trim().StartsWith(\"and\", StringComparison.OrdinalIgnoreCase))");
sb.AppendLine(" whereText += \" where 1 = 1 \" + textWhere;");
sb.AppendLine(" else");
sb.AppendLine(" whereText += \" where \" + textWhere;");
sb.AppendLine(" }");
sb.AppendLine(" else");
sb.AppendLine(" whereText += \" where 1 = 1 \";");
sb.AppendLine(" if (string.IsNullOrEmpty(textOrder))");
SelectedField keyField = selectedFields.FirstOrDefault(d => d.keyFieldFlag);
if (keyField == null)
{
return;
}
sb.AppendLine(" textOrder += \"dbo.[" + keyField.tableName + "].[" + keyField.entityFieldName + "] desc\";");
sb.AppendLine(" string sql = \"select top \" + pageRow + \" * from \"");
sb.AppendLine(" + \"(select row_number() over(order by \" + textOrder + \") as rownumber\"");
SelectedTable mainTable = selectedTables.FirstOrDefault(d => d.mainTableFlag);
if (mainTable == null)
{
return;
}
sb.AppendLine(" + \", \" + FieldSql + \" from dbo.[" + mainTable.tableName + "] with(nolock)\" + LeftJoinSql + \" \" + whereText + \") T \"");
sb.AppendLine(" + \"where rownumber > \" + (pageIndex - 1) * pageRow + \" \";");
sb.AppendLine(" Database db = DatabaseFactory.CreateDatabase();");
sb.AppendLine(" DbCommand cmd = db.GetSqlStringCommand(sql);");
sb.AppendLine(" List<" + entityName + "Info> list = new List<" + entityName + "Info>();");
sb.AppendLine(" using (IDataReader dr = db.ExecuteReader(cmd))");
sb.AppendLine(" {");
sb.AppendLine(" while (dr.Read())");
sb.AppendLine(" {");
sb.AppendLine(" " + entityName + "Info info = new " + entityName + "Info();");
foreach (var table in selectedTables)
{
sb.AppendLine(" Update" + GetEntityNameByTableName(table.tableName) + "Fields(info, dr);");
}
sb.AppendLine(" list.Add(info);");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine(" return list;");
sb.AppendLine(" }");
///GetCounts
sb.AppendLine(" public int GetCounts(string textWhere)");
sb.AppendLine(" {");
sb.AppendLine(" if (string.IsNullOrEmpty(textWhere))");
sb.AppendLine(" textWhere = string.Empty;");
sb.AppendLine(" else");
sb.AppendLine(" {");
sb.AppendLine(" if (!textWhere.Trim().StartsWith(\"and\", StringComparison.OrdinalIgnoreCase))");
sb.AppendLine(" textWhere = \" and \" + textWhere;");
sb.AppendLine(" }");
sb.AppendLine(" string sql = \"select count(1) from dbo.[" + mainTable.tableName + "] with(nolock)\" + LeftJoinSql + \" where 1 = 1 {0}; \";");
sb.AppendLine(" Database db = DatabaseFactory.CreateDatabase();");
sb.AppendLine(" DbCommand cmd = db.GetSqlStringCommand(string.Format(sql, textWhere));");
sb.AppendLine(" return Convert.ToInt32(db.ExecuteScalar(cmd));");
sb.AppendLine(" }");
///GetInfo
string keyFieldType = "string";
string dbType = "String";
switch (keyField.dataType)
{
case "int": keyFieldType = "int"; dbType = "Int32"; break;
case "numeric": keyFieldType = "decimal"; dbType = "Decimal"; break;
}
sb.AppendLine(" public " + entityName + "Info GetInfo(" + keyFieldType + " " + keyField.fieldName + ")");
sb.AppendLine(" {");
sb.AppendLine(" string sql = \"select \" + FieldSql + \" from dbo.[" + mainTable.tableName + "] with(nolock)\" + LeftJoinSql + \" where dbo.[" + keyField.tableName + "].[" + keyField.entityFieldName + "] = @" + keyField.fieldName + "; \";");
sb.AppendLine(" Database db = DatabaseFactory.CreateDatabase();");
sb.AppendLine(" DbCommand cmd = db.GetSqlStringCommand(sql);");
sb.AppendLine(" db.AddInParameter(cmd, \"@" + keyField.entityFieldName + "\", DbType." + dbType + ", " + keyField.fieldName + ");");
sb.AppendLine(" using (IDataReader dr = db.ExecuteReader(cmd))");
sb.AppendLine(" {");
sb.AppendLine(" if (dr.Read())");
sb.AppendLine(" {");
sb.AppendLine(" " + entityName + "Info info = new " + entityName + "Info();");
foreach (var table in selectedTables)
{
sb.AppendLine(" Update" + GetEntityNameByTableName(table.tableName) + "Fields(info, dr);");
}
sb.AppendLine(" return info;");
sb.AppendLine(" }");
sb.AppendLine(" }");
sb.AppendLine(" return null;");
sb.AppendLine(" }");
///LeftJoinSql
sb.AppendLine(" private string LeftJoinSql = \"" + joinStr + "\";");
///FieldSql
sb.AppendLine(" private string FieldSql = string.Empty");
bool firstFlag = true;
foreach (var field in selectedFields)
{
sb.AppendLine(" + \"" + (firstFlag ? string.Empty : ",")
+ "dbo.[" + field.tableName + "].[" + field.fieldName + "]"
+ (field.fieldName == field.entityFieldName ? string.Empty : (" as " + field.entityFieldName)) + "\"");
firstFlag = false;
}
sb.AppendLine(" ;");
///UpdateFields
foreach (var table in selectedTables)
{
sb.AppendLine(" private void Update" + GetEntityNameByTableName(table.tableName) + "Fields(" + entityName + "Info info, IDataReader dr)");
sb.AppendLine(" {");
foreach (var field in selectedFields)
{
string getDataType = "GetString";
switch (field.dataType)
{
case "int": getDataType = "GetInt32"; break;
case "numeric": getDataType = "GetDecimal"; break;
}
if (field.keyFieldFlag)
{
sb.AppendLine(" info." + GetEntityNameByTableName(field.fieldName) + " = DBConvert." + getDataType + "(dr, dr.GetOrdinal(\"" + field.entityFieldName + "\"));");
}
else
{
sb.AppendLine(" info." + GetEntityNameByTableName(field.tableName) + "_" + field.fieldName + " = DBConvert." + getDataType + "(dr, dr.GetOrdinal(\"" + field.entityFieldName + "\"));");
}
}
sb.AppendLine(" }");
}
///
///
sb.AppendLine(" }");
sb.AppendLine("}");
SaveFileDialog savedialog = new SaveFileDialog();
savedialog.FileName = entityName + "DAL.cs";
if (savedialog.ShowDialog(this) == DialogResult.OK)
{
if (!File.Exists(savedialog.FileName))
{
File.Create(savedialog.FileName).Close();
}
using (StreamWriter sw = new StreamWriter(savedialog.FileName))
{
sw.WriteLine(sb.ToString());
sw.Close();
}
}
}