// 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; }