private bool ValidateTable(Type interfaceType, string tableName, StringBuilder errors) { ISqlTableInformation sqlTableInformation = SqlTableInformationStore.GetTableInformation(_connectionString, tableName); if (sqlTableInformation == null) { errors.AppendLine("Table '{0}' does not exist".FormatWith(tableName)); return(false); } int primaryKeyCount = sqlTableInformation.ColumnInformations.Count(column => column.IsPrimaryKey); if (primaryKeyCount == 0) { errors.AppendLine(string.Format("The table '{0}' is missing a primary key", tableName)); return(false); } var columns = new List <SqlColumnInformation>(sqlTableInformation.ColumnInformations); var properties = interfaceType.GetPropertiesRecursively(); foreach (PropertyInfo property in properties) { if (property.Name == "DataSourceId") { continue; } SqlColumnInformation column = columns.Find(col => col.ColumnName == property.Name); if (column == null) { errors.AppendLine(string.Format("The interface property named '{0}' does not exist in the table '{1}' as a column", property.Name, sqlTableInformation.TableName)); return(false); } if (!column.IsNullable || column.Type == typeof(string)) { if (column.Type != property.PropertyType) { errors.AppendLine(string.Format("Type mismatch. The interface type '{0}' does not match the database type '{1}'", property.PropertyType, column.Type)); return(false); } } } // Updating schema from C1 4.1, to be removed in future versions. // if (typeof (ILocalizedControlled).IsAssignableFrom(interfaceType) // && !properties.Any(p => p.Name == "CultureName") // && columns.Any(c => c.ColumnName == "CultureName")) // { // Log.LogInformation(LogTitle, "Removing obsolete 'CultureName' column from table '{0}'", tableName); // string selectConstraintName = string.Format( // @"SELECT df.name 'ConstraintName' // FROM sys.default_constraints df // INNER JOIN sys.tables t ON df.parent_object_id = t.object_id // INNER JOIN sys.columns c ON df.parent_object_id = c.object_id AND df.parent_column_id = c.column_id // where t.name = '{0}' // and c.name = 'CultureName'", tableName); // var dt = ExecuteReader(selectConstraintName); // List<string> constraints = (from DataRow dr in dt.Rows select dr["ConstraintName"].ToString()).ToList(); // foreach (var constrainName in constraints) // { // ExecuteSql("ALTER TABLE [{0}] DROP CONSTRAINT [{1}]".FormatWith(tableName, constrainName)); // } // string sql = "ALTER TABLE [{0}] DROP COLUMN [CultureName]".FormatWith(tableName); // ExecuteSql(sql); // } return(true); }
protected void Page_Load(object sender, System.EventArgs e) { SqlServer server = SqlServer.CurrentServer; server.Connect(); SqlDatabase database = SqlDatabase.CurrentDatabase(server); SqlTable table = database.Tables[Request["table"]]; // Set link for add new column AddNewColumnHyperLink.NavigateUrl = String.Format("editcolumn.aspx?database={0}&table={1}", Server.UrlEncode(Request["database"]), Server.UrlEncode(Request["table"])); if (table != null) { // The table exists and we do normal column editing ColumnsDataGrid.Visible = true; NoColumnsLabel.Visible = false; if (!IsPostBack) { // Update table properties // Get columns list SqlColumnCollection columns = table.Columns; DataSet ds = new DataSet(); ds.Tables.Add(); ds.Tables[0].Columns.Add("key", typeof(bool)); ds.Tables[0].Columns.Add("id", typeof(bool)); ds.Tables[0].Columns.Add("name", typeof(string)); ds.Tables[0].Columns.Add("datatype", typeof(string)); ds.Tables[0].Columns.Add("size", typeof(int)); ds.Tables[0].Columns.Add("precision", typeof(int)); ds.Tables[0].Columns.Add("scale", typeof(int)); ds.Tables[0].Columns.Add("nulls", typeof(bool)); ds.Tables[0].Columns.Add("default", typeof(string)); ds.Tables[0].Columns.Add("encodedname", typeof(string)); for (int i = 0; i < columns.Count; i++) { SqlColumnInformation columnInfo = columns[i].ColumnInformation; ds.Tables[0].Rows.Add(new object[] { columnInfo.Key, columnInfo.Identity, Server.HtmlEncode(columnInfo.Name), Server.HtmlEncode(columnInfo.DataType), columnInfo.Size, columnInfo.Precision, columnInfo.Scale, columnInfo.Nulls, Server.HtmlEncode(columnInfo.DefaultValue), Server.UrlEncode(columnInfo.Name) }); } ColumnsDataGrid.DataSource = ds; ColumnsDataGrid.DataBind(); } // If the table has data in it, disable edit column if (table.Rows > 0) { ColumnsDataGrid.Columns[2].Visible = true; ColumnsDataGrid.Columns[3].Visible = false; ColumnsDataGrid.Columns[8].Visible = false; } else { ColumnsDataGrid.Columns[2].Visible = false; ColumnsDataGrid.Columns[3].Visible = true; ColumnsDataGrid.Columns[8].Visible = true; } // If the table has only one column, do not allow delete if (table.Columns.Count == 1) { ColumnsDataGrid.Columns[9].Visible = false; } else { ColumnsDataGrid.Columns[9].Visible = true; } } else { // The table does not exist, implying that it is new ColumnsDataGrid.Visible = false; NoColumnsLabel.Visible = true; } server.Disconnect(); }
protected void UpdateButton_Click(object sender, System.EventArgs e) { if (!IsValid) return; SqlServer server = SqlServer.CurrentServer; try { server.Connect(); } catch (System.Exception ex) { //Response.Redirect("Error.aspx?errorPassCode=" + 2002); Response.Redirect(String.Format("error.aspx?errormsg={0}&stacktrace={1}", Server.UrlEncode(ex.Message), Server.UrlEncode(ex.StackTrace))); } SqlDatabase database = SqlDatabase.CurrentDatabase(server); // Parse user input and stick it into ColumnInfo SqlColumnInformation columnInfo = new SqlColumnInformation(); columnInfo.Key = PrimaryKeyCheckbox.Checked; columnInfo.Name = ColumnNameTextbox.Text; columnInfo.DataType = DataTypeDropdownlist.SelectedItem.Text; try { columnInfo.Size = Convert.ToInt32(LengthTextbox.Text); } catch { // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "Invalid input: Size must be an integer"; return; } columnInfo.Nulls = AllowNullCheckbox.Checked; columnInfo.DefaultValue = DefaultValueTextbox.Text; try { columnInfo.Precision = Convert.ToInt32(PrecisionTextbox.Text); } catch { // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "Invalid input: Precision must be an integer"; return; } try { columnInfo.Scale = Convert.ToInt32(ScaleTextbox.Text); } catch { // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "Invalid input: Scale must be an integer"; return; } columnInfo.Identity = IdentityCheckBox.Checked; try { columnInfo.IdentitySeed = Convert.ToInt32(IdentitySeedTextbox.Text); } catch { // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "Invalid input: Identity seed must be an integer"; return; } try { columnInfo.IdentityIncrement = Convert.ToInt32(IdentityIncrementTextbox.Text); } catch { // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "Invalid input: Identity increment must be an integer"; return; } columnInfo.IsRowGuid = IsRowGuidCheckBox.Checked; SqlTable table = database.Tables[Request["table"]]; // First check if the table exists or not // If it doesn't exist, that means we are adding the first column of a new table // If it does exist, then either we are adding a new column to an existing table // or we are editing an existing column in an existing table if (table == null) { // Table does not exist - create a new table and add the new column try { SqlColumnInformation[] columnInfos = new SqlColumnInformation[1] { columnInfo }; table = database.Tables.Add(Request["table"], columnInfos); } catch (Exception ex) { // If the table was somehow created, get rid of it table = database.Tables[Request["table"]]; if (table != null) table.Remove(); // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "The following error occured while trying to apply the changes.<br>" + Server.HtmlEncode(ex.Message).Replace("\n", "<br>"); server.Disconnect(); return; } } else { // Table does exist, do further check // If original name is blank that means it is a new column string originalColumnName = Request["column"]; if (originalColumnName == null || originalColumnName.Length == 0) { try { table.Columns.Add(columnInfo); } catch (Exception ex) { // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "The following error occured while trying to apply the changes:<br>" + Server.HtmlEncode(ex.Message).Replace("\n", "<br>"); server.Disconnect(); return; } } else { // If we get here that means we are editing an existing column // Simply set the column info - internally the table gets recreated try { table.Columns[originalColumnName].ColumnInformation = columnInfo; } catch (Exception ex) { // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "The following error occured while trying to apply the changes.<br>" + Server.HtmlEncode(ex.Message).Replace("\n", "<br>"); server.Disconnect(); return; } } } server.Disconnect(); // If we get here then that means a column was successfully added/edited Response.Redirect(String.Format("columns.aspx?database={0}&table={1}", Server.UrlEncode(Request["database"]), Server.UrlEncode(Request["table"]))); }
private void AddPropertiesAddProperty(CodeTypeDeclaration declaration, string propertyName, Type propertyType, string fieldName, string nullableFieldName) { SqlColumnInformation columnInformation = _dataTypeDescriptor.CreateSqlColumnInformation(propertyName); string name = propertyName; Type type = propertyType; string dbName = propertyName; string dbType = EntityCodeGeneratorHelper.GetDbType(columnInformation.SqlDbType, columnInformation.IsNullable); bool isNullable = columnInformation.IsNullable; bool isId = columnInformation.IsPrimaryKey; bool isAutoGen = columnInformation.IsIdentity; CodeMemberProperty propertyMember = new CodeMemberProperty(); propertyMember.Name = name; propertyMember.Type = new CodeTypeReference(type); propertyMember.Attributes = MemberAttributes.Public; propertyMember.HasSet = true; propertyMember.HasGet = true; propertyMember.GetStatements.Add( new CodeConditionStatement( new CodeBinaryOperatorExpression( new CodeFieldReferenceExpression( new CodeThisReferenceExpression(), nullableFieldName ), CodeBinaryOperatorType.IdentityInequality, new CodePrimitiveExpression(null) ), new CodeStatement[] { new CodeMethodReturnStatement( new CodePropertyReferenceExpression( new CodeFieldReferenceExpression( new CodeThisReferenceExpression(), nullableFieldName ), "Value" ) ) }, new CodeStatement[] { new CodeMethodReturnStatement( new CodeFieldReferenceExpression( new CodeThisReferenceExpression(), fieldName ) ) } ) ); propertyMember.SetStatements.Add( new CodeAssignStatement( new CodePropertyReferenceExpression( new CodeBaseReferenceExpression(), name ), new CodeArgumentReferenceExpression("value") ) ); propertyMember.CustomAttributes.Add(new CodeAttributeDeclaration(new CodeTypeReference(typeof(DebuggerNonUserCodeAttribute)))); var codeAttributeArguments = new List <CodeAttributeArgument> { new CodeAttributeArgument("Name", new CodePrimitiveExpression(dbName)), new CodeAttributeArgument("Storage", new CodePrimitiveExpression(fieldName)), new CodeAttributeArgument("DbType", new CodePrimitiveExpression(dbType)), new CodeAttributeArgument("CanBeNull", new CodePrimitiveExpression(isNullable)), new CodeAttributeArgument("IsPrimaryKey", new CodePrimitiveExpression(isId)), new CodeAttributeArgument("IsDbGenerated", new CodePrimitiveExpression(isAutoGen)), new CodeAttributeArgument("UpdateCheck", new CodeFieldReferenceExpression( new CodeTypeReferenceExpression(typeof(UpdateCheck)), nameof(UpdateCheck.Never)) ) }; propertyMember.CustomAttributes.Add(new CodeAttributeDeclaration( new CodeTypeReference(typeof(ColumnAttribute)), codeAttributeArguments.ToArray() )); declaration.Members.Add(propertyMember); }
protected void Page_Load(object sender, System.EventArgs e) { SqlServer server = SqlServer.CurrentServer; server.Connect(); SqlDatabase database = SqlDatabase.CurrentDatabase(server); if (!IsPostBack) { DataLossWarningLabel.Visible = false; // If column isn't specified in request, that means we're adding a new column, not editing an existing one if (Request["column"] == null || Request["column"].Length == 0) { // Set update button text to "Add" instead of "Update" UpdateButton.Text = "Add"; // Create new unique column name string columnName = ""; SqlTable table = database.Tables[Request["table"]]; if (table == null) { // If table doesn't exist (e.g. new table), set default column name columnName = "Column1"; } else { // Come up with non-existent name ColumnXX int i = 1; do { columnName = "Column" + i; i++; } while (table.Columns[columnName] != null); } // Initialize column editor with default values PrimaryKeyCheckbox.Checked = false; ColumnNameTextbox.Text = columnName; DataTypeDropdownlist.SelectedIndex = DataTypeDropdownlist.Items.IndexOf(new ListItem("char")); LengthTextbox.Text = "10"; AllowNullCheckbox.Checked = true; DefaultValueTextbox.Text = ""; PrecisionTextbox.Text = "0"; ScaleTextbox.Text = "0"; IdentityCheckBox.Checked = false; IdentitySeedTextbox.Text = "1"; IdentityIncrementTextbox.Text = "1"; IsRowGuidCheckBox.Checked = false; } else { // Set update button text to "Update" instead of "Add" UpdateButton.Text = "Update"; // Load column from table SqlTable table = database.Tables[Request["table"]]; if (table == null) { server.Disconnect(); // Table doesn't exist - break out and go to error page Response.Redirect(String.Format("error.aspx?error={0}", 1002)); return; } // Select column from table SqlColumn column = table.Columns[Request["column"]]; if (column == null) { server.Disconnect(); // Column doesn't exist - break out and go to error page Response.Redirect(String.Format("error.aspx?error={0}", 1003)); return; } SqlColumnInformation columnInfo = column.ColumnInformation; // Initialize column editor PrimaryKeyCheckbox.Checked = columnInfo.Key; ColumnNameTextbox.Text = columnInfo.Name; DataTypeDropdownlist.SelectedIndex = DataTypeDropdownlist.Items.IndexOf(new ListItem(columnInfo.DataType)); LengthTextbox.Text = Convert.ToString(columnInfo.Size); AllowNullCheckbox.Checked = columnInfo.Nulls; DefaultValueTextbox.Text = columnInfo.DefaultValue; PrecisionTextbox.Text = Convert.ToString(columnInfo.Precision); ScaleTextbox.Text = Convert.ToString(columnInfo.Scale); IdentityCheckBox.Checked = columnInfo.Identity; IdentitySeedTextbox.Text = Convert.ToString(columnInfo.IdentitySeed); IdentityIncrementTextbox.Text = Convert.ToString(columnInfo.IdentityIncrement); IsRowGuidCheckBox.Checked = columnInfo.IsRowGuid; // Since we are editing an existing column, the table will be recreated, // so we must warn about data loss DataLossWarningLabel.Visible = true; } } server.Disconnect(); }
protected void UpdateButton_Click(object sender, System.EventArgs e) { if (!IsValid) { return; } SqlServer server = SqlServer.CurrentServer; server.Connect(); SqlDatabase database = SqlDatabase.CurrentDatabase(server); // Parse user input and stick it into ColumnInfo SqlColumnInformation columnInfo = new SqlColumnInformation(); columnInfo.Key = PrimaryKeyCheckbox.Checked; columnInfo.Name = ColumnNameTextbox.Text; columnInfo.DataType = DataTypeDropdownlist.SelectedItem.Text; try { columnInfo.Size = Convert.ToInt32(LengthTextbox.Text); } catch { // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "Invalid input: Size must be an integer"; return; } columnInfo.Nulls = AllowNullCheckbox.Checked; columnInfo.DefaultValue = DefaultValueTextbox.Text; try { columnInfo.Precision = Convert.ToInt32(PrecisionTextbox.Text); } catch { // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "Invalid input: Precision must be an integer"; return; } try { columnInfo.Scale = Convert.ToInt32(ScaleTextbox.Text); } catch { // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "Invalid input: Scale must be an integer"; return; } columnInfo.Identity = IdentityCheckBox.Checked; try { columnInfo.IdentitySeed = Convert.ToInt32(IdentitySeedTextbox.Text); } catch { // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "Invalid input: Identity seed must be an integer"; return; } try { columnInfo.IdentityIncrement = Convert.ToInt32(IdentityIncrementTextbox.Text); } catch { // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "Invalid input: Identity increment must be an integer"; return; } columnInfo.IsRowGuid = IsRowGuidCheckBox.Checked; SqlTable table = database.Tables[Request["table"]]; // First check if the table exists or not // If it doesn't exist, that means we are adding the first column of a new table // If it does exist, then either we are adding a new column to an existing table // or we are editing an existing column in an existing table if (table == null) { // Table does not exist - create a new table and add the new column try { SqlColumnInformation[] columnInfos = new SqlColumnInformation[1] { columnInfo }; table = database.Tables.Add(Request["table"], columnInfos); } catch (Exception ex) { // If the table was somehow created, get rid of it table = database.Tables[Request["table"]]; if (table != null) { table.Remove(); } // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "The following error occured while trying to apply the changes.<br>" + Server.HtmlEncode(ex.Message).Replace("\n", "<br>"); server.Disconnect(); return; } } else { // Table does exist, do further check // If original name is blank that means it is a new column string originalColumnName = Request["column"]; if (originalColumnName == null || originalColumnName.Length == 0) { try { table.Columns.Add(columnInfo); } catch (Exception ex) { // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "The following error occured while trying to apply the changes:<br>" + Server.HtmlEncode(ex.Message).Replace("\n", "<br>"); server.Disconnect(); return; } } else { // If we get here that means we are editing an existing column // Simply set the column info - internally the table gets recreated try { table.Columns[originalColumnName].ColumnInformation = columnInfo; } catch (Exception ex) { // Show error and quit ErrorUpdatingColumnLabel.Visible = true; ErrorUpdatingColumnLabel.Text = "The following error occured while trying to apply the changes.<br>" + Server.HtmlEncode(ex.Message).Replace("\n", "<br>"); server.Disconnect(); return; } } } server.Disconnect(); // If we get here then that means a column was successfully added/edited Response.Redirect(String.Format("columns.aspx?database={0}&table={1}", Server.UrlEncode(Request["database"]), Server.UrlEncode(Request["table"]))); }