/// <summary>
/// Returns the distinct values for any column from the database table
/// This is needed on the BCP Split screen to get the distinct list of values for splitting the BCP file
/// </summary>
/// <param name="SchemaName"></param>
/// <param name="TableName"></param>
/// <param name="ColumnName"></param>
/// <param name="AppConfig"></param>
/// <returns></returns>
public static DataTable GetColumnValues(string SchemaName, string TableName, string ColumnName, MySettingsEnvironments AppConfig)
{
DataTable dt = new DataTable();
string connstr = AppConfig.GetConnectionString();
SqlConnection conn = new SqlConnection(connstr);
string sql = "SELECT DISTINCT [" + ColumnName + "] Col FROM [" + SchemaName + "].[" + TableName + "] ORDER BY Col";
try
{
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
comm.CommandTimeout = 0;
SqlDataReader rdr = comm.ExecuteReader();
dt.Load(rdr);
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Gets the table size, row count etc. from the Database
/// </summary>
/// <param name="SQLDwConfig"></param>
public void RefreshTableListFromDB(MySettingsEnvironments SQLDwConfig)
{
string connstr = SQLDwConfig.GetConnectionString();
string sql;
string strFolderApplication = UtilGeneral.GetApplicationFolder();
string strFileTableList = strFolderApplication + "\\" + Constants.FOLDER_CONNECTION_TYPE + "\\" + SQLDwConfig.ConnectionType + "\\" + Constants.FILE_TABLE_LIST;
sql = System.IO.File.ReadAllText(strFileTableList);
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
try
{
comm.CommandTimeout = 120;
SqlDataReader rdr = comm.ExecuteReader();
TableList.Clear();
TableList.Load(rdr);
rdr = null;
}
catch (Exception ex)
{
throw new System.Exception(ex.Message);
}
finally
{
conn.Close();
}
}
/// <summary>
/// Get Distinct list of Date values from database based for any Date format.
/// This is needed on the BCP Split screen to get the distinct list of values for splitting the BCP file
/// </summary>
/// <param name="SchemaName"></param>
/// <param name="TableName"></param>
/// <param name="ColumnName"></param>
/// <param name="DataType"></param>
/// <param name="TimePeriodType"></param>
/// <param name="AppConfig"></param>
/// <returns></returns>
public static DataTable GetColumnValuesWithDateFormat(string SchemaName, string TableName, string ColumnName, string DataType, int TimePeriodType, MySettingsEnvironments AppConfig)
{
DataTable dt = new DataTable();
string connstr = AppConfig.GetConnectionString();
SqlConnection conn = new SqlConnection(connstr);
string strColumnFormatted = null;
TableName = UtilGeneral.GetQuotedString(TableName);
string sql = "WITH AllDates AS ( ";
sql += "SELECT DISTINCT " + ColumnName + " FROM " + SchemaName + "." + TableName + " ";
sql += ")";
sql += "SELECT DISTINCT ";
strColumnFormatted = GetDateSQLString(ColumnName, DataType, TimePeriodType);
//In case there are invalid values, then use that value instead of formatting it
if (DataType == Constants.DATA_TYPE_INT)
{
strColumnFormatted = "CASE WHEN LEN(" + ColumnName + ") = 8 THEN " + strColumnFormatted + " ELSE CONVERT(VARCHAR, " + ColumnName + ") END ";
}
strColumnFormatted = strColumnFormatted + " " + ColumnName;
sql += strColumnFormatted;
sql += " FROM AllDates ORDER BY " + ColumnName;
try
{
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
comm.CommandTimeout = 0;
SqlDataReader rdr = comm.ExecuteReader();
dt.Load(rdr);
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Creates a new ColumnList instance based on Schema and Table Name.
/// Uses the current configuration data for more details
/// </summary>
/// <param name="strSchemaName"></param>
/// <param name="strTableName"></param>
/// <param name="SQLDwConfig">The application configuration is passed to get all details related to currently selected configuration</param>
internal MyColumnList(string strSchemaName, string strTableName, MySettingsEnvironments SQLDwConfig)
{
ColumnList = new DataTable();
ColumnList.Columns.Add(COLUMN_NAME);
ColumnList.Columns.Add(DATA_TYPE);
ColumnList.Columns.Add(LENGTH, Type.GetType("System.Int32"));
ColumnList.Columns.Add(PRECISION, Type.GetType("System.Int32"));
ColumnList.Columns.Add(SCALE, Type.GetType("System.Int32"));
ColumnList.Columns.Add(IS_NULLABLE, Type.GetType("System.Int32"));
ColumnList.Columns.Add(ORDER, Type.GetType("System.Int32"));
ColumnList.Columns.Add(PKCOLUMN, Type.GetType("System.Int32"));
ColumnList.Columns.Add(PKDESCENDING, Type.GetType("System.Int32"));
// Get the connection string from the current selected configuration
string connstr = SQLDwConfig.GetConnectionString();
string strFolderApplication = UtilGeneral.GetApplicationFolder();
string strFileTableList = strFolderApplication + "\\" + Constants.FOLDER_CONNECTION_TYPE + "\\" + SQLDwConfig.ConnectionType + "\\" + Constants.FILE_COLUMN_LIST;
string sql = System.IO.File.ReadAllText(strFileTableList);
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.Add(new SqlParameter("@SchemaName", SqlDbType.VarChar));
comm.Parameters["@SchemaName"].Value = strSchemaName;
comm.Parameters.Add(new SqlParameter("@TableName", SqlDbType.VarChar));
comm.Parameters["@TableName"].Value = strTableName;
try
{
SqlDataReader rdr = comm.ExecuteReader();
ColumnList.Load(rdr);
}
catch (Exception ex)
{
throw new System.Exception(ex.ToString());
}
finally
{
conn.Close();
}
}