public TableNode(TableInfo table)
{
NodeType = NodeType.Table;
this.Text = table.TableName;
this.Table = table;
this.ImageIndex = DataConvert.GetInt32(IconType.Table);
this.SelectedImageIndex = DataConvert.GetInt32(IconType.Table);
}
/// <summary>
///
/// </summary>
/// <param name="ConnectionString"></param>
/// <param name="ServerName"></param>
/// <param name="DatabaseName"></param>
/// <returns></returns>
public static Collection<TableInfo> GetTables(string connectionstring, string servername, string databasename)
{
Collection<TableInfo> tbls = new Collection<TableInfo>();
DataTable dt = Sql2kDriver.GetTables(connectionstring, databasename);
foreach (DataRow dataRow in dt.Rows)
{
TableInfo tb = new TableInfo((string)dataRow["TABLE_NAME"], (string)dataRow["TABLE_SCHEMA"]);
tb.ServerName = servername;
tb.DatabaseName = databasename;
tb.Columns = GetColumns(connectionstring + "Database=" + databasename + ";", (string)dataRow["TABLE_NAME"]);
tbls.Add(tb);
}
return tbls;
}
public static TaskInfo GetTask(string taskid, string taskname)
{
try
{
XmlDocument objXmlDoc;
TaskInfo task = new TaskInfo();
if (taskid == null || taskid == string.Empty)
return null;
objXmlDoc = GetTaskConfigFile(taskid, taskname);
if (objXmlDoc == null)
return null;
task.TaskId = taskid;
task.TaskName = taskname;
//root node
XmlNode objTaskNode = objXmlDoc.SelectSingleNode(Constants.CONFIG_ROOT + Constants.CHAR_SLASH + Constants.TASK_TASK);
if (objTaskNode == null)
return null;
XmlNode objNode;
DataView dv;
/*------------------------------Code Generator-----------------------------------*/
objNode = objTaskNode.SelectSingleNode(Constants.COMP_CODEGENERATOR);
if (objNode != null)
{
XmlNode nodeDbServer = objNode.SelectSingleNode(Constants.COMP_CODEGENERATOR_DBSERVER);
task.DatabaseServer.ServerName = DataConvert.GetString(nodeDbServer.SelectSingleNode(Constants.COMP_CODEGENERATOR_SERVERNAME).InnerText);
task.DatabaseServer.DatabaseName = DataConvert.GetString(nodeDbServer.SelectSingleNode(Constants.COMP_CODEGENERATOR_DATABASENAME).InnerText);
task.DatabaseServer.ConnectionString = DataConvert.GetString(nodeDbServer.SelectSingleNode(Constants.COMP_CODEGENERATOR_CONNECTIONSTRING).InnerText);
task.Solution = DataConvert.GetString(objNode.SelectSingleNode(Constants.COMP_CODEGENERATOR_SOLUTION).InnerText);
task.Project = DataConvert.GetString(objNode.SelectSingleNode(Constants.COMP_CODEGENERATOR_PROJECT).InnerText);
task.Template = DataConvert.GetString(objNode.SelectSingleNode(Constants.COMP_CODEGENERATOR_TEMPLATE).InnerText);
task.NameSpacePrefix = DataConvert.GetString(objNode.SelectSingleNode(Constants.COMP_CODEGENERATOR_NAMESPACEPREFIX).InnerText);
task.NameSpaceSuffix = DataConvert.GetString(objNode.SelectSingleNode(Constants.COMP_CODEGENERATOR_NAMESPACESUFFIX).InnerText);
/*------------------------------Tables-----------------------------------*/
dv = GetData(objXmlDoc, Constants.CONFIG_ROOT + Constants.CHAR_SLASH + Constants.TASK_TASK + Constants.CHAR_SLASH + Constants.COMP_CODEGENERATOR + Constants.CHAR_SLASH + Constants.COMP_CODEGENERATOR_TABLES);
Collection<TableInfo> tables = new Collection<TableInfo>();
for (int ix = 0; ix < dv.Table.Rows.Count; ix++)
{
TableInfo table = new TableInfo();
table.TableName = DataConvert.GetString(dv.Table.Rows[ix][0]);
tables.Add(table);
}
task.Tables = tables;
/*------------------------------Modules-----------------------------------*/
dv = GetData(objXmlDoc, Constants.CONFIG_ROOT + Constants.CHAR_SLASH + Constants.TASK_TASK + Constants.CHAR_SLASH + Constants.COMP_CODEGENERATOR + Constants.CHAR_SLASH + Constants.COMP_CODEGENERATOR_MODULES);
Collection<ModuleInfo> modules = new Collection<ModuleInfo>();
for (int ix = 0; ix < dv.Table.Rows.Count; ix++)
{
ModuleInfo module = new ModuleInfo();
module.ModuleName = DataConvert.GetString(dv.Table.Rows[ix][0]);
modules.Add(module);
}
task.Modules = modules;
}
/*------------------------------Code Generator-----------------------------------*/
/*------------------------------Miscellaneous-----------------------------------*/
objNode = objTaskNode.SelectSingleNode(Constants.TASK_NODE_MISCELLANEOUS);
if (objNode != null)
{
if (objNode.SelectSingleNode(Constants.TASK_MISCELLANEOUS_WRITELOGTOFILE) != null)
task.WriteLogToFile = DataConvert.GetBool(objNode.SelectSingleNode(Constants.TASK_MISCELLANEOUS_WRITELOGTOFILE).InnerText);
else
task.WriteLogToFile = false;
}
/*------------------------------Miscellaneous-----------------------------------*/
return task;
}
catch (Exception ex)
{
Log4Helper.Write("ConfigCtrl.GetTask", taskname + "(" + taskid.ToString() + ")", ex, LogSeverity.Fatal);
throw;
}
}
public static void GenerateEntityXml(string connectionstring, string tablename, string schema, string servername, string databasename, string outputPath, string prefix, string suffix)
{
TableInfo tb = new TableInfo(tablename, schema);
tb.ServerName = servername;
tb.DatabaseName = databasename;
tb.Columns = DatabaseCtrl.GetColumns(connectionstring + "Database=" + databasename + ";", tablename);
GenerateEntityXmlFromTable(tb, outputPath, prefix, suffix);
}
public static void GenerateTextXmlFromTable(TableInfo table, string outputPath)
{
if (table == null || !Directory.Exists(outputPath))
return;
Log4Helper.Write("GenerateTextXmlFromTable", String.Format("Process of table {0} starts at {1}.", table.TableName, System.DateTime.Now.ToString("s")), LogSeverity.Info);
string entityName = GetModelName(table.TableName);
using (System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(System.IO.Path.Combine(outputPath, String.Concat(entityName, ".xml")), System.Text.Encoding.UTF8))
{
xtw.Formatting = System.Xml.Formatting.Indented;
xtw.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"UTF-8\"");
//generate entity calss
xtw.WriteStartElement("modeltext");
#region columns/properties
foreach (ColumnInfo c in table.Columns)
{
xtw.WriteStartElement("field");
xtw.WriteAttributeString("key", entityName + "_" + c.ColumnName);
xtw.WriteAttributeString("value", c.ColumnDescription);
xtw.WriteEndElement();
}
xtw.WriteEndElement();
#endregion
xtw.Flush();
xtw.Close();
}
Log4Helper.Write("GenerateTextXmlFromTable", String.Format("Process of table {0} ends at {1}.", table.TableName, System.DateTime.Now.ToString("s")), LogSeverity.Info);
}
public static void GenerateEntityXmlFromTable(TableInfo table, string outputPath, string prefix, string suffix)
{
if (table == null || !Directory.Exists(outputPath))
return;
Log4Helper.Write("GenerateEntityXmlFromTable", String.Format("Process of table {0} starts at {1}.", table.TableName, System.DateTime.Now.ToString("s")), LogSeverity.Info);
string entityName = GetModelName(table.TableName);
using (System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(System.IO.Path.Combine(outputPath, String.Concat(entityName, ".xml")), System.Text.Encoding.UTF8))
{
xtw.Formatting = System.Xml.Formatting.Indented;
xtw.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"UTF-8\"");
//generate entity calss
xtw.WriteStartElement("entity");
xtw.WriteAttributeString("name", entityName);
xtw.WriteAttributeString("modelName", string.Concat(prefix, ".OM.", suffix, ".", entityName));
xtw.WriteAttributeString("namespaceBLL", string.Concat(prefix, ".BLL.", suffix));
xtw.WriteAttributeString("namespaceDAL", string.Concat(prefix, ".DAL.", suffix));
xtw.WriteAttributeString("namespaceModel", string.Concat(prefix, ".OM.", suffix));
xtw.WriteAttributeString("author", UtilityHelper.GetCurrentIdentityName());
xtw.WriteAttributeString("createdDateTime", System.DateTime.Now.ToString("s"));
xtw.WriteAttributeString("schema", table.Schema);
xtw.WriteAttributeString("tableName", table.TableName);
xtw.WriteAttributeString("description", table.TableName);
#region primary key
xtw.WriteStartElement("primaryKey");
foreach (ColumnInfo col in table.Columns)
{
if (col.IsPrimaryKey)
{
xtw.WriteStartElement("column");
xtw.WriteAttributeString("columnName", col.ColumnName);
xtw.WriteAttributeString("lowerName", col.ColumnName.ToLower());
xtw.WriteAttributeString("sqlParameter", GetParameter(col.DataType, col.ColumnLength));
xtw.WriteEndElement();
}
}
xtw.WriteEndElement();
#endregion
#region ForeignKeys
//xtw.WriteStartElement("foreignKeys");
//foreach (ForeignKey fk in table.ForeignKeys)
//{
// xtw.WriteStartElement("foreignKey");
// xtw.WriteAttributeString("name", fk.Name);
// xtw.WriteAttributeString("referencedTableSchema", fk.ReferencedTableSchema);
// xtw.WriteAttributeString("referencedTable", fk.ReferencedTable);
// xtw.WriteAttributeString("referencedKey", fk.ReferencedKey);
// foreach (ForeignKeyColumn fkCol in fk.Columns)
// {
// xtw.WriteStartElement("column");
// xtw.WriteAttributeString("columnName", fkCol.Name);
// xtw.WriteAttributeString("referencedColumn", fkCol.ReferencedColumn);
// xtw.WriteEndElement();
// }
// xtw.WriteEndElement();
//}
//xtw.WriteEndElement();
//#endregion
//#region indexes
//xtw.WriteStartElement("indexes");
//foreach (Index idx in table.Indexes)
//{
// xtw.WriteStartElement("index");
// xtw.WriteAttributeString("name", idx.Name);
// xtw.WriteAttributeString("isClustered", idx.IsClustered.ToString(System.Globalization.CultureInfo.InvariantCulture));
// xtw.WriteAttributeString("isUnique", idx.IsUnique.ToString(System.Globalization.CultureInfo.InvariantCulture));
// xtw.WriteAttributeString("ignoreDuplicateKeys", idx.IgnoreDuplicateKeys.ToString(System.Globalization.CultureInfo.InvariantCulture));
// foreach (IndexedColumn idxcol in idx.IndexedColumns)
// {
// xtw.WriteStartElement("column");
// xtw.WriteAttributeString("columnName", idxcol.Name);
// xtw.WriteAttributeString("descending", idxcol.Descending.ToString(System.Globalization.CultureInfo.InvariantCulture));
// //xtw.WriteAttributeString("isIncluded", idxcol.IsIncluded.ToString(System.Globalization.CultureInfo.InvariantCulture));
// xtw.WriteEndElement();
// }
// xtw.WriteEndElement();
//}
//xtw.WriteEndElement();
#endregion
#region columns/properties
xtw.WriteStartElement("columns");
foreach (ColumnInfo c in table.Columns)
{
GenerateXmlElementFromColumn(c, xtw);
}
xtw.WriteEndElement();
#endregion
xtw.WriteEndElement();
xtw.Flush();
xtw.Close();
}
Log4Helper.Write("GenerateEntityXmlFromTable", String.Format("Process of table {0} ends at {1}.", table.TableName, System.DateTime.Now.ToString("s")), LogSeverity.Info);
}
/*
#region old
public static event CountUpdate DatabaseCounted;
public static event CountUpdate TableCounted;
/// <summary>
/// Generates the SQL and C# code for the specified database.
/// </summary>
/// <param name="outputDirectory">The directory where the C# and SQL code should be created.</param>
/// <param name="connectionString">The connection string to be used to connect the to the database.</param>
/// <param name="grantLoginName">The SQL Server login name that should be granted execute rights on the generated stored procedures.</param>
/// <param name="storedProcedurePrefix">The prefix that should be used when creating stored procedures.</param>
/// <param name="createMultipleFiles">A flag indicating if the generated stored procedures should be created in one file or separate files.</param>
/// <param name="targetNamespace">The namespace that the generated C# classes should contained in.</param>
/// <param name="daoSuffix">The suffix to be applied to all generated DAO classes.</param>
public static void Generate(string outputDirectory, string connectionString, string grantLoginName, string storedProcedurePrefix, bool createMultipleFiles, string targetNamespace, string daoSuffix)
{
List<Table> tableList = new List<Table>();
string databaseName;
string sqlPath;
string csPath;
using (SqlConnection connection = new SqlConnection(connectionString))
{
databaseName = Utility.FormatPascal(connection.Database);
sqlPath = Path.Combine(outputDirectory, "SQL");
csPath = Path.Combine(outputDirectory, "CS");
connection.Open();
// Get a list of the entities in the database
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(Utility.GetTableQuery(connection.Database), connection);
dataAdapter.Fill(dataTable);
// Process each table
foreach (DataRow dataRow in dataTable.Rows)
{
Table table = new Table();
table.Name = (string) dataRow["TABLE_NAME"];
QueryTable(connection, table);
tableList.Add(table);
}
}
DatabaseCounted(null, new CountEventArgs(tableList.Count));
// Generate the necessary SQL and C# code for each table
int count = 0;
if (tableList.Count > 0)
{
// Create the necessary directories
Utility.CreateSubDirectory(sqlPath, true);
Utility.CreateSubDirectory(csPath, true);
Utility.CreateSubDirectory(Path.Combine(csPath, "Data"), true);
Utility.CreateSubDirectory(Path.Combine(csPath, "Data\\Mocks"), true);
Utility.CreateSubDirectory(Path.Combine(csPath, "Services"), true);
// Create the necessary database logins
SqlGenerator.CreateUserQueries(databaseName, grantLoginName, sqlPath, createMultipleFiles);
// Create the CRUD stored procedures and data access code for each table
foreach (Table table in tableList)
{
SqlGenerator.CreateInsertStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
SqlGenerator.CreateUpdateStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
SqlGenerator.CreateDeleteStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
SqlGenerator.CreateDeleteAllByStoredProcedures(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
SqlGenerator.CreateSelectStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
SqlGenerator.CreateSelectAllStoredProcedure(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
SqlGenerator.CreateSelectAllByStoredProcedures(table, grantLoginName, storedProcedurePrefix, sqlPath, createMultipleFiles);
CsGenerator.CreateDataTransferClass(table, targetNamespace, storedProcedurePrefix, csPath);
CsGenerator.CreateDataAccessClass(databaseName, table, targetNamespace, storedProcedurePrefix, daoSuffix, csPath);
CsGenerator.CreateMockClass(table, targetNamespace, storedProcedurePrefix, daoSuffix, csPath);
count++;
TableCounted(null, new CountEventArgs(count));
}
CsGenerator.CreateSharpCore(csPath);
CsGenerator.CreateAssemblyInfo(csPath, databaseName, databaseName);
CsGenerator.CreateService(csPath, targetNamespace);
CsGenerator.CreateServiceUtility(csPath, targetNamespace);
CsGenerator.CreateProjectFile(csPath, databaseName, tableList, daoSuffix);
ConfigGenerator.CreateAppConfig(outputDirectory, tableList, databaseName, connectionString, targetNamespace, daoSuffix);
ConfigGenerator.CreateMockConfig(outputDirectory, tableList, databaseName, connectionString, targetNamespace, daoSuffix);
}
}
/// <summary>
/// Retrieves the column, primary key, and foreign key information for the specified table.
/// </summary>
/// <param name="connection">The SqlConnection to be used when querying for the table information.</param>
/// <param name="table">The table instance that information should be retrieved for.</param>
private static void QueryTable(SqlConnection connection, Table table)
{
// Get a list of the entities in the database
DataTable dataTable = new DataTable();
SqlDataAdapter dataAdapter = new SqlDataAdapter(Utility.GetColumnQuery(table.Name), connection);
dataAdapter.Fill(dataTable);
foreach (DataRow columnRow in dataTable.Rows)
{
Column column = new Column();
column.Name = columnRow["COLUMN_NAME"].ToString();
column.Type = columnRow["DATA_TYPE"].ToString();
column.Precision = columnRow["NUMERIC_PRECISION"].ToString();
column.Scale = columnRow["NUMERIC_SCALE"].ToString();
// Determine the column's length
if (columnRow["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
{
column.Length = columnRow["CHARACTER_MAXIMUM_LENGTH"].ToString();
}
else
{
column.Length = columnRow["COLUMN_LENGTH"].ToString();
}
// Is the column a RowGuidCol column?
if (columnRow["IS_ROWGUIDCOL"].ToString() == "1")
{
column.IsRowGuidCol = true;
}
// Is the column an Identity column?
if (columnRow["IS_IDENTITY"].ToString() == "1")
{
column.IsIdentity = true;
}
// Is columnRow column a computed column?
if (columnRow["IS_COMPUTED"].ToString() == "1")
{
column.IsComputed = true;
}
table.Columns.Add(column);
}
// Get the list of primary keys
DataTable primaryKeyTable = Utility.GetPrimaryKeyList(connection, table.Name);
foreach (DataRow primaryKeyRow in primaryKeyTable.Rows)
{
string primaryKeyName = primaryKeyRow["COLUMN_NAME"].ToString();
foreach (Column column in table.Columns)
{
if (column.Name == primaryKeyName)
{
table.PrimaryKeys.Add(column);
break;
}
}
}
// Get the list of foreign keys
DataTable foreignKeyTable = Utility.GetForeignKeyList(connection, table.Name);
foreach (DataRow foreignKeyRow in foreignKeyTable.Rows)
{
string name = foreignKeyRow["FK_NAME"].ToString();
string columnName = foreignKeyRow["FKCOLUMN_NAME"].ToString();
if (table.ForeignKeys.ContainsKey(name) == false)
{
table.ForeignKeys.Add(name, new List<Column>());
}
List<Column> foreignKeys = table.ForeignKeys[name];
foreach (Column column in table.Columns)
{
if (column.Name == columnName)
{
foreignKeys.Add(column);
break;
}
}
}
}
#endregion
#region GenerateCode
public static void GenerateCode(string xmlRootPath, string xsltRootPath, string outputRootPath, string outputFileExtension)
{
if (Directory.Exists(xmlRootPath) && Directory.Exists(xsltRootPath))
{
string[] xsltDiretories = Directory.GetDirectories(xsltRootPath);
string[] xmlFiles = Directory.GetFiles(xmlRootPath);
for (int ix = 0; ix < xsltDiretories.Length; ix++)
{
string strProjectName = GetTemplateName(xsltDiretories[ix]);
string xsltFileName = Path.Combine(xsltDiretories[ix], String.Concat(strProjectName, ".xslt"));
if (File.Exists(xsltFileName))
{
if (!Directory.Exists(Path.Combine(outputRootPath, strProjectName)))
{
Directory.CreateDirectory(Path.Combine(outputRootPath, strProjectName));
}
Transform(xsltFileName, xmlFiles, Path.Combine(outputRootPath, strProjectName), outputFileExtension);
string[] csFiles = new string[xmlFiles.Length];
for (int iy = 0; iy < xmlFiles.Length; iy++)
{
csFiles[iy] = String.Concat(Path.GetFileNameWithoutExtension(xmlFiles[iy]), ".", outputFileExtension);
}
string projectFileName = Path.Combine(outputRootPath, strProjectName) + String.Concat("\\", strProjectName, ".csproj");
BuildProject(Path.Combine(xsltDiretories[ix], String.Concat(strProjectName, ".csproj")), csFiles, projectFileName);
}
}
}
}
#endregion
#region BuildProject
public static void BuildProject(string projPath, string[] csFiles, string outputPath)
{
StreamReader sr = new StreamReader(projPath);
string template = sr.ReadToEnd();
StringBuilder sb = new StringBuilder();
foreach (string csFileName in csFiles)
{
//<Compile Include="ServerInfo.cs" />
sb.Append(String.Concat(" <Compile Include=\"", csFileName, "\" />\r\n"));
}
sb.Remove(sb.Length - 2, 2);
template = template.Replace("$CsFiles$", sb.ToString());
StreamWriter sw = new StreamWriter(outputPath);
sw.Write(template);
sw.Close();
sw = null;
}
#endregion
#region GetTemplateName
private static string GetTemplateName(string path)
{
if (path == null || path == string.Empty)
return "";
return path.Substring(path.LastIndexOf("\\") + 1);
}
#endregion
*/
public static void BulkGenerator(TableInfo table, string entityfolder, string prefix, string suffix, string xmlfile, string xsltfile, string outputfile)
{
GenerateEntityXmlFromTable(table, entityfolder, prefix, suffix);
if (!String.IsNullOrEmpty(xsltfile) && !String.IsNullOrEmpty(xmlfile))
{
if (File.Exists(xsltfile) && File.Exists(xmlfile))
{
using (StringWriter resultWriter = new StringWriter(System.Globalization.CultureInfo.CurrentUICulture))
{
//Load xslt via XmlTextReader
XslCompiledTransform xslCompiledTransform = new XslCompiledTransform();
xslCompiledTransform.Load(xsltfile, XsltSettings.Default, new XmlUrlResolver());
//transform the input to output
xslCompiledTransform.Transform(xmlfile, null, resultWriter);
string retVal = resultWriter.ToString();
resultWriter.Close();
StreamWriter sw = new StreamWriter(outputfile);
sw.Write(retVal);
sw.Close();
sw = null;
}
}
}
}