internal static IList <string> GetDatabases(TextBox SQLServer, TextBox SQLUser, CheckBox SQLUseSQLAuth, string SQLPassword, out string errorMessage)
{
errorMessage = "";
try
{
if (StringExtentions.IsNullOrWhiteSpace(SQLServer.Value, SQLUser.Value) == false &&
(SQLUseSQLAuth.Value == false || String.IsNullOrWhiteSpace(SQLPassword) == false))
{
using (SqlConnection connection = new SqlConnection(GetConnectionString(SQLServer.Value, SQLUser.Value, SQLPassword, SQLUseSQLAuth.Value, null)))
{
connection.Open();
using (SqlCommand command = new SqlCommand("select name from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') ORDER BY name", connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
var databases = new List <string>();
while (reader.Read())
{
databases.Add(reader.GetString(0));
}
return(databases);
}
}
}
}
SQLServer.HelpText = String.Empty;
}
catch (Exception ex)
{
errorMessage = "Unable to fetch list of Databases. Check the login details or try entering the Database name manually.";
}
return(new List <string>());
}
internal static IList <string> GetTables(TextBox SQLServer, TextBox SQLUser, CheckBox SQLUseSQLAuth, string SQLPassword, DropDown SQLDatabase, out string errorMessage)
{
errorMessage = "";
try
{
if (StringExtentions.IsNullOrWhiteSpace(SQLServer.Value, SQLUser.Value, SQLDatabase.Value) == false &&
(SQLUseSQLAuth.Value == false || String.IsNullOrWhiteSpace(SQLPassword) == false))
{
using (SqlConnection connection = new SqlConnection(GetConnectionString(SQLServer.Value, SQLUser.Value, SQLPassword, SQLUseSQLAuth.Value, SQLDatabase.Value)))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT [TABLE_SCHEMA] + '.' + [TABLE_NAME] FROM INFORMATION_SCHEMA.TABLES ORDER BY [TABLE_SCHEMA], [TABLE_NAME]", connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
var tables = new List <string>();
while (reader.Read())
{
tables.Add(reader.GetString(0));
}
return(tables);
}
}
}
}
SQLServer.HelpText = String.Empty;
}
catch (Exception ex)
{
errorMessage = "Unable to fetch list of Tables";
}
return(new List <string>());
}
internal static IList <string> GetStoredProcs(TextBox SQLServer, TextBox SQLUser, CheckBox SQLUseSQLAuth, string SQLPassword, DropDown SQLDatabase, out string errorMessage)
{
errorMessage = "";
try
{
if (StringExtentions.IsNullOrWhiteSpace(SQLServer.Value, SQLUser.Value, SQLDatabase.Value) == false &&
(SQLUseSQLAuth.Value == false || String.IsNullOrWhiteSpace(SQLPassword) == false))
{
using (SqlConnection connection = new SqlConnection(GetConnectionString(SQLServer.Value, SQLUser.Value, SQLPassword, SQLUseSQLAuth.Value, SQLDatabase.Value)))
{
connection.Open();
using (SqlCommand command = new SqlCommand("select s.name+'.'+p.name as oname from sys.procedures p inner join sys.schemas s on p.schema_id = s.schema_id order by s.name, p.name", connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
var procs = new List <string>();
while (reader.Read())
{
procs.Add(reader.GetString(0));
}
return(procs);
}
}
}
}
SQLServer.HelpText = String.Empty;
}
catch (Exception ex)
{
errorMessage = "Unable to fetch list of Stored Procedures";
}
return(new List <string>());
}
internal static List <string> GetTableTypeCoumns(TextBox SQLServer, TextBox SQLUser, CheckBox SQLUseSQLAuth, string SQLPassword, DropDown SQLDatabase, string SQLTableTypeName, out string errorMessage)
{
errorMessage = "";
try
{
if (StringExtentions.IsNullOrWhiteSpace(SQLServer.Value, SQLUser.Value, SQLDatabase.Value) == false &&
(SQLUseSQLAuth.Value == false || String.IsNullOrWhiteSpace(SQLPassword) == false))
{
using (SqlConnection connection = new SqlConnection(GetConnectionString(SQLServer.Value, SQLUser.Value, SQLPassword, SQLUseSQLAuth.Value, SQLDatabase.Value)))
{
connection.Open();
using (SqlCommand command = new SqlCommand("select c.name as column_name from sys.table_types t inner join sys.columns c on c.object_id = t.type_table_object_id inner join sys.types y on y.user_type_id = c.user_type_id where t.is_user_defined = 1 and t.is_table_type = 1 and t.name = '" + SQLTableTypeName + "'", connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
var tables = new List <string>();
while (reader.Read())
{
tables.Add(reader.GetString(0));
}
return(tables);
}
}
}
}
SQLServer.HelpText = String.Empty;
}
catch (Exception ex)
{
errorMessage = "Unable to fetch list of user defined Table Type Columns";
}
return(new List <string>());
}
internal static IList <string> GetUserDefinedTableTypes(TextBox SQLServer, TextBox SQLUser, CheckBox SQLUseSQLAuth, string SQLPassword, DropDown SQLDatabase, out string errorMessage)
{
errorMessage = "";
try
{
if (StringExtentions.IsNullOrWhiteSpace(SQLServer.Value, SQLUser.Value, SQLDatabase.Value) == false &&
(SQLUseSQLAuth.Value == false || String.IsNullOrWhiteSpace(SQLPassword) == false))
{
using (SqlConnection connection = new SqlConnection(GetConnectionString(SQLServer.Value, SQLUser.Value, SQLPassword, SQLUseSQLAuth.Value, SQLDatabase.Value)))
{
connection.Open();
using (SqlCommand command = new SqlCommand("select name from sys.types where is_user_defined = 1 and is_table_type = 1", connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
var tables = new List <string>();
while (reader.Read())
{
tables.Add(reader.GetString(0));
}
return(tables);
}
}
}
}
SQLServer.HelpText = String.Empty;
}
catch (Exception ex)
{
errorMessage = "Unable to fetch list of user defined Table Types";
}
return(new List <string>());
}
internal static List <SqlParameter> GetStoredProcParams(string SQLServer, string SQLUser, bool SQLUseSQLAuth, string SQLPassword, string SQLDatabase, string storedProcName)
{
if (StringExtentions.IsNullOrWhiteSpace(SQLServer, SQLUser, SQLDatabase) == false &&
(SQLUseSQLAuth == false || String.IsNullOrWhiteSpace(SQLPassword) == false))
{
using (SqlConnection connection = new SqlConnection(GetConnectionString(SQLServer, SQLUser, SQLPassword, SQLUseSQLAuth, SQLDatabase)))
{
SqlCommand cmd = new SqlCommand(storedProcName, connection)
{
CommandType = CommandType.StoredProcedure
};
cmd.Connection.Open();
SqlCommandBuilder.DeriveParameters(cmd);
return(cmd.Parameters.Cast <SqlParameter>().ToList());
}
}
return(new List <SqlParameter>());
}
internal static IList <DataColumn> GetColumns(string SQLServer, string SQLUser, bool SQLUseSQLAuth, string SQLPassword, string SQLDatabase, string SQLTable)
{
if (StringExtentions.IsNullOrWhiteSpace(SQLServer, SQLUser, SQLDatabase, SQLTable) == false &&
(SQLUseSQLAuth == false || String.IsNullOrWhiteSpace(SQLPassword) == false))
{
DataTable dt = new DataTable();
using (SqlConnection connection = new SqlConnection(GetConnectionString(SQLServer, SQLUser, SQLPassword, SQLUseSQLAuth, SQLDatabase)))
{
using (SqlDataAdapter a = new SqlDataAdapter(string.Format("SELECT * FROM {0}", SQLHelpers.AddTableQuotes(SQLTable)), connection))
{
a.FillSchema(dt, SchemaType.Source);
return(dt.Columns.Cast <DataColumn>().ToList());
}
}
}
else
{
return(new List <DataColumn>());
}
}
