public static Dictionary <string, KeyColumn> GetKeysDictionary(string tableName) { Dictionary <string, KeyColumn> collection = new Dictionary <string, KeyColumn>(); string connectionString = Session.ConnectionString; string sql = string.Format("select * from information_schema.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME LIKE '%PK_%' AND TABLE_NAME = '{0}'", tableName); SqlDataReader dataReader = SqlHelperSqlServer.ExecuteReader(connectionString, CommandType.Text, sql); Dictionary <string, string> fields = GetFieldsEasy(tableName); try { int sequence = 1; while (dataReader.Read()) { //TODO: Per ora le chiavi le ipotizzo solo GUID KeyColumn column = new KeyColumn(); column.Name = dataReader["COLUMN_NAME"].ToString(); column.Unique = true; column.Type = fields[column.Name]; column.NetType = GetDatatableSqlServer.GetSystemType(column.Type, false); column.Sequence = sequence; collection.Add(column.Name, column); sequence++; } } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (!dataReader.IsClosed) { dataReader.Close(); } dataReader.Dispose(); } return(collection); }
public static string GetStored(SqlConnection connection, string tableName, string type) { string storedFunctions = string.Empty; SqlConnection conn = new SqlConnection(Session.ConnectionString); ArrayList storedProcedures = GetReader(conn, tableName); DataTable tableParameters = GetDatatableSqlServer.GetDatatableSchema(); foreach (string storedProcedure in storedProcedures) { SqlCommand cmd = new SqlCommand(storedProcedure, conn); cmd.CommandType = CommandType.StoredProcedure; conn.Open(); SqlCommandBuilder.DeriveParameters(cmd); foreach (SqlParameter parameter in cmd.Parameters) { if (!parameter.ParameterName.Equals("@RETURN_VALUE")) { DataRow row = tableParameters.NewRow(); row["Name"] = parameter.ParameterName.Replace("@", string.Empty); row["Type"] = GetDatatableSqlServer.GetSystemType(parameter.SqlDbType.ToString().ToLower(), parameter.IsNullable); row["DbType"] = parameter.SqlDbType; row["Length"] = parameter.Size; row["Nullable"] = parameter.IsNullable; row["Table"] = storedProcedure; tableParameters.Rows.Add(row); } } storedFunctions = string.Concat(storedFunctions, BuildStoredProcedureList(tableParameters, tableName, type), "\n\n\t\t"); conn.Close(); tableParameters.Clear(); } return(storedFunctions); }
public bool BuildControlsPage(string nameSpace, ArrayList tables, string pathfile, SqlConnection connection) { bool result = false; foreach (DataTable dt in tables) { string getData = string.Empty; string setData = string.Empty; string templatePopulateList = string.Empty; string tableName = dt.Rows[0]["Table"].ToString().Trim(); if (getData.Equals(string.Empty)) { if (Session.Modality == Modality.Professional) { if (getData.Equals(string.Empty)) { getData = string.Concat("\t\titem = Manager", tableName, ".GetByKeyuni(Key);\n"); } } if (Session.Modality == Modality.Easy) { //if (getData.Equals(string.Empty)) getData = "\t\titem = item.GetByKeyuni(Key);\n"; } } string template = "<table>"; foreach (DataRow dr in dt.Rows) { if ((!dr["Name"].Equals(Session.TableKey) && (!dr["Name"].Equals("Keyuni")) && (!dr["Name"].Equals("Timespan")))) { string templateControl = string.Empty; string tableLink = string.Empty; #region GerValue string name = dr["Name"].ToString(); string type = dr["Type"].ToString(); string dbType = dr["DbType"].ToString(); int length = (int)dr["Length"]; bool nullable = (bool)dr["Nullable"]; bool isSearch = false; int searchPosition = 0; bool isDetail = false; int detailPosition = 0; string descriptionName = string.Empty; GetDatatableSqlServer.GetPlusInformation(tableName, name, ref descriptionName, ref isSearch, ref searchPosition, ref isDetail, ref detailPosition); if (descriptionName.Equals(string.Empty)) { descriptionName = name; } #endregion switch (type) { case "decimal": case "int": if (type.Equals("int")) { tableLink = GetInfoFieldSqlServer.IsForeignKeySimple(tableName, name); } if (!tableLink.Equals(string.Empty)) { templateControl = GetLabel(templateControl, name, descriptionName); templateControl = string.Concat(templateControl, ManageTemplate.GetTemplate("WebTemplate//dropdownlist.ico")); templateControl = templateControl.Replace("***Name***", name); getData = string.Concat(getData, WriteGetDataControl(name, type, WebControl.DropDownList)); setData = string.Concat(setData, WriteSetDataControl(name, type, WebControl.DropDownList, nullable)); string nameTable = name.Replace("Code", string.Empty); string populateList = ManageTemplate.GetTemplate("WebTemplate//populateList.ico"); populateList = populateList.Replace("***Name***", nameTable); templatePopulateList = string.Concat(templatePopulateList, populateList); break; } else { templateControl = GetLabel(templateControl, name, descriptionName); templateControl = string.Concat(templateControl, ManageTemplate.GetTemplate("WebTemplate//textbox.ico")); templateControl = templateControl.Replace("***Name***", name); templateControl = templateControl.Replace("#Length#", length.ToString()); templateControl = templateControl.Replace("#SkinId#", GetSkinByLength(length)); getData = string.Concat(getData, WriteGetDataControl(name, type, WebControl.TextBox)); setData = string.Concat(setData, WriteSetDataControl(name, type, WebControl.TextBox, nullable)); } break; case "string": templateControl = GetLabel(templateControl, name, descriptionName); templateControl = string.Concat(templateControl, ManageTemplate.GetTemplate("WebTemplate//textbox.ico")); templateControl = templateControl.Replace("***Name***", name); templateControl = templateControl.Replace("#Length#", length.ToString()); templateControl = templateControl.Replace("#SkinId#", GetSkinByLength(length)); getData = string.Concat(getData, WriteGetDataControl(name, type, WebControl.TextBox)); setData = string.Concat(setData, WriteSetDataControl(name, type, WebControl.TextBox, nullable)); break; case "System.DateTime": templateControl = GetLabel(templateControl, name, descriptionName); templateControl = string.Concat(templateControl, ManageTemplate.GetTemplate("WebTemplate//calendar.ico")); templateControl = templateControl.Replace("***Name***", name); templateControl = templateControl.Replace("#SkinId#", GetSkinByLength(length)); getData = string.Concat(getData, WriteGetDataControl(name, type, WebControl.Calendar)); setData = string.Concat(setData, WriteSetDataControl(name, type, WebControl.Calendar, nullable)); break; } if ((!nullable)) { string templateRequiredFieldValidator = string.Empty; templateRequiredFieldValidator = string.Concat(ManageTemplate.GetTemplate("WebTemplate//requiredFieldValidator.ico")); templateRequiredFieldValidator = templateRequiredFieldValidator.Replace("***Name***", name); templateControl = string.Concat(templateControl, " ", templateRequiredFieldValidator); } if (!templateControl.Equals(string.Empty)) { templateControl = CloseRow(templateControl); } template = string.Concat(template, templateControl); } } template = string.Concat(template, "</table>"); if (ManageTemplate.WriteTemplate(string.Concat(pathfile, "WebControls//"), template, tableName, ".aspx")) { result = true; } else { result = false; } #region GerData string templateGetData = string.Concat(ManageTemplate.GetTemplate("WebTemplate//getData.ico")); templateGetData = templateGetData.Replace("***Body***", getData); string templateSetData = string.Concat(ManageTemplate.GetTemplate("WebTemplate//setData.ico")); templateSetData = templateSetData.Replace("***Body***", setData); string finallyTemplate = string.Concat(templateGetData, "\n\n", templateSetData); bool pageEasy = true; if (pageEasy) { string templateHtml = string.Concat(ManageTemplate.GetTemplate("WebForm//pageListDetail.aspx")); templateHtml = templateHtml.Replace("***Name***", tableName); templateHtml = templateHtml.Replace("#WebControls#", template); if (ManageTemplate.WriteTemplate(string.Concat(pathfile, "WebControls//Page//"), templateHtml, tableName, "ListDetail.aspx")) { result = true; } string templateCode = string.Concat(ManageTemplate.GetTemplate("WebForm//pageListDetail.aspx.ico")); templateCode = templateCode.Replace("***Name***", tableName); templateCode = templateCode.Replace("#GetData#", getData); templateCode = templateCode.Replace("#SetData#", setData); templateCode = templateCode.Replace("#PopulateList#", templatePopulateList); if (ManageTemplate.WriteTemplate(string.Concat(pathfile, "WebControls//Page//"), templateCode, tableName, "ListDetail.aspx.ico")) { result = true; } } else { string templateHtml = string.Concat(ManageTemplate.GetTemplate("WebForm//pageDetail.aspx")); templateHtml = templateHtml.Replace("***Name***", tableName); templateHtml = templateHtml.Replace("#WebControls#", template); if (ManageTemplate.WriteTemplate(string.Concat(pathfile, "WebControls//Page//"), templateHtml, tableName, "Detail.aspx")) { result = true; } string templateCode = string.Concat(ManageTemplate.GetTemplate("WebForm//pageDetail.aspx.ico")); templateCode = templateCode.Replace("***Name***", tableName); templateCode = templateCode.Replace("#GetData#", getData); templateCode = templateCode.Replace("#SetData#", setData); templateCode = templateCode.Replace("#PopulateList#", templatePopulateList); if (ManageTemplate.WriteTemplate(string.Concat(pathfile, "WebControls//Page//"), templateCode, tableName, "Detail.aspx.ico")) { result = true; } } #endregion } return(true); }