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);
}
