static private ExecuteDataset ( string connectionString, string sql ) : |
||
connectionString | string | |
sql | string | |
return |
public bool IsSupportedSQLVersion(string connectionString) { var ds = DatabaseHelper.ExecuteDataset(connectionString, "SELECT SERVERPROPERTY('productversion')"); var version = (string)ds.Tables[0].Rows[0][0]; if (version.StartsWith("10.")) { return(true); } else if (version.StartsWith("9.")) { return(true); } else { return(false); } }
internal static string GetFunctionBody(string schema, string name, string connectionString) { var sb = new StringBuilder(); var ds = DatabaseHelper.ExecuteDataset(connectionString, "sp_helptext '[" + schema + "].[" + name + "]'"); foreach (DataRow dr in ds.Tables[0].Rows) { var t = (string)dr["Text"] + string.Empty; sb.AppendLine(t.Replace("\r\n", string.Empty).Replace("\r", string.Empty).Replace("\n", string.Empty)); } var sql = sb.ToString(); var regEx = new Regex(@"CREATE\s*FUNCTION[\r\n\s]*[a-zA-Z0-9\[\]_\.]*.*RETURNS.*AS[\r\n\s]+(RETURN[\s\S\r\n]*)", RegexOptions.IgnoreCase | RegexOptions.Singleline); var match = regEx.Match(sql); if (match != null && match.Groups != null && match.Groups.Count == 2) { sql = match.Groups[1].Value; } else { regEx = new Regex(@"CREATE\s*FUNCTION[\r\n\s]*[a-zA-Z0-9\[\]_\.]*.*RETURNS.*(BEGIN[\s\S\r\n]*)", RegexOptions.IgnoreCase | RegexOptions.Singleline); match = regEx.Match(sql); if (match != null && match.Groups != null && match.Groups.Count == 2) { sql = match.Groups[1].Value; } else { regEx = new Regex(@"CREATE\s*FUNCTION[\r\n\s]*[a-zA-Z0-9\[\]_\.]*.*RETURNS.*AS[\r\n\s]+([\s\S\r\n]*)", RegexOptions.IgnoreCase | RegexOptions.Singleline); match = regEx.Match(sql); if (match != null && match.Groups != null && match.Groups.Count == 2) { sql = match.Groups[1].Value; } else { System.Diagnostics.Debug.Write(string.Empty); } } } return(sql.Trim()); }
public SQLServerTypeConstants GetSQLVersion(string connectionString) { var ds = DatabaseHelper.ExecuteDataset(connectionString, "SELECT SERVERPROPERTY('productversion')"); var version = (string)ds.Tables[0].Rows[0][0]; if (version.StartsWith("10.")) { var ds2 = DatabaseHelper.ExecuteDataset(connectionString, "SELECT SERVERPROPERTY('Edition')"); var version2 = (string)ds2.Tables[0].Rows[0][0]; if (version2 == "SQL Azure") { return(SQLServerTypeConstants.SQLAzure); } else { return(SQLServerTypeConstants.SQL2008); } } else { return(SQLServerTypeConstants.SQL2005); } }
public Database Import(string connectionString, IEnumerable <SpecialField> auditFields) { try { var database = new Database(); #region Load user defined types LoadUdts(database, connectionString); #endregion #region Load Entities this.ProgressText = "Loading Entities..."; using (var tableReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlDatabaseTables())) { while (tableReader.Read()) { var newEntity = new Entity(); newEntity.Name = tableReader["name"].ToString(); database.EntityList.Add(newEntity); newEntity.Schema = tableReader["schema"].ToString(); } } #endregion #region Load Entity Fields using (var columnReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlColumnsForTable())) { while (columnReader.Read()) { var columnName = columnReader["columnName"].ToString(); var tableName = columnReader["tableName"].ToString(); var entity = database.EntityList.FirstOrDefault(x => x.Name == tableName); //Ensure the field name is not an Audit field if (entity != null && !auditFields.Any(x => x.Name.Match(columnName))) { var maxSortOrder = 0; if (entity.FieldList.Count > 0) { maxSortOrder = entity.FieldList.Max(x => x.SortOrder); } var newColumn = new Field() { Name = columnName, SortOrder = ++maxSortOrder }; entity.FieldList.Add(newColumn); newColumn.Nullable = (int)columnReader["allow_null"] == 1; if ((int)columnReader["is_identity"] == 1) { newColumn.Identity = true; } if (columnReader["isPrimaryKey"] != System.DBNull.Value) { newColumn.PrimaryKey = true; } try { newColumn.DataType = DatabaseHelper.GetSQLDataType(columnReader["system_type_id"].ToString(), database.UserDefinedTypes); } catch { } var defaultvalue = columnReader["default_value"].ToString(); SetupDefault(newColumn, defaultvalue); //newColumn.ImportedDefaultName = ""; newColumn.Length = (int)columnReader["max_length"]; //Decimals are a little different if (newColumn.DataType == SqlDbType.Decimal) { newColumn.Length = (byte)columnReader["precision"]; newColumn.Scale = (int)columnReader["scale"]; } } else if (entity != null) { if (auditFields.Any(x => (x.Type == SpecialFieldTypeConstants.CreatedDate || x.Type == SpecialFieldTypeConstants.CreatedBy) && x.Name.ToLower() == columnName.ToLower())) { entity.AllowCreateAudit = true; } if (auditFields.Any(x => (x.Type == SpecialFieldTypeConstants.ModifiedDate || x.Type == SpecialFieldTypeConstants.ModifiedBy) && x.Name.ToLower() == columnName.ToLower())) { entity.AllowModifyAudit = true; } if (auditFields.Any(x => x.Type == SpecialFieldTypeConstants.Timestamp && x.Name.ToLower() == columnName.ToLower())) { entity.AllowTimestamp = true; } if (auditFields.Any(x => x.Type == SpecialFieldTypeConstants.Tenant && x.Name.ToLower() == columnName.ToLower())) { entity.IsTenant = true; } } } } using (var columnReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlColumnsForComputed())) { while (columnReader.Read()) { var tableName = columnReader["tableName"].ToString(); var columnName = columnReader["columnName"].ToString(); var entity = database.EntityList.FirstOrDefault(x => x.Name == tableName); if (entity != null) { var column = entity.FieldList.FirstOrDefault(x => x.Name.ToLower() == columnName.ToLower()); if (column != null) { column.IsComputed = true; column.Formula = columnReader["definition"].ToString(); } } } } #endregion #region Load Entity Indexes using (var indexReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlIndexesForTable())) { while (indexReader.Read()) { var indexName = indexReader["indexname"].ToString(); var columnName = indexReader["columnname"].ToString(); var tableName = indexReader["tableName"].ToString(); var entity = database.EntityList.FirstOrDefault(x => x.Name == tableName); if (entity != null) { var pk = bool.Parse(indexReader["is_primary_key"].ToString()); var column = entity.FieldList.FirstOrDefault(x => x.Name == columnName); if (column != null && !pk) { column.IsIndexed = true; } } } } #endregion #region Load Relations var dsRelationship = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForRelationships()); foreach (DataRow rowRelationship in dsRelationship.Tables[0].Rows) { var constraintName = rowRelationship["FK_CONSTRAINT_NAME"].ToString(); var parentTableName = (string)rowRelationship["UQ_TABLE_NAME"]; var childTableName = (string)rowRelationship["FK_TABLE_NAME"]; var parentTable = database.EntityList.FirstOrDefault(x => x.Name == parentTableName); var childTable = database.EntityList.FirstOrDefault(x => x.Name == childTableName); if (parentTable != null && childTable != null) { Relationship newRelation = null; var isAdd = false; if (database.RelationshipList.Count(x => x.ConstraintName == constraintName) == 0) { newRelation = new Relationship(); if (rowRelationship["object_id"] != System.DBNull.Value) { newRelation.ImportData = rowRelationship["object_id"].ToString(); } newRelation.SourceEntity = parentTable; newRelation.TargetEntity = childTable; newRelation.ConstraintName = constraintName; var search = ("_" + childTable.Name + "_" + parentTable.Name).ToLower(); var roleName = constraintName.ToLower().Replace(search, string.Empty); if (roleName.Length >= 3) { roleName = roleName.Remove(0, 3); } var v = roleName.ToLower(); if (v != "fk") { newRelation.RoleName = v; } isAdd = true; } else { newRelation = database.RelationshipList.First(x => x.ConstraintName == constraintName); } //add the column relationship to the relation var columnRelationship = new RelationshipDetail(); var parentColumnName = (string)rowRelationship["UQ_COLUMN_NAME"]; var childColumnName = (string)rowRelationship["FK_COLUMN_NAME"]; if (parentTable.FieldList.Count(x => x.Name == parentColumnName) == 1 && (childTable.FieldList.Count(x => x.Name == childColumnName) == 1)) { columnRelationship.ParentField = parentTable.FieldList.First(x => x.Name == parentColumnName); columnRelationship.ChildField = childTable.FieldList.First(x => x.Name == childColumnName); newRelation.RelationshipColumnList.Add(columnRelationship); //ONLY ADD THIS RELATION IF ALL WENT WELL if (isAdd) { parentTable.RelationshipList.Add(newRelation); } } else { System.Diagnostics.Debug.Write(string.Empty); } } } #endregion #region Load Views this.ProgressText = "Loading Views..."; LoadViews(database, connectionString); #endregion #region Load Indexes this.ProgressText = "Loading Indexes..."; LoadIndexes(database, connectionString); #endregion LoadUniqueFields(database, connectionString); return(database); } catch (Exception ex /*ignored*/) { throw; } finally { this.ProgressText = string.Empty; this.ProgressValue = 0; } }
private static void LoadFunctions(Database database, string connectionString) { try { //Add the Functions var dsFunction = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForFunctions()); if (dsFunction.Tables.Count > 0) { foreach (DataRow rowFunction in dsFunction.Tables[0].Rows) { var name = (string)rowFunction["name"]; var schema = (string)rowFunction["schemaname"]; var sql = SchemaModelHelper.GetFunctionBody(schema, name, connectionString); var function = database.FunctionList.FirstOrDefault(x => x.Name == name); if (function == null) { function = new Function(); function.Name = name; function.Schema = schema; function.SQL = sql; database.FunctionList.Add(function); } } } foreach (var function in database.FunctionList) { var dsFunctionAux = DatabaseHelper.ExecuteDataset(connectionString, "sp_help '[" + function.Schema + "].[" + function.Name + "]'"); DataTable dtColumn = null; DataTable dtParameter = null; foreach (DataTable dt in dsFunctionAux.Tables) { if (dt.Columns.Contains("column_name")) { dtColumn = dt; } else if (dt.Columns.Contains("parameter_name")) { dtParameter = dt; } } //Add the columns if (dtColumn != null) { foreach (DataRow row in dtColumn.Rows) { var field = new Field(); field.Name = (string)row["column_name"]; var dataType = DatabaseHelper.GetSQLDataType((string)row["type"], database.UserDefinedTypes); var length = int.Parse(row["length"].ToString()); //The length is half the bytes for these types if ((dataType == SqlDbType.NChar) || (dataType == SqlDbType.NVarChar)) { length = length / 2; } field.DataType = dataType; field.Nullable = row["column_name"].ToString() == "yes" ? true : false; field.Length = length; if (row["scale"] != System.DBNull.Value && !string.IsNullOrEmpty((string)row["scale"]) && ((string)row["scale"]).Trim() != string.Empty) { field.Scale = int.Parse(row["scale"].ToString()); } function.FieldList.Add(field); } } function.IsTable = (dtColumn != null); //Add the parameters if (dtParameter != null) { var sortOrder = 1; foreach (DataRow row in dtParameter.Rows) { var name = ((string)row["parameter_name"]).Replace("@", string.Empty); if (string.IsNullOrEmpty(name)) { //This is a return value for a scalar function //If there is no name then this is the return var field = new Field(); field.Name = "Value"; field.Nullable = true; var dataType = DatabaseHelper.GetSQLDataType((string)row["type"], database.UserDefinedTypes); var length = int.Parse(row["length"].ToString()); //The length is half the bytes for these types if ((dataType == SqlDbType.NChar) || (dataType == SqlDbType.NVarChar)) { length = length / 2; } field.DataType = dataType; field.Length = length; if (row["scale"] != System.DBNull.Value) { field.Scale = int.Parse(row["scale"].ToString()); } function.FieldList.Add(field); } else { //This is a parameter var parameter = new Parameter(); parameter.Name = name; parameter.SortOrder = sortOrder; sortOrder++; var dataType = DatabaseHelper.GetSQLDataType((string)row["type"], database.UserDefinedTypes); parameter.DataType = dataType; var length = int.Parse(row["length"].ToString()); //The length is half the bytes for these types if ((dataType == SqlDbType.NChar) || (dataType == SqlDbType.NVarChar)) { length = length / 2; } parameter.Length = length; if (row["scale"] != System.DBNull.Value) { parameter.Scale = int.Parse(row["scale"].ToString()); } function.ParameterList.Add(parameter); } } } } } catch (Exception /*ignored*/) { throw; } }
private static bool LoadStoredProcedures(Database database, string procName, string connectionString) { try { var dsSP = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForStoredProcedures(procName)); var dsSPParameter = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForStoredProceduresParameters()); //Add the Stored Procedures StoredProc customStoredProcedure = null; foreach (DataRow rowSP in dsSP.Tables[0].Rows) { var id = (int)rowSP["id"]; var name = (string)rowSP["name"]; var schema = (string)rowSP["schemaname"]; customStoredProcedure = database.StoredProcList.FirstOrDefault(x => x.Name == name); if (customStoredProcedure == null) { customStoredProcedure = new StoredProc(); customStoredProcedure.Name = name; customStoredProcedure.SQL = SchemaModelHelper.GetSqlForStoredProceduresBody(schema, name, connectionString); customStoredProcedure.Schema = schema; database.StoredProcList.Add(customStoredProcedure); } } //Add the parameters var sortOrder = 1; foreach (DataRow rowSP in dsSPParameter.Tables[0].Rows) { if (!DatabaseHelper.IsValidSQLDataType((SqlNativeTypes)int.Parse(rowSP["xtype"].ToString()))) { customStoredProcedure.InError = true; customStoredProcedure.ParameterList.Clear(); customStoredProcedure.FieldList.Clear(); return(false); } var id = (int)rowSP["id"]; var spName = (string)rowSP["name"]; var name = (string)rowSP["ColName"]; var typeName = (string)rowSP["ColType"]; var dataType = DatabaseHelper.GetSQLDataType(rowSP["xtype"].ToString(), database.UserDefinedTypes); var length = int.Parse(rowSP["length"].ToString()); var isOutput = ((int)rowSP["isoutparam"] != 0); //The length is half the bytes for these types if ((dataType == SqlDbType.NChar) || (dataType == SqlDbType.NVarChar)) { length = length / 2; } if (customStoredProcedure != null) { var parameter = new Parameter(); parameter.Name = name.Replace("@", string.Empty); parameter.SortOrder = sortOrder; sortOrder++; parameter.DataType = dataType; parameter.Length = length; parameter.Nullable = (int)rowSP["isnullable"] == 1 ? true : false; parameter.IsOutputParameter = isOutput; customStoredProcedure.ParameterList.Add(parameter); } } //Try to get the columns var errorItems = new List <string>(); foreach (var sp in database.StoredProcList) { try { DataSet dsSPColumn = null; try { dsSPColumn = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForStoredProceduresColumns(sp)); } catch (Exception) { sp.ColumnFailure = true; } if ((dsSPColumn != null) && dsSPColumn.Tables.Count > 0) { var dt = dsSPColumn.Tables[0]; foreach (DataColumn column in dt.Columns) { var newColumn = new Field(); var dataType = Extensions.GetSqlDbType(column.DataType); var length = newColumn.DataType.ValidateDataTypeMax(1000000); newColumn.Name = column.ColumnName; newColumn.DataType = dataType; newColumn.Nullable = true; newColumn.Length = length; if (newColumn.DataType == SqlDbType.Decimal) { newColumn.Length = 18; newColumn.Scale = 4; } if (newColumn.DataType == SqlDbType.DateTime2) { newColumn.Length = 7; newColumn.Scale = 0; } if (newColumn.DataType == SqlDbType.VarChar) { newColumn.Length = 50; } sp.FieldList.Add(newColumn); } } } catch (Exception ex) { //Do Nothing - Skip to next if (ex.Message.Contains("Invalid object name '#")) //this is a temp table. it cannot be run so there is nothing we can do { //Do Nothing } else { errorItems.Add(sp.Name); } } } return(true); } catch (Exception /*ignored*/) { throw; } }
private static void LoadViews(Database database, string connectionString) { var dsView = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForViews()); var dsViewColumn = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForViewsColumns()); //Add the Views if (dsView.Tables.Count > 0) { foreach (DataRow rowView in dsView.Tables[0].Rows) { var name = (string)rowView["name"]; var schema = (string)rowView["schemaname"]; var sql = SchemaModelHelper.GetViewBody((string)rowView["definition"]); var view = database.ViewList.FirstOrDefault(x => x.Name == name); if (view == null) { view = new View { Name = name, SQL = sql, Schema = schema }; database.ViewList.Add(view); } } } //Add the columns if (dsViewColumn.Tables.Count > 0) { foreach (DataRow rowView in dsViewColumn.Tables[0].Rows) { var viewName = (string)rowView["viewname"]; var columnName = (string)rowView["columnname"]; var dataType = DatabaseHelper.GetSQLDataType(rowView["system_type_id"].ToString(), database.UserDefinedTypes); var length = int.Parse(rowView["max_length"].ToString()); var view = database.ViewList.FirstOrDefault(x => x.Name.ToLower() == viewName.ToLower()); //The length is half the bytes for these types if ((dataType == SqlDbType.NChar) || (dataType == SqlDbType.NVarChar)) { length /= 2; } else if (dataType == SqlDbType.DateTime2) { length = int.Parse(rowView["scale"].ToString()); } if (view != null) { var field = new Field { Name = columnName, DataType = dataType, Length = length, Scale = int.Parse(rowView["scale"].ToString()), Nullable = (bool)rowView["is_nullable"] }; view.FieldList.Add(field); } } } }