/// <summary>
/// Gets the columns for datasource.
/// </summary>
/// <param name="dr">The dr.</param>
/// <returns></returns>
public DataTable GetColumnsForDatasource(DataRow[] dr)
{
Security.Cryptography cy = new Security.Cryptography();
string tableName = cy.Decrypt(dr[0]["DatabaseObject"].ToString());
string externalConnectionString = Utilities.CreateConnectionString(DataBaseTypeEnum.PostgreSQL, dr);
string query = "";
if (tableName.SQLTest())
{
query = string.Format("SELECT * FROM ( {0} ) as table1 limit 1; ", tableName);
}
else
{
query = string.Format("SELECT * FROM {0} limit 1;", tableName);
}
DataTable dt = new DataTable();
PostgreSQLDB pstdb = new PostgreSQLDB(externalConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandText = query;
Command.CommandType = CommandType.Text;
dt = pstdb.ExecuteDataSet(Command).Tables[0];
return(dt);
}
/// <summary>
/// Gets the columns for datasource.
/// </summary>
/// <param name="dr">The dr.</param>
/// <returns></returns>
public DataTable GetColumnsForDatasource(DataRow[] dr)
{
Security.Cryptography cy = new Security.Cryptography();
string tableName = cy.Decrypt(dr[0]["DatabaseObject"].ToString());
string externalConnectionString = Utilities.CreateConnectionString(DataBaseTypeEnum.SQLServer, dr);
string query = "";
// if (tableName.ToLower().Contains("select"))
if (tableName.SQLTest())
{
query = string.Format("SELECT TOP 1 * FROM ( {0} ) as table1 ", tableName);
}
else
{
query = string.Format("SELECT TOP 1 * FROM {0}", tableName);
}
DataTable dt = new DataTable();
SqlDatabase sqd = new SqlDatabase(externalConnectionString);
//dt = sqd.ExecuteDataSet("usp_read_columns_for_datasource", tableName, tableName.SQLTest()).Tables[0];
dt = sqd.ExecuteDataSet(CommandType.Text, query).Tables[0];
return(dt);
}
/// <summary>
/// Gets the external connection string.
/// </summary>
/// <param name="dataSourceName">Name of the data source.</param>
/// <param name="tableName"></param>
/// <returns></returns>
public string GetExternalConnectionString(string dataSourceName, out string tableName) // out DataBaseTypeEnum databaseType)
{
DataTable dtGet;
PostgreSQLDB ewavDB = new PostgreSQLDB(this.MetaDataConnectionString);
NpgsqlCommand Command = new NpgsqlCommand();
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "read_external_connec_str";
NpgsqlParameter parameter = new NpgsqlParameter("dsname", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = dataSourceName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
parameter = new NpgsqlParameter("dbobject", NpgsqlTypes.NpgsqlDbType.Varchar);
parameter.Value = this.MetaDataViewName;
parameter.Direction = ParameterDirection.Input;
Command.Parameters.Add(parameter);
try
{
dtGet = ewavDB.ExecuteDataSet(Command).Tables[0];
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
string extConnectionString = " ";
extConnectionString = Utilities.CreateConnectionString(DataBaseTypeEnum.PostgreSQL,
new DataRow[] { dtGet.Rows[0] });
Ewav.Security.Cryptography cy = new Security.Cryptography();
tableName = cy.Decrypt(dtGet.Rows[0]["DatabaseObject"].ToString());
return(extConnectionString);
}
public static void InitConnections( )
{
STDataServersController serverCtrl = new STDataServersController();
foreach (STDataServerSyncsInfo syncInfo in new STDataServerSyncsController().GetListAllObjects().Cast <STDataServerSyncsInfo>().ToList())
{
if (!syncInfo.FK_STDataServerID.HasValue)
{
continue;
}
STDataServersInfo server = serverCtrl.GetObjectByID(syncInfo.FK_STDataServerID.Value) as STDataServersInfo;
if (server == null)
{
return;
}
#region Init Linked Server
Security.Cryptography cryp = new Security.Cryptography();
String strPassword = cryp.Decrypt(server.EncryptedPassword);
String strQuery = String.Format(@"IF EXISTS(SELECT * FROM sys.servers WHERE name = '{0}') EXEC master.sys.sp_dropserver '{0}','droplogins'
EXEC master.dbo.sp_addlinkedserver
@server = '{0}',
@srvproduct=N'MSSQL',
@provider=N'SQLNCLI',
@provstr=N'PROVIDER=SQLOLEDB;SERVER={1}'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='{0}',
@useself=N'False',@locallogin=NULL,@rmtuser=N'{2}',@rmtpassword='******'", server.Name, server.ServerAddress, server.UserName, strPassword);
BusinessObjectController.RunQuery(strQuery);
#endregion
InitCompanyConnection(server.STDataServerID);
InitSystemConnection(server.STDataServerID);
}
}