public void generateStoredProcedures(string constr, string outputDir, string namesp)
{
//try
//{
// Directory.CreateDirectory(outputDir + "\\StoredProcedures\\");
//}
//catch { }
DataAccess cls = new DataAccess();
DataAccess.strConn = constr;
DataSet dsTables = new DataSet();
string sql = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE' and table_name <> 'sysdiagrams'";
string m = cls.getData(sql, ref dsTables);
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
string classStr = "";
sql = "select * from information_schema.columns where table_name = '" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "'";
DataSet ds = new DataSet();
m = cls.getData(sql, ref ds);
classStr += getMethods(ds, dsTables.Tables[0].Rows[i]["Name"].ToString());
classStr += "";
TextWriter tw = new StreamWriter(outputDir + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + ".sql");
tw.WriteLine(classStr);
tw.Close();
}
}
public void generateRepositories(string constr, string outputDir, string namesp)
{
//try
//{
// Directory.CreateDirectory(outputDir + "\\Repository\\");
//}
//catch { }
DataAccess cls = new DataAccess();
DataAccess.strConn = constr;
DataSet dsTables = new DataSet();
string sql = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE' and table_name <> 'sysdiagrams'";
string m = cls.getData(sql, ref dsTables);
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
string classStr = "";
classStr += getUsingSection(namesp);
classStr += getHeader(namesp, dsTables.Tables[0].Rows[i]["Name"].ToString() + "_Repository");
sql = "select * from information_schema.columns where table_name = '" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "'";
DataSet ds = new DataSet();
m = cls.getData(sql, ref ds);
classStr += getMethods(ds, dsTables.Tables[0].Rows[i]["Name"].ToString());
classStr += "\n }\n}";
TextWriter tw = new StreamWriter(outputDir + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "_Repository" + ".cs");
tw.WriteLine(classStr);
tw.Close();
}
}
public void generateReports(string constr, string outputDir, string namesp)
{
//try
//{
// Directory.CreateDirectory(outputDir + "\\Reports\\");
//}
//catch { }
DataAccess cls = new DataAccess();
DataAccess.strConn = constr;
DataSet dsTables = new DataSet();
string sql = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE' and table_name <> 'sysdiagrams'";
string m = cls.getData(sql, ref dsTables);
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
string classStr = "";
classStr += getMainPage(namesp, dsTables.Tables[0].Rows[i]["Name"].ToString());
TextWriter tw = new StreamWriter(outputDir + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "Report.xaml");
tw.WriteLine(classStr);
tw.Close();
}
}
public string getMethods(DataSet dsTableDefenation, string TableName)
{
string Methods = " #region Methods\n{0}\n #endregion";
string str = getInserMethod(dsTableDefenation, TableName);
str += "\n\n" + getUpdateMethod(dsTableDefenation, TableName);
str += "\n\n" + getDeleteMethod(dsTableDefenation, TableName);
str += "\n\n" + getFindByIdMethod(dsTableDefenation, TableName);
str += "\n\n" + getFindAllMethod(dsTableDefenation, TableName);
string sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
where FK.TABLE_NAME= '" + TableName + "' ORDER BY 1,2,3,4";
DataSet dsFK = new DataSet();
DataAccess cls = new DataAccess();
string m = cls.getData(sql, ref dsFK);
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
if (dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Updated_By")
{
str += "\n\n" + getFindByMethod(dsTableDefenation, TableName, dsFK.Tables[0].Rows[j]["FK_Column"].ToString());
}
}
Methods = System.String.Format(Methods, str);
return(Methods);
}
public string getFK_Method(string tableName)
{
string str = "";
string sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
where FK.TABLE_NAME= '" + tableName + "' ORDER BY 1,2,3,4";
DataSet ds = new DataSet();
DataAccess cls = new DataAccess();
string m = cls.getData(sql, ref ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
str += " public IEnumerable<" + ds.Tables[0].Rows[i]["PK_Table"].ToString() + "> get" + ds.Tables[0].Rows[i]["PK_Table"].ToString() + "()\n";
str += " {\n";
str += " List<" + ds.Tables[0].Rows[i]["PK_Table"].ToString() + "> entities = new List<" + ds.Tables[0].Rows[i]["PK_Table"].ToString() + ">();\n";
str += " " + ds.Tables[0].Rows[i]["PK_Table"].ToString() + "_Repository entity_rep = new " + ds.Tables[0].Rows[i]["PK_Table"].ToString() + "_Repository();\n";
str += " entities = entity_rep.FindAll();\n";
str += " return entities; \n";
str += " }\n";
str += "\n\n";
}
return(str);
}
public void generateView(string constr, string outputDir, string namesp)
{
//try
//{
// Directory.CreateDirectory(outputDir + "\\Views\\");
//}
//catch { }
DataAccess cls = new DataAccess();
DataAccess.strConn = constr;
DataSet dsTables = new DataSet();
string sql = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE' and table_name <> 'sysdiagrams'";
string m = cls.getData(sql, ref dsTables);
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
string classStr = "";
classStr += getMainPage(namesp, dsTables.Tables[0].Rows[i]["Name"].ToString());
TextWriter tw = new StreamWriter(outputDir + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "Page.xaml");
tw.WriteLine(classStr);
tw.Close();
}
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
string classStr = "";
classStr += getCobeBehindMainPage(namesp, dsTables.Tables[0].Rows[i]["Name"].ToString());
TextWriter tw = new StreamWriter(outputDir + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "Page.xaml.cs");
tw.WriteLine(classStr);
tw.Close();
}
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
string classStr = "";
classStr += getAddEdit(namesp, dsTables.Tables[0].Rows[i]["Name"].ToString());
TextWriter tw = new StreamWriter(outputDir + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "AddEdit.xaml");
tw.WriteLine(classStr);
tw.Close();
}
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
string classStr = "";
classStr += getCobeBehindAddEdit(namesp, dsTables.Tables[0].Rows[i]["Name"].ToString());
TextWriter tw = new StreamWriter(outputDir + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "AddEdit.xaml.cs");
tw.WriteLine(classStr);
tw.Close();
}
}
public void generateDir(string constr, string outputDir, string namesp)
{
DataAccess cls = new DataAccess();
DataAccess.strConn = constr;
DataSet dsTables = new DataSet();
string sql = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE' and table_name <> 'sysdiagrams'";
string m = cls.getData(sql, ref dsTables);
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
string classStr = "";
try
{
Directory.CreateDirectory(outputDir + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\\");
}
catch { }
}
}
public void generateDir(string constr, string outputDir, string namesp)
{
DataAccess cls = new DataAccess();
DataAccess.strConn = constr;
DataSet dsTables = new DataSet();
string sql = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE' and table_name <> 'sysdiagrams'";
string m = cls.getData(sql, ref dsTables);
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
string classStr = "";
try
{
Directory.CreateDirectory(outputDir + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\\");
}
catch { }
}
}
public void generateDomainServices(string constr, string outputDir, string namesp)
{
try
{
Directory.CreateDirectory(outputDir + "\\Services\\");
}
catch { }
DataAccess cls = new DataAccess();
DataAccess.strConn = constr;
DataSet dsTables = new DataSet();
string sql = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE' and table_name <> 'sysdiagrams'";
string m = cls.getData(sql, ref dsTables);
string classStr = "";
classStr += getHeader(namesp, "GloblDomainService");
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
//sql = "select * from information_schema.columns where table_name = '" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "'";
//DataSet ds = new DataSet();
//m = cls.getData(sql, ref ds);
classStr += " #region " + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\n";
classStr += getMethods(dsTables.Tables[0].Rows[i]["Name"].ToString());
classStr += " #endregion\n\n";
}
classStr += "\n }\n}";
TextWriter tw = new StreamWriter(outputDir + "\\Services\\GloblDomainService" + ".cs");
tw.WriteLine(classStr);
tw.Close();
}
public void generateDomainServices(string constr, string outputDir, string namesp)
{
try
{
Directory.CreateDirectory(outputDir + "\\Services\\");
}
catch { }
DataAccess cls = new DataAccess();
DataAccess.strConn = constr;
DataSet dsTables = new DataSet();
string sql = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE' and table_name <> 'sysdiagrams'";
string m = cls.getData(sql, ref dsTables);
string classStr = "";
classStr += getHeader(namesp, "GloblDomainService");
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
//sql = "select * from information_schema.columns where table_name = '" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "'";
//DataSet ds = new DataSet();
//m = cls.getData(sql, ref ds);
classStr += " #region " + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\n";
classStr += getMethods(dsTables.Tables[0].Rows[i]["Name"].ToString());
classStr += " #endregion\n\n";
}
classStr += "\n }\n}";
TextWriter tw = new StreamWriter(outputDir + "\\Services\\GloblDomainService" + ".cs");
tw.WriteLine(classStr);
tw.Close();
}
private string getCobeBehindMainPage(string namesp, string tableName)
{
string str = "";
DataAccess cls = new DataAccess();
string sql = "select * from information_schema.columns where table_name = '" + tableName + "'";
DataSet dsColumns = new DataSet();
string m = cls.getData(sql, ref dsColumns);
//=================================================
string adds = tableName.ElementAt(tableName.Length - 1) + "s";
if (tableName.ElementAt(tableName.Length - 1) == 'y')
{
adds = "ies";
}
if (tableName.ElementAt(tableName.Length - 1) == 's')
{
adds = "s";
}
str += "using System;\n";
str += "using System.Collections.Generic;\n";
str += "using System.Linq;\n";
str += "using System.Net;\n";
str += "using System.Windows;\n";
str += "using System.Windows.Controls;\n";
str += "using System.Windows.Documents;\n";
str += "using System.Windows.Input;\n";
str += "using System.Windows.Media;\n";
str += "using System.Windows.Media.Animation;\n";
str += "using System.Windows.Shapes;\n";
str += "using System.Windows.Navigation;\n";
str += "using SilverlightReport;\n";
str += "using " + namesp + ".ViewModel;\n";
str += "using " + namesp + ".Entities;\n";
str += "using System.Windows.Data;\n";
str += "using System.Reflection;\n\n\n\n";
str += "namespace " + namesp + ".Views\n";
str += "{\n";
str += " public partial class " + tableName + "Page : Page\n";
str += " {\n";
str += " public " + tableName + "ViewModel emv { set; get; }\n\n";
str += " public " + tableName + "Page()\n";
str += " {\n";
str += " InitializeComponent();\n";
str += " emv = new " + tableName + "ViewModel();\n";
str += " this.DataContext = emv;\n";
str += " MainPage m = (MainPage)Application.Current.RootVisual;\n";
str += " if (m.cmboxBoxLang.SelectedIndex == 1)\n";
str += " {\n";
str += " Helpers.LanguageTool.TranslateChildWindow(this, \"ar_JO\");\n";
str += " }\n";
str += " else\n";
str += " {\n";
str += " Helpers.LanguageTool.TranslateChildWindow(this, \"en_US\");\n";
str += " }\n";
str += "\n";
str += " }\n";
str += "\n";
str += " // Executes when the user navigates to this page.\n";
str += " protected override void OnNavigatedTo(NavigationEventArgs e)\n";
str += " {\n";
str += " }\n";
str += " private void txtSearch_KeyUp(object sender, KeyEventArgs e)\n";
str += " {\n";
str += " if (e.Key == Key.Enter)\n";
str += " {\n";
str += " Predicate<object> predicate = null;\n";
str += " predicate = p => (Helpers.FullTextSearch.Matches(txtSearch.Text.ToLower().Split(' '), p));\n";
str += " PagedCollectionView view = new PagedCollectionView(emv." + tableName.Substring(0, tableName.Length - 1) + adds + ");\n";
str += " view.Filter = null;\n";
str += " view.Filter = predicate;\n";
str += " grd" + tableName + ".ItemsSource = view;\n";
str += " }\n";
str += " }\n";
str += "\n";
str += "\n";
str += " private Report report;\n";
str += " private void btnPrint_Click(object sender, RoutedEventArgs e)\n";
str += " {\n";
str += " string name = \"" + tableName + "Report.xaml\";\n";
str += " string fullName = string.Format(\"/" + namesp + ";component/Reports/{0}\", name);\n";
str += " this.report = Report.LoadFromXaml(fullName);\n";
//str += " this.report.ItemsSource = emv." + tableName.Substring(0, tableName.Length - 1) + adds + ";\n";
str += " //==============================================================\n";
str += " List<Entities." + tableName + "> entities = new List<Entities." + tableName + ">();\n";
str += " for (int i = 0; i < " + tableName + "Pager.PageCount; i++)\n";
str += " {\n";
str += " " + tableName + "Pager.PageIndex = i;\n";
str += " foreach (Entities." + tableName + " data in " + tableName + "Pager.Source)\n";
str += " {\n";
str += " entities.Add(data);\n";
str += " }\n";
str += " }\n";
str += " this.report.ItemsSource = entities;\n";
str += " //==============================================================\n";
str += "\n";
str += " Button btn = sender as Button;\n";
str += " if (btn.Name == \"btnPrint\")\n";
str += " {\n";
str += " this.report.Print();\n";
str += " }\n";
str += " else\n";
str += " {\n";
str += " this.report.Preview();\n";
str += " }\n";
str += " }\n";
str += " }\n";
str += "}\n";
return str;
}
public string getMethods(DataSet dsTableDefenation, string TableName)
{
string Methods = "{0}";
string sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
where FK.TABLE_NAME= '" + TableName + "' ORDER BY 1,2,3,4";
DataSet dsFK = new DataSet();
DataAccess cls = new DataAccess();
string m = cls.getData(sql, ref dsFK);
string str = "/****** Object: StoredProcedure [dbo].[sp_Add_" + TableName + "] Script Date: " + DateTime.Now + " ******/\n";
str += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Add_" + TableName + "]') AND type in (N'P', N'PC'))\n";
str += "DROP PROCEDURE [dbo].[sp_Add_" + TableName + "]\n";
str += "Go\n\n";
str += "/****** Object: StoredProcedure [dbo].[sp_Update_" + TableName + "] Script Date: " + DateTime.Now + " ******/\n";
str += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Update_" + TableName + "]') AND type in (N'P', N'PC'))\n";
str += "DROP PROCEDURE [dbo].[sp_Update_" + TableName + "]\n";
str += "Go\n\n";
str += "/****** Object: StoredProcedure [dbo].[sp_Delete_" + TableName + "] Script Date: " + DateTime.Now + " ******/\n";
str += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Delete_" + TableName + "]') AND type in (N'P', N'PC'))\n";
str += "DROP PROCEDURE [dbo].[sp_Delete_" + TableName + "]\n";
str += "Go\n\n";
str += "/****** Object: StoredProcedure [dbo].[sp_GetById_" + TableName + "] Script Date: " + DateTime.Now + " ******/\n";
str += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetById_" + TableName + "]') AND type in (N'P', N'PC'))\n";
str += "DROP PROCEDURE [dbo].[sp_GetById_" + TableName + "]\n";
str += "Go\n\n";
str += "/****** Object: StoredProcedure [dbo].[sp_FindAll_" + TableName + "] Script Date: " + DateTime.Now + " ******/\n";
str += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_FindAll_" + TableName + "]') AND type in (N'P', N'PC'))\n";
str += "DROP PROCEDURE [dbo].[sp_FindAll_" + TableName + "]\n";
str += "Go\n\n";
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
str += "/****** Object: StoredProcedure [dbo].[sp_GetBy" + "_" + TableName + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "] Script Date: " + DateTime.Now + " ******/\n";
str += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetBy" + "_" + TableName + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "]') AND type in (N'P', N'PC'))\n";
str += "DROP PROCEDURE [dbo].[sp_GetBy" + "_" + TableName + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "]\n";
str += "Go\n\n";
}
str += "\n\n--****** Object: StoredProcedure [dbo].[sp_Add_" + TableName + "] Script Date: " + DateTime.Now + " ******\n\n";
str += InsertStatment(dsTableDefenation, TableName);
str += "\n\n--****** Object: StoredProcedure [dbo].[sp_Update_" + TableName + "] Script Date: " + DateTime.Now + " ******\n\n";
str += "\n\n" + UpdateStatment(dsTableDefenation, TableName);
str += "\n\n--****** Object: StoredProcedure [dbo].[sp_Delete_" + TableName + "] Script Date: " + DateTime.Now + " ******\n\n";
str += "\n\n" + DeleteStatment(dsTableDefenation, TableName);
str += "\n\n--****** Object: StoredProcedure [dbo].[sp_GetById_" + TableName + "] Script Date: " + DateTime.Now + " ******\n\n";
str += "\n\n" + GetStatmentById(dsTableDefenation, TableName);
str += "\n\n--****** Object: StoredProcedure [dbo].[sp_FindAll_" + TableName + "] Script Date: " + DateTime.Now + " ******\n\n";
str += "\n\n" + GetStatmenFindAll(dsTableDefenation, TableName);
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
if (dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Updated_By")
{
str += "\n\n--****** Object: StoredProcedure [dbo].[sp_GetBy" + "_" + TableName + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "] Script Date: " + DateTime.Now + " ******\n\n";
str += "\n\n" + GetStatmentByFK_Column(TableName, dsFK.Tables[0].Rows[j]["FK_Column"].ToString());
}
}
Methods = System.String.Format(Methods, str);
return(Methods);
}
private string getMainPage(string namesp, string tableName)
{
string str = "";
DataAccess cls = new DataAccess();
string sql = "select * from information_schema.columns where table_name = '" + tableName + "'";
DataSet dsColumns = new DataSet();
string m = cls.getData(sql, ref dsColumns);
//=================================================
string adds = tableName.ElementAt(tableName.Length - 1) + "s";
if (tableName.ElementAt(tableName.Length - 1) == 'y')
{
adds = "ies";
}
if (tableName.ElementAt(tableName.Length - 1) == 's')
{
adds = "s";
}
//========================================================
str += "<sr:Report\n";
str += " xmlns=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation\" \n";
str += " xmlns:x=\"http://schemas.microsoft.com/winfx/2006/xaml\"\n";
str += " xmlns:sr=\"clr-namespace:SilverlightReport;assembly=SilverlightReport\">\n";
str += " <sr:ReportBand Kind=\"ReportHeader\">\n";
str += " <TextBlock FontSize=\"20\" FontWeight=\"Bold\" Margin=\"10\" HorizontalAlignment=\"Center\">\n";
str += " " + tableName.Replace("_", " ").Substring(0, tableName.Length - 1) + adds + " List</TextBlock>\n";
str += " </sr:ReportBand>\n";
str += " <sr:Report.DataGrid>\n";
str += " <sr:CDataGrid HeaderHorizontalAlignment=\"Center\">\n";
for (int j = 0; j < dsColumns.Tables[0].Rows.Count; j++)
{
if (dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Contains("Is_Deleted") || dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Contains("Date_Created") || dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Contains("Date_Updated"))
{
continue;
}
sql = @"SELECT FK.TABLE_NAME AS FK_Table, CU.COLUMN_NAME AS FK_Column, PK.TABLE_NAME AS PK_Table, PT.COLUMN_NAME AS PK_Column,
C.CONSTRAINT_NAME AS Constraint_Name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN
(SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS i1 INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE (i1.CONSTRAINT_TYPE = 'PRIMARY KEY')) AS PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE (FK.TABLE_NAME = '" + tableName + "') AND (CU.COLUMN_NAME = '" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + "') ORDER BY FK_Table, FK_Column, PK_Table, PK_Column";
DataSet dsFK = new DataSet();
m = cls.getData(sql, ref dsFK);
string Data_Type = "";
if (dsColumns.Tables[0].Rows[j]["Data_Type"] != DBNull.Value)
{
Data_Type = dsColumns.Tables[0].Rows[j]["Data_Type"].ToString();
}
string widthCol = "100";
switch (Data_Type)
{
case "int": widthCol = "0.5"; break;
case "datetime": widthCol = "1.3"; break;
case "bit": widthCol = "0.5"; break;
case "real": widthCol = "0.5"; break;
case "money": widthCol = "0.5"; break;
case "float": widthCol = "0.5"; break;
default: widthCol = "2.2"; break;
}
if (dsFK.Tables[0].Rows.Count == 0)
{
str += " <sr:CDataGridColumn Header=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + "\" Binding=\"{Binding " + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + "}\" Width=\"" + widthCol + "\"></sr:CDataGridColumn>\n";
}
else
{
if (dsFK.Tables[0].Rows[0]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[0]["FK_Column"].ToString() != "Updated_By")
{
str += " <sr:CDataGridColumn Header=\"" + dsFK.Tables[0].Rows[0]["FK_Column"].ToString().Replace("_", " ").Replace("Id", "") + "\" Binding=\"{Binding " + dsFK.Tables[0].Rows[0]["Constraint_Name"].ToString() + ".Name" + "}\" Width=\"" + widthCol + "\"></sr:CDataGridColumn>\n";
}
else
{
//str += " <sr:CDataGridColumn Header=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + "\" Binding=\"{Binding " + dsFK.Tables[0].Rows[0]["PK_Table"].ToString().Replace("_", " ") + "_" + dsFK.Tables[0].Rows[0]["FK_Column"].ToString() + ".Name" + "}\" Width=\"" + widthCol + "\"></sr:CDataGridColumn>\n";
}
}
}
str += " </sr:CDataGrid>\n";
str += " </sr:Report.DataGrid>\n";
str += "</sr:Report>\n";
return(str);
}
public string getMethods(DataSet dsTableDefenation, string TableName)
{
string Methods = " #region Methods\n{0}\n #endregion";
string str = getInserMethod(dsTableDefenation, TableName);
str += "\n\n" + getUpdateMethod(dsTableDefenation, TableName);
str += "\n\n" + getDeleteMethod(dsTableDefenation, TableName);
str += "\n\n" + getFindByIdMethod(dsTableDefenation, TableName);
str += "\n\n" + getFindAllMethod(dsTableDefenation, TableName);
string sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
where FK.TABLE_NAME= '" + TableName + "' ORDER BY 1,2,3,4";
DataSet dsFK = new DataSet();
DataAccess cls = new DataAccess();
string m = cls.getData(sql, ref dsFK);
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
if (dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Updated_By")
{
str += "\n\n" + getFindByMethod(dsTableDefenation, TableName, dsFK.Tables[0].Rows[j]["FK_Column"].ToString());
}
}
Methods = System.String.Format(Methods, str);
return Methods;
}
public string getProperties(DataSet dsTableDefenation, string TableName)
{
string Statment = "\n";
Statment += " #region Properties\n";
Statment += "\n";
Statment += " public event PropertyChangedEventHandler PropertyChanged;\n";
Statment += " private void RaisePropertyChanged(string propertyname)\n";
Statment += " {\n";
Statment += " if (PropertyChanged != null)\n";
Statment += " {\n";
Statment += " try\n";
Statment += " {\n";
Statment += " PropertyChanged(this, new PropertyChangedEventArgs(propertyname));\n";
Statment += " }\n";
Statment += " catch { }\n";
Statment += " }\n";
Statment += " }\n\n";
Statment += " private bool _isBusy;\n";
Statment += " public bool IsBusy\n";
Statment += " {\n";
Statment += " get\n";
Statment += " {\n";
Statment += " return _isBusy;\n";
Statment += " }\n";
Statment += " set\n";
Statment += " {\n";
Statment += " _isBusy = value;\n";
Statment += " RaisePropertyChanged(\"IsBusy\");\n";
Statment += " }\n";
Statment += " }\n\n";
string sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
where FK.TABLE_NAME= '" + TableName + "' ORDER BY 1,2,3,4";
DataSet dsFK = new DataSet();
DataAccess cls = new DataAccess();
string m = cls.getData(sql, ref dsFK);
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
if (dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Updated_By")
{
string PK_Table = dsFK.Tables[0].Rows[j]["PK_Table"].ToString();
string adds = PK_Table.ElementAt(PK_Table.Length - 1) + "s";
if (PK_Table.ElementAt(PK_Table.Length - 1) == 'y')
{
adds = "ies";
}
if (PK_Table.ElementAt(PK_Table.Length - 1) == 's')
{
adds = "s";
}
Statment += " public ObservableCollection<" + PK_Table + "> _" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + ";\n";
Statment += " public ObservableCollection<" + PK_Table + "> " + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + "\n";
Statment += " {\n";
Statment += " get\n";
Statment += " {\n";
Statment += " return _" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + ";\n";
Statment += " }\n";
Statment += " }\n\n";
}
else
{
string PK_Table = dsFK.Tables[0].Rows[j]["PK_Table"].ToString();
string FK_Column = dsFK.Tables[0].Rows[j]["FK_Column"].ToString();
Statment += " public ObservableCollection<" + PK_Table + "> _" + FK_Column.ToString().Substring(0, 1).ToLower() + FK_Column.Substring(1, FK_Column.ToString().Length - 1).ToLower() + ";\n";
Statment += " public ObservableCollection<" + PK_Table + "> " + FK_Column + "\n";
Statment += " {\n";
Statment += " get\n";
Statment += " {\n";
Statment += " return _" + FK_Column.ToString().Substring(0, 1).ToLower() + FK_Column.Substring(1, FK_Column.ToString().Length - 1).ToLower() + ";\n";
Statment += " }\n";
Statment += " }\n\n";
}
}
Statment += " private " + TableName + " _selected" + TableName + " = null;\n";
Statment += " public " + TableName + " Selected" + TableName + "\n";
Statment += " {\n";
Statment += " get\n";
Statment += " {\n";
Statment += " return _selected" + TableName + ";\n";
Statment += " }\n";
Statment += " set\n";
Statment += " {\n";
Statment += " _selected" + TableName + " = value;\n";
Statment += " RaisePropertyChanged(\"Selected" + TableName + "\");\n";
Statment += " }\n";
Statment += " }\n\n";
string addss = TableName.ElementAt(TableName.Length - 1) + "s";
if (TableName.ElementAt(TableName.Length - 1) == 'y')
{
addss = "ies";
}
if (TableName.ElementAt(TableName.Length - 1) == 's')
{
addss = "s";
}
Statment += " public ObservableCollection<" + TableName + "> _" + TableName.Substring(0, TableName.Length - 1) + addss + ";\n";
Statment += " public ObservableCollection<" + TableName + "> " + TableName.Substring(0, TableName.Length - 1) + addss + "\n";
Statment += " {\n";
Statment += " get\n";
Statment += " {\n";
Statment += " return _" + TableName.Substring(0, TableName.Length - 1) + addss + ";\n";
Statment += " }\n";
Statment += " }\n";
Statment += " #endregion\n";
return Statment;
}
private string getCobeBehindMainPage(string namesp, string tableName)
{
string str = "";
DataAccess cls = new DataAccess();
string sql = "select * from information_schema.columns where table_name = '" + tableName + "'";
DataSet dsColumns = new DataSet();
string m = cls.getData(sql, ref dsColumns);
//=================================================
string adds = tableName.ElementAt(tableName.Length - 1) + "s";
if (tableName.ElementAt(tableName.Length - 1) == 'y')
{
adds = "ies";
}
if (tableName.ElementAt(tableName.Length - 1) == 's')
{
adds = "s";
}
str += "using System;\n";
str += "using System.Collections.Generic;\n";
str += "using System.Linq;\n";
str += "using System.Net;\n";
str += "using System.Windows;\n";
str += "using System.Windows.Controls;\n";
str += "using System.Windows.Documents;\n";
str += "using System.Windows.Input;\n";
str += "using System.Windows.Media;\n";
str += "using System.Windows.Media.Animation;\n";
str += "using System.Windows.Shapes;\n";
str += "using System.Windows.Navigation;\n";
str += "using SilverlightReport;\n";
str += "using " + namesp + ".ViewModel;\n";
str += "using " + namesp + ".Entities;\n";
str += "using System.Windows.Data;\n";
str += "using System.Reflection;\n\n\n\n";
str += "namespace " + namesp + ".Views\n";
str += "{\n";
str += " public partial class " + tableName + "Page : Page\n";
str += " {\n";
str += " public " + tableName + "ViewModel emv { set; get; }\n\n";
str += " public " + tableName + "Page()\n";
str += " {\n";
str += " InitializeComponent();\n";
str += " emv = new " + tableName + "ViewModel();\n";
str += " this.DataContext = emv;\n";
str += " MainPage m = (MainPage)Application.Current.RootVisual;\n";
str += " if (m.cmboxBoxLang.SelectedIndex == 1)\n";
str += " {\n";
str += " Helpers.LanguageTool.TranslateChildWindow(this, \"ar_JO\");\n";
str += " }\n";
str += " else\n";
str += " {\n";
str += " Helpers.LanguageTool.TranslateChildWindow(this, \"en_US\");\n";
str += " }\n";
str += "\n";
str += " }\n";
str += "\n";
str += " // Executes when the user navigates to this page.\n";
str += " protected override void OnNavigatedTo(NavigationEventArgs e)\n";
str += " {\n";
str += " }\n";
str += " private void txtSearch_KeyUp(object sender, KeyEventArgs e)\n";
str += " {\n";
str += " if (e.Key == Key.Enter)\n";
str += " {\n";
str += " Predicate<object> predicate = null;\n";
str += " predicate = p => (Helpers.FullTextSearch.Matches(txtSearch.Text.ToLower().Split(' '), p));\n";
str += " PagedCollectionView view = new PagedCollectionView(emv." + tableName.Substring(0, tableName.Length - 1) + adds + ");\n";
str += " view.Filter = null;\n";
str += " view.Filter = predicate;\n";
str += " grd" + tableName + ".ItemsSource = view;\n";
str += " }\n";
str += " }\n";
str += "\n";
str += "\n";
str += " private Report report;\n";
str += " private void btnPrint_Click(object sender, RoutedEventArgs e)\n";
str += " {\n";
str += " string name = \"" + tableName + "Report.xaml\";\n";
str += " string fullName = string.Format(\"/" + namesp + ";component/Reports/{0}\", name);\n";
str += " this.report = Report.LoadFromXaml(fullName);\n";
//str += " this.report.ItemsSource = emv." + tableName.Substring(0, tableName.Length - 1) + adds + ";\n";
str += " //==============================================================\n";
str += " List<Entities." + tableName + "> entities = new List<Entities." + tableName + ">();\n";
str += " for (int i = 0; i < " + tableName + "Pager.PageCount; i++)\n";
str += " {\n";
str += " " + tableName + "Pager.PageIndex = i;\n";
str += " foreach (Entities." + tableName + " data in " + tableName + "Pager.Source)\n";
str += " {\n";
str += " entities.Add(data);\n";
str += " }\n";
str += " }\n";
str += " this.report.ItemsSource = entities;\n";
str += " //==============================================================\n";
str += "\n";
str += " Button btn = sender as Button;\n";
str += " if (btn.Name == \"btnPrint\")\n";
str += " {\n";
str += " this.report.Print();\n";
str += " }\n";
str += " else\n";
str += " {\n";
str += " this.report.Preview();\n";
str += " }\n";
str += " }\n";
str += " }\n";
str += "}\n";
return(str);
}
private string getMainPage(string namesp, string tableName)
{
string str = "";
DataAccess cls = new DataAccess();
string sql = "select * from information_schema.columns where table_name = '" + tableName + "'";
DataSet dsColumns = new DataSet();
string m = cls.getData(sql, ref dsColumns);
//=================================================
string adds = tableName.ElementAt(tableName.Length - 1) + "s";
if (tableName.ElementAt(tableName.Length - 1) == 'y')
{
adds = "ies";
}
if (tableName.ElementAt(tableName.Length - 1) == 's')
{
adds = "s";
}
//========================================================
str += "<sr:Report\n";
str += " xmlns=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation\" \n";
str += " xmlns:x=\"http://schemas.microsoft.com/winfx/2006/xaml\"\n";
str += " xmlns:sr=\"clr-namespace:SilverlightReport;assembly=SilverlightReport\">\n";
str += " <sr:ReportBand Kind=\"ReportHeader\">\n";
str += " <TextBlock FontSize=\"20\" FontWeight=\"Bold\" Margin=\"10\" HorizontalAlignment=\"Center\">\n";
str += " " + tableName.Replace("_"," ").Substring(0, tableName.Length - 1) + adds + " List</TextBlock>\n";
str += " </sr:ReportBand>\n";
str += " <sr:Report.DataGrid>\n";
str += " <sr:CDataGrid HeaderHorizontalAlignment=\"Center\">\n";
for (int j = 0; j < dsColumns.Tables[0].Rows.Count; j++)
{
if (dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Contains("Is_Deleted") || dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Contains("Date_Created") || dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Contains("Date_Updated"))
{
continue;
}
sql = @"SELECT FK.TABLE_NAME AS FK_Table, CU.COLUMN_NAME AS FK_Column, PK.TABLE_NAME AS PK_Table, PT.COLUMN_NAME AS PK_Column,
C.CONSTRAINT_NAME AS Constraint_Name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN
(SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS i1 INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE (i1.CONSTRAINT_TYPE = 'PRIMARY KEY')) AS PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE (FK.TABLE_NAME = '" + tableName + "') AND (CU.COLUMN_NAME = '" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + "') ORDER BY FK_Table, FK_Column, PK_Table, PK_Column";
DataSet dsFK = new DataSet();
m = cls.getData(sql, ref dsFK);
string Data_Type = "";
if (dsColumns.Tables[0].Rows[j]["Data_Type"] != DBNull.Value)
Data_Type = dsColumns.Tables[0].Rows[j]["Data_Type"].ToString();
string widthCol = "100";
switch (Data_Type)
{
case "int": widthCol = "0.5"; break;
case "datetime": widthCol = "1.3"; break;
case "bit": widthCol = "0.5"; break;
case "real": widthCol = "0.5"; break;
case "money": widthCol = "0.5"; break;
case "float": widthCol = "0.5"; break;
default: widthCol = "2.2"; break;
}
if (dsFK.Tables[0].Rows.Count == 0)
{
str += " <sr:CDataGridColumn Header=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + "\" Binding=\"{Binding " + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + "}\" Width=\"" + widthCol + "\"></sr:CDataGridColumn>\n";
}
else
{
if (dsFK.Tables[0].Rows[0]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[0]["FK_Column"].ToString() != "Updated_By")
{
str += " <sr:CDataGridColumn Header=\"" + dsFK.Tables[0].Rows[0]["FK_Column"].ToString().Replace("_", " ").Replace("Id", "") + "\" Binding=\"{Binding " + dsFK.Tables[0].Rows[0]["Constraint_Name"].ToString() + ".Name" + "}\" Width=\"" + widthCol + "\"></sr:CDataGridColumn>\n";
}
else
{
//str += " <sr:CDataGridColumn Header=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + "\" Binding=\"{Binding " + dsFK.Tables[0].Rows[0]["PK_Table"].ToString().Replace("_", " ") + "_" + dsFK.Tables[0].Rows[0]["FK_Column"].ToString() + ".Name" + "}\" Width=\"" + widthCol + "\"></sr:CDataGridColumn>\n";
}
}
}
str += " </sr:CDataGrid>\n";
str += " </sr:Report.DataGrid>\n";
str += "</sr:Report>\n";
return str;
}
public void generateEntities(string constr, string outputDir, string namesp)
{
//try
//{
// Directory.CreateDirectory(outputDir + "\\Entities\\");
//}
//catch { }
DataAccess cls = new DataAccess();
DataAccess.strConn = constr;
DataSet dsTables = new DataSet();
string sql = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE' and table_name <> 'sysdiagrams'";
string m = cls.getData(sql, ref dsTables);
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
string classStr = "";
classStr += "using System;\n";
classStr += "using System.Collections.Generic;\n";
classStr += "using System.Linq;\n";
classStr += "using System.Text;\n";
//classStr += "using System.Text.RegularExpressions;\n";
classStr += "using System.ComponentModel.DataAnnotations;\n\n";
classStr += "namespace " + namesp + ".Entities\n";
classStr += "{\n";
classStr += " public class " + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\n";
classStr += " {\n";
// classStr += " private static Regex RegexEmail = new Regex(@\"^([\\w\\-\\.]+)@((\\[([0-9]{1,3}\\.){3}[0-9]{1,3}\\])|(([\\w\\-]+\\.)+)([a-zA-Z]{2,4}))$\", RegexOptions.Multiline | RegexOptions.IgnoreCase);";
//===========================================================================================
DataSet dsColumns = new DataSet();
sql = "select * from information_schema.columns where table_name = '" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "'";
m = cls.getData(sql, ref dsColumns);
for (int j = 0; j < dsColumns.Tables[0].Rows.Count; j++)
{
sql = @"SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1 and OBJECT_NAME(ic.OBJECT_ID) ='" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "' and COL_NAME(ic.OBJECT_ID,ic.column_id)='" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + "'";
DataSet dsKey = new DataSet();
m = cls.getData(sql, ref dsKey);
if (dsKey.Tables[0].Rows.Count != 0)
{
classStr += " [Key]\n";
}
string Data_Type = "";
if (dsColumns.Tables[0].Rows[j]["Data_Type"] != DBNull.Value)
{
Data_Type = dsColumns.Tables[0].Rows[j]["Data_Type"].ToString();
}
string dType = "";
switch (Data_Type)
{
case "int": dType = "int"; break;
case "datetime": dType = "DateTime"; break;
case "bit": dType = "bool"; break;
case "real": dType = "double"; break;
case "money": dType = "double"; break;
case "float": dType = "double"; break;
default: dType = "string"; break;
}
if (dsColumns.Tables[0].Rows[j]["IS_NULLABLE"].ToString() != "YES" && dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() != "Id")
{
classStr += " [Required]\n";
}
if (Data_Type == "nvarchar")
{
if (dsColumns.Tables[0].Rows[j]["CHARACTER_MAXIMUM_LENGTH"].ToString() == "-1")
{
classStr += " [StringLength(4000)]\n";
}
else
{
classStr += " [StringLength(" + dsColumns.Tables[0].Rows[j]["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")]\n";
}
}
classStr += " public " + dType + " " + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + " { get; set; }\n";
}
classStr += "\n\n";
sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
where FK.TABLE_NAME= '" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "' ORDER BY 1,2,3,4";
DataSet dsFK = new DataSet();
m = cls.getData(sql, ref dsFK);
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
if (dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Updated_By")
{
classStr += " private " + dsFK.Tables[0].Rows[j]["PK_Table"].ToString() + " _" + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Substring(0, 1).ToLower() + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Substring(1, dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Length - 1).ToLower() + ";\n";
classStr += " [Association(\"" + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString() + "\",\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "\", \"" + dsFK.Tables[0].Rows[j]["PK_Column"].ToString() + "\", IsForeignKey = true)]\n";
classStr += " public " + dsFK.Tables[0].Rows[j]["PK_Table"].ToString() + " " + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString() + "\n";
classStr += " {\n";
classStr += " get\n";
classStr += " {\n";
classStr += " return _" + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Substring(0, 1).ToLower() + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Substring(1, dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Length - 1).ToLower() + ";\n";
classStr += " }\n";
classStr += " set\n";
classStr += " {\n";
classStr += " _" + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Substring(0, 1).ToLower() + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Substring(1, dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Length - 1).ToLower() + "= value;\n";
classStr += " }\n";
classStr += " }\n";
}
else
{
classStr += " private " + dsFK.Tables[0].Rows[j]["PK_Table"].ToString() + " _" + dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Substring(0, 1).ToLower() + dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Substring(1, dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Length - 1).ToLower() + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + ";\n";
classStr += " [Association(\"" + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString() + "\",\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "\", \"" + dsFK.Tables[0].Rows[j]["PK_Column"].ToString() + "\", IsForeignKey = true)]\n";
classStr += " public " + dsFK.Tables[0].Rows[j]["PK_Table"].ToString() + " " + dsFK.Tables[0].Rows[j]["PK_Table"].ToString() + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "\n";
classStr += " {\n";
classStr += " get\n";
classStr += " {\n";
classStr += " return _" + dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Substring(0, 1).ToLower() + dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Substring(1, dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Length - 1).ToLower() + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + ";\n";
classStr += " }\n";
classStr += " set\n";
classStr += " {\n";
classStr += " _" + dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Substring(0, 1).ToLower() + dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Substring(1, dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Length - 1).ToLower() + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "= value;\n";
classStr += " }\n";
classStr += " }\n";
}
}
classStr += " }\n";
classStr += "}";
//RichTextBox rich = new RichTextBox();
//rich.Text = classStr;
TextWriter tw = new StreamWriter(outputDir + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + ".cs");
tw.WriteLine(classStr);
tw.Close();
//rich.SaveFile(outputDir + "\\Entities\\" + dsTables.Tables[0].Rows[i]["Name"].ToString()+ ".cs", RichTextBoxStreamType.PlainText);
}
}
public void generatepartialEntities(string constr, string outputDir, string namesp)
{
//try
//{
// Directory.CreateDirectory(outputDir + "\\Model\\");
//}
//catch { }
DataAccess cls = new DataAccess();
DataAccess.strConn = constr;
DataSet dsTables = new DataSet();
string sql = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE' and table_name <> 'sysdiagrams'";
string m = cls.getData(sql, ref dsTables);
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
if (dsTables.Tables[0].Rows[i]["Name"].ToString() == "Contact")
{
MessageBox.Show("hi");
}
string classStr = "";
classStr += "using System;\n";
classStr += "using System.Collections.Generic;\n";
classStr += "using System.Linq;\n";
classStr += "using System.Text;\n";
classStr += "using System.Runtime.Serialization;\n";
classStr += "using System.ComponentModel.DataAnnotations;\n\n";
classStr += "namespace " + namesp + ".Entities\n";
classStr += "{\n";
classStr += " public partial class " + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\n";
classStr += " {\n";
// classStr += " private static Regex RegexEmail = new Regex(@\"^([\\w\\-\\.]+)@((\\[([0-9]{1,3}\\.){3}[0-9]{1,3}\\])|(([\\w\\-]+\\.)+)([a-zA-Z]{2,4}))$\", RegexOptions.Multiline | RegexOptions.IgnoreCase);";
//===========================================================================================
DataSet dsColumns = new DataSet();
sql = "select * from information_schema.columns where table_name = '" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "'"; //and column_name like '%Name'";
m = cls.getData(sql, ref dsColumns);
//===================================================================================================================
sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
where FK.TABLE_NAME= '" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "' ORDER BY 1,2,3,4";
DataSet dsFK = new DataSet();
m = cls.getData(sql, ref dsFK);
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
if (dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Updated_By")
{
classStr += " #region " + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\n";
classStr += " partial void On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changing(string value);\n";
classStr += " partial void On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changed();\n";
classStr += " private string _" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + ";\n";
classStr += " [DataMember()]\n";
classStr += " public string " + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\n";
classStr += " {\n";
classStr += " get\n";
classStr += " {\n";
classStr += " if (_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " == null)\n";
classStr += " {\n";
classStr += " try\n";
classStr += " {\n";
classStr += " _" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " = " + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString() + ".Name;\n";
classStr += " }\n";
classStr += " catch { }\n";
classStr += " }\n";
classStr += " return _" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + ";\n";
classStr += " }\n";
classStr += " set\n";
classStr += " {\n";
classStr += " if ((this._" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " != value))\n";
classStr += " {\n";
classStr += " this.On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changing(value);\n";
classStr += " this.RaiseDataMemberChanging(\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\");\n";
classStr += " this.ValidateProperty(\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\", value);\n";
classStr += " this._" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " = value;\n";
classStr += " this.RaiseDataMemberChanged(\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\");\n";
classStr += " this.On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changed();\n";
classStr += " }\n";
classStr += " }\n";
classStr += " }\n";
classStr += " #endregion\n\n";
}
else
{
classStr += " #region " + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\n";
classStr += " partial void On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changing(string value);\n";
classStr += " partial void On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changed();\n";
classStr += " private string _" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + ";\n";
classStr += " [DataMember()]\n";
classStr += " public string " + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\n";
classStr += " {\n";
classStr += " get\n";
classStr += " {\n";
classStr += " if (_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " == null)\n";
classStr += " {\n";
classStr += " try\n";
classStr += " {\n";
classStr += " _" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " = " + dsFK.Tables[0].Rows[j]["PK_Table"].ToString() + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + ".Name;\n";
classStr += " }\n";
classStr += " catch { }\n";
classStr += " }\n";
classStr += " return _" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + ";\n";
classStr += " }\n";
classStr += " set\n";
classStr += " {\n";
classStr += " if ((this._" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " != value))\n";
classStr += " {\n";
classStr += " this.On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changing(value);\n";
classStr += " this.RaiseDataMemberChanging(\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\");\n";
classStr += " this.ValidateProperty(\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\", value);\n";
classStr += " this._" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " = value;\n";
classStr += " this.RaiseDataMemberChanged(\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\");\n";
classStr += " this.On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changed();\n";
classStr += " }\n";
classStr += " }\n";
classStr += " }\n";
classStr += " #endregion\n\n";
}
}
//======================================================================================================================
classStr += " public override string ToString()\n";
classStr += " {\n";
classStr += " return Name;\n";
classStr += " }\n\n";
classStr += "\n\n";
classStr += " }\n";
classStr += "}";
//RichTextBox rich = new RichTextBox();
//rich.Text = classStr;
if (dsColumns.Tables[0].Rows.Count != 0)
{
TextWriter tw = new StreamWriter(outputDir + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "Model.cs");
tw.WriteLine(classStr);
tw.Close();
}
//rich.SaveFile(outputDir + "\\Entities\\" + dsTables.Tables[0].Rows[i]["Name"].ToString()+ ".cs", RichTextBoxStreamType.PlainText);
}
}
public void generateEntities(string constr, string outputDir, string namesp)
{
//try
//{
// Directory.CreateDirectory(outputDir + "\\Entities\\");
//}
//catch { }
DataAccess cls = new DataAccess();
DataAccess.strConn = constr;
DataSet dsTables = new DataSet();
string sql = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE' and table_name <> 'sysdiagrams'";
string m = cls.getData(sql, ref dsTables);
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
string classStr = "";
classStr += "using System;\n";
classStr += "using System.Collections.Generic;\n";
classStr += "using System.Linq;\n";
classStr += "using System.Text;\n";
//classStr += "using System.Text.RegularExpressions;\n";
classStr += "using System.ComponentModel.DataAnnotations;\n\n";
classStr += "namespace " + namesp + ".Entities\n";
classStr += "{\n";
classStr += " public class " + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\n";
classStr += " {\n";
// classStr += " private static Regex RegexEmail = new Regex(@\"^([\\w\\-\\.]+)@((\\[([0-9]{1,3}\\.){3}[0-9]{1,3}\\])|(([\\w\\-]+\\.)+)([a-zA-Z]{2,4}))$\", RegexOptions.Multiline | RegexOptions.IgnoreCase);";
//===========================================================================================
DataSet dsColumns = new DataSet();
sql = "select * from information_schema.columns where table_name = '" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "'";
m = cls.getData(sql, ref dsColumns);
for (int j = 0; j < dsColumns.Tables[0].Rows.Count; j++)
{
sql = @"SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1 and OBJECT_NAME(ic.OBJECT_ID) ='" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "' and COL_NAME(ic.OBJECT_ID,ic.column_id)='" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + "'";
DataSet dsKey = new DataSet();
m = cls.getData(sql, ref dsKey);
if (dsKey.Tables[0].Rows.Count != 0)
{
classStr += " [Key]\n";
}
string Data_Type = "";
if (dsColumns.Tables[0].Rows[j]["Data_Type"] != DBNull.Value)
Data_Type = dsColumns.Tables[0].Rows[j]["Data_Type"].ToString();
string dType = "";
switch (Data_Type)
{
case "int": dType = "int"; break;
case "datetime": dType = "DateTime"; break;
case "bit": dType = "bool"; break;
case "real": dType = "double"; break;
case "money": dType = "double"; break;
case "float": dType = "double"; break;
default: dType = "string"; break;
}
if (dsColumns.Tables[0].Rows[j]["IS_NULLABLE"].ToString() != "YES" && dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() != "Id")
{
classStr += " [Required]\n";
}
if (Data_Type == "nvarchar")
{
if (dsColumns.Tables[0].Rows[j]["CHARACTER_MAXIMUM_LENGTH"].ToString() == "-1")
{
classStr += " [StringLength(4000)]\n";
}
else
{
classStr += " [StringLength(" + dsColumns.Tables[0].Rows[j]["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")]\n";
}
}
classStr += " public " + dType + " " + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + " { get; set; }\n";
}
classStr += "\n\n";
sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
where FK.TABLE_NAME= '" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "' ORDER BY 1,2,3,4";
DataSet dsFK = new DataSet();
m = cls.getData(sql, ref dsFK);
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
if (dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Updated_By")
{
classStr += " private " + dsFK.Tables[0].Rows[j]["PK_Table"].ToString() + " _" + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Substring(0, 1).ToLower() + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Substring(1, dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Length - 1).ToLower() + ";\n";
classStr += " [Association(\"" + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString() + "\",\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "\", \"" + dsFK.Tables[0].Rows[j]["PK_Column"].ToString() + "\", IsForeignKey = true)]\n";
classStr += " public " + dsFK.Tables[0].Rows[j]["PK_Table"].ToString() + " " + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString() + "\n";
classStr += " {\n";
classStr += " get\n";
classStr += " {\n";
classStr += " return _" + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Substring(0, 1).ToLower() + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Substring(1, dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Length - 1).ToLower() + ";\n";
classStr += " }\n";
classStr += " set\n";
classStr += " {\n";
classStr += " _" + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Substring(0, 1).ToLower() + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Substring(1, dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString().Length - 1).ToLower() + "= value;\n";
classStr += " }\n";
classStr += " }\n";
}
else
{
classStr += " private " + dsFK.Tables[0].Rows[j]["PK_Table"].ToString() + " _" + dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Substring(0, 1).ToLower() + dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Substring(1, dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Length - 1).ToLower() + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + ";\n";
classStr += " [Association(\"" + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString() + "\",\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "\", \"" + dsFK.Tables[0].Rows[j]["PK_Column"].ToString() + "\", IsForeignKey = true)]\n";
classStr += " public " + dsFK.Tables[0].Rows[j]["PK_Table"].ToString() + " " + dsFK.Tables[0].Rows[j]["PK_Table"].ToString() + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "\n";
classStr += " {\n";
classStr += " get\n";
classStr += " {\n";
classStr += " return _" + dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Substring(0, 1).ToLower() + dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Substring(1, dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Length - 1).ToLower() + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + ";\n";
classStr += " }\n";
classStr += " set\n";
classStr += " {\n";
classStr += " _" + dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Substring(0, 1).ToLower() + dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Substring(1, dsFK.Tables[0].Rows[j]["PK_Table"].ToString().Length - 1).ToLower() + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "= value;\n";
classStr += " }\n";
classStr += " }\n";
}
}
classStr += " }\n";
classStr += "}";
//RichTextBox rich = new RichTextBox();
//rich.Text = classStr;
TextWriter tw = new StreamWriter(outputDir + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + ".cs");
tw.WriteLine(classStr);
tw.Close();
//rich.SaveFile(outputDir + "\\Entities\\" + dsTables.Tables[0].Rows[i]["Name"].ToString()+ ".cs", RichTextBoxStreamType.PlainText);
}
}
public string getMethods1(DataSet dsTableDefenation, string TableName)
{
string Statment = "\n";
Statment += " #region Methods\n";
Statment += " public " + TableName + "ViewModel()\n";
Statment += " {\n";
Statment += " IsBusy = true;\n";
string sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
where FK.TABLE_NAME= '" + TableName + "' ORDER BY 1,2,3,4";
DataSet dsFK = new DataSet();
DataAccess cls = new DataAccess();
string m = cls.getData(sql, ref dsFK);
bool foundCreatedBy = false;
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
if (dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Updated_By")
{
string PK_Table = dsFK.Tables[0].Rows[j]["PK_Table"].ToString();
string adds = PK_Table.ElementAt(PK_Table.Length - 1) + "s";
if (PK_Table.ElementAt(PK_Table.Length - 1) == 'y')
{
adds = "ies";
}
if (PK_Table.ElementAt(PK_Table.Length - 1) == 's')
{
adds = "s";
}
Statment += " if (m._context." + PK_Table.Substring(0, PK_Table.ToString().Length - 1) + "" + adds + ".Count == 0)\n";
Statment += " {\n";
Statment += " m._context.Load(m._context.get" + PK_Table.Substring(0, PK_Table.ToString().Length - 1) + "" + adds + "Query(), Load" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + "Completed, null);\n";
Statment += " }\n";
Statment += " else\n";
Statment += " {\n";
Statment += " _" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + " = new ObservableCollection<" + PK_Table + ">(m._context." + PK_Table.Substring(0, PK_Table.Length - 1) + "" + adds + ");\n";
Statment += " RaisePropertyChanged(\"" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + "\");\n";
Statment += " }\n\n";
}
else
{
foundCreatedBy = true;
}
}
if (foundCreatedBy == true)
{
Statment += " if (m._context.Users.Count == 0)\n";
Statment += " {\n";
Statment += " m._context.Load(m._context.getUsersQuery(), LoadUsersCompleted, null);\n";
Statment += " }\n";
Statment += " else\n";
Statment += " {\n";
Statment += " _updated_by = new ObservableCollection<User>(m._context.Users);\n\n";
Statment += " RaisePropertyChanged(\"Updated_By\");\n";
Statment += " _created_by = new ObservableCollection<User>(m._context.Users);\n";
Statment += " RaisePropertyChanged(\"Created_By\");\n";
Statment += " }\n";
}
string addss = TableName.ElementAt(TableName.Length - 1) + "s";
if (TableName.ElementAt(TableName.Length - 1) == 'y')
{
addss = "ies";
}
if (TableName.ElementAt(TableName.Length - 1) == 's')
{
addss = "s";
}
Statment += " if (m._context." + TableName.Substring(0, TableName.Length - 1) + addss + ".Count == 0)\n";
Statment += " {\n";
Statment += " m._context.Load(m._context.get" + TableName.Substring(0, TableName.Length - 1) + addss + "Query(), Load" + TableName.Substring(0, TableName.Length - 1) + addss + "Completed, null);\n";
Statment += " }\n";
Statment += " else\n";
Statment += " {\n";
Statment += " _" + TableName.Substring(0, TableName.Length - 1) + addss + " = new ObservableCollection<" + TableName + ">(m._context." + TableName.Substring(0, TableName.Length - 1) + addss + ");\n";
Statment += " RaisePropertyChanged(\"" + TableName.Substring(0, TableName.Length - 1) + addss + "\");\n";
Statment += " IsBusy = false;\n";
Statment += " }\n";
Statment += " }\n\n";
//=================================================================== Load Copleted
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
if (dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Updated_By")
{
string PK_Table = dsFK.Tables[0].Rows[j]["PK_Table"].ToString();
string adds = PK_Table.ElementAt(PK_Table.Length - 1) + "s";
if (PK_Table.ElementAt(PK_Table.Length - 1) == 'y')
{
adds = "ies";
}
if (PK_Table.ElementAt(PK_Table.Length - 1) == 's')
{
adds = "s";
}
Statment += " private void Load" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + "Completed(LoadOperation<" + PK_Table + "> lo)\n";
Statment += " {\n";
Statment += " _" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + " = new ObservableCollection<" + PK_Table + ">(m._context." + PK_Table.Substring(0, PK_Table.Length - 1) + "" + adds + ");\n";
Statment += " RaisePropertyChanged(\"" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + "\");\n";
Statment += " }\n\n";
}
}
if (foundCreatedBy == true)
{
Statment += " private void LoadUsersCompleted(LoadOperation<User> lo)\n";
Statment += " {\n";
Statment += " _updated_by = new ObservableCollection<User>(m._context.Users);\n";
Statment += " RaisePropertyChanged(\"Updated_By\");\n";
Statment += "\n";
Statment += " _created_by = new ObservableCollection<User>(m._context.Users);\n";
Statment += " RaisePropertyChanged(\"Created_By\");\n";
Statment += " }\n";
}
Statment += " private void Load" + TableName.Substring(0, TableName.Length - 1) + addss + "Completed(LoadOperation<" + TableName + "> lo)\n";
Statment += " {\n";
Statment += " _" + TableName.Substring(0, TableName.Length - 1) + addss + " = new ObservableCollection<" + TableName + ">(m._context." + TableName.Substring(0, TableName.Length - 1) + addss + ");\n";
Statment += " RaisePropertyChanged(\"" + TableName.Substring(0, TableName.Length - 1) + addss + "\");\n";
Statment += " IsBusy = false;\n";
Statment += " }\n";
Statment += " #endregion\n";
Statment += "\n";
return(Statment);
}
public string getProperties(DataSet dsTableDefenation, string TableName)
{
string Statment = "\n";
Statment += " #region Properties\n";
Statment += "\n";
Statment += " public event PropertyChangedEventHandler PropertyChanged;\n";
Statment += " private void RaisePropertyChanged(string propertyname)\n";
Statment += " {\n";
Statment += " if (PropertyChanged != null)\n";
Statment += " {\n";
Statment += " try\n";
Statment += " {\n";
Statment += " PropertyChanged(this, new PropertyChangedEventArgs(propertyname));\n";
Statment += " }\n";
Statment += " catch { }\n";
Statment += " }\n";
Statment += " }\n\n";
Statment += " private bool _isBusy;\n";
Statment += " public bool IsBusy\n";
Statment += " {\n";
Statment += " get\n";
Statment += " {\n";
Statment += " return _isBusy;\n";
Statment += " }\n";
Statment += " set\n";
Statment += " {\n";
Statment += " _isBusy = value;\n";
Statment += " RaisePropertyChanged(\"IsBusy\");\n";
Statment += " }\n";
Statment += " }\n\n";
string sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
where FK.TABLE_NAME= '" + TableName + "' ORDER BY 1,2,3,4";
DataSet dsFK = new DataSet();
DataAccess cls = new DataAccess();
string m = cls.getData(sql, ref dsFK);
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
if (dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Updated_By")
{
string PK_Table = dsFK.Tables[0].Rows[j]["PK_Table"].ToString();
string adds = PK_Table.ElementAt(PK_Table.Length - 1) + "s";
if (PK_Table.ElementAt(PK_Table.Length - 1) == 'y')
{
adds = "ies";
}
if (PK_Table.ElementAt(PK_Table.Length - 1) == 's')
{
adds = "s";
}
Statment += " public ObservableCollection<" + PK_Table + "> _" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + ";\n";
Statment += " public ObservableCollection<" + PK_Table + "> " + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + "\n";
Statment += " {\n";
Statment += " get\n";
Statment += " {\n";
Statment += " return _" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + ";\n";
Statment += " }\n";
Statment += " }\n\n";
}
else
{
string PK_Table = dsFK.Tables[0].Rows[j]["PK_Table"].ToString();
string FK_Column = dsFK.Tables[0].Rows[j]["FK_Column"].ToString();
Statment += " public ObservableCollection<" + PK_Table + "> _" + FK_Column.ToString().Substring(0, 1).ToLower() + FK_Column.Substring(1, FK_Column.ToString().Length - 1).ToLower() + ";\n";
Statment += " public ObservableCollection<" + PK_Table + "> " + FK_Column + "\n";
Statment += " {\n";
Statment += " get\n";
Statment += " {\n";
Statment += " return _" + FK_Column.ToString().Substring(0, 1).ToLower() + FK_Column.Substring(1, FK_Column.ToString().Length - 1).ToLower() + ";\n";
Statment += " }\n";
Statment += " }\n\n";
}
}
Statment += " private " + TableName + " _selected" + TableName + " = null;\n";
Statment += " public " + TableName + " Selected" + TableName + "\n";
Statment += " {\n";
Statment += " get\n";
Statment += " {\n";
Statment += " return _selected" + TableName + ";\n";
Statment += " }\n";
Statment += " set\n";
Statment += " {\n";
Statment += " _selected" + TableName + " = value;\n";
Statment += " RaisePropertyChanged(\"Selected" + TableName + "\");\n";
Statment += " }\n";
Statment += " }\n\n";
string addss = TableName.ElementAt(TableName.Length - 1) + "s";
if (TableName.ElementAt(TableName.Length - 1) == 'y')
{
addss = "ies";
}
if (TableName.ElementAt(TableName.Length - 1) == 's')
{
addss = "s";
}
Statment += " public ObservableCollection<" + TableName + "> _" + TableName.Substring(0, TableName.Length - 1) + addss + ";\n";
Statment += " public ObservableCollection<" + TableName + "> " + TableName.Substring(0, TableName.Length - 1) + addss + "\n";
Statment += " {\n";
Statment += " get\n";
Statment += " {\n";
Statment += " return _" + TableName.Substring(0, TableName.Length - 1) + addss + ";\n";
Statment += " }\n";
Statment += " }\n";
Statment += " #endregion\n";
return(Statment);
}
public void generatepartialEntities(string constr, string outputDir, string namesp)
{
//try
//{
// Directory.CreateDirectory(outputDir + "\\Model\\");
//}
//catch { }
DataAccess cls = new DataAccess();
DataAccess.strConn = constr;
DataSet dsTables = new DataSet();
string sql = "select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE' and table_name <> 'sysdiagrams'";
string m = cls.getData(sql, ref dsTables);
for (int i = 0; i < dsTables.Tables[0].Rows.Count; i++)
{
if (dsTables.Tables[0].Rows[i]["Name"].ToString() == "Contact")
{
MessageBox.Show("hi");
}
string classStr = "";
classStr += "using System;\n";
classStr += "using System.Collections.Generic;\n";
classStr += "using System.Linq;\n";
classStr += "using System.Text;\n";
classStr += "using System.Runtime.Serialization;\n";
classStr += "using System.ComponentModel.DataAnnotations;\n\n";
classStr += "namespace " + namesp + ".Entities\n";
classStr += "{\n";
classStr += " public partial class " + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\n";
classStr += " {\n";
// classStr += " private static Regex RegexEmail = new Regex(@\"^([\\w\\-\\.]+)@((\\[([0-9]{1,3}\\.){3}[0-9]{1,3}\\])|(([\\w\\-]+\\.)+)([a-zA-Z]{2,4}))$\", RegexOptions.Multiline | RegexOptions.IgnoreCase);";
//===========================================================================================
DataSet dsColumns = new DataSet();
sql = "select * from information_schema.columns where table_name = '" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "'"; //and column_name like '%Name'";
m = cls.getData(sql, ref dsColumns);
//===================================================================================================================
sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
where FK.TABLE_NAME= '" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "' ORDER BY 1,2,3,4";
DataSet dsFK = new DataSet();
m = cls.getData(sql, ref dsFK);
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
if (dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Updated_By")
{
classStr += " #region " + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\n";
classStr += " partial void On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changing(string value);\n";
classStr += " partial void On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changed();\n";
classStr += " private string _" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + ";\n";
classStr += " [DataMember()]\n";
classStr += " public string " + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\n";
classStr += " {\n";
classStr += " get\n";
classStr += " {\n";
classStr += " if (_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " == null)\n";
classStr += " {\n";
classStr += " try\n";
classStr += " {\n";
classStr += " _" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " = " + dsFK.Tables[0].Rows[j]["Constraint_Name"].ToString() + ".Name;\n";
classStr += " }\n";
classStr += " catch { }\n";
classStr += " }\n";
classStr += " return _" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + ";\n";
classStr += " }\n";
classStr += " set\n";
classStr += " {\n";
classStr += " if ((this._" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " != value))\n";
classStr += " {\n";
classStr += " this.On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changing(value);\n";
classStr += " this.RaiseDataMemberChanging(\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\");\n";
classStr += " this.ValidateProperty(\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\", value);\n";
classStr += " this._" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " = value;\n";
classStr += " this.RaiseDataMemberChanged(\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\");\n";
classStr += " this.On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changed();\n";
classStr += " }\n";
classStr += " }\n";
classStr += " }\n";
classStr += " #endregion\n\n";
}
else
{
classStr += " #region " + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\n";
classStr += " partial void On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changing(string value);\n";
classStr += " partial void On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changed();\n";
classStr += " private string _" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + ";\n";
classStr += " [DataMember()]\n";
classStr += " public string " + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\n";
classStr += " {\n";
classStr += " get\n";
classStr += " {\n";
classStr += " if (_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " == null)\n";
classStr += " {\n";
classStr += " try\n";
classStr += " {\n";
classStr += " _" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " = " + dsFK.Tables[0].Rows[j]["PK_Table"].ToString() + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + ".Name;\n";
classStr += " }\n";
classStr += " catch { }\n";
classStr += " }\n";
classStr += " return _" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + ";\n";
classStr += " }\n";
classStr += " set\n";
classStr += " {\n";
classStr += " if ((this._" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " != value))\n";
classStr += " {\n";
classStr += " this.On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changing(value);\n";
classStr += " this.RaiseDataMemberChanging(\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\");\n";
classStr += " this.ValidateProperty(\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\", value);\n";
classStr += " this._" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + " = value;\n";
classStr += " this.RaiseDataMemberChanged(\"" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "\");\n";
classStr += " this.On" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "Changed();\n";
classStr += " }\n";
classStr += " }\n";
classStr += " }\n";
classStr += " #endregion\n\n";
}
}
//======================================================================================================================
classStr += " public override string ToString()\n";
classStr += " {\n";
classStr += " return Name;\n";
classStr += " }\n\n";
classStr += "\n\n";
classStr += " }\n";
classStr += "}";
//RichTextBox rich = new RichTextBox();
//rich.Text = classStr;
if (dsColumns.Tables[0].Rows.Count != 0)
{
TextWriter tw = new StreamWriter(outputDir + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "\\" + dsTables.Tables[0].Rows[i]["Name"].ToString() + "Model.cs");
tw.WriteLine(classStr);
tw.Close();
}
//rich.SaveFile(outputDir + "\\Entities\\" + dsTables.Tables[0].Rows[i]["Name"].ToString()+ ".cs", RichTextBoxStreamType.PlainText);
}
}
private string getMainPage(string namesp, string tableName)
{
string str = "";
DataAccess cls = new DataAccess();
string sql = "select * from information_schema.columns where table_name = '" + tableName + "'";
DataSet dsColumns = new DataSet();
string m = cls.getData(sql, ref dsColumns);
//=================================================
string adds = tableName.ElementAt(tableName.Length - 1) + "s";
if (tableName.ElementAt(tableName.Length - 1) == 'y')
{
adds = "ies";
}
if (tableName.ElementAt(tableName.Length - 1) == 's')
{
adds = "s";
}
//========================================================
str += "<navigation:Page x:Class=\"" + namesp + ".Views." + tableName + "Page\" \n";
str += " xmlns=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation\" \n";
str += " xmlns:x=\"http://schemas.microsoft.com/winfx/2006/xaml\"\n";
str += " xmlns:d=\"http://schemas.microsoft.com/expression/blend/2008\"\n";
str += " xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\"\n";
str += " mc:Ignorable=\"d\"\n";
str += " xmlns:navigation=\"clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Navigation\"\n";
str += " xmlns:jib=\"clr-namespace:Jib.Controls.DataGrid;assembly=Jib.Controls\" \n";
str += " xmlns:my=\"clr-namespace:SilverlightClassLibrary1;assembly=SilverlightClassLibrary1\"\n";
str += " d:DesignWidth=\"940\" d:DesignHeight=\"480\"\n";
str += " Title=\"" + tableName + " Page\"\n";
str += " xmlns:sdk=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk\" \n";
str += " xmlns:toolkit=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation/toolkit\">\n\n";
str += " <Grid x:Name=\"LayoutRoot\">\n";
str += " <Grid.RowDefinitions>\n";
str += " <RowDefinition Height=\"55\" />\n";
str += " <RowDefinition Height=\"5\" />\n";
str += " <RowDefinition Height=\"435*\" />\n";
str += " <RowDefinition Height=\"30\" />\n";
str += " <RowDefinition Height=\"5\" />\n";
str += " </Grid.RowDefinitions>\n\n";
str += " <StackPanel Margin=\"0,0,0,0\" Orientation=\"Horizontal\" Grid.Row=\"0\">\n";
str += " <StackPanel.Effect>\n";
str += " <DropShadowEffect/>\n";
str += " </StackPanel.Effect>\n";
str += "\n";
str += " <Button Width=\"80\" Cursor=\"Hand\" Style=\"{StaticResource GreenButton}\" Command=\"{Binding InsertCommand}\" Name=\"btnAdd\" Margin=\"5,0,0,0\">\n";
str += " <StackPanel Orientation=\"Vertical\" >\n";
str += " <Image Source=\"/" + namesp + ";component/Images/Add.png\" HorizontalAlignment=\"Center\" Height=\"30\" Width=\"30\"/>\n";
str += " <TextBlock Text=\"Add New\" HorizontalAlignment=\"Center\" FontSize=\"10\"/>\n";
str += " </StackPanel>\n";
str += " </Button>\n";
str += "\n";
str += " <Button Width=\"80\" Cursor=\"Hand\" Style=\"{StaticResource GreenButton}\" Command=\"{Binding EditCommand}\" Name=\"btnEdit\" Margin=\"5,0,0,0\">\n";
str += " <StackPanel Orientation=\"Vertical\" >\n";
str += " <Image Source=\"/" + namesp + ";component/Images/Edit.png\" HorizontalAlignment=\"Center\" Height=\"30\" Width=\"30\"/>\n";
str += " <TextBlock Text=\"Edit\" HorizontalAlignment=\"Center\" FontSize=\"10\"/>\n";
str += " </StackPanel>\n";
str += " </Button>\n";
str += "\n";
str += " <Button Width=\"80\" Cursor=\"Hand\" Style=\"{StaticResource GreenButton}\" Command=\"{Binding DeleteCommand}\" Name=\"btnDelete\" Margin=\"5,0,0,0\">\n";
str += " <StackPanel Orientation=\"Vertical\" >\n";
str += " <Image Source=\"/" + namesp + ";component/Images/Delete.png\" HorizontalAlignment=\"Center\" Height=\"30\" Width=\"30\"/>\n";
str += " <TextBlock Text=\"Delete\" HorizontalAlignment=\"Center\" FontSize=\"10\"/>\n";
str += " </StackPanel>\n";
str += " </Button>\n";
str += "\n";
str += " <Button Width=\"80\" Cursor=\"Hand\" Style=\"{StaticResource GreenButton}\" Command=\"{Binding RefreshCommand}\" Name=\"btnRefresh\" Margin=\"5,0,0,0\">\n";
str += " <StackPanel Orientation=\"Vertical\" >\n";
str += " <Image Source=\"/" + namesp + ";component/Images/Refresh.png\" HorizontalAlignment=\"Center\" Height=\"30\" Width=\"30\"/>\n";
str += " <TextBlock Text=\"Refresh\" HorizontalAlignment=\"Center\" FontSize=\"10\"/>\n";
str += " </StackPanel>\n";
str += " </Button>\n";
str += "\n";
str += " <Button Width=\"80\" Cursor=\"Hand\" Style=\"{StaticResource GreenButton}\" Name=\"btnPrint\" Margin=\"5,0,0,0\" Click=\"btnPrint_Click\">\n";
str += " <StackPanel Orientation=\"Vertical\" >\n";
str += " <Image Source=\"/" + namesp + ";component/Images/Print.png\" HorizontalAlignment=\"Center\" Height=\"30\" Width=\"30\"/>\n";
str += " <TextBlock Text=\"Print\" HorizontalAlignment=\"Center\" FontSize=\"10\"/>\n";
str += " </StackPanel>\n";
str += " </Button>\n";
str += "\n";
str += " <Button Width=\"80\" Cursor=\"Hand\" Style=\"{StaticResource GreenButton}\" Name=\"btnPrintPreview\" Margin=\"5,0,0,0\" Click=\"btnPrint_Click\">\n";
str += " <StackPanel Orientation=\"Vertical\" >\n";
str += " <Image Source=\"/" + namesp + ";component/Images/print-preview.png\" HorizontalAlignment=\"Center\" Height=\"30\" Width=\"30\"/>\n";
str += " <TextBlock Text=\"Preview\" HorizontalAlignment=\"Center\" FontSize=\"10\"/>\n";
str += " </StackPanel>\n";
str += " </Button>\n";
str += "\n";
str += " <StackPanel.Background>\n";
str += " <LinearGradientBrush EndPoint=\"0.5,1\" StartPoint=\"0.5,0\">\n";
str += " <GradientStop Color=\"#FF0E78BA\" Offset=\"0\" />\n";
str += " <GradientStop Color=\"#FF013D98\" Offset=\"1\" />\n";
str += " </LinearGradientBrush>\n";
str += " </StackPanel.Background>\n";
str += " </StackPanel>\n";
str += "\n";
str += " <my:QueryTextBox Height=\"25\" Name=\"txtSearch\" Width=\"200\" HorizontalAlignment=\"Right\" Margin=\"0,15,15,10\" KeyUp=\"txtSearch_KeyUp\" />";
str += "\n";
str += "\n";
//str += " <StackPanel Margin=\"0,0,0,0\" Name=\"stackPanel1\" Orientation=\"Horizontal\" Grid.Row=\"0\" Background=\"#FFD3D3D8\" >\n";
//str += " <StackPanel.Effect>\n";
//str += " <DropShadowEffect/>\n";
//str += " </StackPanel.Effect>\n";
//str += " <Button Command=\"{Binding InsertCommand}\" Content=\"Add New\" Name=\"btnAdd\" Height=\"25\" Margin=\"5,0,0,0\" FontSize=\"9\" />\n";
//str += " <Button Content=\"Edit\" Name=\"btnEdit\" Command=\"{Binding EditCommand}\" Height=\"25\" FontSize=\"9\" />\n";
//str += " <Button Command=\"{Binding DeleteCommand}\" Content=\"Delete\" Name=\"btnDelete\" Height=\"25\" FontSize=\"9\" />\n";
//str += " <Button Command=\"{Binding RefreshCommand}\" Content=\"Refresh\" Name=\"btnRefresh\" Height=\"25\" FontSize=\"9\" />\n";
//str += " </StackPanel>\n\n";
// str += " <sdk:DataPager HorizontalContentAlignment=\"Right\" x:Name=\"myPager\" Source=\"{Binding Path=ItemsSource, ElementName=grd" + tableName + "}\" PageSize=\"20\" Grid.Row=\"0\" Width=\"200\" Margin=\"5\" HorizontalAlignment=\"Right\" Height=\"25\" />\n\n";
//str += " <sdk:DataPager HorizontalContentAlignment=\"Right\" x:Name=\"" + tableName + "Pager\" Source=\"{Binding Path=ItemsSource, ElementName=grd" + tableName + "}\" PageSize=\"20\" Grid.Row=\"0\" Width=\"400\" Margin=\"5\" HorizontalAlignment=\"Right\" Height=\"25\" Padding=\"1\" DisplayMode=\"FirstLastPreviousNextNumeric\" NumericButtonCount=\"10\" Cursor=\"Hand\" />\n";
str += " <jib:JibGrid AutoGenerateColumns=\"False\" IsReadOnly=\"True\" Name=\"grd" + tableName + "\" FilteredItemsSource=\"{Binding " + tableName.Substring(0, tableName.Length - 1) + adds + ", Mode=TwoWay}\" Grid.Row=\"2\"\n";
str += " SelectedItem=\"{Binding Selected" + tableName + ", Mode=TwoWay}\" RowBackground=\"#FFD2D3D4\" Background=\"#FFE0E2E5\" >\n";
str += " <sdk:DataGrid.Columns>\n";
for (int j = 0; j < dsColumns.Tables[0].Rows.Count; j++)
{
if (dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Contains("Is_Deleted"))
{
continue;
}
sql = @"SELECT FK.TABLE_NAME AS FK_Table, CU.COLUMN_NAME AS FK_Column, PK.TABLE_NAME AS PK_Table, PT.COLUMN_NAME AS PK_Column,
C.CONSTRAINT_NAME AS Constraint_Name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN
(SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS i1 INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE (i1.CONSTRAINT_TYPE = 'PRIMARY KEY')) AS PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE (FK.TABLE_NAME = '" + tableName + "') AND (CU.COLUMN_NAME = '" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + "') ORDER BY FK_Table, FK_Column, PK_Table, PK_Column";
DataSet dsFK = new DataSet();
m = cls.getData(sql, ref dsFK);
string Data_Type = "";
if (dsColumns.Tables[0].Rows[j]["Data_Type"] != DBNull.Value)
Data_Type = dsColumns.Tables[0].Rows[j]["Data_Type"].ToString();
string dType = "100";
switch (Data_Type)
{
case "int": dType = "80"; break;
case "datetime": dType = "150"; break;
case "bit": dType = "80"; break;
case "real": dType = "80"; break;
case "money": dType = "80"; break;
case "float": dType = "80"; break;
default: dType = "250"; break;
}
if (dsFK.Tables[0].Rows.Count == 0)
{
str += " <sdk:DataGridTextColumn Header=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + "\" Binding=\"{Binding " + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + "}\" Width=\"" + dType + "\" Foreground=\"Black\" />\n";
}
else
{
if (dsFK.Tables[0].Rows[0]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[0]["FK_Column"].ToString() != "Updated_By")
{
str += " <sdk:DataGridTextColumn Header=\"" + dsFK.Tables[0].Rows[0]["FK_Column"].ToString().Replace("_", " ").Replace("Id", "") + "\" Binding=\"{Binding " + dsFK.Tables[0].Rows[0]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "" + "}\" Width=\"" + 200 + "\" Foreground=\"Black\" />\n";
}
else
{
str += " <sdk:DataGridTextColumn Header=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + "\" Binding=\"{Binding " + dsFK.Tables[0].Rows[0]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "" + "}\" Width=\"" + dType + "\" Foreground=\"Black\" />\n";
//str += " <sdk:DataGridTextColumn Header=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + "\" Binding=\"{Binding " + dsFK.Tables[0].Rows[0]["PK_Table"].ToString().Replace("_", " ") + "_" + dsFK.Tables[0].Rows[0]["FK_Column"].ToString() + ".Name" + "}\" Width=\"" + dType + "\" Foreground=\"Black\" />\n";
}
}
}
str += " </sdk:DataGrid.Columns>\n";
str += " <toolkit:ContextMenuService.ContextMenu>\n";
str += " <toolkit:ContextMenu>\n";
str += " <toolkit:MenuItem Header=\"Add New\" Command=\"{Binding InsertCommand}\" >\n";
str += " <toolkit:MenuItem.Icon>\n";
str += " <Image Width=\"16\" Height=\"16\" Source=\"/" + namesp + ";component/Images/Add.png\" />\n";
str += " </toolkit:MenuItem.Icon>\n";
str += " </toolkit:MenuItem>\n";
str += "\n";
str += " <toolkit:MenuItem Header=\"Edit\" Command=\"{Binding EditCommand}\" >\n";
str += " <toolkit:MenuItem.Icon>\n";
str += " <Image Width=\"16\" Height=\"16\" Source=\"/" + namesp + ";component/Images/Edit.png\" />\n";
str += " </toolkit:MenuItem.Icon>\n";
str += " </toolkit:MenuItem>\n";
str += "\n";
str += " <toolkit:MenuItem Header=\"Delete\" Command=\"{Binding DeleteCommand}\" >\n";
str += " <toolkit:MenuItem.Icon>\n";
str += " <Image Width=\"16\" Height=\"16\" Source=\"/" + namesp + ";component/Images/Delete.png\" />\n";
str += " </toolkit:MenuItem.Icon>\n";
str += " </toolkit:MenuItem>\n";
str += "\n";
str += " <toolkit:MenuItem Header=\"Refresh\" Command=\"{Binding RefreshCommand}\" >\n";
str += " <toolkit:MenuItem.Icon>\n";
str += " <Image Width=\"16\" Height=\"16\" Source=\"/" + namesp + ";component/Images/Refresh.png\" />\n";
str += " </toolkit:MenuItem.Icon>\n";
str += " </toolkit:MenuItem>\n";
str += "\n";
str += " </toolkit:ContextMenu>\n";
str += " </toolkit:ContextMenuService.ContextMenu>\n";
str += "\n";
str += " </jib:JibGrid>\n";
str += " <sdk:DataPager x:Name=\"" + tableName + "Pager\" Source=\"{Binding Path=ItemsSource, ElementName=grd" + tableName + "}\" PageSize=\"20\" Grid.Row=\"3\" Cursor=\"Hand\" />\n";
str += " <toolkit:BusyIndicator Grid.Row=\"0\" Grid.RowSpan=\"4\" Name=\"busy" + tableName + "\" IsBusy=\"{Binding IsBusy,Mode=TwoWay}\" />\n";
//str += " <sdk:DataPager HorizontalContentAlignment=\"Right\" x:Name=\"" + tableName + "Pager\" Source=\"{Binding Path=ItemsSource, ElementName=grd" + tableName + "}\" PageSize=\"20\" Grid.Row=\"0\" Width=\"400\" Margin=\"5\" HorizontalAlignment=\"Right\" Height=\"25\" Padding=\"1\" DisplayMode=\"FirstLastPreviousNextNumeric\" NumericButtonCount=\"10\" Cursor=\"Hand\" />\n";
str += "\n";
str += " </Grid>\n";
str += "</navigation:Page>\n";
return str;
}
private string getAddEdit(string namesp, string tableName)
{
int Height = 171;
DataAccess cls = new DataAccess();
string sql = "select * from information_schema.columns where table_name = '" + tableName + "'";
DataSet dsColumns = new DataSet();
string m = cls.getData(sql, ref dsColumns);
Height = ((dsColumns.Tables[0].Rows.Count - 4) / 2 * 33) + 80;
string str = "";
str += "<sdk:ChildWindow\n";
str += " xmlns=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation\" \n";
str += " xmlns:x=\"http://schemas.microsoft.com/winfx/2006/xaml\" \n";
str += " xmlns:controls=\"clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls\"\n";
str += " xmlns:my=\"clr-namespace:" + namesp + ".Views\" \n";
str += " xmlns:sdk=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk\"\n";
str += " xmlns:toolkit=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation/toolkit\"\n";
str += " xmlns:d=\"http://schemas.microsoft.com/expression/blend/2008\"\n";
str += " xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\"\n";
str += " mc:Ignorable=\"d\"\n";
str += " x:Class=\"" + namesp + ".Views." + tableName + "AddEdit\"\n";
str += " Width=\"620\" Height=\"" + Height + "\"\n";
str += " Title=\"" + tableName.Replace("_", " ") + " Information\" Closed=\"ChildWindow_Closed\">\n\n";
str += " <Grid x:Name=\"LayoutRoot\" Margin=\"2\">\n\n";
//====================================RowDefinitions
str += " <Grid.RowDefinitions>\n";
for (int i = 0; i < (dsColumns.Tables[0].Rows.Count - 4) / 2; i++)
{
str += " <RowDefinition Height=\"33\" />\n";
}
str += " <RowDefinition Height=\"50*\" />\n";
str += " </Grid.RowDefinitions>\n";
//=================================== ColumnDefinitions
str += " <Grid.ColumnDefinitions>\n";
str += " <ColumnDefinition Width=\"100\" />\n";
str += " <ColumnDefinition Width=\"200\" />\n";
str += " <ColumnDefinition Width=\"100\" />\n";
str += " <ColumnDefinition Width=\"200\" />\n";
str += " </Grid.ColumnDefinitions>\n\n";
//======================================================
for (int j = 0; j < dsColumns.Tables[0].Rows.Count; j++)
{
if (dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() != "Is_Deleted" && dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() != "Created_By" && dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() != "Updated_By" && dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() != "Date_Created" && dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() != "Date_Updated")
{
sql = @"SELECT FK.TABLE_NAME AS FK_Table, CU.COLUMN_NAME AS FK_Column, PK.TABLE_NAME AS PK_Table, PT.COLUMN_NAME AS PK_Column,
C.CONSTRAINT_NAME AS Constraint_Name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN
(SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS i1 INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE (i1.CONSTRAINT_TYPE = 'PRIMARY KEY')) AS PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE (FK.TABLE_NAME = '" + tableName + "') AND (CU.COLUMN_NAME = '" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + "') ORDER BY FK_Table, FK_Column, PK_Table, PK_Column";
DataSet dsFK = new DataSet();
m = cls.getData(sql, ref dsFK);
string Data_Type = "";
if (dsColumns.Tables[0].Rows[j]["Data_Type"] != DBNull.Value)
{
Data_Type = dsColumns.Tables[0].Rows[j]["Data_Type"].ToString();
}
string dType = "100";
switch (Data_Type)
{
case "int": dType = "100"; break;
case "datetime": dType = "100"; break;
case "bit": dType = "80"; break;
case "real": dType = "80"; break;
case "money": dType = "80"; break;
case "float": dType = "80"; break;
default: dType = "200"; break;
}
if (dsFK.Tables[0].Rows.Count == 0)
{
int row = j / 2;
if (dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() == "Id")
{
str += " <sdk:Label Content=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + ":\" Grid.Column=\"0\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <TextBox Text=\"{Binding Selected" + tableName + "." + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + ", Mode=TwoWay}\" Grid.Column=\"1\" Grid.Row=\"" + row + "\" IsReadOnly=\"True\" Margin=\"10,3,10,3\"/>\n";
}
else
{
switch (Data_Type)
{
case "datetime":
{
if (j % 2 == 0)
{
str += " <sdk:Label Content=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + ":\" Grid.Column=\"0\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <sdk:DatePicker SelectedDate=\"{Binding Selected" + tableName + "." + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + ", Mode=TwoWay}\" Grid.Column=\"1\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"/>\n";
}
else
{
str += " <sdk:Label Content=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + ":\" Grid.Column=\"2\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <sdk:DatePicker SelectedDate=\"{Binding Selected" + tableName + "." + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + ", Mode=TwoWay}\" Grid.Column=\"3\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"/>\n";
}
str += "\n";
} break;
case "bit":
{
if (j % 2 == 0)
{
str += " <sdk:Label Content=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + ":\" Grid.Column=\"0\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <CheckBox IsChecked=\"{Binding Selected" + tableName + "." + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + ", Mode=TwoWay}\" Grid.Column=\"1\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"/>\n";
}
else
{
str += " <sdk:Label Content=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + ":\" Grid.Column=\"2\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <CheckBox IsChecked=\"{Binding Selected" + tableName + "." + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + ", Mode=TwoWay}\" Grid.Column=\"3\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"/>\n";
}
str += "\n";
} break;
default:
{
if (j % 2 == 0)
{
str += " <sdk:Label Content=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + ":\" Grid.Column=\"0\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <TextBox Text=\"{Binding Selected" + tableName + "." + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + ", Mode=TwoWay}\" Grid.Column=\"1\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"/>\n";
}
else
{
str += " <sdk:Label Content=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + ":\" Grid.Column=\"2\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <TextBox Text=\"{Binding Selected" + tableName + "." + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + ", Mode=TwoWay}\" Grid.Column=\"3\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"/>\n";
}
str += "\n";
} break;
}
}
}
else
{
str += "\n";
int row = j / 2;
string PK_Table = dsFK.Tables[0].Rows[0]["PK_Table"].ToString();
string adds = PK_Table.ElementAt(PK_Table.Length - 1) + "s";
if (PK_Table.ElementAt(PK_Table.Length - 1) == 'y')
{
adds = "ies";
}
if (PK_Table.ElementAt(PK_Table.Length - 1) == 's')
{
adds = "s";
}
if (j % 2 == 0)
{
str += " <sdk:Label Content=\"" + dsFK.Tables[0].Rows[0]["FK_Column"].ToString().Replace("_", " ").Replace("Id", "") + ":\" Grid.Column=\"0\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <ComboBox Grid.Column=\"1\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"\n";
str += " ItemsSource=\"{Binding " + dsFK.Tables[0].Rows[0]["CONSTRAINT_NAME"].ToString() + "}\"\n";
str += " DisplayMemberPath=\"Name\"\n";
str += " SelectedValuePath =\"" + dsFK.Tables[0].Rows[0]["PK_Column"].ToString() + "\"\n";
str += " SelectedValue=\"{Binding Selected" + tableName + "." + dsFK.Tables[0].Rows[0]["FK_Column"].ToString() + ", Mode=TwoWay}\"\n";
str += " />\n";
}
else
{
str += " <sdk:Label Content=\"" + dsFK.Tables[0].Rows[0]["FK_Column"].ToString().Replace("_", " ").Replace("Id", "") + ":\" Grid.Column=\"2\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <ComboBox Grid.Column=\"3\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"\n";
str += " ItemsSource=\"{Binding " + dsFK.Tables[0].Rows[0]["CONSTRAINT_NAME"].ToString() + "}\"\n";
str += " DisplayMemberPath=\"Name\"\n";
str += " SelectedValuePath =\"" + dsFK.Tables[0].Rows[0]["PK_Column"].ToString() + "\"\n";
str += " SelectedValue=\"{Binding Selected" + tableName + "." + dsFK.Tables[0].Rows[0]["FK_Column"].ToString() + ", Mode=TwoWay}\"\n";
str += " />\n";
}
}
}
}
int row2 = dsColumns.Tables[0].Rows.Count / 2 + 1;
str += " <Button Content=\"Close\" Click=\"CancelButton_Click\" Grid.Row=\"" + row2 + "\" Grid.Column=\"3\" HorizontalAlignment=\"Right\" FontSize=\"9\" Margin=\"0,0,5,0\" Height=\"25\" />\n";
str += " <Button Content=\"Save\" Grid.Row=\"" + row2 + "\" Grid.Column=\"3\" HorizontalAlignment=\"Left\" Margin=\"70,0,0,0\" Style=\"{StaticResource GreenButton}\" Click=\"Button_Click\" FontSize=\"9\" Height=\"25\" />\n";
str += " </Grid>\n";
str += "</sdk:ChildWindow>\n";
return(str);
}
private string getAddEdit(string namesp, string tableName)
{
int Height = 171;
DataAccess cls = new DataAccess();
string sql = "select * from information_schema.columns where table_name = '" + tableName + "'";
DataSet dsColumns = new DataSet();
string m = cls.getData(sql, ref dsColumns);
Height = ((dsColumns.Tables[0].Rows.Count - 4) / 2 * 33) + 80;
string str = "";
str += "<sdk:ChildWindow\n";
str += " xmlns=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation\" \n";
str += " xmlns:x=\"http://schemas.microsoft.com/winfx/2006/xaml\" \n";
str += " xmlns:controls=\"clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls\"\n";
str += " xmlns:my=\"clr-namespace:" + namesp + ".Views\" \n";
str += " xmlns:sdk=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk\"\n";
str += " xmlns:toolkit=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation/toolkit\"\n";
str += " xmlns:d=\"http://schemas.microsoft.com/expression/blend/2008\"\n";
str += " xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\"\n";
str += " mc:Ignorable=\"d\"\n";
str += " x:Class=\"" + namesp + ".Views." + tableName + "AddEdit\"\n";
str += " Width=\"620\" Height=\"" + Height + "\"\n";
str += " Title=\"" + tableName.Replace("_", " ") + " Information\" Closed=\"ChildWindow_Closed\">\n\n";
str += " <Grid x:Name=\"LayoutRoot\" Margin=\"2\">\n\n";
//====================================RowDefinitions
str += " <Grid.RowDefinitions>\n";
for (int i = 0; i < (dsColumns.Tables[0].Rows.Count - 4) / 2; i++)
{
str += " <RowDefinition Height=\"33\" />\n";
}
str += " <RowDefinition Height=\"50*\" />\n";
str += " </Grid.RowDefinitions>\n";
//=================================== ColumnDefinitions
str += " <Grid.ColumnDefinitions>\n";
str += " <ColumnDefinition Width=\"100\" />\n";
str += " <ColumnDefinition Width=\"200\" />\n";
str += " <ColumnDefinition Width=\"100\" />\n";
str += " <ColumnDefinition Width=\"200\" />\n";
str += " </Grid.ColumnDefinitions>\n\n";
//======================================================
for (int j = 0; j < dsColumns.Tables[0].Rows.Count; j++)
{
if (dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() != "Is_Deleted" && dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() != "Created_By" && dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() != "Updated_By" && dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() != "Date_Created" && dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() != "Date_Updated")
{
sql = @"SELECT FK.TABLE_NAME AS FK_Table, CU.COLUMN_NAME AS FK_Column, PK.TABLE_NAME AS PK_Table, PT.COLUMN_NAME AS PK_Column,
C.CONSTRAINT_NAME AS Constraint_Name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN
(SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS i1 INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE (i1.CONSTRAINT_TYPE = 'PRIMARY KEY')) AS PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE (FK.TABLE_NAME = '" + tableName + "') AND (CU.COLUMN_NAME = '" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + "') ORDER BY FK_Table, FK_Column, PK_Table, PK_Column";
DataSet dsFK = new DataSet();
m = cls.getData(sql, ref dsFK);
string Data_Type = "";
if (dsColumns.Tables[0].Rows[j]["Data_Type"] != DBNull.Value)
Data_Type = dsColumns.Tables[0].Rows[j]["Data_Type"].ToString();
string dType = "100";
switch (Data_Type)
{
case "int": dType = "100"; break;
case "datetime": dType = "100"; break;
case "bit": dType = "80"; break;
case "real": dType = "80"; break;
case "money": dType = "80"; break;
case "float": dType = "80"; break;
default: dType = "200"; break;
}
if (dsFK.Tables[0].Rows.Count == 0)
{
int row = j / 2;
if (dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() == "Id")
{
str += " <sdk:Label Content=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + ":\" Grid.Column=\"0\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <TextBox Text=\"{Binding Selected" + tableName + "." + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + ", Mode=TwoWay}\" Grid.Column=\"1\" Grid.Row=\"" + row + "\" IsReadOnly=\"True\" Margin=\"10,3,10,3\"/>\n";
}
else
{
switch (Data_Type)
{
case "datetime":
{
if (j % 2 == 0)
{
str += " <sdk:Label Content=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + ":\" Grid.Column=\"0\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <sdk:DatePicker SelectedDate=\"{Binding Selected" + tableName + "." + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + ", Mode=TwoWay}\" Grid.Column=\"1\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"/>\n";
}
else
{
str += " <sdk:Label Content=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + ":\" Grid.Column=\"2\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <sdk:DatePicker SelectedDate=\"{Binding Selected" + tableName + "." + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + ", Mode=TwoWay}\" Grid.Column=\"3\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"/>\n";
}
str += "\n";
} break;
case "bit":
{
if (j % 2 == 0)
{
str += " <sdk:Label Content=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + ":\" Grid.Column=\"0\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <CheckBox IsChecked=\"{Binding Selected" + tableName + "." + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + ", Mode=TwoWay}\" Grid.Column=\"1\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"/>\n";
}
else
{
str += " <sdk:Label Content=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + ":\" Grid.Column=\"2\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <CheckBox IsChecked=\"{Binding Selected" + tableName + "." + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + ", Mode=TwoWay}\" Grid.Column=\"3\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"/>\n";
}
str += "\n";
} break;
default:
{
if (j % 2 == 0)
{
str += " <sdk:Label Content=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + ":\" Grid.Column=\"0\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <TextBox Text=\"{Binding Selected" + tableName + "." + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + ", Mode=TwoWay}\" Grid.Column=\"1\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"/>\n";
}
else
{
str += " <sdk:Label Content=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + ":\" Grid.Column=\"2\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <TextBox Text=\"{Binding Selected" + tableName + "." + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + ", Mode=TwoWay}\" Grid.Column=\"3\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"/>\n";
}
str += "\n";
} break;
}
}
}
else
{
str += "\n";
int row = j / 2;
string PK_Table = dsFK.Tables[0].Rows[0]["PK_Table"].ToString();
string adds = PK_Table.ElementAt(PK_Table.Length - 1) + "s";
if (PK_Table.ElementAt(PK_Table.Length - 1) == 'y')
{
adds = "ies";
}
if (PK_Table.ElementAt(PK_Table.Length - 1) == 's')
{
adds = "s";
}
if (j % 2 == 0)
{
str += " <sdk:Label Content=\"" + dsFK.Tables[0].Rows[0]["FK_Column"].ToString().Replace("_", " ").Replace("Id", "") + ":\" Grid.Column=\"0\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <ComboBox Grid.Column=\"1\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"\n";
str += " ItemsSource=\"{Binding " + dsFK.Tables[0].Rows[0]["CONSTRAINT_NAME"].ToString() + "}\"\n";
str += " DisplayMemberPath=\"Name\"\n";
str += " SelectedValuePath =\"" + dsFK.Tables[0].Rows[0]["PK_Column"].ToString() + "\"\n";
str += " SelectedValue=\"{Binding Selected" + tableName + "." + dsFK.Tables[0].Rows[0]["FK_Column"].ToString() + ", Mode=TwoWay}\"\n";
str += " />\n";
}
else
{
str += " <sdk:Label Content=\"" + dsFK.Tables[0].Rows[0]["FK_Column"].ToString().Replace("_", " ").Replace("Id", "") + ":\" Grid.Column=\"2\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\" />\n";
str += " <ComboBox Grid.Column=\"3\" Grid.Row=\"" + row + "\" Margin=\"10,3,10,3\"\n";
str += " ItemsSource=\"{Binding " + dsFK.Tables[0].Rows[0]["CONSTRAINT_NAME"].ToString() + "}\"\n";
str += " DisplayMemberPath=\"Name\"\n";
str += " SelectedValuePath =\"" + dsFK.Tables[0].Rows[0]["PK_Column"].ToString() + "\"\n";
str += " SelectedValue=\"{Binding Selected" + tableName + "." + dsFK.Tables[0].Rows[0]["FK_Column"].ToString() + ", Mode=TwoWay}\"\n";
str += " />\n";
}
}
}
}
int row2 = dsColumns.Tables[0].Rows.Count / 2 + 1;
str += " <Button Content=\"Close\" Click=\"CancelButton_Click\" Grid.Row=\"" + row2 + "\" Grid.Column=\"3\" HorizontalAlignment=\"Right\" FontSize=\"9\" Margin=\"0,0,5,0\" Height=\"25\" />\n";
str += " <Button Content=\"Save\" Grid.Row=\"" + row2 + "\" Grid.Column=\"3\" HorizontalAlignment=\"Left\" Margin=\"70,0,0,0\" Style=\"{StaticResource GreenButton}\" Click=\"Button_Click\" FontSize=\"9\" Height=\"25\" />\n";
str += " </Grid>\n";
str += "</sdk:ChildWindow>\n";
return str;
}
private string getMainPage(string namesp, string tableName)
{
string str = "";
DataAccess cls = new DataAccess();
string sql = "select * from information_schema.columns where table_name = '" + tableName + "'";
DataSet dsColumns = new DataSet();
string m = cls.getData(sql, ref dsColumns);
//=================================================
string adds = tableName.ElementAt(tableName.Length - 1) + "s";
if (tableName.ElementAt(tableName.Length - 1) == 'y')
{
adds = "ies";
}
if (tableName.ElementAt(tableName.Length - 1) == 's')
{
adds = "s";
}
//========================================================
str += "<navigation:Page x:Class=\"" + namesp + ".Views." + tableName + "Page\" \n";
str += " xmlns=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation\" \n";
str += " xmlns:x=\"http://schemas.microsoft.com/winfx/2006/xaml\"\n";
str += " xmlns:d=\"http://schemas.microsoft.com/expression/blend/2008\"\n";
str += " xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\"\n";
str += " mc:Ignorable=\"d\"\n";
str += " xmlns:navigation=\"clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Navigation\"\n";
str += " xmlns:jib=\"clr-namespace:Jib.Controls.DataGrid;assembly=Jib.Controls\" \n";
str += " xmlns:my=\"clr-namespace:SilverlightClassLibrary1;assembly=SilverlightClassLibrary1\"\n";
str += " d:DesignWidth=\"940\" d:DesignHeight=\"480\"\n";
str += " Title=\"" + tableName + " Page\"\n";
str += " xmlns:sdk=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation/sdk\" \n";
str += " xmlns:toolkit=\"http://schemas.microsoft.com/winfx/2006/xaml/presentation/toolkit\">\n\n";
str += " <Grid x:Name=\"LayoutRoot\">\n";
str += " <Grid.RowDefinitions>\n";
str += " <RowDefinition Height=\"55\" />\n";
str += " <RowDefinition Height=\"5\" />\n";
str += " <RowDefinition Height=\"435*\" />\n";
str += " <RowDefinition Height=\"30\" />\n";
str += " <RowDefinition Height=\"5\" />\n";
str += " </Grid.RowDefinitions>\n\n";
str += " <StackPanel Margin=\"0,0,0,0\" Orientation=\"Horizontal\" Grid.Row=\"0\">\n";
str += " <StackPanel.Effect>\n";
str += " <DropShadowEffect/>\n";
str += " </StackPanel.Effect>\n";
str += "\n";
str += " <Button Width=\"80\" Cursor=\"Hand\" Style=\"{StaticResource GreenButton}\" Command=\"{Binding InsertCommand}\" Name=\"btnAdd\" Margin=\"5,0,0,0\">\n";
str += " <StackPanel Orientation=\"Vertical\" >\n";
str += " <Image Source=\"/" + namesp + ";component/Images/Add.png\" HorizontalAlignment=\"Center\" Height=\"30\" Width=\"30\"/>\n";
str += " <TextBlock Text=\"Add New\" HorizontalAlignment=\"Center\" FontSize=\"10\"/>\n";
str += " </StackPanel>\n";
str += " </Button>\n";
str += "\n";
str += " <Button Width=\"80\" Cursor=\"Hand\" Style=\"{StaticResource GreenButton}\" Command=\"{Binding EditCommand}\" Name=\"btnEdit\" Margin=\"5,0,0,0\">\n";
str += " <StackPanel Orientation=\"Vertical\" >\n";
str += " <Image Source=\"/" + namesp + ";component/Images/Edit.png\" HorizontalAlignment=\"Center\" Height=\"30\" Width=\"30\"/>\n";
str += " <TextBlock Text=\"Edit\" HorizontalAlignment=\"Center\" FontSize=\"10\"/>\n";
str += " </StackPanel>\n";
str += " </Button>\n";
str += "\n";
str += " <Button Width=\"80\" Cursor=\"Hand\" Style=\"{StaticResource GreenButton}\" Command=\"{Binding DeleteCommand}\" Name=\"btnDelete\" Margin=\"5,0,0,0\">\n";
str += " <StackPanel Orientation=\"Vertical\" >\n";
str += " <Image Source=\"/" + namesp + ";component/Images/Delete.png\" HorizontalAlignment=\"Center\" Height=\"30\" Width=\"30\"/>\n";
str += " <TextBlock Text=\"Delete\" HorizontalAlignment=\"Center\" FontSize=\"10\"/>\n";
str += " </StackPanel>\n";
str += " </Button>\n";
str += "\n";
str += " <Button Width=\"80\" Cursor=\"Hand\" Style=\"{StaticResource GreenButton}\" Command=\"{Binding RefreshCommand}\" Name=\"btnRefresh\" Margin=\"5,0,0,0\">\n";
str += " <StackPanel Orientation=\"Vertical\" >\n";
str += " <Image Source=\"/" + namesp + ";component/Images/Refresh.png\" HorizontalAlignment=\"Center\" Height=\"30\" Width=\"30\"/>\n";
str += " <TextBlock Text=\"Refresh\" HorizontalAlignment=\"Center\" FontSize=\"10\"/>\n";
str += " </StackPanel>\n";
str += " </Button>\n";
str += "\n";
str += " <Button Width=\"80\" Cursor=\"Hand\" Style=\"{StaticResource GreenButton}\" Name=\"btnPrint\" Margin=\"5,0,0,0\" Click=\"btnPrint_Click\">\n";
str += " <StackPanel Orientation=\"Vertical\" >\n";
str += " <Image Source=\"/" + namesp + ";component/Images/Print.png\" HorizontalAlignment=\"Center\" Height=\"30\" Width=\"30\"/>\n";
str += " <TextBlock Text=\"Print\" HorizontalAlignment=\"Center\" FontSize=\"10\"/>\n";
str += " </StackPanel>\n";
str += " </Button>\n";
str += "\n";
str += " <Button Width=\"80\" Cursor=\"Hand\" Style=\"{StaticResource GreenButton}\" Name=\"btnPrintPreview\" Margin=\"5,0,0,0\" Click=\"btnPrint_Click\">\n";
str += " <StackPanel Orientation=\"Vertical\" >\n";
str += " <Image Source=\"/" + namesp + ";component/Images/print-preview.png\" HorizontalAlignment=\"Center\" Height=\"30\" Width=\"30\"/>\n";
str += " <TextBlock Text=\"Preview\" HorizontalAlignment=\"Center\" FontSize=\"10\"/>\n";
str += " </StackPanel>\n";
str += " </Button>\n";
str += "\n";
str += " <StackPanel.Background>\n";
str += " <LinearGradientBrush EndPoint=\"0.5,1\" StartPoint=\"0.5,0\">\n";
str += " <GradientStop Color=\"#FF0E78BA\" Offset=\"0\" />\n";
str += " <GradientStop Color=\"#FF013D98\" Offset=\"1\" />\n";
str += " </LinearGradientBrush>\n";
str += " </StackPanel.Background>\n";
str += " </StackPanel>\n";
str += "\n";
str += " <my:QueryTextBox Height=\"25\" Name=\"txtSearch\" Width=\"200\" HorizontalAlignment=\"Right\" Margin=\"0,15,15,10\" KeyUp=\"txtSearch_KeyUp\" />";
str += "\n";
str += "\n";
//str += " <StackPanel Margin=\"0,0,0,0\" Name=\"stackPanel1\" Orientation=\"Horizontal\" Grid.Row=\"0\" Background=\"#FFD3D3D8\" >\n";
//str += " <StackPanel.Effect>\n";
//str += " <DropShadowEffect/>\n";
//str += " </StackPanel.Effect>\n";
//str += " <Button Command=\"{Binding InsertCommand}\" Content=\"Add New\" Name=\"btnAdd\" Height=\"25\" Margin=\"5,0,0,0\" FontSize=\"9\" />\n";
//str += " <Button Content=\"Edit\" Name=\"btnEdit\" Command=\"{Binding EditCommand}\" Height=\"25\" FontSize=\"9\" />\n";
//str += " <Button Command=\"{Binding DeleteCommand}\" Content=\"Delete\" Name=\"btnDelete\" Height=\"25\" FontSize=\"9\" />\n";
//str += " <Button Command=\"{Binding RefreshCommand}\" Content=\"Refresh\" Name=\"btnRefresh\" Height=\"25\" FontSize=\"9\" />\n";
//str += " </StackPanel>\n\n";
// str += " <sdk:DataPager HorizontalContentAlignment=\"Right\" x:Name=\"myPager\" Source=\"{Binding Path=ItemsSource, ElementName=grd" + tableName + "}\" PageSize=\"20\" Grid.Row=\"0\" Width=\"200\" Margin=\"5\" HorizontalAlignment=\"Right\" Height=\"25\" />\n\n";
//str += " <sdk:DataPager HorizontalContentAlignment=\"Right\" x:Name=\"" + tableName + "Pager\" Source=\"{Binding Path=ItemsSource, ElementName=grd" + tableName + "}\" PageSize=\"20\" Grid.Row=\"0\" Width=\"400\" Margin=\"5\" HorizontalAlignment=\"Right\" Height=\"25\" Padding=\"1\" DisplayMode=\"FirstLastPreviousNextNumeric\" NumericButtonCount=\"10\" Cursor=\"Hand\" />\n";
str += " <jib:JibGrid AutoGenerateColumns=\"False\" IsReadOnly=\"True\" Name=\"grd" + tableName + "\" FilteredItemsSource=\"{Binding " + tableName.Substring(0, tableName.Length - 1) + adds + ", Mode=TwoWay}\" Grid.Row=\"2\"\n";
str += " SelectedItem=\"{Binding Selected" + tableName + ", Mode=TwoWay}\" RowBackground=\"#FFD2D3D4\" Background=\"#FFE0E2E5\" >\n";
str += " <sdk:DataGrid.Columns>\n";
for (int j = 0; j < dsColumns.Tables[0].Rows.Count; j++)
{
if (dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Contains("Is_Deleted"))
{
continue;
}
sql = @"SELECT FK.TABLE_NAME AS FK_Table, CU.COLUMN_NAME AS FK_Column, PK.TABLE_NAME AS PK_Table, PT.COLUMN_NAME AS PK_Column,
C.CONSTRAINT_NAME AS Constraint_Name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN
(SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS i1 INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE (i1.CONSTRAINT_TYPE = 'PRIMARY KEY')) AS PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE (FK.TABLE_NAME = '" + tableName + "') AND (CU.COLUMN_NAME = '" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + "') ORDER BY FK_Table, FK_Column, PK_Table, PK_Column";
DataSet dsFK = new DataSet();
m = cls.getData(sql, ref dsFK);
string Data_Type = "";
if (dsColumns.Tables[0].Rows[j]["Data_Type"] != DBNull.Value)
{
Data_Type = dsColumns.Tables[0].Rows[j]["Data_Type"].ToString();
}
string dType = "100";
switch (Data_Type)
{
case "int": dType = "80"; break;
case "datetime": dType = "150"; break;
case "bit": dType = "80"; break;
case "real": dType = "80"; break;
case "money": dType = "80"; break;
case "float": dType = "80"; break;
default: dType = "250"; break;
}
if (dsFK.Tables[0].Rows.Count == 0)
{
str += " <sdk:DataGridTextColumn Header=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + "\" Binding=\"{Binding " + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString() + "}\" Width=\"" + dType + "\" Foreground=\"Black\" />\n";
}
else
{
if (dsFK.Tables[0].Rows[0]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[0]["FK_Column"].ToString() != "Updated_By")
{
str += " <sdk:DataGridTextColumn Header=\"" + dsFK.Tables[0].Rows[0]["FK_Column"].ToString().Replace("_", " ").Replace("Id", "") + "\" Binding=\"{Binding " + dsFK.Tables[0].Rows[0]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "" + "}\" Width=\"" + 200 + "\" Foreground=\"Black\" />\n";
}
else
{
str += " <sdk:DataGridTextColumn Header=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + "\" Binding=\"{Binding " + dsFK.Tables[0].Rows[0]["FK_Column"].ToString().Replace("_Id", "_Name").Replace("_", "") + "" + "}\" Width=\"" + dType + "\" Foreground=\"Black\" />\n";
//str += " <sdk:DataGridTextColumn Header=\"" + dsColumns.Tables[0].Rows[j]["COLUMN_NAME"].ToString().Replace("_", " ") + "\" Binding=\"{Binding " + dsFK.Tables[0].Rows[0]["PK_Table"].ToString().Replace("_", " ") + "_" + dsFK.Tables[0].Rows[0]["FK_Column"].ToString() + ".Name" + "}\" Width=\"" + dType + "\" Foreground=\"Black\" />\n";
}
}
}
str += " </sdk:DataGrid.Columns>\n";
str += " <toolkit:ContextMenuService.ContextMenu>\n";
str += " <toolkit:ContextMenu>\n";
str += " <toolkit:MenuItem Header=\"Add New\" Command=\"{Binding InsertCommand}\" >\n";
str += " <toolkit:MenuItem.Icon>\n";
str += " <Image Width=\"16\" Height=\"16\" Source=\"/" + namesp + ";component/Images/Add.png\" />\n";
str += " </toolkit:MenuItem.Icon>\n";
str += " </toolkit:MenuItem>\n";
str += "\n";
str += " <toolkit:MenuItem Header=\"Edit\" Command=\"{Binding EditCommand}\" >\n";
str += " <toolkit:MenuItem.Icon>\n";
str += " <Image Width=\"16\" Height=\"16\" Source=\"/" + namesp + ";component/Images/Edit.png\" />\n";
str += " </toolkit:MenuItem.Icon>\n";
str += " </toolkit:MenuItem>\n";
str += "\n";
str += " <toolkit:MenuItem Header=\"Delete\" Command=\"{Binding DeleteCommand}\" >\n";
str += " <toolkit:MenuItem.Icon>\n";
str += " <Image Width=\"16\" Height=\"16\" Source=\"/" + namesp + ";component/Images/Delete.png\" />\n";
str += " </toolkit:MenuItem.Icon>\n";
str += " </toolkit:MenuItem>\n";
str += "\n";
str += " <toolkit:MenuItem Header=\"Refresh\" Command=\"{Binding RefreshCommand}\" >\n";
str += " <toolkit:MenuItem.Icon>\n";
str += " <Image Width=\"16\" Height=\"16\" Source=\"/" + namesp + ";component/Images/Refresh.png\" />\n";
str += " </toolkit:MenuItem.Icon>\n";
str += " </toolkit:MenuItem>\n";
str += "\n";
str += " </toolkit:ContextMenu>\n";
str += " </toolkit:ContextMenuService.ContextMenu>\n";
str += "\n";
str += " </jib:JibGrid>\n";
str += " <sdk:DataPager x:Name=\"" + tableName + "Pager\" Source=\"{Binding Path=ItemsSource, ElementName=grd" + tableName + "}\" PageSize=\"20\" Grid.Row=\"3\" Cursor=\"Hand\" />\n";
str += " <toolkit:BusyIndicator Grid.Row=\"0\" Grid.RowSpan=\"4\" Name=\"busy" + tableName + "\" IsBusy=\"{Binding IsBusy,Mode=TwoWay}\" />\n";
//str += " <sdk:DataPager HorizontalContentAlignment=\"Right\" x:Name=\"" + tableName + "Pager\" Source=\"{Binding Path=ItemsSource, ElementName=grd" + tableName + "}\" PageSize=\"20\" Grid.Row=\"0\" Width=\"400\" Margin=\"5\" HorizontalAlignment=\"Right\" Height=\"25\" Padding=\"1\" DisplayMode=\"FirstLastPreviousNextNumeric\" NumericButtonCount=\"10\" Cursor=\"Hand\" />\n";
str += "\n";
str += " </Grid>\n";
str += "</navigation:Page>\n";
return(str);
}
public string getMethods(DataSet dsTableDefenation, string TableName)
{
string Methods = "{0}";
string sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
where FK.TABLE_NAME= '" + TableName + "' ORDER BY 1,2,3,4";
DataSet dsFK = new DataSet();
DataAccess cls = new DataAccess();
string m = cls.getData(sql, ref dsFK);
string str = "/****** Object: StoredProcedure [dbo].[sp_Add_" + TableName + "] Script Date: " + DateTime.Now + " ******/\n";
str += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Add_" + TableName + "]') AND type in (N'P', N'PC'))\n";
str += "DROP PROCEDURE [dbo].[sp_Add_" + TableName + "]\n";
str += "Go\n\n";
str += "/****** Object: StoredProcedure [dbo].[sp_Update_" + TableName + "] Script Date: " + DateTime.Now + " ******/\n";
str += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Update_" + TableName + "]') AND type in (N'P', N'PC'))\n";
str += "DROP PROCEDURE [dbo].[sp_Update_" + TableName + "]\n";
str += "Go\n\n";
str += "/****** Object: StoredProcedure [dbo].[sp_Delete_" + TableName + "] Script Date: " + DateTime.Now + " ******/\n";
str += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Delete_" + TableName + "]') AND type in (N'P', N'PC'))\n";
str += "DROP PROCEDURE [dbo].[sp_Delete_" + TableName + "]\n";
str += "Go\n\n";
str += "/****** Object: StoredProcedure [dbo].[sp_GetById_" + TableName + "] Script Date: " + DateTime.Now + " ******/\n";
str += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetById_" + TableName + "]') AND type in (N'P', N'PC'))\n";
str += "DROP PROCEDURE [dbo].[sp_GetById_" + TableName + "]\n";
str += "Go\n\n";
str += "/****** Object: StoredProcedure [dbo].[sp_FindAll_" + TableName + "] Script Date: " + DateTime.Now + " ******/\n";
str += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_FindAll_" + TableName + "]') AND type in (N'P', N'PC'))\n";
str += "DROP PROCEDURE [dbo].[sp_FindAll_" + TableName + "]\n";
str += "Go\n\n";
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
str += "/****** Object: StoredProcedure [dbo].[sp_GetBy" + "_" + TableName + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "] Script Date: " + DateTime.Now + " ******/\n";
str += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetBy" + "_" + TableName + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "]') AND type in (N'P', N'PC'))\n";
str += "DROP PROCEDURE [dbo].[sp_GetBy" + "_" + TableName + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "]\n";
str += "Go\n\n";
}
str += "\n\n--****** Object: StoredProcedure [dbo].[sp_Add_" + TableName + "] Script Date: " + DateTime.Now + " ******\n\n";
str += InsertStatment(dsTableDefenation, TableName);
str += "\n\n--****** Object: StoredProcedure [dbo].[sp_Update_" + TableName + "] Script Date: " + DateTime.Now + " ******\n\n";
str += "\n\n" + UpdateStatment(dsTableDefenation, TableName);
str += "\n\n--****** Object: StoredProcedure [dbo].[sp_Delete_" + TableName + "] Script Date: " + DateTime.Now + " ******\n\n";
str += "\n\n" + DeleteStatment(dsTableDefenation, TableName);
str += "\n\n--****** Object: StoredProcedure [dbo].[sp_GetById_" + TableName + "] Script Date: " + DateTime.Now + " ******\n\n";
str += "\n\n" + GetStatmentById(dsTableDefenation, TableName);
str += "\n\n--****** Object: StoredProcedure [dbo].[sp_FindAll_" + TableName + "] Script Date: " + DateTime.Now + " ******\n\n";
str += "\n\n" + GetStatmenFindAll(dsTableDefenation, TableName);
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
if (dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Updated_By")
{
str += "\n\n--****** Object: StoredProcedure [dbo].[sp_GetBy" + "_" + TableName + "_" + dsFK.Tables[0].Rows[j]["FK_Column"].ToString() + "] Script Date: " + DateTime.Now + " ******\n\n";
str += "\n\n" + GetStatmentByFK_Column(TableName, dsFK.Tables[0].Rows[j]["FK_Column"].ToString());
}
}
Methods = System.String.Format(Methods, str);
return Methods;
}
public string getFK_Method(string tableName)
{
string str = "";
string sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
where FK.TABLE_NAME= '" + tableName + "' ORDER BY 1,2,3,4";
DataSet ds = new DataSet();
DataAccess cls = new DataAccess();
string m = cls.getData(sql, ref ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
str += " public IEnumerable<" + ds.Tables[0].Rows[i]["PK_Table"].ToString() + "> get" + ds.Tables[0].Rows[i]["PK_Table"].ToString() + "()\n";
str += " {\n";
str += " List<" + ds.Tables[0].Rows[i]["PK_Table"].ToString() + "> entities = new List<" + ds.Tables[0].Rows[i]["PK_Table"].ToString() + ">();\n";
str += " " + ds.Tables[0].Rows[i]["PK_Table"].ToString() + "_Repository entity_rep = new " + ds.Tables[0].Rows[i]["PK_Table"].ToString() + "_Repository();\n";
str += " entities = entity_rep.FindAll();\n";
str += " return entities; \n";
str += " }\n";
str += "\n\n";
}
return str;
}
public string getMethods1(DataSet dsTableDefenation, string TableName)
{
string Statment = "\n";
Statment += " #region Methods\n";
Statment += " public " + TableName + "ViewModel()\n";
Statment += " {\n";
Statment += " IsBusy = true;\n";
string sql = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
where FK.TABLE_NAME= '" + TableName + "' ORDER BY 1,2,3,4";
DataSet dsFK = new DataSet();
DataAccess cls = new DataAccess();
string m = cls.getData(sql, ref dsFK);
bool foundCreatedBy = false;
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
if (dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Updated_By")
{
string PK_Table = dsFK.Tables[0].Rows[j]["PK_Table"].ToString();
string adds = PK_Table.ElementAt(PK_Table.Length - 1) + "s";
if (PK_Table.ElementAt(PK_Table.Length - 1) == 'y')
{
adds = "ies";
}
if (PK_Table.ElementAt(PK_Table.Length - 1) == 's')
{
adds = "s";
}
Statment += " if (m._context." + PK_Table.Substring(0, PK_Table.ToString().Length - 1) + "" + adds + ".Count == 0)\n";
Statment += " {\n";
Statment += " m._context.Load(m._context.get" + PK_Table.Substring(0, PK_Table.ToString().Length - 1) + "" + adds + "Query(), Load" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + "Completed, null);\n";
Statment += " }\n";
Statment += " else\n";
Statment += " {\n";
Statment += " _" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + " = new ObservableCollection<" + PK_Table + ">(m._context." + PK_Table.Substring(0, PK_Table.Length - 1) + "" + adds + ");\n";
Statment += " RaisePropertyChanged(\"" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + "\");\n";
Statment += " }\n\n";
}
else
{
foundCreatedBy = true;
}
}
if (foundCreatedBy == true)
{
Statment += " if (m._context.Users.Count == 0)\n";
Statment += " {\n";
Statment += " m._context.Load(m._context.getUsersQuery(), LoadUsersCompleted, null);\n";
Statment += " }\n";
Statment += " else\n";
Statment += " {\n";
Statment += " _updated_by = new ObservableCollection<User>(m._context.Users);\n\n";
Statment += " RaisePropertyChanged(\"Updated_By\");\n";
Statment += " _created_by = new ObservableCollection<User>(m._context.Users);\n";
Statment += " RaisePropertyChanged(\"Created_By\");\n";
Statment += " }\n";
}
string addss = TableName.ElementAt(TableName.Length - 1) + "s";
if (TableName.ElementAt(TableName.Length - 1) == 'y')
{
addss = "ies";
}
if (TableName.ElementAt(TableName.Length - 1) == 's')
{
addss = "s";
}
Statment += " if (m._context." + TableName.Substring(0, TableName.Length - 1) + addss + ".Count == 0)\n";
Statment += " {\n";
Statment += " m._context.Load(m._context.get" + TableName.Substring(0, TableName.Length - 1) + addss + "Query(), Load" + TableName.Substring(0, TableName.Length - 1) + addss + "Completed, null);\n";
Statment += " }\n";
Statment += " else\n";
Statment += " {\n";
Statment += " _" + TableName.Substring(0, TableName.Length - 1) + addss + " = new ObservableCollection<" + TableName + ">(m._context." + TableName.Substring(0, TableName.Length - 1) + addss + ");\n";
Statment += " RaisePropertyChanged(\"" + TableName.Substring(0, TableName.Length - 1) + addss + "\");\n";
Statment += " IsBusy = false;\n";
Statment += " }\n";
Statment += " }\n\n";
//=================================================================== Load Copleted
for (int j = 0; j < dsFK.Tables[0].Rows.Count; j++)
{
if (dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Created_By" && dsFK.Tables[0].Rows[j]["FK_Column"].ToString() != "Updated_By")
{
string PK_Table = dsFK.Tables[0].Rows[j]["PK_Table"].ToString();
string adds = PK_Table.ElementAt(PK_Table.Length - 1) + "s";
if (PK_Table.ElementAt(PK_Table.Length - 1) == 'y')
{
adds = "ies";
}
if (PK_Table.ElementAt(PK_Table.Length - 1) == 's')
{
adds = "s";
}
Statment += " private void Load" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + "Completed(LoadOperation<" + PK_Table + "> lo)\n";
Statment += " {\n";
Statment += " _" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + " = new ObservableCollection<" + PK_Table + ">(m._context." + PK_Table.Substring(0, PK_Table.Length - 1) + "" + adds + ");\n";
Statment += " RaisePropertyChanged(\"" + dsFK.Tables[0].Rows[j]["CONSTRAINT_NAME"].ToString() + "\");\n";
Statment += " }\n\n";
}
}
if (foundCreatedBy == true)
{
Statment += " private void LoadUsersCompleted(LoadOperation<User> lo)\n";
Statment += " {\n";
Statment += " _updated_by = new ObservableCollection<User>(m._context.Users);\n";
Statment += " RaisePropertyChanged(\"Updated_By\");\n";
Statment += "\n";
Statment += " _created_by = new ObservableCollection<User>(m._context.Users);\n";
Statment += " RaisePropertyChanged(\"Created_By\");\n";
Statment += " }\n";
}
Statment += " private void Load" + TableName.Substring(0, TableName.Length - 1) + addss + "Completed(LoadOperation<" + TableName + "> lo)\n";
Statment += " {\n";
Statment += " _" + TableName.Substring(0, TableName.Length - 1) + addss + " = new ObservableCollection<" + TableName + ">(m._context." + TableName.Substring(0, TableName.Length - 1) + addss + ");\n";
Statment += " RaisePropertyChanged(\"" + TableName.Substring(0, TableName.Length - 1) + addss + "\");\n";
Statment += " IsBusy = false;\n";
Statment += " }\n";
Statment += " #endregion\n";
Statment += "\n";
return Statment;
}