public ConnectionObject AddServers(ConnectionObject connectionObject)
{
var servers = new List <Server>
{
new Server {
ServerId = 1, ServerAddress = "192.168.17.xx"
},
new Server {
ServerId = 2, ServerAddress = "192.168.17.xx"
},
new Server {
ServerId = 3, ServerAddress = "192.168.17.xx"
},
new Server {
ServerId = 4, ServerAddress = "192.168.17.xx"
}
};
var localConnectionObject = new ConnectionObject
{
Servers = servers.Select(x => new Server
{
ServerId = x.ServerId,
ServerAddress = x.ServerAddress
})
};
connectionObject.Servers = localConnectionObject.Servers;
return(connectionObject);
}
public string GetConnectionString(ConnectionObject currentConnection)
{
string connectionString = null;
if (currentConnection.DatabaseSelectedName != null)
{
connectionString = "Data Source= " + currentConnection.ServerSelectedName + "; Database=" + currentConnection.DatabaseSelectedName + "; User ID=" + userName + ";Password="******";";
}
return(connectionString);
}
public ConnectionObject executeStoredProcedure(ConnectionObject currentConnection, string fullProcedureName, List <string> parameterList, bool hasResult)
{
try
{
connection = "Data Source= " + currentConnection.ServerSelectedName + "; Database=" + currentConnection.DatabaseSelectedName + "; User ID=" + userName + ";Password="******";";
SqlConnection databaseConnection = new SqlConnection(connection);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = fullProcedureName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = databaseConnection;
cmd.CommandTimeout = 20000;
databaseConnection.Open();
SqlCommandBuilder.DeriveParameters(cmd);
if (parameterList.Count() > 1)
{
for (var i = 0; i < parameterList.Count; i++)
{
cmd.Parameters[i + 1].Value = parameterList[i];
}
}
if (hasResult == true)
{
SqlDataReader result = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(result);
currentConnection.dataTable = dt;
}
else
{
int result = cmd.ExecuteNonQuery();
}
databaseConnection.Close();
currentConnection.isConnected = true;
}
catch (Exception ex)
{
//currentConnection.isConnected = false;
currentConnection.error = ex.Message;
}
return(currentConnection);
}
public ConnectionObject getDatabases(ConnectionObject currentConnection)
{
Server server = (from Server s in currentConnection.Servers
where s.ServerId == currentConnection.ServerSelected
select s).Single();
connection = "Data Source= " + server.ServerAddress + " ; User ID=" + userName + ";Password="******";";
currentConnection.ServerSelectedName = server.ServerAddress;
using (var con = new SqlConnection(connection))
{
var databasesList = new List <Database>();
try
{
con.Open();
DataTable databases = con.GetSchema("Databases");
for (int i = 0; i <= databases.Rows.Count - 1; i++)
{
string databaseName = databases.Rows[i].Field <String>("database_name");
databasesList.Add(new Database {
DatabaseId = i.ToString(), DatabaseName = databaseName
});
}
//now sort the list for a friendly UX
databasesList = databasesList.OrderBy(x => x.DatabaseName).ToList();
var connectionObject = new ConnectionObject
{
Databases = databasesList.Select(x => new Database
{
DatabaseId = x.DatabaseId,
DatabaseName = x.DatabaseName
})
};
currentConnection.Databases = connectionObject.Databases;
}
catch (Exception ex)
{
// do something with this later
string exception = ex.Message;
}
return(currentConnection);
}
}
public ConnectionObject connectToDatabase(ConnectionObject currentConnection)
{
if (currentConnection.DatabaseSelected != null)
{
Database database = (from Database d in currentConnection.Databases
where d.DatabaseId == currentConnection.DatabaseSelected
select d).Single();
currentConnection.DatabaseSelectedName = database.DatabaseName;
connection = "Data Source= " + currentConnection.ServerSelectedName + "; Database=" + currentConnection.DatabaseSelectedName + "; User ID=" + userName + ";Password="******";";
SqlConnection databaseConnection = new SqlConnection(connection);
using (databaseConnection)
{
try
{
databaseConnection.Open();
databaseConnection.Close();
currentConnection.isConnected = true;
}
catch (Exception ex)
{
currentConnection.isConnected = false;
currentConnection.error = ex.Message;
}
}
}
else
{
currentConnection.error = "No Database selected";
currentConnection.isConnected = false;
}
return(currentConnection);
}