public static void ExportDataTableToCsv(Model.Table table)
{
string filePath = Directory.GetCurrentDirectory() + "/" + DateTime.Now.ToString("ddMMyyyy_HHmmss") + ".csv";
if (!File.Exists(filePath))
{
File.Create(filePath).Close();
}
List <string> output = new List <string>();
List <string> headersValueLine = new List <string>();
foreach (Model.Cell cell in table.Headers.Cells)
{
headersValueLine.Add(cell.Value);
}
output.Add(string.Join(";", headersValueLine));
foreach (Model.Row row in table.Rows)
{
List <string> rowValuesLine = new List <string>();
foreach (Model.Cell cell in row.Cells)
{
rowValuesLine.Add(cell.Value);
}
output.Add(string.Join(";", rowValuesLine));
}
System.IO.File.WriteAllLines(filePath, output);
}
void frmDatabase_CreateCode(Model.Table table)
{
CreateCodeForm frm = new CreateCodeForm(table);
frm.ShowReadmeOfTemplate += new Action <string>(ShowTemplateReadme);
frm.Show(MainForm.dockPanel);
}
public List <Model.Table> Select()
{
List <Model.Table> lstTable = new List <Model.Table>();
SqlConnection conexao = new SqlConnection(strCon);
string sql = "Select * from Table";
SqlCommand cmd = new SqlCommand(sql, conexao);
conexao.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
try
{
while (reader.Read())
{
Model.Table table = new Model.Table();
table.IdDevolucao = Convert.ToInt32(reader[0]);
table.IdCliente = Convert.ToInt32(reader["IdCliente"]);
table.IdVeiculo = Convert.ToInt32(reader["IdVeiculo"]);
table.Data_devolucao = Convert.ToDateTime(reader["Data_devolucao"]);
lstTable.Add(table);
}
}
finally
{
conexao.Close();
}
return(lstTable);
}
private static void PrepareExistCommand(Model.Database db, Model.Table table, Model.CodeStyle style, StringBuilder code)
{
AppendFormatLine(code, 2, "/// <summary>");
AppendFormatLine(code, 2, "/// 为查询是否存在一条数据准备参数");
AppendFormatLine(code, 2, "/// </summary>");
switch (style.CmdType)
{
case CommandType.StoredProcedure:
AppendFormatLine(code, 2, "internal static void PrepareExistCommand({0}, out DbParameter[] cmdParms)", GetArgumentsOfFunction(table));
break;
case CommandType.TableDirect:
case CommandType.Text:
AppendFormatLine(code, 2, "internal static void PrepareExistCommand({0}, out StringBuilder strSql, out DbParameter[] cmdParms)", GetArgumentsOfFunction(table));
break;
default:
break;
}
AppendFormatLine(code, 2, "{");
if (style.CmdType != CommandType.StoredProcedure)
{
AppendFormatLine(code, 3, "strSql = new StringBuilder();");
AppendFormatLine(code, 3, "strSql.Append(\"SELECT COUNT(1) FROM {0}\");", table.Name);
AppendFormatLine(code, 3, "strSql.Append(\" WHERE {0}\");", GetConditonOfMySql(table));
}
GetConditionParms(table, style, code);
AppendFormatLine(code, 2, "}");
}
public static string ReadFromTemplate(string file, string dbName, Model.Table table, Model.Field IdentifierRow, Model.CodeStyle style)
{
string code = ReadFile(file);
if (dbName != null)
{
code = code.Replace("$DBName$", dbName);
}
if (table != null)
{
code = code.Replace("$TableName$", table.Name);
}
if (IdentifierRow != null)
{
code = code.Replace("$IdType$", TypeConverter.DataTypeToCSharpTypeString(IdentifierRow.FieldType));
code = code.Replace("$IdName$", IdentifierRow.FieldName);
code = code.Replace("$NullValue$", GetNullValueString(IdentifierRow));
}
if (style != null)
{
code = code.Replace("$BeforeNamespaceDot$", style.BeforeNamespaceDot);
code = code.Replace("$BeforeNamespace$", style.BeforeNamespace);
code = code.Replace("$DotAfterNamespace$", style.DotAfterNamespace);
code = code.Replace("$AfterNamespace$", style.AfterNamespace);
}
return(code);
}
public ConvertToNumWindow(Model.Table table)
{
InitializeComponent();
mainTable = table;
comboBoxColumn.ItemsSource = Services.DataTableService.GetColumnHeadersAsList(table);
}
public FormCodeCreate(Model.Database.DatabaseType dbType, string dbName, Model.Table table)
{
InitializeComponent();
//如果是ACCESS数据库，不允许使用缓存依赖，不能使用存储过程方式
if (dbType == Model.Database.DatabaseType.Access)
{
cobCacheFrame.Items.Remove(cobCacheFrame.Items[2]);
cobDALFrame.Enabled = false;
}
_dbType = dbType;
tcCodes.Controls.Clear();
cobCodeFrame.SelectedIndex = 0;
cobCacheFrame.SelectedIndex = 0;
cobDALFrame.SelectedIndex = 0;
_dbName = dbName;
_table = table;
TextEditor.SetStyle(txtModel, "C#");
TextEditor.SetStyle(txtIDAL, "C#");
TextEditor.SetStyle(txtDBUtility, "C#");
TextEditor.SetStyle(txtDALFactory, "C#");
TextEditor.SetStyle(txtDAL, "C#");
TextEditor.SetStyle(txtBLL, "C#");
TextEditor.SetStyle(txtUserControl, "HTML");
TextEditor.SetStyle(txtUserControlCs, "C#");
TextEditor.SetStyle(txtICacheDependency, "C#");
TextEditor.SetStyle(txtTableDependency, "C#");
TextEditor.SetStyle(txtTableCacheDependency, "C#");
TextEditor.SetStyle(txtDependencyAccess, "C#");
TextEditor.SetStyle(txtDependencyFacade, "C#");
}
private static void GetList(Model.Database db, Model.Table table, Model.CodeStyle style, StringBuilder code)
{
AppendFormatLine(code, 2, "/// <summary>");
AppendFormatLine(code, 2, "/// 获取泛型数据列表");
AppendFormatLine(code, 2, "/// </summary>");
AppendFormatLine(code, 2, "public List<{0}.{1}> GetList()", style.ModelNameSpace, table.Name);
AppendFormatLine(code, 2, "{");
switch (style.CmdType)
{
case CommandType.StoredProcedure:
AppendFormatLine(code, 3, "using (DbDataReader dr = {0}.ExecuteReader(CommandType.StoredProcedure, \"sp_{1}_GetAllList\", null))",
style.DBHelperName, table.Name);
break;
case CommandType.Text:
case CommandType.TableDirect:
default:
AppendFormatLine(code, 3, "StringBuilder strSql = new StringBuilder(\"SELECT * FROM {0}\");", table.Name);
AppendFormatLine(code, 3, "using (DbDataReader dr = {0}.ExecuteReader(CommandType.Text, strSql.ToString(), null))",
style.DBHelperName);
break;
}
AppendFormatLine(code, 3, "{");
AppendFormatLine(code, 4, "List<{0}.{1}> lst = GetList(dr);", style.ModelNameSpace, table.Name);
AppendFormatLine(code, 4, "return lst;");
AppendFormatLine(code, 3, "}");
AppendFormatLine(code, 2, "}");
}
private void menuDelete_Click(object sender, EventArgs e)
{
if (CreateCode != null)
{
string dbName = tvDatabase.SelectedNode.Parent.Parent.Text;
Model.Database db = tvDatabase.SelectedNode.Parent.Parent.Tag as Model.Database;
Model.Table table = tvDatabase.SelectedNode.Tag as Model.Table;
if (table != null)
{
SqlSeleteViewForm ssv = new SqlSeleteViewForm(db, table);
string str = "\nwhere 1 = 1";
string str2 = "delete from ";
str2 = (str2 + "" + this.tvDatabase.SelectedNode.Text + "") + str;
if ((ssv.sqlTextEditor.Text.Trim().Length != 0) && (ssv.sqlTextEditor.Text[ssv.sqlTextEditor.Text.Length - 1] != '\n'))
{
ssv.sqlTextEditor.Text = ssv.sqlTextEditor.Text + "\n";
}
ssv.sqlTextEditor.Text = ssv.sqlTextEditor.Text + str2;
ssv.Show(MainForm.dockPanel);
}
else
{
ShowMessage.Alert("请先选中一个表或视图。");
}
}
}
private static void GetPageListPrivate(Model.Database db, Model.Table table, Model.CodeStyle style, StringBuilder code)
{
AppendFormatLine(code, 2, "/// <summary>");
AppendFormatLine(code, 2, "/// 由DbDataReader得到分页泛型数据列表");
AppendFormatLine(code, 2, "/// </summary>");
AppendFormatLine(code, 2, "private List<{0}.{1}> GetPageList(DbDataReader dr, int first, int count)", style.ModelNameSpace, table.Name);
AppendFormatLine(code, 2, "{");
AppendFormatLine(code, 3, "List<{0}.{1}> lst = new List<{0}.{1}>();", style.ModelNameSpace, table.Name);
code.AppendLine();
AppendFormatLine(code, 3, "for (int i = 0; i < first; i++)", style.ModelNameSpace, table.Name);
AppendFormatLine(code, 3, "{");
AppendFormatLine(code, 4, "if (!dr.Read())");
AppendFormatLine(code, 4, "{");
AppendFormatLine(code, 5, "return lst;");
AppendFormatLine(code, 4, "}");
AppendFormatLine(code, 3, "}");
code.AppendLine();
AppendFormatLine(code, 3, "int resultsFetched = 0;");
AppendFormatLine(code, 3, "while (resultsFetched < count && dr.Read())");
AppendFormatLine(code, 3, "{");
AppendFormatLine(code, 4, "lst.Add(GetModel(dr));");
AppendFormatLine(code, 4, "resultsFetched++;");
AppendFormatLine(code, 3, "}");
code.AppendLine();
AppendFormatLine(code, 3, "return lst;");
AppendFormatLine(code, 2, "}");
}
private List <Model.Table> GetSQLTableList(DataSet ds)
{
DataTable dt = ds.Tables[0];
List <Model.Table> tables = new List <Model.Table>();
foreach (DataRow r in dt.Rows)
{
Model.Field field = GetField(r);
string parentTableName = SchemaHelper.GetString(r["TableName"]);
bool hasTable = false;
foreach (Model.Table modelTable in tables)
{
if (parentTableName == modelTable.Name)
{
modelTable.AddField(field);
hasTable = true;
break;
}
}
if (!hasTable)
{
Model.Table newTable = new Model.Table();
newTable.Name = parentTableName;
newTable.AddField(field);
tables.Add(newTable);
}
}
foreach (Model.Table table in tables)
{
table.Fields.Sort();
}
return(tables);
}
public KNNChartWindow(Model.Table table)
{
InitializeComponent();
mainTable = table;
columnBinding = DataTableService.GetColumnHeadersAsList(table);
DataContext = this;
}
/// <summary>
/// 取得主键
/// </summary>
public static Model.Field GetKeyField(Model.Table table, out bool HasIdentifierField)
{
List <Model.Field> l = table.Fields;
Model.Field IdentifierRow = null;
HasIdentifierField = false;
foreach (Model.Field model in l)
{
if (model.IsIdentifier)
{
IdentifierRow = model;
HasIdentifierField = true;
break;
}
}
if (IdentifierRow == null)
{
foreach (Model.Field model in l)
{
if (model.IsKeyField)
{
IdentifierRow = model;
break;
}
}
}
if (IdentifierRow == null)
{
IdentifierRow = l[0];
}
return(IdentifierRow);
}
public static Model.Table Normalize(Model.Table table, int selectedColumnIndex)
{
List <double> columnValues = GetListOfColumnValues(table, selectedColumnIndex);
double columnAvarage = columnValues.Average();
double standardDeviation = CalculateStdDev(columnValues);
table.Headers.Cells.Add(
new Model.Cell(
table.Headers.Cells[selectedColumnIndex].Value + "_Norm"
));
foreach (Model.Row row in table.Rows)
{
row.Cells.Add(
new Model.Cell(
GetNormalizedValue(Convert.ToDouble(row.Cells[selectedColumnIndex].Value), columnAvarage, standardDeviation).ToString())
);
}
//double columnAvarage = GetAvarageOfColumn(table, selectedColumnIndex);
return(table);
}
private List <Model.Table> GetSelectedTables(Database database)
{
var selectedTables = new List <DBLint.Model.Table>();
var tid = new WriteableTableID(_executer.ViewModel.MetadataSelection.Extractor.DatabaseName, null, null);
foreach (var schema in _executer.ViewModel.MetadataSelection.Schemas)
{
if (schema.Include.HasValue && !schema.Include.Value)
{
continue;
}
tid.SetSchemaName(schema.Name);
foreach (var tbl in schema.Tables.Value)
{
if (!tbl.Include)
{
continue;
}
tid.SetTableName(tbl.Name);
Model.Table table = database.GetTable(tid);
selectedTables.Add(table);
}
}
return(selectedTables);
}
public static string GetMySqlStoreProcedureCode(Model.Table table, Model.CodeStyle style)
{
StringBuilder code = new StringBuilder();
Add(table, code, style);
code.AppendLine();
Update(table, code);
code.AppendLine();
Delete(table, code);
code.AppendLine();
Exists(table, code);
code.AppendLine();
GetCount(table, code);
code.AppendLine();
GetModel(table, code);
code.AppendLine();
GetAllList(table, code);
return(code.ToString());
}
public static string BookTable(Model.Table table, string employeeName)
{
string url = SERVER + "/tables/Book/" + table.number;
string json = "{\"employeeName\": \"" + employeeName +
"\", \"number\": \"" + table.number +
"\", \"note\": \"" + table.note +
"\", \"status\": \"" + table.status +
"\", \"customer\": { \"fullName\": \"" + table.customer.fullName +
"\", \"phone\": \"" + table.customer.phone + "\"}}";
string url1 = SERVER + "/bills";
string json1 = "{\"employeeName\": \"" + employeeName +
"\", \"number\": \"" + table.number +
"\", \"numberOfSeat\": \"" + table.numberOfSeat.ToString() +
"\", \"type\": \"" + table.type +
"\", \"customer\": { \"fullName\": \"" + table.customer.fullName +
"\", \"phone\": \"" + table.customer.phone + "\"}}";
try
{
POST(url1, json1);
return(PUT(url, json));
}
catch
{
MessageBox.Show("Kết nối không ổn định. Xin thử lại.");
return("");
}
}
public CodeCreateForm(ContextMenuStrip cms, Model.Database db, Model.Table table)
{
InitializeComponent();
this.TabPageContextMenuStrip = cms;
styleUserControl1.DB = db;
styleUserControl1.LoadConfig();
this.db = db;
this.table = table;
this.TabText = string.Format("生成代码 {0}", table.Name);
tcCodes.Controls.Clear();
TextEditor.SetStyle(txtInternalModel, "C#");
TextEditor.SetStyle(txtModel, "C#");
TextEditor.SetStyle(txtIDAL, "C#");
TextEditor.SetStyle(txtDALFactory, "C#");
TextEditor.SetStyle(txtInternalDAL, "C#");
TextEditor.SetStyle(txtDAL, "C#");
TextEditor.SetStyle(txtBL, "C#");
TextEditor.SetStyle(txtUserControl, "HTML");
TextEditor.SetStyle(txtUserControlDesignerCs, "C#");
TextEditor.SetStyle(txtUserControlCs, "C#");
TextEditor.SetStyle(txtICacheDependency, "C#");
TextEditor.SetStyle(txtTableDependency, "C#");
TextEditor.SetStyle(txtTableCacheDependency, "C#");
TextEditor.SetStyle(txtDependencyAccess, "C#");
TextEditor.SetStyle(txtDependencyFacade, "C#");
}
private static void Delete(Model.Database db, Model.Table table, Model.CodeStyle style, StringBuilder code)
{
AppendFormatLine(code, 2, "/// <summary>");
AppendFormatLine(code, 2, "/// 删除一条数据");
AppendFormatLine(code, 2, "/// </summary>");
AppendFormatLine(code, 2, "public int Delete({0})", GetArgumentsOfFunction(table));
AppendFormatLine(code, 2, "{");
if (style.CmdType != CommandType.StoredProcedure)
{
AppendFormatLine(code, 3, "StringBuilder strSql;");
}
AppendFormatLine(code, 3, "DbParameter[] cmdParms;");
switch (style.CmdType)
{
case System.Data.CommandType.StoredProcedure:
AppendFormatLine(code, 3, "PrepareDeleteCommand({0}, out cmdParms);", GetArgumentValuesOfFunction(table));
AppendFormatLine(code, 3, "return {0}.ExecuteNonQuery(CommandType.StoredProcedure, \"sp_{1}_Delete\", cmdParms);",
style.DBHelperName,
table.Name);
break;
case System.Data.CommandType.Text:
default:
AppendFormatLine(code, 3, "PrepareDeleteCommand({0}, out strSql, out cmdParms);", GetArgumentValuesOfFunction(table));
AppendFormatLine(code, 3, "return {0}.ExecuteNonQuery(CommandType.Text, strSql.ToString(), cmdParms);",
style.DBHelperName);
break;
}
AppendFormatLine(code, 2, "}");
}
public static List <Model.Table> GetTableList(DataSet ds)
{
DataTable dt = ds.Tables[0];
List <Model.Table> lTable = new List <Model.Table>();
foreach (DataRow r in dt.Rows)
{
Model.Field model = GetField(r);
foreach (Model.Table modelTable in lTable)
{
if (model.TableName == modelTable.Fields[0].TableName)
{
modelTable.Fields.Add(model);
}
else
{
Model.Table newTable = new Model.Table();
newTable.Name = model.TableName;
newTable.Fields.Add(model);
}
}
//lTable.Add(model);
}
return(lTable);
}
private static Model.Table GetTable(DBUtility.DBHelper dbHelper, string dbName, DataRow rTable)
{
string tableName = rTable[0].ToString();
Model.Table table = new Model.Table();
table.Name = tableName;
// 对每个表取字段属性
DataSet dsColumns = dbHelper.ExecuteQuery(CommandType.Text,
string.Format("select * from COLUMNS where TABLE_SCHEMA='{0}' and TABLE_NAME='{1}'", dbName, tableName),
null);
foreach (DataRow rField in dsColumns.Tables[0].Rows)
{
Model.Field field = new Model.Field();
field.IsIdentifier = rField["EXTRA"].ToString().ToLower() == "auto_increment";
field.IsKeyField = rField["COLUMN_KEY"].ToString().ToLower() == "pri";
field.AllowNull = rField["IS_NULLABLE"].ToString().ToLower() == "yes";
field.MySqlTypeString = SchemeHelper.GetString(rField["DATA_TYPE"]);
field.DefaultValue = SchemeHelper.GetString(rField["COLUMN_DEFAULT"]);
field.FieldDescn = SchemeHelper.GetString(rField["COLUMN_COMMENT"]);
field.FieldLength = SchemeHelper.GetLong(rField["CHARACTER_MAXIMUM_LENGTH"]);
field.FieldName = SchemeHelper.GetString(rField["COLUMN_NAME"]);
field.FieldNumber = SchemeHelper.GetInt(rField["ORDINAL_POSITION"]);
table.Fields.Add(field);
}
return(table);
}
private static void GetCS3OrMvc2Model(Model.Table table, StringBuilder code, Model.CodeStyle style)
{
foreach (Model.Field model in table.Fields)
{
AppendFormatLine(code, 2, "/// <summary>");
AppendFormatLine(code, 2, "/// {0}", model.FieldDescn);
AppendFormatLine(code, 2, "/// </summary>");
if (style.ModelStyle == Model.CodeStyle.ModelStyles.MVC2)
{
int minLength = 0;
if (!model.IsIdentifier && !model.IsKeyField && CodeHelper.IsStringDbType(model) && !model.AllowNull)
{
AppendFormatLine(code, 2, "[Required(ErrorMessage=\"{0}不能为空\")]", model.FieldDescn);
minLength = 1;
}
AppendFormatLine(code, 2, "[DisplayName(\"{0}\")]", model.FieldDescn);
if (!model.IsIdentifier && !model.IsKeyField && CodeHelper.IsStringDbType(model))
{
AppendFormatLine(code, 2, "[RegularExpression(@\"[\\w\\W]{{1,{0}}}\", ErrorMessage = \"{1}为{2}－{0}位\")]//此默认生成的正则为允许任意字符，请根据业务逻辑修改", model.FieldLength, model.FieldDescn, minLength);
}
}
AppendFormatLine(code, 2, "public {0} {1} {{ get; set; }}", model.CSharpType, model.FieldName);
code.AppendLine();
}
}
private static void GetCount(Model.Database db, Model.Table table, Model.CodeStyle style, StringBuilder code)
{
AppendFormatLine(code, 2, "/// <summary>");
AppendFormatLine(code, 2, "/// 获取数量");
AppendFormatLine(code, 2, "/// </summary>");
AppendFormatLine(code, 2, "public int GetCount()");
AppendFormatLine(code, 2, "{");
switch (style.CmdType)
{
case System.Data.CommandType.StoredProcedure:
AppendFormatLine(code, 3, "object obj = {0}.ExecuteScalar(CommandType.StoredProcedure, \"sp_{1}_GetCount\", null);",
style.DBHelperName, table.Name);
break;
case System.Data.CommandType.Text:
default:
AppendFormatLine(code, 3, "object obj = {0}.ExecuteScalar(CommandType.Text, \"SELECT count(*) FROM {1}\", null);",
style.DBHelperName, table.Name);
break;
}
AppendFormatLine(code, 3, "return int.Parse(obj.ToString());");
AppendFormatLine(code, 2, "}");
}
void frmDatabase_CreateCode(Model.Table table)
{
CreateCodeForm frm = new CreateCodeForm(table);
frm.ShowDebug += new Action <string, string, bool>(frm_ShowDebug);
frm.Show(MainForm.dockPanel);
}
private void menuInsert_Click(object sender, EventArgs e)
{
if (CreateCode != null)
{
string dbName = tvDatabase.SelectedNode.Parent.Parent.Text;
Model.Database db = tvDatabase.SelectedNode.Parent.Parent.Tag as Model.Database;
Model.Table table = tvDatabase.SelectedNode.Tag as Model.Table;
if (table != null)
{
SqlSeleteViewForm ssv = new SqlSeleteViewForm(db, table);
string str = "";
string str2 = "insert into ";
str2 = (str2 + "" + this.tvDatabase.SelectedNode.Text + "") + " (" + this.tvDatabase.SelectedNode.Nodes[0].Text;
for (int i = 1; i < this.tvDatabase.SelectedNode.Nodes.Count; i++)
{
str = str + ",''";
str2 = str2 + "," + this.tvDatabase.SelectedNode.Nodes[i].Text;
}
str2 = (str2 + ") \n" + "values(''") + str + ")";
if ((ssv.sqlTextEditor.Text.Trim().Length != 0) && (ssv.sqlTextEditor.Text[ssv.sqlTextEditor.Text.Length - 1] != '\n'))
{
ssv.sqlTextEditor.Text = ssv.sqlTextEditor.Text + "\n";
}
ssv.sqlTextEditor.Text = ssv.sqlTextEditor.Text + str2;
ssv.Show(MainForm.dockPanel);
}
else
{
ShowMessage.Alert("请先选中一个表或视图。");
}
}
}
private static void Exists(Model.Database db, Model.Table table, Model.CodeStyle style, StringBuilder code)
{
AppendFormatLine(code, 2, "/// <summary>");
AppendFormatLine(code, 2, "/// 是否存在该记录");
AppendFormatLine(code, 2, "/// </summary>");
AppendFormatLine(code, 2, "public bool Exists({0})", GetArgumentsOfFunction(table));
AppendFormatLine(code, 2, "{");
if (style.CmdType != CommandType.StoredProcedure)
{
AppendFormatLine(code, 3, "StringBuilder strSql;");
}
AppendFormatLine(code, 3, "DbParameter[] cmdParms;");
switch (style.CmdType)
{
case System.Data.CommandType.StoredProcedure:
AppendFormatLine(code, 3, "PrepareExistCommand({0}, out cmdParms);", GetArgumentValuesOfFunction(table));
AppendFormatLine(code, 3, "object obj = {0}.ExecuteScalar(CommandType.StoredProcedure, \"sp_{1}_Exists\", cmdParms);",
style.DBHelperName, table.Name);
break;
case System.Data.CommandType.Text:
default:
AppendFormatLine(code, 3, "PrepareExistCommand({0}, out strSql, out cmdParms);", GetArgumentValuesOfFunction(table));
AppendFormatLine(code, 3, "object obj = {0}.ExecuteScalar(CommandType.Text, strSql.ToString(), cmdParms);",
style.DBHelperName);
break;
}
AppendFormatLine(code, 3, "return int.Parse(obj.ToString()) > 0;");
AppendFormatLine(code, 2, "}");
}
private static void Add(Model.Database db, Model.Table table, Model.CodeStyle style, StringBuilder code)
{
AppendFormatLine(code, 2, "/// <summary>");
AppendFormatLine(code, 2, "/// 增加一条数据");
AppendFormatLine(code, 2, "/// </summary>");
AppendFormatLine(code, 2, "public int Add({0}.{1} model)", style.ModelNameSpace, table.Name);
AppendFormatLine(code, 2, "{");
if (style.CmdType != CommandType.StoredProcedure)
{
AppendFormatLine(code, 3, "StringBuilder strSql;");
}
AppendFormatLine(code, 3, "DbParameter[] cmdParms;");
switch (style.CmdType)
{
case System.Data.CommandType.StoredProcedure:
AppendFormatLine(code, 3, "PrepareAddCommand(model, out cmdParms);");
AppendFormatLine(code, 3, "return {0}.ExecuteNonQuery(CommandType.StoredProcedure, \"sp_{1}_Add\", cmdParms);", style.DBHelperName, table.Name);
break;
case System.Data.CommandType.Text:
default:
AppendFormatLine(code, 3, "PrepareAddCommand(model, out strSql, out cmdParms);");
AppendFormatLine(code, 3, "return {0}.ExecuteNonQuery(CommandType.Text, strSql.ToString(), cmdParms);", style.DBHelperName);
break;
}
AppendFormatLine(code, 2, "}");
}
private List <Model.Table> GetSQLTableList(DataSet ds)
{
DataTable dt = ds.Tables[0];
List <Model.Table> lTable = new List <Model.Table>();
foreach (DataRow r in dt.Rows)
{
Model.Field model = GetField(r);
bool hasTable = false;
foreach (Model.Table modelTable in lTable)
{
if (model.TableName == modelTable.Fields[0].TableName)
{
modelTable.Fields.Add(model);
hasTable = true;
break;
}
}
if (!hasTable)
{
Model.Table newTable = new Model.Table();
newTable.Name = model.TableName;
List <Model.Field> lFields = new List <Model.Field>();
lFields.Add(model);
newTable.Fields = lFields;
lTable.Add(newTable);
}
}
foreach (Model.Table table in lTable)
{
table.Fields.Sort();
}
return(lTable);
}
/// <summary>
/// 生成Web用户控件
/// </summary>
public static string GetUserControlCode(Model.Table table, Model.CodeStyle style)
{
int width = table.Fields.Count > 1 ? 100 / (table.Fields.Count - 1) : 100;
StringBuilder code = new StringBuilder(CommonCode.GetHtmlCopyrightCode());
AppendFormatLine(code, 0, "<%@ Control Language=\"C#\" AutoEventWireup=\"true\" Codebehind=\"{0}{1}Grid.ascx.cs\"",
style.AfterNamespace, table.Name);
AppendFormatLine(code, 1, "Inherits=\"Controls_{0}{1}Grid\" %>", style.AfterNamespace, table.Name);
AppendFormatLine(code, 0, "<table id=\"{0}Grid\" class=\"data\" style=\"width: 100%;\">", table.Name);
AppendFormatLine(code, 1, "<tr class=\"title\">");
int i = 0;
foreach (Model.Field field in table.Fields)
{
if (field != table.ConditionRows[0])
{
AppendFormatLine(code, 2, "<td style=\"width: {0}%;\">", width);
if (i == 0)
{
AppendFormatLine(code, 3, "<input id=\"chkChooseAll{0}\" type=\"checkbox\" onclick=\"chooseAll('{0}Grid','chkChooseAll{0}')\" />",
table.Name);
}
AppendFormatLine(code, 3, "{0}", field.FieldDescn);
AppendFormatLine(code, 2, "</td>");
i++;
}
}
AppendFormatLine(code, 1, "</tr>");
AppendFormatLine(code, 1, "<asp:Repeater ID=\"grd{0}\" runat=\"server\" OnItemCommand=\"grd{0}_ItemCommand\">", table.Name);
AppendFormatLine(code, 2, "<ItemTemplate>");
AppendFormatLine(code, 3, "<tr>");
i = 0;
foreach (Model.Field field in table.Fields)
{
if (field != table.ConditionRows[0])
{
AppendFormatLine(code, 4, "<td>");
if (i == 0)
{
AppendFormatLine(code, 5, "<asp:CheckBox ID=\"chkChoose\" runat=\"server\" /><asp:HiddenField ID=\"hf{0}\"", table.Name);
AppendFormatLine(code, 6, "runat=\"server\" Value='<%# Eval(\"{0}\") %>' />", table.ConditionRows[0].FieldName);
}
AppendFormatLine(code, 5, "<span title=\"<%# Eval(\"{0}\") %>\">", field.FieldName);
AppendFormatLine(code, 6, "<%# Eval(\"{0}\") %></span>", field.FieldName);
AppendFormatLine(code, 4, "</td>");
i++;
}
}
AppendFormatLine(code, 3, "</tr>");
AppendFormatLine(code, 2, "</ItemTemplate>");
AppendFormatLine(code, 1, "</asp:Repeater>");
AppendFormatLine(code, 0, "</table>");
return(code.ToString());
}
public Model.Database GetSchema(string connectionString, Model.Database.DatabaseType type)
{
Model.Database database = new Model.Database();
database.ConnectionString = connectionString;
database.Type = type;
//得到获取MySql结构的语句
string dbName;
string connStr = database.ConnectionString;
Match mDatabase = Regex.Match(connStr, @"Database=(?<Database>[^\;]*);");
if (mDatabase.Success)
{
dbName = mDatabase.Groups["Database"].Value;
connStr = connStr.Replace(string.Format("Database={0};", dbName), "Database=information_schema;");
}
else
{
return(null);
}
DBUtility.DBHelper dbHelper = new DBUtility.DBHelper(DBUtility.DBHelper.DatabaseTypes.MySql, connStr);
// 获取表
DataSet dsTables = dbHelper.ExecuteQuery(CommandType.Text,
string.Format("select distinct TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='{0}' and TABLE_TYPE='BASE TABLE'", dbName),
null);
foreach (DataRow rTable in dsTables.Tables[0].Rows)
{
Model.Table table = GetTable(dbHelper, dbName, rTable);
table.Fields.Sort();
database.Tables.Add(table);
}
// 获取视图
DataSet dsViews = dbHelper.ExecuteQuery(CommandType.Text,
string.Format("select distinct TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='{0}' and TABLE_TYPE='VIEW'", dbName),
null);
foreach (DataRow rView in dsViews.Tables[0].Rows)
{
Model.Table view = GetTable(dbHelper, dbName, rView);
view.Fields.Sort();
database.Views.Add(view);
}
// 获取存储过程
DataSet dsStoreProcedure = dbHelper.ExecuteQuery(CommandType.Text,
string.Format("select distinct SPECIFIC_NAME from ROUTINES where ROUTINE_SCHEMA='{0}'", dbName),
null);
foreach (DataRow r in dsStoreProcedure.Tables[0].Rows)
{
database.StoreProcedures.Add(r[0].ToString());
}
return(database);
}
private Model.Table GetNewTable(string tableName)
{
ArchAngel.Interfaces.Events.RaiseObjectBeingProcessedEvent(tableName, "Table");
//_columns = null; // Reset the columns
//_indexes = null;
//_dtReferencedColumns = null;
//return new Model.Table();
Model.Table table = new Model.Table(tableName, false);
#region Columns
DataRow[] columnRows = Columns.Select(string.Format("TABLE_NAME = '{0}'", tableName));
foreach (DataRow row in columnRows)
{
bool isReadOnly = false;
if (!row.IsNull("IsIdentity") && (int)row["IsIdentity"] == 1)
{
isReadOnly = true;
}
else if (!row.IsNull("IsComputed") && (int)row["IsComputed"] == 1)
{
isReadOnly = true;
}
else if (Slyce.Common.Utility.StringsAreEqual((string)row["DATA_TYPE"], "timestamp", false))
{
isReadOnly = true;
}
// Check whether we have added this column before. Columns are repeated if they are both a PRIMARY_KEY and a FOREIGN_KEY
Column column = new Column(
(string)row["COLUMN_NAME"],
false,
table,
(int)row["ORDINAL_POSITION"],
Slyce.Common.Utility.StringsAreEqual((string)row["IS_NULLABLE"], "YES", false),
(string)row["DATA_TYPE"],
row.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]),
(int)row["InPrimaryKey"] == 1,
row.IsNull("IsIdentity") ? false : Convert.ToInt32(row["IsIdentity"]) == 1,
row.IsNull("COLUMN_DEFAULT") ? "" : (string)row["COLUMN_DEFAULT"],
isReadOnly,
row.IsNull("IsComputed") ? false : Convert.ToInt32(row["IsComputed"]) == 1,
row.IsNull("NUMERIC_PRECISION") ? 0 : Convert.ToInt32(row["NUMERIC_PRECISION"]),
row.IsNull("NUMERIC_SCALE") ? 0 : Convert.ToInt32(row["NUMERIC_SCALE"]));
table.AddColumn(column);
//ordinalPosition++;
}
#endregion
#region Indexes
DataRow[] indexRows = Indexes.Select(string.Format("TABLE_NAME = '{0}'", tableName));
foreach (DataRow indexRow in indexRows)
{
string indexType;
string indexKeyType = indexRow["CONSTRAINT_TYPE"].ToString();
if (indexKeyType == "PRIMARY KEY")
{
continue;
}
else if (indexKeyType == "FOREIGN KEY")
{
continue;
}
else if (indexKeyType == "UNIQUE")
{
continue;
//indexType = DatabaseConstant.IndexType.Unique;
}
else if (indexKeyType == "CHECK")
{
indexType = DatabaseConstant.IndexType.Check;
}
else if (indexKeyType == "NONE") //TODO check is NONE
{
indexType = DatabaseConstant.IndexType.None;
}
else
{
//continue;
throw new Exception("IndexType " + indexKeyType + " Not Defined");
}
DataRow[] indexColumnRows;// = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, indexRow["CONSTRAINT_NAME"]));
if (indexKeyType == "NONE")
{
indexColumnRows = Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME = '{1}'", tableName, indexRow["ColumnName"]));
}
else
{
indexColumnRows = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, indexRow["CONSTRAINT_NAME"]));
}
Index index = new Index(indexRow["CONSTRAINT_NAME"].ToString(), false, indexType, table, (bool)indexRow["IS_UNIQUE"], (bool)indexRow["IS_CLUSTERED"]);
// Fill Columns
foreach (DataRow indexColumnRow in indexColumnRows)
{
Column indexColumn = new Column(indexColumnRow["COLUMN_NAME"].ToString(), false);
index.AddColumn(indexColumn);
}
index.ResetDefaults();
table.AddIndex(index);
}
// Indexes -- that should be keys
foreach (DataRow keyRow in indexRows)
{
string keyType;
string indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString();
if (indexKeyType == "PRIMARY KEY")
{
keyType = DatabaseConstant.KeyType.Primary;
}
else if (indexKeyType == "FOREIGN KEY")
{
keyType = DatabaseConstant.KeyType.Foreign;
}
else if (indexKeyType == "UNIQUE")
{
keyType = DatabaseConstant.KeyType.Unique;
}
else if (indexKeyType == "CHECK")
{
continue;
}
else if (indexKeyType == "NONE")
{
continue;
//keyType = DatabaseConstant.KeyType.None;
}
else
{
//continue;
throw new Exception("KeyType " + indexKeyType + " Not Defined");
}
Key key = new Key(keyRow["CONSTRAINT_NAME"].ToString(), false, keyType, table, false);
DataRow[] keyColumnRows = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, keyRow["CONSTRAINT_NAME"]));
// Fill Columns
foreach (DataRow keyColumnRow in keyColumnRows)
{
Column keyColumn = new Column(keyColumnRow["COLUMN_NAME"].ToString(), false);
keyColumn.DataType = (string)keyColumnRow["DATA_TYPE"];
key.AddColumn(keyColumn);
}
if (keyType == DatabaseConstant.KeyType.Foreign)
{
DataRow[] keyReferencedColumnRows = IndexReferencedColumns.Select(string.Format("ForeignKey = '{0}'", keyRow["CONSTRAINT_NAME"]));
DataRow firstKeyReferencedColumnRow = keyReferencedColumnRows[0];
// Fill References
key.ReferencedTable = new Model.Table(firstKeyReferencedColumnRow["ReferencedTable"].ToString(), false);
//if (dmoKey.ReferencedKey != null)
//{
key.ReferencedKey = new Key(firstKeyReferencedColumnRow["ReferencedKey"].ToString(), false, true);
//}
// Fill Referenced Columns
foreach (DataRow keyReferencedColumnRow in keyReferencedColumnRows)
{
Column keyReferencedColumn = new Column(keyReferencedColumnRow["ReferencedColumn"].ToString(), false);
key.AddReferencedColumn(keyReferencedColumn);
}
}
key.ResetDefaults();
table.AddKey(key);
}
#endregion
//#region Indexes -- that should be keys
//string prevConstraintName = "";
//Key key = null;
//DataRow[] indexRows = DtIndexes.Select(string.Format("TABLE_NAME = '{0}'", tableName));
//for (int rowCounter = 0; rowCounter < indexRows.Length; rowCounter++)
//{
// DataRow row = indexRows[rowCounter];
// for (int colCounter = 0; colCounter < DtIndexes.Columns.Count; colCounter++)
// {
// string colName = DtIndexes.Columns[colCounter].ColumnName;
// }
// string keyType;
// string indexKeyType = row.IsNull("Constraint_Type") ? "NONE" : (string)row["Constraint_Type"];
// string constraintName = row.IsNull("Constraint_Name") ? "" : (string)row["Constraint_Name"];
// string columnName = (string)row["COLUMN_NAME"];
// if (indexKeyType == "NONE")
// {
// keyType = DatabaseConstant.KeyType.None;
// }
// else if (indexKeyType == "PRIMARY KEY")
// {
// keyType = DatabaseConstant.KeyType.Primary;
// }
// else if (indexKeyType == "UNIQUE")
// {
// keyType = DatabaseConstant.KeyType.Unique;
// }
// else if (indexKeyType == "CHECK") // TODO: was 'None' for SMO
// {
// keyType = DatabaseConstant.KeyType.None;
// }
// else if (indexKeyType == "FOREIGN KEY") // TODO: was 'None' for SMO
// {
// keyType = DatabaseConstant.KeyType.Foreign;
// }
// else
// {
// throw new Exception("KeyType " + indexKeyType + " Not Defined");
// }
// // Create Alias
// if (string.Format("{0}{1}", constraintName, keyType) != prevConstraintName)
// {
// if (key != null)
// {
// // Reset the alias, because it is based on the Columns collection which has just finished being modified.
// key.ResetDefaults();
// }
// // Create a new Key
// key = new Key(constraintName, false, keyType, table, false);
// table.AddKey(key);
// prevConstraintName = string.Format("{0}{1}", constraintName, keyType);
// Column keyColumn = new Column(columnName, false);
// key.AddColumn(keyColumn);
// }
// else
// {
// // We are processing another column of the same Index as the previous index
// Column keyColumn = new Column(columnName, false);
// key.AddColumn(keyColumn);
// }
// if (keyType == DatabaseConstant.KeyType.Foreign)
// {
// DataRow[] referencedColumnRows = DtReferencedColumns.Select(string.Format("FOREIGN_KEY = '{0}'", key.Name));
// foreach (DataRow refColRow in referencedColumnRows)
// {
// // Fill References
// if (key.ReferencedTable == null)
// {
// string referencedTableName = (string)refColRow["Referenced_Table_Name"];
// string referencedKeyName = (string)refColRow["Referenced_Key"];
// key.ReferencedTable = new Model.Table(referencedTableName, false);
// key.ReferencedKey = new Key(referencedKeyName, false, true);
// }
// string referencedColumnName = (string)refColRow["Referenced_Column_Name"];
// // Fill Referenced Columns
// Column referencedKeyColumn = new Column(referencedColumnName, false);
// key.AddReferencedColumn(referencedKeyColumn);
// }
// }
// key.ResetDefaults();
//}
//#endregion
return table;
}
protected virtual void OnSelectedTableChanged(object sender, Model.Table oldTable, Model.Table newTable)
{
if (newTable != null)
{
newTable.PropertyChanged += SelectedTablePropertyChanged;
}
if (oldTable != null)
{
oldTable.PropertyChanged -= SelectedTablePropertyChanged;
}
this.ColumnsGrid = new ColumnsGrid();
this.ColumnsGrid.SelectedTable = newTable;
this.ColumnsGrid.DataContext = newTable;
}
protected void OnSelectedTableChanged(object sender, RoutedPropertyChangedEventArgs<object> e)
{
WaveTech.Dafuscator.Model.Table t = e.NewValue as WaveTech.Dafuscator.Model.Table;
SelectedTable = t;
}
private Model.Table GetNewTable(string schema, string tableName)
{
Interfaces.Events.RaiseObjectBeingProcessedEvent(tableName, "Table");
Model.Table table = new Model.Table(tableName, false);
#region Columns
DataRow[] columnRows = Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", schema, tableName));
foreach (DataRow columnRow in columnRows)
{
bool isReadOnly = false;
if (!columnRow.IsNull("IsIdentity") && (int)columnRow["IsIdentity"] == 1)
{
isReadOnly = true;
}
else if (!columnRow.IsNull("IsComputed") && (int)columnRow["IsComputed"] == 1)
{
isReadOnly = true;
}
else if (Slyce.Common.Utility.StringsAreEqual((string)columnRow["DATA_TYPE"], "timestamp", false))
{
isReadOnly = true;
}
Column column = new Column(
(string)columnRow["COLUMN_NAME"],
false,
table,
(int)columnRow["ORDINAL_POSITION"],
Slyce.Common.Utility.StringsAreEqual((string)columnRow["IS_NULLABLE"], "YES", false),
(string)columnRow["DATA_TYPE"],
columnRow.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : Convert.ToInt32(columnRow["CHARACTER_MAXIMUM_LENGTH"]),
(int)columnRow["InPrimaryKey"] == 1,
columnRow.IsNull("IsIdentity") ? false : Convert.ToInt32(columnRow["IsIdentity"]) == 1,
columnRow.IsNull("COLUMN_DEFAULT") ? "" : (string)columnRow["COLUMN_DEFAULT"],
isReadOnly,
columnRow.IsNull("IsComputed") ? false : Convert.ToInt32(columnRow["IsComputed"]) == 1,
columnRow.IsNull("NUMERIC_PRECISION") ? 0 : Convert.ToInt32(columnRow["NUMERIC_PRECISION"]),
columnRow.IsNull("NUMERIC_SCALE") ? 0 : Convert.ToInt32(columnRow["NUMERIC_SCALE"]));
if (IsSupported(column))
{
table.AddColumn(column);
}
}
#endregion
#region Indexes
DataRow[] indexRows = Indexes.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", schema, tableName));
//foreach (DataRow indexRow in indexRows)
for (int i = 0; i < indexRows.Length; i++)
{
DataRow indexRow = indexRows[i];
string indexType;
string indexKeyType = indexRow["CONSTRAINT_TYPE"].ToString();
if (indexKeyType == "PRIMARY KEY")
{
indexType = DatabaseConstant.IndexType.PrimaryKey;
//continue;
}
else if (indexKeyType == "FOREIGN KEY")
{
indexType = DatabaseConstant.IndexType.ForeignKey;
//continue;
}
else if (indexKeyType == "UNIQUE")
{
//continue;
indexType = DatabaseConstant.IndexType.Unique;
}
else if (indexKeyType == "CHECK")
{
indexType = DatabaseConstant.IndexType.Check;
}
else if (indexKeyType == "NONE") //TODO check is NONE
{
indexType = DatabaseConstant.IndexType.None;
}
else
{
//continue;
throw new Exception("IndexType " + indexKeyType + " Not Defined");
}
List<DataRow> indexColumnRows = new List<DataRow>();// = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, indexRow["CONSTRAINT_NAME"]));
indexColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema, tableName, indexRow["COLUMN_NAME"])));
while ((i < indexRows.Length - 1) && (string)indexRows[i + 1]["TABLE_NAME"] == tableName && (string)indexRows[i + 1]["CONSTRAINT_NAME"] == (string)indexRow["CONSTRAINT_NAME"])
{
i++;
indexRow = indexRows[i];
indexColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema, tableName, indexRow["COLUMN_NAME"])));
}
bool isUnique = (int)indexRow["IS_UNIQUE"] == 1 ? true : false;
bool isClustered = (int)indexRow["IS_CLUSTERED"] == 1 ? true : false;
Index index = new Index(indexRow["CONSTRAINT_NAME"].ToString(), false, indexType, table, isUnique, isClustered);
// Fill Columns
foreach (DataRow indexColumnRow in indexColumnRows)
{
Column indexColumn = new Column(indexColumnRow["COLUMN_NAME"].ToString(), false);
index.AddColumn(indexColumn);
}
index.ResetDefaults();
table.AddIndex(index);
}
// Indexes -- that should be keys
DataRow[] keyRows = Indexes.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'", schema, tableName));
//foreach (DataRow keyRow in indexRows)
for (int i = 0; i < keyRows.Length; i++)
{
DataRow keyRow = keyRows[i];
string keyType;
string indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString();
if (indexKeyType == "PRIMARY KEY")
{
keyType = DatabaseConstant.KeyType.Primary;
}
else if (indexKeyType == "FOREIGN KEY")
{
keyType = DatabaseConstant.KeyType.Foreign;
}
else if (indexKeyType == "UNIQUE")
{
keyType = DatabaseConstant.KeyType.Unique;
}
else
{
continue;
}
Key key = new Key(keyRow["CONSTRAINT_NAME"].ToString(), false, keyType, table, false);
// Fill Columns
List<DataRow> keyColumnRows = new List<DataRow>();
keyColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema, tableName, keyRow["COLUMN_NAME"])));
while ((i < keyRows.Length - 1) && (string)keyRows[i + 1]["TABLE_NAME"] == tableName && (string)keyRows[i + 1]["CONSTRAINT_NAME"] == (string)keyRow["CONSTRAINT_NAME"])
{
i++;
keyRow = keyRows[i];
keyColumnRows.AddRange(Columns.Select(string.Format("TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}'", schema, tableName, keyRow["COLUMN_NAME"])));
}
// Fill Columns
foreach (DataRow keyColumnRow in keyColumnRows)
{
Column keyColumn = new Column(keyColumnRow["COLUMN_NAME"].ToString(), false);
keyColumn.DataType = (string)keyColumnRow["DATA_TYPE"];
key.AddColumn(keyColumn);
}
if (keyType == DatabaseConstant.KeyType.Foreign)
{
DataRow[] keyReferencedColumnRows = IndexReferencedColumns.Select(string.Format("ForeignKeySchema = '{0}' AND ForeignKeyTable = '{1}' AND ForeignKey = '{2}'", schema, tableName, keyRow["CONSTRAINT_NAME"]));
DataRow firstKeyReferencedColumnRow = keyReferencedColumnRows[0];
// Fill References
key.ReferencedTable = new Model.Table(firstKeyReferencedColumnRow["ReferencedTable"].ToString(), false) { Schema = firstKeyReferencedColumnRow["ReferencedSchema"].ToString() };
key.ReferencedKey = new Key(firstKeyReferencedColumnRow["ReferencedKey"].ToString(), false, true);
// Fill Referenced Columns
foreach (DataRow keyReferencedColumnRow in keyReferencedColumnRows)
{
Column keyReferencedColumn = new Column(keyReferencedColumnRow["ReferencedColumn"].ToString(), false);
key.AddReferencedColumn(keyReferencedColumn);
}
}
key.ResetDefaults();
table.AddKey(key);
}
#endregion
return table;
}
private Model.Table GetNewTable(SQLDMO.Table dmoTable)
{
Model.Table table = new Model.Table(dmoTable.Name, Script.GetSingluar(dmoTable.Name), false);
// Columns
int ordinalPosition = 0;
List<SQLDMO.Column> dmoColumns = new List<SQLDMO.Column>();
foreach (SQLDMO.Column dmoColumn in dmoTable.Columns)
{
dmoColumns.Add(dmoColumn);
}
foreach (SQLDMO.Column dmoColumn in dmoColumns)
{
if (UnsupportedDataTypes.ToLower().IndexOf("'" + dmoColumn.PhysicalDatatype.ToLower() + "'") >= 0)
{
continue;
}
Column column = new Column(dmoColumn.Name, Script.GetSingluar(dmoColumn.Name), false, dmoColumn.Name, table, ordinalPosition, dmoColumn.AllowNulls, dmoColumn.PhysicalDatatype, dmoColumn.Length,
dmoColumn.InPrimaryKey, dmoColumn.Identity, dmoColumn.Default, dmoColumn.IsComputed);
table.AddColumn(column);
ordinalPosition++;
}
// Index
foreach (SQLDMO.Index dmoIndex in dmoTable.Indexes)
{
string indexType;
if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIPrimaryKey)
{
continue;
}
if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIUniqueKey)
{
continue;
}
else
{
continue;
//throw new Exception("IndexType " + dmoIndex.Type + " Not Defined");
}
// Create Alias
string indexAlias = indexType + "_";
SQLDMO.SQLObjectList indexes = dmoIndex.ListIndexedColumns();
for (int i = 1; i <= indexes.Count; i++)
{
SQLDMO._Column dmoColumn = (SQLDMO._Column)indexes.Item(i);
indexAlias += dmoColumn.Name;
if (i < indexes.Count)
{
indexAlias += "And";
}
}
Index index = new Index(dmoIndex.Name, Script.GetSingluar(indexAlias), false, indexType, table);
// Fill Columns
for (int i = 1; i <= indexes.Count; i++)
{
SQLDMO._Column dmoColumn = (SQLDMO._Column)indexes.Item(i);
Column indexColumn = new Column(dmoColumn.Name, Script.GetSingluar(dmoColumn.Name), false);
index.AddColumn(indexColumn);
}
table.AddIndex(index);
}
// Indexes -- that should be keys
foreach (SQLDMO.Index dmoIndex in dmoTable.Indexes)
{
string keyType;
if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIPrimaryKey)
{
keyType = DatabaseConstant.KeyType.Primary;
}
if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIUniqueKey)
{
keyType = DatabaseConstant.KeyType.Unique;
}
else
{
continue;
//throw new Exception("KeyType " + dmoIndex.Type + " Not Defined");
}
// Create Alias
string keyAlias = keyType + "_";
SQLDMO.SQLObjectList indexes = dmoIndex.ListIndexedColumns();
for (int i = 1; i <= indexes.Count; i++)
{
SQLDMO._Column dmoColumn = dmoTable.Columns.Item(indexes.Item(i));
keyAlias += dmoColumn.Name;
if (i < indexes.Count)
{
keyAlias += "And";
}
}
Key key = new Key(dmoIndex.Name, Script.GetSingluar(keyAlias), false, keyType, table);
// Fill Columns
for (int i = 1; i <= indexes.Count; i++)
{
Column keyColumn = new Column(indexes.Item(i).ToString(), Script.GetSingluar(indexes.Item(i).ToString()), false);
key.AddColumn(keyColumn);
}
table.AddKey(key);
}
// Keys
foreach (SQLDMO.Key dmoKey in dmoTable.Keys)
{
string keyType;
if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary)
{
keyType = DatabaseConstant.KeyType.Primary;
}
else if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Foreign)
{
keyType = DatabaseConstant.KeyType.Foreign;
}
else if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Unique)
{
keyType = DatabaseConstant.KeyType.Unique;
}
else if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Unknown)
{
continue;
}
else
{
throw new Exception("KeyType " + dmoKey.Type.ToString() + " Not Defined");
}
// Create Alias
string keyAlias = keyType + "_";
for (int i = 1; i <= dmoKey.KeyColumns.Count; i++)
{
SQLDMO._Column dmoColumn = dmoTable.Columns.Item(dmoKey.KeyColumns.Item(i));
keyAlias += dmoColumn.Name;
if (i < dmoKey.KeyColumns.Count)
{
keyAlias += "And";
}
}
Key key = new Key(dmoKey.Name, Script.GetSingluar(keyAlias), false, keyType, table);
// Fill Columns
for (int i = 1; i <= dmoKey.KeyColumns.Count; i++)
{
Column keyColumn = new Column(dmoKey.KeyColumns.Item(i), Script.GetSingluar(dmoKey.KeyColumns.Item(i)), false);
key.AddColumn(keyColumn);
}
if (keyType == DatabaseConstant.KeyType.Foreign)
{
// Fill References
key.ReferencedTable = new Model.Table(dmoKey.ReferencedTable, Script.GetSingluar(dmoKey.ReferencedTable), false);
if (dmoKey.ReferencedKey != null)
{
key.ReferencedKey = new Key(dmoKey.ReferencedKey, Script.GetSingluar(dmoKey.ReferencedKey), false);
}
// Fill Referenced Columns
for (int i = 1; i <= dmoKey.KeyColumns.Count; i++)
{
Column referencedKeyColumn = new Column(dmoKey.ReferencedColumns.Item(i), Script.GetSingluar(dmoKey.ReferencedColumns.Item(i)), false);
key.AddReferencedColumn(referencedKeyColumn);
}
}
table.AddKey(key);
}
return table;
}
private Model.Table GetNewTable(Microsoft.SqlServer.Management.Smo.Table smoTable)
{
Model.Table table = new Model.Table(smoTable.Name, Script.GetSingluar(smoTable.Name), false);
#region Columns
int ordinalPosition = 0;
List<Microsoft.SqlServer.Management.Smo.Column> smoColumns = new List<Microsoft.SqlServer.Management.Smo.Column>();
foreach (Microsoft.SqlServer.Management.Smo.Column smoColumn in smoTable.Columns)
{
smoColumns.Add(smoColumn);
}
foreach (Microsoft.SqlServer.Management.Smo.Column smoColumn in smoColumns)
{
if (UnsupportedDataTypes.ToLower().IndexOf("'" + smoColumn.DataType.Name.ToLower() + "'") >= 0)
{
continue;
}
// Some columns do not have default values
string defaultValue = null;
try
{
defaultValue = smoColumn.Default;
}
catch { }
Column column = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), false, smoColumn.Name, table, ordinalPosition, smoColumn.Nullable, smoColumn.DataType.Name,
smoColumn.DataType.MaximumLength, smoColumn.InPrimaryKey, smoColumn.Identity, defaultValue, smoColumn.Computed);
table.AddColumn(column);
ordinalPosition++;
}
#endregion
#region Indexes
//foreach (Microsoft.SqlServer.Management.Smo.Index smoIndex in smoTable.Indexes)
//{
// string indexType;
// string indexKeyType = smoIndex.IndexKeyType.ToString();
// if (indexKeyType == "DriPrimaryKey")
// {
// continue;
// }
// else if (indexKeyType == "DriUniqueKey")
// {
// continue;
// }
// else if (indexKeyType == "None")
// {
// continue;
// //indexType = DatabaseConstant.IndexType.None;
// }
// else
// {
// throw new Exception("IndexType " + indexKeyType + " Not Defined");
// }
// // Create Alias
// string indexAlias = indexType + "_";
// for (int i = 0; i <= smoIndex.IndexedColumns.Count - 1; i++)
// {
// Microsoft.SqlServer.Management.Smo.IndexedColumn smoIndexedColumn = smoIndex.IndexedColumns[i];
// indexAlias += smoIndexedColumn.Name;
// if (i < smoIndex.IndexedColumns.Count - 1)
// {
// indexAlias += "And";
// }
// }
// Index index = new Index(smoIndex.Name, Script.GetSingluar(indexAlias), false, indexType, table);
// // Fill Columns
// foreach (Microsoft.SqlServer.Management.Smo.IndexedColumn smoColumn in smoIndex.IndexedColumns)
// {
// Column indexColumn = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), false);
// index.AddColumn(indexColumn);
// }
// table.AddIndex(index);
//}
// Indexes -- that should be keys
foreach (Microsoft.SqlServer.Management.Smo.Index smoIndex in smoTable.Indexes)
{
string keyType;
string indexKeyType = smoIndex.IndexKeyType.ToString();
if (indexKeyType == "DriPrimaryKey")
{
keyType = DatabaseConstant.KeyType.Primary;
}
else if (indexKeyType == "DriUniqueKey")
{
keyType = DatabaseConstant.KeyType.Unique;
}
else if (indexKeyType == "None")
{
keyType = DatabaseConstant.KeyType.None;
continue;
}
else
{
throw new Exception("KeyType " + indexKeyType + " Not Defined");
}
// Create Alias
string keyAlias = keyType + "_";
for (int i = 0; i <= smoIndex.IndexedColumns.Count - 1; i++)
{
Microsoft.SqlServer.Management.Smo.Column smoColumn = smoTable.Columns[smoIndex.IndexedColumns[i].Name];
keyAlias += smoColumn.Name;
if (i < smoIndex.IndexedColumns.Count - 1)
{
keyAlias += "And";
}
}
Key key = new Key(smoIndex.Name, Script.GetSingluar(keyAlias), false, keyType, table);
// Fill Columns
foreach (Microsoft.SqlServer.Management.Smo.IndexedColumn smoColumn in smoIndex.IndexedColumns)
{
Column keyColumn = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), false);
key.AddColumn(keyColumn);
}
table.AddKey(key);
}
#endregion
#region Keys
foreach (Microsoft.SqlServer.Management.Smo.ForeignKey smoForeignKey in smoTable.ForeignKeys)
{
string keyType = DatabaseConstant.KeyType.Foreign;
// Create Alias
string keyAlias = keyType + "_";
for (int i = 0; i <= smoForeignKey.Columns.Count - 1; i++)
{
Microsoft.SqlServer.Management.Smo.Column smoColumn = smoTable.Columns[smoForeignKey.Columns[i].Name];
keyAlias += smoColumn.Name;
if (i < smoForeignKey.Columns.Count - 1)
{
keyAlias += "And";
}
}
Key key = new Key(smoForeignKey.Name, Script.GetSingluar(keyAlias), false, keyType, table);
// Fill Columns
foreach (Microsoft.SqlServer.Management.Smo.ForeignKeyColumn smoColumn in smoForeignKey.Columns)
{
Column keyColumn = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), false);
key.AddColumn(keyColumn);
}
// Fill References
key.ReferencedTable = new Model.Table(smoForeignKey.ReferencedTable, Script.GetSingluar(smoForeignKey.ReferencedTable), false);
key.ReferencedKey = new Key(smoForeignKey.ReferencedKey, Script.GetSingluar(smoForeignKey.ReferencedKey), false);
// Fill Referenced Columns
foreach (Microsoft.SqlServer.Management.Smo.ForeignKeyColumn smoColumn in smoForeignKey.Columns)
{
Column referencedKeyColumn = new Column(smoColumn.ReferencedColumn, Script.GetSingluar(smoColumn.ReferencedColumn), false);
key.AddReferencedColumn(referencedKeyColumn);
}
table.AddKey(key);
}
#endregion
return table;
}
