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 SQLServerDatabaseDriver() { Datasources = new DatabaseObjectsCollection <SQLServerDatasource>(null); }
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(); } }