// read list of catalog entries for the given catalog
private void PopulateMembers(string lib, string cat)
{
Cursor c = Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
lvMembers.BeginUpdate();
lvMembers.Items.Clear();
SAS.Workspace ws = null;
try
{
ws = consumer.Workspace(currentServer) as SAS.Workspace;
}
catch (Exception ex)
{
throw new System.Exception("ISASTaskConsumer.Workspace is not usable!", ex);
}
if (currentServer.Length > 0 && ws != null)
{
// use the SAS IOM OLEDB provider to read data from the SAS workspace
ADODB.Recordset adorecordset = new ADODB.RecordsetClass();
ADODB.Connection adoconnect = new ADODB.ConnectionClass();
try
{
adoconnect.Open("Provider=sas.iomprovider.1; SAS Workspace ID=" + ws.UniqueIdentifier, "", "", 0);
// use the SASHELP.VCATALG view to get all of the catalog entries in the specified library/catalog
string selectclause = "select * from sashelp.vcatalg where libname='" + lib + "' and memname = '" + cat + "'";
adorecordset.Open(selectclause, adoconnect, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, (int)ADODB.CommandTypeEnum.adCmdText);
while (!adorecordset.EOF)
{
ListViewItem lvi = new ListViewItem();
lvi.Text = adorecordset.Fields["objname"].Value.ToString();
lvi.SubItems.Add(adorecordset.Fields["objtype"].Value.ToString());
lvi.SubItems.Add(adorecordset.Fields["objdesc"].Value.ToString());
lvi.SubItems.Add(ConvertSASDate(adorecordset.Fields["created"].Value.ToString()));
lvi.SubItems.Add(ConvertSASDate(adorecordset.Fields["modified"].Value.ToString()));
lvi.ImageIndex = GetImageIndexForEntry(adorecordset.Fields["objtype"].Value.ToString());
lvi.Tag = string.Format("{0}.{1}.{2}.{3}", lib, cat, lvi.Text, adorecordset.Fields["objtype"].Value.ToString());
lvMembers.Items.Add(lvi);
adorecordset.MoveNext();
}
}
catch {}
finally
{
adoconnect.Close();
}
}
lvMembers.EndUpdate();
Cursor.Current = c;
UpdateToolbar();
}
// read list of catalogs for the given library (in the tree node)
private void PopulateCatalogs(TreeNode tn)
{
Cursor c = Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
tvLibsCats.BeginUpdate();
if (tn != null)
{
SAS.Workspace ws = null;
try
{
ws = consumer.Workspace(currentServer) as SAS.Workspace;
}
catch (Exception ex)
{
throw new System.Exception("ISASTaskConsumer.Workspace is not usable!", ex);
}
if (currentServer.Length > 0 && ws != null)
{
// use the SAS IOM OLEDB provider to read data from the SAS workspace
ADODB.Recordset adorecordset = new ADODB.RecordsetClass();
ADODB.Connection adoconnect = new ADODB.ConnectionClass();
try
{
adoconnect.Open("Provider=sas.iomprovider.1; SAS Workspace ID=" + ws.UniqueIdentifier, "", "", 0);
// use the SASHELP.VMEMBER view to get names of all of the catalogs in the specified library
string selectclause = "select memname from sashelp.vmember where libname='" + tn.Text + "' and memtype in ('CATALOG')";
adorecordset.Open(selectclause, adoconnect, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, (int)ADODB.CommandTypeEnum.adCmdText);
while (!adorecordset.EOF)
{
TreeNode cat = tn.Nodes.Add(adorecordset.Fields["memname"].Value.ToString());
cat.ImageIndex = (int)CatImages.Catalog;
cat.SelectedImageIndex = (int)CatImages.Catalog;
cat.Tag = "CATALOG";
adorecordset.MoveNext();
}
}
catch {}
finally
{
adoconnect.Close();
}
}
}
tvLibsCats.EndUpdate();
Cursor.Current = c;
UpdateToolbar();
}
static void Main(string[] args)
{
//先引用SAS IOM、SASObjectManager
//OF SD WS OK
SASObjectManager.ObjectFactoryClass sasOF = new SASObjectManager.ObjectFactoryClass();
SASObjectManager.ServerDefClass sasSD = new SASObjectManager.ServerDefClass();
sasSD.MachineDNSName = "localhost";
sasSD.Protocol = SASObjectManager.Protocols.ProtocolCom;
SAS.Workspace sasWS = (SAS.Workspace)sasOF.CreateObjectByServer("", true, sasSD, null, null);
SASObjectManager.ObjectKeeperClass sasOK = new SASObjectManager.ObjectKeeperClass();
sasOK.AddObject(1, "mySAS", sasWS);
//SAS.Libref libRef = ws.DataService.AssignLibref("fin", "", @"d:\fin\sas\data\", "");
SAS.LanguageService sasLS = sasWS.LanguageService;
string query = @"libname fin 'd:\fin\sas\data\';";
query += "proc sql; create table fin.test1 as select * from fin.test;quit;";
sasLS.Submit(query);
OleDbDataAdapter da = new OleDbDataAdapter("select * from fin.test1 where f3>0",
"Provider=sas.IOMProvider.1;SAS Workspace ID=" + sasWS.UniqueIdentifier + ";");
DataSet ds = new DataSet();
da.Fill(ds, "test");
foreach (DataRow dr in ds.Tables["test"].Rows)
{
Console.WriteLine("{0} {1}", Convert.IsDBNull(dr[0]) ? "" :Convert.ToString(dr[0]), Convert.IsDBNull(dr[0]) ? "" : Convert.ToString(dr[2]));
}
sasOK.RemoveObject(sasWS);//...............
sasWS.Close();
//OleDbConnection cn = new OleDbConnection("Provider=sas.IOMProvider.1; Data Source=_LOCAL_");
//cn.Open();
//OleDbCommand cmd = cn.CreateCommand();
//cmd.CommandType = CommandType.Text;
//cmd.CommandText = @"libname fin 'd:\fin\sas\data'";
//cmd.ExecuteNonQuery();
//cmd.CommandText = @"select * from fin.test";
//OleDbDataReader reader = cmd.ExecuteReader();
//int i = 0;
//while (i++ < 20 && reader.Read())
//{
// Console.WriteLine("{0} {1}", reader.GetValue(0).ToString(), reader.GetValue(2).ToString());
//}
//reader.Close();
//cmd.CommandText = @"insert into fin.test(f1,f2,f3) values('aaaa',1111,222)";
//cmd.ExecuteNonQuery();
//cn.Close();
Console.ReadKey();
}
// download the catalog entry contents and view in an editor
private void ViewInEditor(string catEntry)
{
SAS.FileService fs = null;
SAS.IFileref fr = null;
SAS.Workspace ws = null;
SAS.TextStream ts = null;
string fileref;
StringBuilder sb = new StringBuilder();
try
{
// use SAS workspace and fileservice to download the file
ws = consumer.Workspace(currentServer) as SAS.Workspace;
fs = ws.FileService;
// using the FILENAME CATALOG access method
fr = fs.AssignFileref("", "CATALOG", catEntry, "", out fileref);
ts = fr.OpenTextStream(SAS.StreamOpenMode.StreamOpenModeForReading, 16500);
ts.Separator = Environment.NewLine;
// downloading catalog entry contents to local temp file
int lines = 1;
Array truncLines, readLines;
// iterate through until all lines are read in
while (lines > 0)
{
ts.ReadLines(100, out truncLines, out readLines);
lines = readLines.GetLength(0);
for (int i = 0; i < lines; i++)
{
sb.AppendLine(readLines.GetValue(i).ToString());
}
}
ts.Close();
fs.DeassignFileref(fileref);
// show the text in a modal dialog
SAS.Tasks.Toolkit.Controls.SASCodeViewDialog dlg = new SAS.Tasks.Toolkit.Controls.SASCodeViewDialog(
Control.FromHandle(this.Handle),
catEntry, sb.ToString());
dlg.ShowDialog();
}
catch (Exception ex)
{
MessageBox.Show(string.Format(Translate.MessageCatalogReadError, catEntry, ex.Message), Translate.TitleError);
}
finally
{
ts = null;
fr = null;
fs = null;
ws = null;
}
}
// download the catalog entry contents and view in an editor
private void ViewInEditor(string catEntry)
{
SAS.FileService fs = null;
SAS.Fileref fr = null;
SAS.Workspace ws = null;
SAS.TextStream ts = null;
string fileref;
try
{
// use SAS workspace and fileservice to download the file
ws = consumer.Workspace(currentServer) as SAS.Workspace;
fs = ws.FileService;
// using the FILENAME CATALOG access method
fr = fs.AssignFileref("", "CATALOG", catEntry, "", out fileref);
ts = fr.OpenTextStream(SAS.StreamOpenMode.StreamOpenModeForReading, 16500);
ts.Separator = Environment.NewLine;
string tempFn = System.IO.Path.GetTempFileName();
// downloading catalog entry contents to local temp file
using (System.IO.StreamWriter sw = System.IO.File.CreateText(tempFn))
{
int lines = 1;
Array truncLines, readLines;
// iterate through until all lines are read in
while (lines > 0)
{
ts.ReadLines(100, out truncLines, out readLines);
lines = readLines.GetLength(0);
for (int i = 0; i < lines; i++)
{
sw.WriteLine(readLines.GetValue(i));
}
}
sw.Close();
}
EEFileViewer dlg = new EEFileViewer(tempFn);
dlg.Text = catEntry.ToUpper();
dlg.Icon = new Icon(typeof(Global), "icons.entry.ico");
// show modal dialog with editor
dlg.ShowDialog();
// clean up
System.IO.File.Delete(tempFn);
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Cannot open the catalog entry {0}. Reason: {1}", catEntry, ex.Message));
}
}
// download the catalog entry contents and view in an editor
override public void ReadEntry(ISASTaskConsumer3 consumer, string serverName, string catEntry)
{
Consumer = consumer;
SAS.FileService fs = null;
SAS.IFileref fr = null;
SAS.Workspace ws = null;
SAS.TextStream ts = null;
string fileref;
try
{
// use SAS workspace and fileservice to download the file
ws = consumer.Workspace(serverName) as SAS.Workspace;
fs = ws.FileService;
// using the FILENAME CATALOG access method
fr = fs.AssignFileref("", "CATALOG", catEntry, "", out fileref);
ts = fr.OpenTextStream(SAS.StreamOpenMode.StreamOpenModeForReading, 16500);
ts.Separator = Environment.NewLine;
StringBuilder sb = new StringBuilder();
// downloading catalog entry contents
int lines = 1;
Array truncLines, readLines;
// iterate through until all lines are read in
while (lines > 0)
{
ts.ReadLines(100, out truncLines, out readLines);
lines = readLines.GetLength(0);
for (int i = 0; i < lines; i++)
{
sb.AppendLine(readLines.GetValue(i).ToString());
}
}
txtEntry.Text = sb.ToString();
// cleanup
ts.Close();
fs.DeassignFileref(fr.FilerefName);
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Cannot open the catalog entry {0}. Reason: {1}", catEntry, ex.Message));
}
}
// download the catalog entry contents and view in an editor
override public void ReadEntry(ISASTaskConsumer3 consumer, string serverName, string catEntry)
{
SAS.FileService fs = null;
SAS.IFileref fr = null;
SAS.Workspace ws = null;
SAS.BinaryStream bs = null;
string fileref;
try
{
// use SAS workspace and fileservice to download the file
ws = consumer.Workspace(serverName) as SAS.Workspace;
fs = ws.FileService;
// using the FILENAME CATALOG access method
fr = fs.AssignFileref("", "CATALOG", catEntry, "", out fileref);
bs = fr.OpenBinaryStream(StreamOpenMode.StreamOpenModeForReading);
using (MemoryStream ms = new MemoryStream())
{
// downloading catalog entry contents
int bytes = 1;
Array buffer;
// iterate through until all lines are read in
while (bytes > 0)
{
bs.Read(32767, out buffer);
bytes = buffer.GetLength(0);
for (int i = 0; i < bytes; i++)
{
ms.WriteByte((byte)buffer.GetValue(0));
}
}
ms.Close();
BitmapImage b = new BitmapImage();
b.BeginInit();
b.StreamSource = ms;
b.EndInit();
imgContent.Source = b;
}
}
catch (Exception ex)
{
MessageBox.Show(string.Format("Cannot open the catalog entry {0}. Reason: {1}", catEntry, ex.Message));
}
}
private string GetTextFromFileref(string fileref)
{
SAS.FileService fs = null;
SAS.IFileref fr = null;
SAS.Workspace ws = null;
SAS.TextStream ts = null;
StringBuilder sb = new StringBuilder();
try
{
// use SAS workspace and fileservice to download the file
ws = consumer.Workspace(currentServer) as SAS.Workspace;
fs = ws.FileService;
fr = fs.UseFileref(fileref);
ts = fr.OpenTextStream(SAS.StreamOpenMode.StreamOpenModeForReading, 16500);
ts.Separator = Environment.NewLine;
// downloading catalog entry contents to local temp file
int lines = 1;
Array truncLines, readLines;
// iterate through until all lines are read in
while (lines > 0)
{
ts.ReadLines(100, out truncLines, out readLines);
lines = readLines.GetLength(0);
for (int i = 0; i < lines; i++)
{
sb.AppendLine(readLines.GetValue(i).ToString());
}
}
ts.Close();
fs.DeassignFileref(fileref);
}
catch (Exception)
{
}
finally
{
ts = null;
fr = null;
fs = null;
ws = null;
}
return(sb.ToString());
}
/// <summary>
/// Connect to a SAS Workspace
/// </summary>
public void Connect()
{
if (_workspace != null)
try
{
Close();
}
catch { }
finally
{
_workspace = null;
}
if (!UseLocal)
{
// Connect using the IOM Bridge (TCP) for remote server
SASObjectManager.IObjectFactory2 obObjectFactory =
new SASObjectManager.ObjectFactoryMulti2();
SASObjectManager.ServerDef obServer =
new SASObjectManager.ServerDef();
obServer.MachineDNSName = Host;
obServer.Protocol = SASObjectManager.Protocols.ProtocolBridge;
obServer.Port = Convert.ToInt32(Port);
obServer.ClassIdentifier = "440196d4-90f0-11d0-9f41-00a024bb830c";
// handle the case where there is no UserID or PW, and try IWA
// Doc on integrated Windows auth in SAS: http://bit.ly/14jAF7X
if (string.IsNullOrEmpty(UserId))
{
obServer.BridgeSecurityPackage = "Negotiate";
}
_workspace = (SAS.Workspace)obObjectFactory.CreateObjectByServer(
Name, true,
obServer,
// if trying IWA, pass null in
// otherwise try supplied credentials
string.IsNullOrEmpty(UserId) ? null : UserId,
string.IsNullOrEmpty(Password) ? null : Password);
objectKeeper.AddObject(1, Name, _workspace);
}
else
{
// Connect using COM protocol, locally installed SAS only
SASObjectManager.IObjectFactory2 obObjectFactory = new SASObjectManager.ObjectFactoryMulti2();
SASObjectManager.ServerDef obServer = new SASObjectManager.ServerDef();
obServer.MachineDNSName = "localhost";
obServer.Protocol = SASObjectManager.Protocols.ProtocolCom;
obServer.Port = 0;
_workspace = (SAS.Workspace)obObjectFactory.CreateObjectByServer(Name, true, obServer, null, null);
objectKeeper.AddObject(1, Name, _workspace);
}
}
/// <summary>
/// Close the Workspace if connected
/// </summary>
public void Close()
{
if (IsConnected) _workspace.Close();
// clear out the ObjectKeeper
objectKeeper.RemoveAllObjects();
_workspace = null;
}