public override DatabaseObjectsCollection <IView> LoadViews(IDatabase database)
{
DatabaseObjectsCollection <IView> views = new DatabaseObjectsCollection <IView>(database);
SqlConnection sqlConnection = new SqlConnection(database.ConnectionString);
using (SqlDataAdapter da = new SqlDataAdapter(_getViews, sqlConnection))
{
DataTable dtViews = new DataTable("Views");
da.Fill(dtViews);
for (int i = 0; i < dtViews.Rows.Count; i++)
{
string schemaName = (string)dtViews.Rows[i]["TABLE_SCHEMA"];
string viewName = (string)dtViews.Rows[i]["TABLE_NAME"];
View view = new View()
{
SchemaName = schemaName, TableName = viewName, Query = LoadViewQuery(sqlConnection, schemaName, viewName)
};
LoadColumns(sqlConnection, view, TableType.View);
views.Add(view);
}
}
return(views);
}
public override DatabaseObjectsCollection <ITable> LoadTables(IDatabase database)
{
DatabaseObjectsCollection <ITable> tables = new DatabaseObjectsCollection <ITable>(database);
SqlConnection sqlConnection = new SqlConnection(database.ConnectionString);
using (SqlDataAdapter da = new SqlDataAdapter(_getConstraintsScript, sqlConnection))
{
DataTable dtConstraints = new DataTable("Constraints");
da.Fill(dtConstraints);
for (int i = 0; i < dtConstraints.Rows.Count; i++)
{
string constraintName = (string)dtConstraints.Rows[i]["ConstraintName"];
IConstraint constraint = database.Constraints.FirstOrDefault(c => c.Name == constraintName);
if (constraint == null)
{
constraint = new ICSharpCode.Data.Core.DatabaseObjects.Constraint();
constraint.Name = constraintName;
constraint.FKTableName = (string)dtConstraints.Rows[i]["FKTable"];
constraint.PKTableName = (string)dtConstraints.Rows[i]["PKTable"];
database.Constraints.Add(constraint);
}
constraint.FKColumnNames.Add((string)dtConstraints.Rows[i]["FKColumn"]);
constraint.PKColumnNames.Add((string)dtConstraints.Rows[i]["PKColumn"]);
}
}
using (SqlDataAdapter da = new SqlDataAdapter(_getTables, sqlConnection))
{
DataTable dtTables = new DataTable("Tables");
da.Fill(dtTables);
for (int i = 0; i < dtTables.Rows.Count; i++)
{
string schemaName = (string)dtTables.Rows[i]["TABLE_SCHEMA"];
string tableName = (string)dtTables.Rows[i]["TABLE_NAME"];
Table table = new Table()
{
SchemaName = schemaName, TableName = tableName
};
LoadColumns(sqlConnection, table, TableType.Table);
table.Constraints = database.Constraints.Where(constraint => constraint.FKTableName == tableName).ToDatabaseObjectsCollection(table);
tables.Add(table);
}
}
return(tables);
}
public override void PopulateDatasources()
{
DatabaseObjectsCollection <SQLServerDatasource> datasources = new DatabaseObjectsCollection <SQLServerDatasource>(null);
DataTable dt = SqlDataSourceEnumerator.Instance.GetDataSources();
foreach (DataRow dr in dt.Rows)
{
string serverName = dr["ServerName"].ToString().Trim().ToUpper();
string instanceName = null;
string version = null;
if (dr["InstanceName"] != null && dr["InstanceName"] != DBNull.Value)
{
instanceName = dr["InstanceName"].ToString().Trim().ToUpper();
}
if (dr["Version"] != null && dr["Version"] != DBNull.Value)
{
version = dr["Version"].ToString().Trim().Split('.').FirstOrDefault();
}
SQLServerDatasource datasource = new SQLServerDatasource(this)
{
Name = serverName
};
if (version == "8")
{
datasource.ProviderManifestToken = "2000";
}
else if (version == "9")
{
datasource.ProviderManifestToken = "2005";
}
else if (version == "10")
{
datasource.ProviderManifestToken = "2008";
}
if (!String.IsNullOrEmpty(instanceName))
{
datasource.Name += "\\" + instanceName;
}
datasources.Add(datasource);
}
Datasources = datasources;
}
public override void PopulateDatasources()
{
DatabaseObjectsCollection <SQLServerDatasource> datasources = new DatabaseObjectsCollection <SQLServerDatasource>(null);
DataTable dt = SqlDataSourceEnumerator.Instance.GetDataSources();
foreach (DataRow dr in dt.Rows)
{
string serverName = dr["ServerName"].ToString().Trim().ToUpper();
string instanceName = null;
Version version = null;
if (dr["InstanceName"] != null && dr["InstanceName"] != DBNull.Value)
{
instanceName = dr["InstanceName"].ToString().Trim().ToUpper();
}
if (dr["Version"] != null && dr["Version"] != DBNull.Value)
{
version = new Version(dr["Version"].ToString().Trim());
}
SQLServerDatasource datasource = new SQLServerDatasource(this)
{
Name = serverName
};
string manifestToken;
if (!IsVersionSupported(version, out manifestToken))
{
throw new NotSupportedException(string.Format("Version '{0}' is not supported!", version == null ? "unknown" : version.ToString()));
}
datasource.ProviderManifestToken = manifestToken;
if (!String.IsNullOrEmpty(instanceName))
{
datasource.Name += "\\" + instanceName;
}
datasources.Add(datasource);
}
Datasources = datasources;
}
public override DatabaseObjectsCollection <IProcedure> LoadProcedures(IDatabase database)
{
DatabaseObjectsCollection <IProcedure> procedures = new DatabaseObjectsCollection <IProcedure>(database);
SqlConnection sqlConnection = new SqlConnection(database.ConnectionString);
using (SqlDataAdapter da = new SqlDataAdapter(_getProcedures, sqlConnection))
{
DataTable dtProcedures = new DataTable("Procedures");
da.Fill(dtProcedures);
for (int i = 0; i < dtProcedures.Rows.Count; i++)
{
Procedure procedure = new Procedure();
procedure.Name = (string)dtProcedures.Rows[i]["ROUTINE_NAME"];
procedure.SchemaName = (string)dtProcedures.Rows[i]["ROUTINE_SCHEMA"];
if (dtProcedures.Rows[i]["DATA_TYPE"] != DBNull.Value)
{
procedure.DataType = (string)dtProcedures.Rows[i]["DATA_TYPE"];
}
if (dtProcedures.Rows[i]["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
{
procedure.Length = Convert.ToInt32(dtProcedures.Rows[i]["CHARACTER_MAXIMUM_LENGTH"]);
}
if (procedure.Length == -1)
{
switch (procedure.DataType.ToLower())
{
case "varchar":
case "nvarchar":
procedure.DataType += "(max)";
break;
default:
break;
}
}
string procedureType = (string)dtProcedures.Rows[i]["ROUTINE_BODY"];
if (procedureType == "SQL")
{
procedure.ProcedureType = ProcedureType.SQL;
}
else
{
procedure.ProcedureType = ProcedureType.External;
}
procedure.Items = new DatabaseObjectsCollection <IProcedureParameter>(procedure);
DatabaseObjectsCollection <IProcedureParameter> procedureParameters = new DatabaseObjectsCollection <IProcedureParameter>(procedure);
da.SelectCommand = new SqlCommand(string.Format(_getProcedureParameters, procedure.Name, procedure.SchemaName, database.Name), sqlConnection);
DataTable dtProcedureParameters = new DataTable("ProcedureParameters");
da.Fill(dtProcedureParameters);
for (int j = 0; j < dtProcedureParameters.Rows.Count; j++)
{
if (string.IsNullOrEmpty((string)dtProcedureParameters.Rows[j]["PARAMETER_NAME"]) &&
(string)dtProcedureParameters.Rows[j]["IS_RESULT"] == "YES") // = ReturnValue
{
continue;
}
ProcedureParameter procedureParameter = new ProcedureParameter();
procedureParameter.Name = (string)dtProcedureParameters.Rows[j]["PARAMETER_NAME"];
if (procedureParameter.Name.StartsWith("@"))
{
procedureParameter.Name = procedureParameter.Name.Substring(1);
}
if (dtProcedureParameters.Rows[j]["DATA_TYPE"] != DBNull.Value)
{
procedureParameter.DataType = (string)dtProcedureParameters.Rows[j]["DATA_TYPE"];
}
if (dtProcedureParameters.Rows[j]["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
{
procedureParameter.Length = Convert.ToInt32(dtProcedureParameters.Rows[j]["CHARACTER_MAXIMUM_LENGTH"]);
}
string parameterMode = (string)dtProcedureParameters.Rows[j]["PARAMETER_MODE"];
if (parameterMode == "IN")
{
procedureParameter.ParameterMode = ParameterMode.In;
}
else if (parameterMode == "OUT")
{
procedureParameter.ParameterMode = ParameterMode.Out;
}
else
{
procedureParameter.ParameterMode = ParameterMode.InOut;
}
procedure.Items.Add(procedureParameter);
}
procedures.Add(procedure);
}
}
return(procedures);
}
public override void PopulateDatabases(IDatasource datasource)
{
DatabaseObjectsCollection <IDatabase> databases = new DatabaseObjectsCollection <IDatabase>(datasource);
SqlConnection sqlConnection = null;
sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = datasource.ConnectionString;
try
{
sqlConnection.Open();
}
catch (SqlException ex)
{
switch (ex.Number)
{
case 2:
case 3:
case 53:
Application.Current.Dispatcher.BeginInvoke(DispatcherPriority.Background, new Action(() =>
{
Datasources.Remove(datasource as SQLServerDatasource);
}));
break;
default:
break;
}
throw ex;
}
Version version = new Version(sqlConnection.ServerVersion);
string manifestToken;
if (!IsVersionSupported(version, out manifestToken))
{
throw new NotSupportedException(string.Format("Version '{0}' is not supported!", version == null ? "unknown" : version.ToString()));
}
string sql = string.Empty;
if (version.Major >= 9)
{
sql = "use master; select name from sys.databases order by name";
}
else
{
sql = "use master; select name from sysdatabases order by name";
}
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
sqlCommand.CommandTimeout = 20;
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
databases.Add(new Database(datasource)
{
Name = sqlDataReader["name"].ToString()
});
}
sqlDataReader.Close();
datasource.Databases = databases;
if (sqlConnection != null && sqlConnection.State == ConnectionState.Open)
{
sqlConnection.Close();
}
}
public override void PopulateDatabases(IDatasource datasource)
{
DatabaseObjectsCollection <IDatabase> databases = new DatabaseObjectsCollection <IDatabase>(datasource);
SqlConnection sqlConnection = null;
sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = datasource.ConnectionString;
try
{
sqlConnection.Open();
}
catch (SqlException ex)
{
switch (ex.Number)
{
case 2:
case 3:
case 53:
Application.Current.Dispatcher.BeginInvoke(DispatcherPriority.Background, new Action(() =>
{
Datasources.Remove(datasource as SQLServerDatasource);
}));
break;
default:
break;
}
throw ex;
}
string sqlversion = sqlConnection.ServerVersion;
sqlversion = sqlversion.Split('.').FirstOrDefault(); //major version
int intsqlversion = Convert.ToInt32(sqlversion);
string sql = string.Empty;
if (intsqlversion == 8)
{
datasource.ProviderManifestToken = "2000";
}
else if (intsqlversion == 9)
{
datasource.ProviderManifestToken = "2005";
}
else if (intsqlversion == 10)
{
datasource.ProviderManifestToken = "2008";
}
if (intsqlversion >= 9)
{
sql = "use master; select name from sys.databases order by name";
}
else
{
sql = "use master; select name from sysdatabases order by name";
}
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
sqlCommand.CommandTimeout = 20;
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
databases.Add(new Database(datasource)
{
Name = sqlDataReader["name"].ToString()
});
}
sqlDataReader.Close();
datasource.Databases = databases;
if (sqlConnection != null && sqlConnection.State == ConnectionState.Open)
{
sqlConnection.Close();
}
}