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