/// <summary>
/// A special version of GetDataStepDefinition that returns just the DATA step
/// header. It's used for purposes of previewing the data set structure.
/// </summary>
/// <param name="output">The name of the output data set.</param>
/// <returns>The DATA step definition.</returns>
public string GetJustDataStepDefinition(string output)
{
// open the connection to data
SasServer sasServer = new SasServer(_server);
// the "using" statement will ensure that the
// connection is closed and disposed of when the
// scope of the "using" block ends.
using (_connection = sasServer.GetOleDbConnection())
{
try
{
_connection.Open();
char[] types;
string code = GetDataStepDefinition(output, out types);
// add the "values omitted" message to the end of this preview code
code = code + Environment.NewLine + Messages.ValuesOmitted;
// add the 4 semicolons to terminate the DATALINES4; statement
code = code + Environment.NewLine + ";;;;";
_connection.Close();
return(code);
}
catch (Exception ex)
{
return(string.Format("/*Error trying to read input data: {0} */ \n", ex.Message));
}
}
}
/// <summary>
/// Query to retrieve the macro values from the active server
/// </summary>
private void RetrieveMacroValues()
{
SasServer s = cmbServers.SelectedItem as SasServer;
using (System.Data.OleDb.OleDbConnection dbConnection = s.GetOleDbConnection())
{
//----- make provider connection
dbConnection.Open();
//----- Read values from query command
string sql = @"select * from sashelp.vmacro order by name";
OleDbCommand cmdDB = new OleDbCommand(sql, dbConnection);
OleDbDataReader dataReader = cmdDB.ExecuteReader(CommandBehavior.CloseConnection);
try
{
MacroVars = new List <MacroVar>();
while (dataReader.Read())
{
MacroVar var = new MacroVar()
{
Name = dataReader["name"].ToString(),
CurrentValue = dataReader["value"].ToString(),
Scope = dataReader["scope"].ToString()
};
MacroVars.Add(var);
}
}
finally
{
dataReader.Close();
dbConnection.Close();
}
}
}
private void PopulateUserFormats(TreeNode tn)
{
SasServer s = new SasServer(currentServer);
using (System.Data.OleDb.OleDbConnection dbConnection = s.GetOleDbConnection())
{
//----- make provider connection
dbConnection.Open();
//----- Read values from query command
string sql = @"select distinct libname, memname from sashelp.vformat where libname NOT is missing";
OleDbCommand cmdDB = new OleDbCommand(sql, dbConnection);
OleDbDataReader dataReader = cmdDB.ExecuteReader(CommandBehavior.CloseConnection);
try
{
while (dataReader.Read())
{
TreeNode n = new TreeNode(string.Format("{0}.{1}", dataReader["libname"].ToString(), dataReader["memname"].ToString()));
n.ImageIndex = (int)CatImages.Catalog;
n.SelectedImageIndex = (int)CatImages.Catalog;
n.Tag = "LIBCAT";
tn.Nodes.Add(n);
}
}
finally
{
dataReader.Close();
dbConnection.Close();
}
}
}
/// <summary>
/// Generate a SAS DATA step program from the active data set.
/// </summary>
/// <remarks>
/// <b>NOTE:</b>The DATALINES content is built up entirely in memory.
/// If you need this to work with very large data sets, this would need
/// to be refactored to emit the contents to a file-based stream as you
/// build it out.
/// <para>
/// Using StringBuilder along the way prevents large strings from being copied around
/// and duplicated, but eventually there is going to
/// be a large block of text represented in the process memory.
/// </para>
/// </remarks>
/// <param name="output">The output location of the SAS data set that the program will create.</param>
/// <returns>The complete SAS DATA step program, complete with DATALINES values.</returns>
public string GetCompleteSasProgram(string output)
{
System.Text.StringBuilder sb = new StringBuilder();
SasServer sasServer = new SasServer(_server);
using (_connection = sasServer.GetOleDbConnection())
{
try
{
_connection.Open();
char[] types;
sb.Append(GetDataStepDefinition(output, out types));
string valuesdata = string.Format("{0}.{1}", _libref, _member);
OleDbCommand command = new OleDbCommand(string.Format("select * from {0}", valuesdata), _connection);
using (OleDbDataReader dataReader = command.ExecuteReader())
{
int fields = dataReader.FieldCount;
ColumnCount = fields;
RowCount = 0;
while (dataReader.Read())
{
StringBuilder line = new StringBuilder();
for (int i = 0; i < fields; i++)
{
string val = dataReader[i].ToString();
if (types[i] == 'C')
{
// quote and escape strange chars for character values
val = string.Format("\"{0}\"", FixUpDatalinesString(val));
}
line.AppendFormat("{0}{1}", val, i < fields - 1 ? "," : "");
}
// increment the row count
RowCount++;
// add to our main program
sb.AppendFormat("{0}\n", line.ToString());
}
}
_connection.Close();
}
catch (Exception ex)
{
sb.AppendFormat("/*Error trying to read input data: {0} */ \n", ex.Message);
}
finally
{
// close out the datalines and add RUN
sb.Append(";;;;\nRUN;\n");
}
}
return(sb.ToString());
}
private void AddCardinalities()
{
SasServer s = new SasServer(Consumer.AssignedServer);
using (OleDbConnection conn = s.GetOleDbConnection())
{
try
{
//----- make provider connection
conn.Open();
//----- Read values from query command
string sql = @"select * from work._ds_cardinalities";
OleDbCommand cmdDB = new OleDbCommand(sql, conn);
OleDbDataReader dataReader = cmdDB.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader.Read())
{
cardinalities.Add(
dataReader["name"].ToString(),
new Cardinality()
{
levels = Convert.ToInt32(dataReader["nlevels"]),
pct_unique = Convert.ToDouble(dataReader["pct_unique"])
}
);
}
lvColumns.BeginUpdate();
foreach (ListViewItem li in lvColumns.Items)
{
Cardinality c = cardinalities[li.Text];
if (c != null)
{
li.SubItems[(int)eColIndex.Cardinality].Text = c.levels.ToString();
li.SubItems[(int)eColIndex.PctUnique].Text = c.pct_unique.ToString("#0.00%");
((ColTag)li.Tag).Cardinality = c;
}
}
lvColumns.EndUpdate();
}
catch { }
finally
{
conn.Close();
}
}
}
private void RetrieveOptionValues()
{
SasServer s = cmbServers.SelectedItem as SasServer;
dServerVer = 9.2;
string ver = s.GetSasMacroValue("SYSVER");
try
{
dServerVer = Convert.ToDouble(ver);
}
catch { }
using (System.Data.OleDb.OleDbConnection dbConnection = s.GetOleDbConnection())
{
//----- make provider connection
dbConnection.Open();
//----- Read values from query command
string sql = @"select * from sashelp.vallopt order by optname";
OleDbCommand cmdDB = new OleDbCommand(sql, dbConnection);
OleDbDataReader dataReader = cmdDB.ExecuteReader(CommandBehavior.CloseConnection);
try
{
Options = new List <Option>();
while (dataReader.Read())
{
Option opt = new Option()
{
Name = dataReader["optname"].ToString(),
Setting = dataReader["setting"].ToString(),
Type = dataReader["opttype"].ToString(),
Description = dataReader["optdesc"].ToString(),
Level = dataReader["level"].ToString(),
Start = dServerVer > 9.2 ? dataReader["optstart"].ToString() : "",
Group = dataReader["group"].ToString()
};
if (opt.Level.ToUpper() == "GRAPH")
{
opt.Group = "GRAPH";
opt.Level = "Portable";
opt.Start = "OPTIONS or GOPTIONS statement";
}
switch (opt.Start.ToLower())
{
case "anytime": opt.Start = "OPTIONS statement or startup";
break;
case "startup": opt.Start = "Startup only";
break;
case "": opt.Start = opt.Start = "(requires SAS 9.3 or later to determine)";
break;
default: break;
}
;
Options.Add(opt);
}
}
finally
{
dataReader.Close();
dbConnection.Close();
}
}
}
// When code query is complete, read the process details from
// a SAS data set on the server session.
// Use this to populate the list view of processes.
private void AddProcesses()
{
listProcesses.BeginUpdate();
listProcesses.Items.Clear();
SasServer s = new SasServer(Consumer.AssignedServer);
string thisProcess = s.GetSasMacroValue("SYSJOBID");
using (OleDbConnection conn = s.GetOleDbConnection())
{
try
{
//----- make provider connection
conn.Open();
//----- Read values from query command
string sql = @"select * from work._allpids_";
OleDbCommand cmdDB = new OleDbCommand(sql, conn);
OleDbDataReader dataReader = cmdDB.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader.Read())
{
// create an in-memory object of the process
// so we can use this in a PropertyGrid control later
SasProcess proc = new SasProcess();
proc.PID = dataReader["ProcessIdentifier"].ToString().Trim();
proc.Host = dataReader["HostKnownBy"].ToString().Trim();
proc.Type = dataReader["ServerComponentName"].ToString().Trim();
proc.ServerPort = dataReader["ServerPort"].ToString().Trim();
proc.UUID = dataReader["UniqueIdentifier"].ToString().Trim();
proc.CPUs = dataReader["CPUCount"].ToString().Trim();
proc.Command = dataReader["Command"].ToString().Trim();
proc.Owner = dataReader["ProcessOwner"].ToString().Trim();
proc.SASVersion = dataReader["VersionLong"].ToString().Trim();
proc.StartTime = dataReader["UpTime"].ToString().Trim();
ListViewItem li = new ListViewItem(proc.PID);
li.Tag = proc;
li.SubItems.Add(proc.Type);
li.SubItems.Add(proc.Host);
li.SubItems.Add(proc.Owner);
li.SubItems.Add(proc.StartTime);
li.SubItems.Add(proc.UUID);
if (proc.PID.Trim() == thisProcess.Trim()) li.BackColor = Color.LightGoldenrodYellow;
listProcesses.Items.Add(li);
}
}
catch
{
MessageBox.Show("An error occurred while trying to retrieve the list of spawned processes.", "Cannot retrieve processes");
}
finally
{
conn.Close();
}
}
listProcesses.EndUpdate();
}
private void AddCardinalities()
{
SasServer s = new SasServer(Consumer.AssignedServer);
using (OleDbConnection conn = s.GetOleDbConnection())
{
try
{
//----- make provider connection
conn.Open();
//----- Read values from query command
string sql = @"select * from work._ds_cardinalities";
OleDbCommand cmdDB = new OleDbCommand(sql, conn);
OleDbDataReader dataReader = cmdDB.ExecuteReader(CommandBehavior.CloseConnection);
while (dataReader.Read())
{
cardinalities.Add(
dataReader["name"].ToString(),
new Cardinality()
{
levels = Convert.ToInt32(dataReader["nlevels"]),
pct_unique = Convert.ToDouble(dataReader["pct_unique"])
}
);
}
lvColumns.BeginUpdate();
foreach (ListViewItem li in lvColumns.Items)
{
Cardinality c = cardinalities[li.Text];
if (c != null)
{
li.SubItems[(int)eColIndex.Cardinality].Text = c.levels.ToString();
li.SubItems[(int)eColIndex.PctUnique].Text = c.pct_unique.ToString("#0.00%");
((ColTag)li.Tag).Cardinality = c;
}
}
lvColumns.EndUpdate();
}
catch { }
finally
{
conn.Close();
}
}
}
/// <summary>
/// Generate a SAS DATA step program from the active data set.
/// </summary>
/// <remarks>
/// <b>NOTE:</b>The DATALINES content is built up entirely in memory.
/// If you need this to work with very large data sets, this would need
/// to be refactored to emit the contents to a file-based stream as you
/// build it out.
/// <para>
/// Using StringBuilder along the way prevents large strings from being copied around
/// and duplicated, but eventually there is going to
/// be a large block of text represented in the process memory.
/// </para>
/// </remarks>
/// <param name="output">The output location of the SAS data set that the program will create.</param>
/// <returns>The complete SAS DATA step program, complete with DATALINES values.</returns>
public string GetCompleteSasProgram(string output)
{
System.Text.StringBuilder sb = new StringBuilder();
SasServer sasServer = new SasServer(_server);
using (_connection = sasServer.GetOleDbConnection())
{
try
{
_connection.Open();
char[] types;
sb.Append(GetDataStepDefinition(output, out types));
string valuesdata = string.Format("{0}.{1}", _libref, _member);
OleDbCommand command = new OleDbCommand(string.Format("select * from {0}", valuesdata), _connection);
using (OleDbDataReader dataReader = command.ExecuteReader())
{
int fields = dataReader.FieldCount;
ColumnCount = fields;
RowCount = 0;
while (dataReader.Read())
{
StringBuilder line = new StringBuilder();
for (int i = 0; i < fields; i++)
{
string val = dataReader[i].ToString();
if (types[i] == 'C')
{
// quote and escape strange chars for character values
val = string.Format("\"{0}\"", FixUpDatalinesString(val));
}
line.AppendFormat("{0}{1}", val, i < fields - 1 ? "," : "");
}
// increment the row count
RowCount++;
// add to our main program
sb.AppendFormat("{0}\n", line.ToString());
}
}
_connection.Close();
}
catch (Exception ex)
{
sb.AppendFormat("/*Error trying to read input data: {0} */ \n", ex.Message);
}
finally
{
// close out the datalines and add RUN
sb.Append(";;;;\nRUN;\n");
}
}
return sb.ToString();
}
/// <summary>
/// A special version of GetDataStepDefinition that returns just the DATA step
/// header. It's used for purposes of previewing the data set structure.
/// </summary>
/// <param name="output">The name of the output data set.</param>
/// <returns>The DATA step definition.</returns>
public string GetJustDataStepDefinition(string output)
{
// open the connection to data
SasServer sasServer = new SasServer(_server);
// the "using" statement will ensure that the
// connection is closed and disposed of when the
// scope of the "using" block ends.
using (_connection = sasServer.GetOleDbConnection())
{
try
{
_connection.Open();
char[] types;
string code = GetDataStepDefinition(output, out types);
// add the "values omitted" message to the end of this preview code
code = code + Environment.NewLine + Messages.ValuesOmitted;
// add the 4 semicolons to terminate the DATALINES4; statement
code = code + Environment.NewLine + ";;;;";
_connection.Close();
return code;
}
catch (Exception ex)
{
return string.Format("/*Error trying to read input data: {0} */ \n", ex.Message);
}
}
}