protected void DetailsView_ItemCommand(object sender, DetailsViewCommandEventArgs e)
{
InfoList.Items.Clear();
switch (e.CommandName) {
case "TestWeb":
try
{
IBaseDriver drv = null;
switch (mm.DbServer)
{
case DbServer.MySql:
drv = new BaseDriverMySql(((TextBox)(DetailsView.Rows[1].Cells[1].Controls[0])).Text);
break;
case DbServer.MsSql:
drv = new BaseDriverMsSql(((TextBox)(DetailsView.Rows[1].Cells[1].Controls[0])).Text);
break;
}
drv.TestConnection();
InfoList.Items.Add("Connection successful");
}
catch (Exception ew) {
InfoList.Items.Add(ew.Message);
}
break;
case "TestIS":
try
{
IBaseDriver drv = null;
switch (mm.DbServer)
{
case DbServer.MySql:
drv = new BaseDriverMySql(((TextBox)(DetailsView.Rows[3].Cells[1].Controls[0])).Text);
break;
case DbServer.MsSql:
drv = new BaseDriverMsSql(((TextBox)(DetailsView.Rows[3].Cells[1].Controls[0])).Text);
break;
}
drv.TestConnection();
InfoList.Items.Add("Connection successful");
}
catch (Exception ei)
{
InfoList.Items.Add(ei.Message);
}
break;
case "Cancel":
Response.RedirectToRoute("ProjectsRoute");
break;
case "Edit":
DetailsView.ChangeMode(DetailsViewMode.Edit);
break;
}
}
public StatsMsSql(BaseDriverMsSql driver)
{
this.driver = driver;
}
/// <summary>
/// extracts information from the COLUMNS table of INFORMATION_SCHEMA
/// sets each column`s datatype, default value, nullability, autoincrement, uniqueness
/// and if the datatype is ENUM, sets the extended property as a List<string> labeled Common.Constants.ENUM_COLUMN_VALUES
/// </summary>
private void GetColumnTypes()
{
DataTable stats = driver.fetchAll("SELECT COLS.*,"
+ " COLUMNPROPERTY(OBJECT_ID(COLS.TABLE_NAME), COLS.COLUMN_NAME, 'IsIdentity') AS 'HAS_IDENTITY'"
+ " FROM INFORMATION_SCHEMA.COLUMNS AS COLS"
+ " JOIN INFORMATION_SCHEMA.TABLES AS TBLS ON COLS.TABLE_NAME = TBLS.TABLE_NAME " +
"WHERE TBLS.TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' ORDER BY COLS.TABLE_NAME, ORDINAL_POSITION");
Dictionary<string, DataColumnCollection> res = new Dictionary<string, DataColumnCollection>();
BaseDriverMsSql tempWebDb = new BaseDriverMsSql(Common.Environment.project.ConnstringWeb);
tempWebDb.BeginTransaction();
//tempWebDb.query("SET SQL_SELECT_LIMIT=0");
foreach (string tableName in TableList())
{
DataTable schema = tempWebDb.fetchAll("SELECT TOP 0 * FROM ", dbe.Table(tableName));
res[tableName] = schema.Columns;
}
//tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT");
tempWebDb.CommitTransaction();
tempWebDb = null;
foreach (DataRow r in stats.Rows)
{
DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string]; // set ColumnName
//string dataType = r["DATA_TYPE"] as string;
if (col.DataType == typeof(string))
{
col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
}
bool hasIdentity = (int)r["HAS_IDENTITY"] == 1; // set AutoIncrement
if (hasIdentity)
col.AutoIncrement = true;
if (!col.AutoIncrement)
col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules...
object colDefault = r["COLUMN_DEFAULT"]; // set DefaultValue
if (!((colDefault is DBNull) || (colDefault.ToString() == String.Empty)) && col.DefaultValue == DBNull.Value)
{
string colDefaltStr = colDefault as string;
// similiar to StatsMySql save for the condition on CURRENT_TIMESTAMP - this could probably be expressed in various ways fro the MSSQL.
// The result will be though the same, just one additional call to refrection when trying to parse the funcion signature into a datetime.
if (col.DataType == typeof(string))
{
col.DefaultValue = colDefaltStr;
}
else if (col.DataType == typeof(bool))
{
switch (colDefaltStr)
{
case "0":
col.DefaultValue = false;
break;
case "1":
col.DefaultValue = true;
break;
default:
break;
}
}
else
{
object parsed = null;
if (Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed))
{
col.DefaultValue = parsed;
}
}
}
col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES";
try
{
if (!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull)) col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
}
catch { }
} // for each row in stats
columnTypes = res;
ColumnsToDisplay = new Dictionary<string, List<string>>();
IComparer<DataColumn> comparer = new Common.ColumnDisplayComparer();
foreach (string tableName in columnTypes.Keys)
{
List<DataColumn> innerList = new List<DataColumn>();
foreach (DataColumn col in columnTypes[tableName])
innerList.Add(col);
innerList.Sort(comparer);
columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList<string>();
}
foreach (string tableName in FKs.Keys)
{
foreach (FK fk in FKs[tableName])
{
columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk;
}
}
}
/// <summary>
/// The main entry point of the application - initializes database drivers, checks access rights, detects the current page type and does more setting accordingly.
/// </summary>
protected void Page_Init(object sender, EventArgs e)
{
DbServer = (DbServer)Enum.Parse(typeof(DbServer), System.Configuration.ConfigurationManager.AppSettings["ServerType"] as string);
bool isFirstRun = System.Configuration.ConfigurationManager.AppSettings["FirstRun"] as string == "True";
// detect the site type based on the beginning of the URL
string lp = Request.Url.LocalPath;
if (lp.StartsWith("/architect")){
Common.Environment.GlobalState = GlobalState.Architect;
}
else if (lp.StartsWith("/admin")) {
Common.Environment.GlobalState = GlobalState.Administer;
}
else if(lp == "/sys/users"){
Common.Environment.GlobalState = GlobalState.UsersManagement;
}
else if(lp == "/sys/projects"){
Common.Environment.GlobalState = GlobalState.ProjectsManagement;
}
else if(lp.StartsWith("/account")){
Common.Environment.GlobalState = GlobalState.Account;
}
else if(lp.StartsWith("/FirstRun")){
Common.Environment.GlobalState = GlobalState.FirstRun;
}
else
Common.Environment.GlobalState = GlobalState.Error;
bool firstRunMono = System.Configuration.ConfigurationManager.AppSettings["FirstRunMono"] == "True";
if(isFirstRun && Common.Environment.GlobalState != GlobalState.FirstRun && !firstRunMono)
{
Response.Redirect("~/FirstRun/FirstRun.aspx");
}
if(!isFirstRun && Common.Environment.GlobalState == GlobalState.FirstRun)
{
Response.RedirectToRoute("DefaultRoute");
}
// set the warning only for logged in users
System.Configuration.ConfigurationManager.AppSettings["SessionWarning"] =
(user is MembershipUser) ? (Session.Timeout - 5).ToString() : "-1";
if (isFirstRun)
{
return;
}
user = Membership.GetUser();
// session expiry means logout, even if the provider would keep the user logged in
if ((Session.IsNewSession || user == null)
&& CE.GlobalState != GlobalState.Account && CE.GlobalState != GlobalState.Error)
{
FormsAuthentication.SignOut();
Response.RedirectToRoute("LockoutRoute", new { message = 7 });
}
IBaseDriver systemBaseDriver = null;
// initialize the system driver based on the server type read from the configuration
switch (DbServer)
{
case DbServer.MySql:
systemBaseDriver = new BaseDriverMySql(ConfigurationManager.ConnectionStrings["MySqlServer"].ConnectionString);
break;
case DbServer.MsSql:
systemBaseDriver = new BaseDriverMsSql(ConfigurationManager.ConnectionStrings["MsSqlServer"].ConnectionString);
break;
default:
break;
}
SysDriver = new SystemDriver(systemBaseDriver);
if (firstRunMono && CE.GlobalState != GlobalState.FirstRun)
{
Response.Redirect("~/FirstRun/FirstRunMono.aspx");
}
if (!firstRunMono && CE.GlobalState == GlobalState.FirstRun)
{
Response.RedirectToRoute("DefaultRoute");
}
if (firstRunMono)
{
return;
}
// global service
// is there a need for a reload of the project architecture?
bool NewProjectLoad = false;
// get current project and init drivers and architect
if (Page.RouteData.Values.ContainsKey("projectName"))
{
ProjectName = Page.RouteData.Values["projectName"] as string;
CE.Project actProject = SysDriver.GetProject(ProjectName);
if (CE.project == null || actProject.Id != CE.project.Id || actProject.Version != CE.project.Version)
{
Session.Clear(); // may not be neccessary in all cases, but better be safe
NewProjectLoad = true;
}
CE.project = SysDriver.GetProject(ProjectName);
IBaseDriver statsBaseDriver = null;
IBaseDriver webBaseDriver = null;
switch (CE.project.ServerType)
{
case DbServer.MySql:
statsBaseDriver = new BaseDriverMySql(CE.project.ConnstringIS);
Stats = new StatsMySql((BaseDriverMySql)statsBaseDriver, CE.project.WebDbName);
webBaseDriver = new BaseDriverMySql(CE._project.ConnstringWeb);
break;
case DbServer.MsSql:
statsBaseDriver = new BaseDriverMsSql(CE.project.ConnstringIS);
Stats = new StatsMsSql((BaseDriverMsSql)statsBaseDriver);
webBaseDriver = new BaseDriverMsSql(CE._project.ConnstringWeb);
break;
default:
break;
}
WebDriver = new WebDriver(webBaseDriver);
Architect = new _min.Models.Architect(SysDriver, Stats);
if ((!Page.IsPostBack || NewProjectLoad) && CE.GlobalState != GlobalState.Error)
// new version or differnet page ~ othervise access must have remained
// at least "allowable", if not allowed
{
LockingAccess(); // just check
}
// check whether there is something to load at all
if (Page.RouteData.Route != RouteTable.Routes["ArchitectInitRoute"])
{
if (!SysDriver.ProposalExists())
{
if (CE.GlobalState == GlobalState.Architect)
{
Response.RedirectToRoute("ArchitectInitRoute", new { projectName = Page.RouteData.Values["projectName"] });
Response.End();
}
else
{
// change to some kind of "Not found" page
Response.RedirectToRoute("DefaultRoute", new { projectName = Page.RouteData.Values["projectName"] });
Response.End();
}
}
// get the current architecture - either extract from Session or directry from the DB, if project version has changed
int actVersion = CE.project.Version;
if (Session[CC.SESSION_ARCHITECTURE] is _min.Models.Panel
&& Session[CC.SESSION_ARCHITECTURE_VERSION] is int
&& (int)Session[CC.SESSION_ARCHITECTURE_VERSION] == actVersion)
{
SysDriver.SetArchitecture((MPanel)Session[CC.SESSION_ARCHITECTURE]);
}
else
{
SysDriver.FullProjectLoad();
Session[CC.SESSION_ARCHITECTURE] = SysDriver.MainPanel;
Session[CC.SESSION_ARCHITECTURE_VERSION] = CE.project.Version;
}
}
}
// local issues
if (!Page.IsPostBack)
{
if (user != null)
{
List<string> adminOf;
List<string> architectOf;
List<CE.Project> allProjects = SysDriver.GetProjectObjects();
List<string> allNames = (from CE.Project p in allProjects select p.Name).ToList<string>();
object userId = user.ProviderUserKey;
int globalRights = SysDriver.GetUserRights(userId, null);
// by default, fetch only the sites to which the access rights are set explicitly,
// if global rights are sufficient, replace them with the complete lists
SysDriver.UserMenuOptions(userId, out adminOf, out architectOf);
if (globalRights % 100 >= 10) adminOf = allNames;
if (globalRights % 1000 >= 100) architectOf = allNames;
// decide on the upper menu content
MenuItem administerItem = new MenuItem("Administer", "admin");
foreach (string site in adminOf)
{
administerItem.ChildItems.Add(new MenuItem(site, site, null, "/admin/" + site));
}
if (adminOf.Count > 0)
NavigationMenu.Items.AddAt(0, administerItem);
// architect menu
MenuItem architectItem = new MenuItem("Architect", "architect");
foreach (string site in architectOf)
{
architectItem.ChildItems.Add(new MenuItem(site, site, null, "/architect/show/" + Server.UrlEncode(site)));
}
if (architectOf.Count > 0)
NavigationMenu.Items.AddAt(1, architectItem);
// user & projects management
NavigationMenu.Items.Add(new MenuItem("Manage users", "users", null, "/sys/users"));
if (globalRights >= 10000) // this is the one and only project manager for this application instance
NavigationMenu.Items.Add(new MenuItem("Manage projects", "projects", null, "/sys/projects"));
// account settings for logged in users
MenuItem accountItem = new MenuItem("Account", "account");
accountItem.ChildItems.Add(new MenuItem("Change password", null, null, "/account/change-password"));
accountItem.ChildItems.Add(new MenuItem("Logout", null, null, "/account/logout"));
NavigationMenu.Items.Add(accountItem);
}
else {
MenuItem accountItem = new MenuItem("Account", "account");
accountItem.ChildItems.Add(new MenuItem("Login", null, null, "/account/login"));
accountItem.ChildItems.Add(new MenuItem("Register", null, null, "/account/register"));
NavigationMenu.Items.Add(accountItem);
}
NavigationMenu.RenderingMode = MenuRenderingMode.Table;
}
}
/// <summary>
/// checks if edited fields exist in webDB and their types are adequate, checks constraints on FKs and mapping tables,
/// also checks if controls` dataTables` columns exist and everything that has to be inserted in DB is a required field,
/// whether attributes don`t colide and every panel has something to display.
/// </summary>
/// <param name="proposalPanel"></param>
/// <param name="recursive">run itself on panel children</param>
/// <returns>true if no errors were found, true otherwise</returns>
public bool checkPanelProposal(Panel proposalPanel, out List <string> errorMsgs, Dictionary <DataColumn, Dictionary <string, object> > customs)
// non-recursive checking after the initial check - after panel modification
{
errorMsgs = new List <string>();
DataColumnCollection cols = stats.ColumnTypes[proposalPanel.tableName];
List <FK> FKs = stats.FKs[proposalPanel.tableName];
List <M2NMapping> mappings = stats.Mappings[proposalPanel.tableName];
bool good = true;
if (proposalPanel.type == PanelTypes.Editable)
// this is indeed the only panelType containing fields => only editable
{
foreach (IField field in proposalPanel.fields)
{
if (!(field is IColumnField))
{
continue; // M2Ns are OK - strict UI
}
IColumnField cf = (IColumnField)field;
string messageBeginning = "Column " + cf.ColumnName + " managed by field " + cf.Caption + " ";
if (!(cols.Contains(cf.ColumnName)))
{
errorMsgs.Add(messageBeginning + "does not exist in table");
good = false;
}
else
{
if (!(cf is FKField)) // NavTable won`t be edited in the panel
{
if (cols[cf.ColumnName].AllowDBNull == false && // things that have to be filled in must be required
!cols[cf.ColumnName].AutoIncrement &&
!(cf is CheckboxField) &&
!cf.Required)
{
errorMsgs.Add(messageBeginning
+ "cannot be set to null, but the coresponding field is not required");
good = false;
}
// PBPR
// other validation requirements will be fullfilled based on the field type - the panel editation GUI will not let the user
// select a field type that is inconsistent with the column datatype
if (cols[cf.ColumnName].Unique && !cf.Unique)
{
errorMsgs.Add(messageBeginning + " is constrained to be unique, but the corresponding field does not have "
+ "the \"Unique\" validation rule set.");
good = false;
}
else if (cf.Unique && !cols[cf.ColumnName].Unique) // require uniqueness that is not guaranteed by the database
{
IBaseDriver tmpBaseDriver = null;
switch (CE.project.ServerType)
{
case DbServer.MySql:
tmpBaseDriver = new BaseDriverMySql(CE.project.ConnstringWeb);
break;
case DbServer.MsSql:
tmpBaseDriver = new BaseDriverMsSql(CE.project.ConnstringWeb);
break;
default:
break;
}
bool truelyUnique = tmpBaseDriver.CheckUniquenessManually(proposalPanel.tableName, cf.ColumnName);
if (!truelyUnique)
{
errorMsgs.Add(String.Format("{0} is not unique in its values - the constraint cannot be enforced.", messageBeginning));
good = false;
}
}
}
}
}
// Columns that are not covered and should be - allowdbnull is false, they do not have any default value
// and are not AutoIncrements and none of the fields refers to them.
var colsx = stats.ColumnTypes[proposalPanel.tableName];
IEnumerable <string> requiredColsMissing = from DataColumn col in stats.ColumnTypes[proposalPanel.tableName]
where col.AllowDBNull == false && col.AutoIncrement == false &&
(col.DefaultValue == null || col.DefaultValue == DBNull.Value) &&
!proposalPanel.fields.Exists(x => x is IColumnField && ((IColumnField)x).ColumnName == col.ColumnName) &&
(!customs.ContainsKey(col) || (bool)customs[col]["required"] == false)
select col.ColumnName;
// if the colum is contained in customs, only the custom factory validation will be left
//so this validation does not need to be called again
foreach (string missingCol in requiredColsMissing)
{
good = false;
errorMsgs.Add("Column " + missingCol + " cannot be NULL and has no default value." +
" It must therefore be included in the panel");
}
if (proposalPanel.panelName == "")
{
errorMsgs.Add("Does this panel not deserve a name?");
good = false;
}
if (proposalPanel.controls.Count == 0)
{
errorMsgs.Add("A panel with no controls would be useless...");
}
}
else
{
throw new Exception("Validation-non editable panel as an editable one.");
}
return(good);
}
// non-recursive checking after the initial check - after panel modification
/// <summary>
/// checks if edited fields exist in webDB and their types are adequate, checks constraints on FKs and mapping tables,
/// also checks if controls` dataTables` columns exist and everything that has to be inserted in DB is a required field,
/// whether attributes don`t colide and every panel has something to display.
/// </summary>
/// <param name="proposalPanel"></param>
/// <param name="recursive">run itself on panel children</param>
/// <returns>true if no errors were found, true otherwise</returns>
public bool checkPanelProposal(Panel proposalPanel, out List<string> errorMsgs, Dictionary<DataColumn, Dictionary<string, object>> customs)
{
errorMsgs = new List<string>();
DataColumnCollection cols = stats.ColumnTypes[proposalPanel.tableName];
List<FK> FKs = stats.FKs[proposalPanel.tableName];
List<M2NMapping> mappings = stats.Mappings[proposalPanel.tableName];
bool good = true;
if (proposalPanel.type == PanelTypes.Editable)
// this is indeed the only panelType containing fields => only editable
{
foreach (IField field in proposalPanel.fields)
{
if (!(field is IColumnField)) continue; // M2Ns are OK - strict UI
IColumnField cf = (IColumnField)field;
string messageBeginning = "Column " + cf.ColumnName + " managed by field " + cf.Caption + " ";
if (!(cols.Contains(cf.ColumnName)))
{
errorMsgs.Add(messageBeginning + "does not exist in table");
good = false;
}
else
{
if (!(cf is FKField)) // NavTable won`t be edited in the panel
{
if (cols[cf.ColumnName].AllowDBNull == false && // things that have to be filled in must be required
!cols[cf.ColumnName].AutoIncrement &&
!(cf is CheckboxField) &&
!cf.Required)
{
errorMsgs.Add(messageBeginning
+ "cannot be set to null, but the coresponding field is not required");
good = false;
}
// PBPR
// other validation requirements will be fullfilled based on the field type - the panel editation GUI will not let the user
// select a field type that is inconsistent with the column datatype
if (cols[cf.ColumnName].Unique && !cf.Unique)
{
errorMsgs.Add(messageBeginning + " is constrained to be unique, but the corresponding field does not have "
+ "the \"Unique\" validation rule set.");
good = false;
}
else if (cf.Unique && !cols[cf.ColumnName].Unique) // require uniqueness that is not guaranteed by the database
{
IBaseDriver tmpBaseDriver = null;
switch (CE.project.ServerType)
{
case DbServer.MySql:
tmpBaseDriver = new BaseDriverMySql(CE.project.ConnstringWeb);
break;
case DbServer.MsSql:
tmpBaseDriver = new BaseDriverMsSql(CE.project.ConnstringWeb);
break;
default:
break;
}
bool truelyUnique = tmpBaseDriver.CheckUniquenessManually(proposalPanel.tableName, cf.ColumnName);
if (!truelyUnique)
{
errorMsgs.Add(String.Format("{0} is not unique in its values - the constraint cannot be enforced.", messageBeginning));
good = false;
}
}
}
}
}
// Columns that are not covered and should be - allowdbnull is false, they do not have any default value
// and are not AutoIncrements and none of the fields refers to them.
var colsx = stats.ColumnTypes[proposalPanel.tableName];
IEnumerable<string> requiredColsMissing = from DataColumn col in stats.ColumnTypes[proposalPanel.tableName]
where col.AllowDBNull == false && col.AutoIncrement == false &&
(col.DefaultValue == null || col.DefaultValue == DBNull.Value) &&
!proposalPanel.fields.Exists(x => x is IColumnField && ((IColumnField)x).ColumnName == col.ColumnName)
&& (!customs.ContainsKey(col) || (bool)customs[col]["required"] == false)
select col.ColumnName;
// if the colum is contained in customs, only the custom factory validation will be left
//so this validation does not need to be called again
foreach (string missingCol in requiredColsMissing)
{
good = false;
errorMsgs.Add("Column " + missingCol + " cannot be NULL and has no default value." +
" It must therefore be included in the panel");
}
if (proposalPanel.panelName == "")
{
errorMsgs.Add("Does this panel not deserve a name?");
good = false;
}
if (proposalPanel.controls.Count == 0)
{
errorMsgs.Add("A panel with no controls would be useless...");
}
}
else throw new Exception("Validation-non editable panel as an editable one.");
return good;
}
protected void SaveButton_Click(object sender, EventArgs e)
{
Errors.Items.Clear();
string serverType = ServerTypeDrop.SelectedValue;
DbServer serverTypeParsed;
if (!Enum.TryParse<DbServer>(serverType, out serverTypeParsed))
{
Errors.Items.Add("Please, choose the type of database engine you wish to use.");
return;
}
// initial testing of the database connection before we attempt to create the main schema
IBaseDriver drv = null;
switch (serverTypeParsed)
{
case DbServer.MySql:
drv = new BaseDriverMySql(SystemConnstringTextBox.Text);
break;
case DbServer.MsSql:
drv = new BaseDriverMsSql(SystemConnstringTextBox.Text);
break;
}
try
{
drv.TestConnection();
drv.TestDatabaseIsEmpty();
}
catch (Exception ex)
{
Errors.Items.Add(ex.Message);
return;
}
if (UsernameTextBox.Text == "")
{
Errors.Items.Add("Please, insert the initial user's name");
return;
}
if (PasswordTextBox.Text.Length < 7)
{
Errors.Items.Add("The password must be at least 7 characters long.");
return;
}
if (PasswordTextBox.Text != RetypePasswordTextBox.Text)
{
Errors.Items.Add("The passwords do not match.");
return;
}
try
{
System.Net.Mail.MailAddress address = new System.Net.Mail.MailAddress(MailTextBox.Text);
}
catch (FormatException fe)
{
Errors.Items.Add(fe.Message);
return;
}
// run the schema dump script
switch (serverTypeParsed)
{
case DbServer.MySql:
MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(SystemConnstringTextBox.Text);
try
{
MySql.Data.MySqlClient.MySqlScript script = new MySql.Data.MySqlClient.MySqlScript(connection);
string scriptText = File.ReadAllText(HttpContext.Current.Server.MapPath(CC.MYSQL_SCHEMA_FILE_PATH));
script.Query = scriptText;
script.Query = scriptText;
connection.Open();
script.Execute();
connection.Clone();
}
catch (Exception esql1)
{
Errors.Items.Add(esql1.Message);
connection.Close();
return;
}
break;
case DbServer.MsSql:
SqlConnection conn = new SqlConnection(SystemConnstringTextBox.Text);
try
{
string query = File.ReadAllText(HttpContext.Current.Server.MapPath(CC.MSSQL_SCHEMA_FILE_PATH));
Microsoft.SqlServer.Management.Smo.Server sqlServer = new Server(new ServerConnection(conn));
conn.Open();
sqlServer.ConnectionContext.ExecuteNonQuery(query);
conn.Close();
SqlMembershipProvider mssqlProvider = new SqlMembershipProvider();
}
catch (Exception esql2)
{
Errors.Items.Add(esql2.Message);
conn.Close();
return;
}
break;
}
var configuration = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~");
var section = (ConnectionStringsSection)configuration.GetSection("connectionStrings");
System.Web.Security.MembershipProvider membership = null;
string username = UsernameTextBox.Text;
string password = PasswordTextBox.Text;
string mail = MailTextBox.Text;
MembershipCreateStatus status;
// rewrite the connection in the database and reload the connstring section, also set the defaultProvidder for the membership tag
switch (serverTypeParsed)
{
case DbServer.MySql:
section.ConnectionStrings["MySqlServer"].ConnectionString = SystemConnstringTextBox.Text;
configuration.AppSettings.Settings["ServerType"].Value = "MySql";
configuration.Save();
SetDefaultMembershipProvider("MySqlMembershipProvider");
// remove the readonly attribute of the connection string variable of the connfiguration
var settingsMy = ConfigurationManager.ConnectionStrings["MsSqlServer"];
var fiMy = typeof( ConfigurationElement ).GetField( "_bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic );
fiMy.SetValue(settingsMy, false);
settingsMy.ConnectionString = SystemConnstringTextBox.Text;
membership = Membership.Providers["MySqlMembershipProvider"];
membership.CreateUser(username, password, mail, "Dummy question", "Dummy answer", true, 1, out status);
break;
case DbServer.MsSql:
section.ConnectionStrings["MsSqlServer"].ConnectionString = SystemConnstringTextBox.Text;
configuration.AppSettings.Settings["ServerType"].Value = "MsSql";
configuration.Save();
SetDefaultMembershipProvider("MsSqlMembershipProvider");
// remove the readonly attribute of the connection string variable of the connfiguration
var settings = ConfigurationManager.ConnectionStrings["MsSqlServer"];
var fi = typeof( ConfigurationElement ).GetField( "_bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic );
fi.SetValue(settings, false);
settings.ConnectionString = SystemConnstringTextBox.Text;
membership = Membership.Providers["MsSqlMembershipProvider"];
// generate a ProviderUserKey
Random rand = new Random();
Guid key = new Guid(rand.Next(), 2, 3, new byte[] { 10, 20, 30, 40, 50, 60, 70, 80 });
((SqlMembershipProvider)membership).CreateUser(username, password, mail, "Dummy question", "Dummy answer", true, key, out status);
break;
}
int totalUsers;
MembershipUser user = membership.FindUsersByName(username, 0, 1, out totalUsers)[username];
SystemDriver sysDriver = new SystemDriver(drv);
sysDriver.SetUserRights((user.ProviderUserKey), null, 11110);
// Set FirstRun to false. This cannot be done by the first configuration object - it wil
// not like the configuration file since it has been modified by SetDefaultMembershipProvider
// in the meantime.
var config2 = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~");
config2.AppSettings.Settings["FirstRun"].Value = "False";
System.Web.Configuration.WebConfigurationManager.AppSettings["FirstRun"] = "False";
config2.Save();
Errors.Items.Add("Done.");
Response.RedirectToRoute("DefaultRoute");
}
/// <summary>
/// extracts information from the COLUMNS table of INFORMATION_SCHEMA
/// sets each column`s datatype, default value, nullability, autoincrement, uniqueness
/// and if the datatype is ENUM, sets the extended property as a List<string> labeled Common.Constants.ENUM_COLUMN_VALUES
/// </summary>
private void GetColumnTypes()
{
DataTable stats = driver.fetchAll("SELECT COLS.*,"
+ " COLUMNPROPERTY(OBJECT_ID(COLS.TABLE_NAME), COLS.COLUMN_NAME, 'IsIdentity') AS 'HAS_IDENTITY'"
+ " FROM INFORMATION_SCHEMA.COLUMNS AS COLS"
+ " JOIN INFORMATION_SCHEMA.TABLES AS TBLS ON COLS.TABLE_NAME = TBLS.TABLE_NAME " +
"WHERE TBLS.TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' ORDER BY COLS.TABLE_NAME, ORDINAL_POSITION");
Dictionary <string, DataColumnCollection> res = new Dictionary <string, DataColumnCollection>();
BaseDriverMsSql tempWebDb = new BaseDriverMsSql(Common.Environment.project.ConnstringWeb);
tempWebDb.BeginTransaction();
//tempWebDb.query("SET SQL_SELECT_LIMIT=0");
foreach (string tableName in TableList())
{
DataTable schema = tempWebDb.fetchAll("SELECT TOP 0 * FROM ", dbe.Table(tableName));
res[tableName] = schema.Columns;
}
//tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT");
tempWebDb.CommitTransaction();
tempWebDb = null;
foreach (DataRow r in stats.Rows)
{
DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string]; // set ColumnName
//string dataType = r["DATA_TYPE"] as string;
if (col.DataType == typeof(string))
{
col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
}
bool hasIdentity = (int)r["HAS_IDENTITY"] == 1; // set AutoIncrement
if (hasIdentity)
{
col.AutoIncrement = true;
}
if (!col.AutoIncrement)
{
col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules...
}
object colDefault = r["COLUMN_DEFAULT"]; // set DefaultValue
if (!((colDefault is DBNull) || (colDefault.ToString() == String.Empty)) && col.DefaultValue == DBNull.Value)
{
string colDefaltStr = colDefault as string;
// similiar to StatsMySql save for the condition on CURRENT_TIMESTAMP - this could probably be expressed in various ways fro the MSSQL.
// The result will be though the same, just one additional call to refrection when trying to parse the funcion signature into a datetime.
if (col.DataType == typeof(string))
{
col.DefaultValue = colDefaltStr;
}
else if (col.DataType == typeof(bool))
{
switch (colDefaltStr)
{
case "0":
col.DefaultValue = false;
break;
case "1":
col.DefaultValue = true;
break;
default:
break;
}
}
else
{
object parsed = null;
if (Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed))
{
col.DefaultValue = parsed;
}
}
}
col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES";
try
{
if (!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull))
{
col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
}
}
catch { }
} // for each row in stats
columnTypes = res;
ColumnsToDisplay = new Dictionary <string, List <string> >();
IComparer <DataColumn> comparer = new Common.ColumnDisplayComparer();
foreach (string tableName in columnTypes.Keys)
{
List <DataColumn> innerList = new List <DataColumn>();
foreach (DataColumn col in columnTypes[tableName])
{
innerList.Add(col);
}
innerList.Sort(comparer);
columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList <string>();
}
foreach (string tableName in FKs.Keys)
{
foreach (FK fk in FKs[tableName])
{
columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk;
}
}
}
public StatsMsSql(BaseDriverMsSql driver)
{
this.driver = driver;
}
/// <summary>
/// extracts information from the COLUMNS table of INFORMATION_SCHEMA
/// sets each column`s datatype, default value, nullability, autoincrement, uniqueness
/// and if the datatype is ENUM, sets the extended property as a List<string> labeled Common.Constants.ENUM_COLUMN_VALUES
/// </summary>
private void GetColumnTypes()
{
DataTable stats = driver.fetchAll("SELECT COLS.*,"
+" COLUMNPROPERTY(OBJECT_ID(COLS.TABLE_NAME), COLS.COLUMN_NAME, 'IsIdentity') AS 'HAS_IDENTITY'"
+" FROM INFORMATION_SCHEMA.COLUMNS AS COLS"
+" JOIN INFORMATION_SCHEMA.TABLES AS TBLS ON COLS.TABLE_NAME = TBLS.TABLE_NAME " +
"WHERE TBLS.TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' ORDER BY COLS.TABLE_NAME, ORDINAL_POSITION");
Dictionary<string, DataColumnCollection> res = new Dictionary<string, DataColumnCollection>();
BaseDriverMsSql tempWebDb = new BaseDriverMsSql(Common.Environment.project.ConnstringWeb);
tempWebDb.BeginTransaction();
//tempWebDb.query("SET SQL_SELECT_LIMIT=0");
foreach(string tableName in TableList()){
DataTable schema = tempWebDb.fetchAll("SELECT TOP 0 * FROM ", dbe.Table(tableName));
res[tableName] = schema.Columns;
}
//tempWebDb.query("SET SQL_SELECT_LIMIT=DEFAULT");
tempWebDb.CommitTransaction();
tempWebDb = null;
foreach (DataRow r in stats.Rows) {
DataColumn col = res[r["TABLE_NAME"] as string][r["COLUMN_NAME"] as string]; // set ColumnName
//string dataType = r["DATA_TYPE"] as string;
if (col.DataType == typeof(string)) {
col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
}
bool hasIdentity = (int)r["HAS_IDENTITY"] == 1; // set AutoIncrement
if (hasIdentity)
col.AutoIncrement = true;
if(!col.AutoIncrement)
col.ExtendedProperties.Add(Common.Constants.COLUMN_EDITABLE, true); // TODO add more restrictive rules...
object colDefault = r["COLUMN_DEFAULT"]; // set DefaultValue
//if(!((colDefault is DBNull) || (colDefault.ToString() == String.Empty))){
//string colDefaltStr = colDefault as string;
//if(colDefaltStr == "CURRENT_TIMESTAMP")
// col.ExtendedProperties.Remove(Common.Constants.COLUMN_EDITABLE);
//else{
//object parsed;
//if(Common.Functions.TryTryParse(colDefaltStr, col.DataType, out parsed)){
// col.DefaultValue = parsed;
//}
//}
//}
col.AllowDBNull = ((string)r["IS_NULLABLE"]) == "YES";
if(!(r["CHARACTER_MAXIMUM_LENGTH"] is DBNull)) col.MaxLength = Convert.ToInt32(r["CHARACTER_MAXIMUM_LENGTH"]);
} // for each row in stats
columnTypes = res;
ColumnsToDisplay = new Dictionary<string, List<string>>();
IComparer<DataColumn> comparer = new Common.ColumnDisplayComparer();
foreach (string tableName in columnTypes.Keys) {
List<DataColumn> innerList = new List<DataColumn>();
foreach (DataColumn col in columnTypes[tableName])
innerList.Add(col);
innerList.Sort(comparer);
columnsToDisplay[tableName] = (from DataColumn c in innerList select c.ColumnName).ToList<string>();
}
foreach (string tableName in FKs.Keys) {
foreach (FK fk in FKs[tableName]) {
columnTypes[fk.myTable][fk.myColumn].ExtendedProperties["FK"] = fk;
}
}
}