|
static private ExecuteDataset ( string connectionString, string sql ) : |
||
| connectionString | string | |
| sql | string | |
| Résultat | ||
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);
}
}
}
}