[Quantproject-developers] QuantDownloader/Downloader DataBaseImporter.cs,NONE,1.1 DataBaseLocator.cs
Brought to you by:
glauco_1
Menu
▾
▴
[Quantproject-developers] QuantDownloader/Downloader DataBaseImporter.cs,NONE,1.1 DataBaseLocator.cs,NONE,1.1 TickerDownloader.cs,NONE,1.1 DataSet1.cs,1.1.1.1,1.2 Downloader.csproj,1.1.1.1,1.2 Main.cs,1.1.1.1,1.2 TestDownloadedData.cs,1.1.1.1,1.2 WebDownloader.cs,1.1.1.1,1.2
|
From: <gla...@us...> - 2003-11-20 22:38:51
|
Update of /cvsroot/quantproject/QuantDownloader/Downloader In directory sc8-pr-cvs1:/tmp/cvs-serv16375/Downloader Modified Files: DataSet1.cs Downloader.csproj Main.cs TestDownloadedData.cs WebDownloader.cs Added Files: DataBaseImporter.cs DataBaseLocator.cs TickerDownloader.cs Log Message: - Added the DataBaseLocator class - The CVS step is avoided now (a DataSet approach is used); the DataBaseImporter class has been added --- NEW FILE: DataBaseImporter.cs --- using System; using System.Data; using System.Data.OleDb; using System.IO; namespace QuantDownloader { /// <summary> /// Summary description for DataBaseImporter. /// </summary> public class DataBaseImporter { private StreamReader streamReader; private OleDbConnection oleDbConnection; public DataBaseImporter( OleDbConnection oleDbConnection , StreamReader streamReader ) { this.streamReader = streamReader; this.oleDbConnection = oleDbConnection; } public void ImportTicker( string ticker ) { string Line; string[] LineIn; string strAccessSelect = "Select * from quotes where 1=2"; OleDbCommand myAccessCommand = new OleDbCommand( strAccessSelect , oleDbConnection ); OleDbDataAdapter myDataAdapter = new OleDbDataAdapter( myAccessCommand ); OleDbCommandBuilder myCB = new OleDbCommandBuilder( myDataAdapter ); //Create Dataset and table DataSet myDataSet = new DataSet(); //Set MissingSchemaAction Property myDataAdapter.MissingSchemaAction = MissingSchemaAction.Add; //Set MissingMappingAction Property myDataAdapter.MissingMappingAction = MissingMappingAction.Passthrough; //Fill data adapter myDataAdapter.Fill( myDataSet , "Data" ); Line = streamReader.ReadLine(); Line = streamReader.ReadLine(); while ( Line != null ) { LineIn=Line.Split(','); DataRow myRow=myDataSet.Tables["Data"].NewRow(); myRow[ "quTicker" ] = ticker; myRow[ "quDate" ]=DateTime.Parse( LineIn[0] ); myRow[ "quOpen" ]=Double.Parse( LineIn[1] ); myRow[ "quHigh" ]=Double.Parse( LineIn[2] ); myRow[ "quLow" ]=Double.Parse( LineIn[3] ); myRow[ "quClose" ]=Double.Parse( LineIn[4] ); myRow[ "quVolume" ]=Double.Parse( LineIn[5] ); myRow[ "quAdjustedClose" ]=Double.Parse( LineIn[6] ); // myRow["date"]=DateTime.Parse(LineIn[0]); // myRow["time"]=DateTime.Parse(LineIn[1]); // myRow["sv1485ri"]=Double.Parse(LineIn[2]); // myRow["sv14856s"]=Double.Parse(LineIn[3]); // myRow["d4461"]=Double.Parse(LineIn[4]); // myRow["d6sf"]=Double.Parse(LineIn[5]); // myRow["d6sdp"]=Double.Parse(LineIn[6]); // myRow["oppai"]=Double.Parse(LineIn[7]); // myRow["oppbi"]=Double.Parse(LineIn[8]); // myRow["opps"]=Double.Parse(LineIn[9]); // myRow["o24hrtf"]=Double.Parse(LineIn[10]); // myRow["oif"]=Double.Parse(LineIn[11]); // myRow["otct"]=Double.Parse(LineIn[12]); // myRow["d1abt"]=Double.Parse(LineIn[13]); // myRow["d1bbt"]=Double.Parse(LineIn[14]); // myRow["d3bt"]=Double.Parse(LineIn[15]); // myRow["d2bt"]=Double.Parse(LineIn[16]); // myRow["d5bt"]=Double.Parse(LineIn[17]); // myRow["d6bt"]=Double.Parse(LineIn[18]); // myRow["cv1480cvpi"]=Double.Parse(LineIn[19]); // myRow["cv1481cvpi"]=Double.Parse(LineIn[20]); myDataSet.Tables["Data"].Rows.Add(myRow); Line = this.streamReader.ReadLine(); } myDataAdapter.Update(myDataSet, "Data"); } } } --- NEW FILE: DataBaseLocator.cs --- using System; using System.Xml; using System.IO; using System.Windows.Forms; namespace QuantDownloader { /// <summary> /// Locate the database /// </summary> public class DataBaseLocator { private string dataBaseType; private StreamReader stream; private XmlTextReader xmlTextReader; private string path; public DataBaseLocator(string fileExtension) { try { this.dataBaseType = fileExtension; //it looks for the file in the application directory if (!File.Exists("DataBase.xml")) createXmlFile(); this.stream = new StreamReader(Application.ExecutablePath.Substring(0, Application.ExecutablePath.LastIndexOf('\\')) + @"\DataBase.xml"); this.xmlTextReader = new XmlTextReader(this.stream); while(xmlTextReader.Read()) { if (xmlTextReader.LocalName.ToString() == this.dataBaseType) { //gets full path of the file that contains the database this.Path = xmlTextReader.GetAttribute(0); } } xmlTextReader.Close(); stream.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); xmlTextReader.Close(); stream.Close(); } } public string Path { get { return path; } set { path = value; } } /// <summary> /// create xmlFile in the application directory /// where to store name and path for the mdb file /// selected by the user /// </summary> private void createXmlFile() { string path; string xmlPath; string selectionByUser; selectionByUser = selectDataBase(); if (selectionByUser == "") { MessageBox.Show("With no selection application won't run!"); Application.Exit(); } else { path = selectionByUser; xmlPath = Application.ExecutablePath.Substring(0, Application.ExecutablePath.LastIndexOf('\\')) + @"\DataBase.xml"; XmlTextWriter xmlTextWriter = new XmlTextWriter(xmlPath, null); xmlTextWriter.Formatting = Formatting.Indented; xmlTextWriter.WriteStartDocument(); xmlTextWriter.WriteStartElement("FILES"); xmlTextWriter.WriteStartElement("MDB"); xmlTextWriter.WriteAttributeString("fullpath",path); xmlTextWriter.WriteEndElement(); xmlTextWriter.WriteEndElement(); xmlTextWriter.WriteEndDocument(); xmlTextWriter.Flush(); xmlTextWriter.Close(); } } private string selectDataBase() { string fileName = ""; switch (this.dataBaseType) { case "MDB": fileName = "QuantProject.mdb"; break; } OpenFileDialog openFileDialog = new OpenFileDialog(); openFileDialog.Title = "Select " + fileName + " please ..."; openFileDialog.Multiselect = false; openFileDialog.CheckFileExists = true; openFileDialog.Filter = fileName + "|" + fileName; openFileDialog.ShowDialog(); return openFileDialog.FileName; } } } --- NEW FILE: TickerDownloader.cs --- using System; using System.Data; using System.IO; using System.Net; using System.Windows.Forms; namespace QuantDownloader { /// <summary> /// Summary description for TickerDownloader. /// </summary> public class TickerDownloader { private System.Data.OleDb.OleDbConnection oleDbConnection1; private WebDownloader p_myForm; private DataRow p_currentDataTickerRow; private string p_quTicker; private int p_numRows; private DateTime startDate = new DateTime( 2000 , 1 , 1 ); private DateTime endDate = DateTime.Today; private int endDay = DateTime.Now.Day; private int endMonth = DateTime.Now.Month; private int endYear = DateTime.Now.Year; public TickerDownloader( WebDownloader myForm, DataRow currentDataTickerRow, string quTicker , int numRows ) { p_myForm = myForm; p_currentDataTickerRow = currentDataTickerRow; p_quTicker = quTicker; p_numRows = numRows; this.oleDbConnection1 = myForm.OleDbConnection1; } private void addTickerTo_gridDataSet() { DataRow newRow = p_myForm.DsTickerCurrentlyDownloaded.Tables[ "Tickers" ].NewRow(); newRow[ "tiTicker" ] = p_quTicker; newRow[ "currentState" ] = "Start"; //lock( p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Rows ) //{ try { //MessageBox.Show( (p_myForm == null).ToString() ); //MessageBox.Show( (p_myForm.dsTickerCurrentlyDownloaded == null).ToString() ); //MessageBox.Show( (p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ] == null).ToString() ); //MessageBox.Show( (p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Rows == null).ToString() ); //MessageBox.Show( (newRow == null).ToString() ); p_myForm.DsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Rows.Add( newRow ); } catch (Exception ex) { MessageBox.Show( ex.ToString() ); } //} p_myForm.dataGrid1.Refresh(); } private void removeTickerFrom_gridDataSet() { DataRow[] myRows = p_myForm.DsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Select( "tiTicker='" + p_quTicker + "'" ); p_myForm.DsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Rows.Remove( myRows[ 0 ] ); DataRow newRow = p_myForm.DsTickerCurrentlyDownloaded.Tables[ "Tickers" ].NewRow(); newRow[ "tiTicker" ] = p_quTicker; newRow[ "currentState" ] = "Start"; p_myForm.DsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Rows.Add( newRow ); p_myForm.dataGrid1.Refresh(); } private void updateCurrentStatus( string newState ) { lock( p_myForm.DsTickerCurrentlyDownloaded.Tables[ "Tickers" ] ) { DataRow[] myRows = p_myForm.DsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Select( "tiTicker='" + p_quTicker + "'" ); myRows[ 0 ][ "currentState" ] = newState; p_myForm.dataGrid1.Refresh(); } } private void addTickerToFaultyTickers() { System.Data.OleDb.OleDbCommand odc = new System.Data.OleDb.OleDbCommand(); odc.CommandText = "insert into faultyTickers ( ftTicker , ftDateTime ) " + "values ( '" + p_quTicker + "' , #" + DateTime.Now.Month + "/" + DateTime.Now.Day + "/" + DateTime.Now.Year + " " + DateTime.Now.Hour + "." + DateTime.Now.Minute + "." + DateTime.Now.Second + "# )"; odc.Connection = this.oleDbConnection1; odc.ExecuteNonQuery(); } private void writeFile_tickerCsv_forNextTimeFrame( DateTime currBeginDate , DateTime currEndDate ) { int a = currBeginDate.Month - 1; int b = currBeginDate.Day; int c = currBeginDate.Year; int d = currEndDate.Month - 1; int e = currEndDate.Day; int f = currEndDate.Year; int numTrials = 1; //updateCurrentStatus( " 1 " ); while (numTrials < 5) { this.p_myForm.Refresh(); try { HttpWebRequest Req = (HttpWebRequest)WebRequest.Create("http:" + "//table.finance.yahoo.com/table.csv?a=" + a + "&b=" + b + "&c=" + c +"&d=" + d + "&e=" + e + "&f=" + f + "&s=" + p_quTicker + "&y=0&g=d&ignore=.csv"); Req.Method = "GET"; Req.Timeout = 10000; HttpWebResponse hwr = (HttpWebResponse)Req.GetResponse(); //updateCurrentStatus( " 2 " ); Stream strm = hwr.GetResponseStream(); //updateCurrentStatus( " 3 " ); StreamReader sr = new StreamReader(strm); // StreamWriter sw=new StreamWriter( "C:\\Documents and Settings\\Glauco\\My Documents\\Visual Studio Projects\\QuantProject\\csvFiles\\" + p_quTicker + ".csv"); // sw.Write(myString); // sw.Close(); DataBaseImporter dataBaseImporter = new DataBaseImporter( this.oleDbConnection1 , sr ); dataBaseImporter.ImportTicker( p_quTicker ); sr.Close(); strm.Close(); // import_tickerCsv_into_quotesCsv(); updateCurrentStatus( d + "/" + e + "/" + f ); numTrials = 6 ; //updateCurrentStatus( " scritto file! " ); } catch (Exception exception) { MessageBox.Show( exception.ToString() ); updateCurrentStatus( "Tentativo: " + numTrials ); numTrials++; if (numTrials > 5) addTickerToFaultyTickers(); } } } private void writeFile_tickerCsv() { DateTime currBeginDate = new DateTime(); currBeginDate = startDate; while ( currBeginDate < endDate ) { DateTime currEndDate = new DateTime(); if ( DateTime.Compare( DateTime.Today , currBeginDate.AddDays( 200 ) ) < 0 ) currEndDate = DateTime.Today; else currEndDate = currBeginDate.AddDays( 200 ); writeFile_tickerCsv_forNextTimeFrame( currBeginDate , currEndDate ); currBeginDate = currEndDate.AddDays( 1 ); } } private void import_tickerCsv_into_quotesCsv() { string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "C:\\Documents and Settings\\Glauco\\My Documents\\Visual Studio Projects\\QuantProject\\csvFiles\\" + ";Extended Properties=\"Text;HDR=YES;FMT=Delimited\""; System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(sConnectionString); objConn.Open(); System.Data.OleDb.OleDbCommand odCommand = new System.Data.OleDb.OleDbCommand(); odCommand.Connection = objConn; try { odCommand.CommandText = "insert into quotes.csv SELECT '" + p_quTicker + "' as Ticker, * FROM " + p_quTicker + ".csv"; odCommand.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show( ex.ToString() ); } objConn.Close(); } public void downloadTicker() { { addTickerTo_gridDataSet(); writeFile_tickerCsv(); //Monitor.Pulse( p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ] ); } } } } Index: DataSet1.cs =================================================================== RCS file: /cvsroot/quantproject/QuantDownloader/Downloader/DataSet1.cs,v retrieving revision 1.1.1.1 retrieving revision 1.2 diff -C2 -d -r1.1.1.1 -r1.2 *** DataSet1.cs 16 Oct 2003 18:08:28 -0000 1.1.1.1 --- DataSet1.cs 20 Nov 2003 22:38:48 -0000 1.2 *************** *** 9,13 **** //------------------------------------------------------------------------------ ! namespace QuantProject { using System; using System.Data; --- 9,13 ---- //------------------------------------------------------------------------------ ! namespace QuantDownloader { using System; using System.Data; Index: Downloader.csproj =================================================================== RCS file: /cvsroot/quantproject/QuantDownloader/Downloader/Downloader.csproj,v retrieving revision 1.1.1.1 retrieving revision 1.2 diff -C2 -d -r1.1.1.1 -r1.2 *** Downloader.csproj 16 Oct 2003 18:08:34 -0000 1.1.1.1 --- Downloader.csproj 20 Nov 2003 22:38:48 -0000 1.2 *************** *** 93,96 **** --- 93,107 ---- <File RelPath = "AssemblyInfo.cs" + SubType = "Code" + BuildAction = "Compile" + /> + <File + RelPath = "DataBaseImporter.cs" + SubType = "Code" + BuildAction = "Compile" + /> + <File + RelPath = "DataBaseLocator.cs" + SubType = "Code" BuildAction = "Compile" /> *************** *** 128,131 **** --- 139,147 ---- DependentUpon = "TestDownloadedData.cs" BuildAction = "EmbeddedResource" + /> + <File + RelPath = "TickerDownloader.cs" + SubType = "Code" + BuildAction = "Compile" /> <File Index: Main.cs =================================================================== RCS file: /cvsroot/quantproject/QuantDownloader/Downloader/Main.cs,v retrieving revision 1.1.1.1 retrieving revision 1.2 diff -C2 -d -r1.1.1.1 -r1.2 *** Main.cs 16 Oct 2003 18:08:41 -0000 1.1.1.1 --- Main.cs 20 Nov 2003 22:38:48 -0000 1.2 *************** *** 5,9 **** using System.Windows.Forms; ! namespace QuantProject.Principale { /// <summary> --- 5,9 ---- using System.Windows.Forms; ! namespace QuantDownloader { /// <summary> Index: TestDownloadedData.cs =================================================================== RCS file: /cvsroot/quantproject/QuantDownloader/Downloader/TestDownloadedData.cs,v retrieving revision 1.1.1.1 retrieving revision 1.2 diff -C2 -d -r1.1.1.1 -r1.2 *** TestDownloadedData.cs 16 Oct 2003 18:08:58 -0000 1.1.1.1 --- TestDownloadedData.cs 20 Nov 2003 22:38:48 -0000 1.2 *************** *** 5,9 **** using System.Windows.Forms; ! namespace QuantProject.Principale { /// <summary> --- 5,9 ---- using System.Windows.Forms; ! namespace QuantDownloader { /// <summary> Index: WebDownloader.cs =================================================================== RCS file: /cvsroot/quantproject/QuantDownloader/Downloader/WebDownloader.cs,v retrieving revision 1.1.1.1 retrieving revision 1.2 diff -C2 -d -r1.1.1.1 -r1.2 *** WebDownloader.cs 16 Oct 2003 18:09:36 -0000 1.1.1.1 --- WebDownloader.cs 20 Nov 2003 22:38:48 -0000 1.2 *************** *** 10,14 **** using System.Threading; ! namespace QuantProject.Principale { /// <summary> --- 10,14 ---- using System.Threading; ! namespace QuantDownloader { /// <summary> *************** *** 17,22 **** public class WebDownloader : System.Windows.Forms.Form { private System.Windows.Forms.Button button1; - public System.Data.OleDb.OleDbConnection oleDbConnection1; public System.Windows.Forms.DataGrid dataGrid1; private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1; --- 17,28 ---- public class WebDownloader : System.Windows.Forms.Form { + private static DataBaseLocator dataBaseLocator = new DataBaseLocator("MDB"); + private static string mdbPath = dataBaseLocator.Path; + private static string connectionString = + @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" + + mdbPath + + @";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"; + public OleDbConnection OleDbConnection1 = new OleDbConnection( connectionString ); private System.Windows.Forms.Button button1; public System.Windows.Forms.DataGrid dataGrid1; private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1; *************** *** 25,31 **** private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1; private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1; ! private QuantProject.DataSet1 dataSet11; private System.Data.OleDb.OleDbCommand oleDbCommand1; ! public QuantProject.DataSet1 dsTickerCurrentlyDownloaded; /// <summary> /// Required designer variable. --- 31,37 ---- private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1; private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1; ! // private QuantProject.DataSet1 dataSet11; private System.Data.OleDb.OleDbCommand oleDbCommand1; ! public DataSet1 DsTickerCurrentlyDownloaded = new DataSet1(); /// <summary> /// Required designer variable. *************** *** 43,49 **** // TODO: Add any constructor code after InitializeComponent call // ! this.oleDbDataAdapter1.Fill(this.dataSet11); ! this.oleDbConnection1.Open(); ! //this.oleDbConnection1.Close(); } --- 49,55 ---- // TODO: Add any constructor code after InitializeComponent call // ! //this.oleDbDataAdapter1.Fill(this.dataSet11); ! this.OleDbConnection1.Open(); ! //this.OleDbConnection1.Close(); } *************** *** 71,75 **** { this.button1 = new System.Windows.Forms.Button(); - this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); this.dataGrid1 = new System.Windows.Forms.DataGrid(); this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter(); --- 77,80 ---- *************** *** 91,98 **** this.button1.Click += new System.EventHandler(this.button1_Click); // - // oleDbConnection1 - // - this.oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\Glauco\My Documents\Visual Studio Projects\QuantProject\QuantProject.mdb;Mode=Share Deny None;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"; - // // dataGrid1 // --- 96,99 ---- *************** *** 123,127 **** // this.oleDbDeleteCommand1.CommandText = @"DELETE FROM quotes WHERE (quId = ?) AND (quClose = ? OR ? IS NULL AND quClose IS NULL) AND (quDate = ? OR ? IS NULL AND quDate IS NULL) AND (quHigh = ? OR ? IS NULL AND quHigh IS NULL) AND (quLow = ? OR ? IS NULL AND quLow IS NULL) AND (quOpen = ? OR ? IS NULL AND quOpen IS NULL) AND (quTicker = ? OR ? IS NULL AND quTicker IS NULL)"; - this.oleDbDeleteCommand1.Connection = this.oleDbConnection1; this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_quId", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(10)), ((System.Byte)(0)), "quId", System.Data.DataRowVersion.Original, null)); this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_quClose", System.Data.OleDb.OleDbType.Single, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(7)), ((System.Byte)(0)), "quClose", System.Data.DataRowVersion.Original, null)); --- 124,127 ---- *************** *** 142,146 **** this.oleDbInsertCommand1.CommandText = "INSERT INTO quotes (quClose, quDate, quHigh, quLow, quOpen, quTicker, quVolume) V" + "ALUES (?, ?, ?, ?, ?, ?, ?)"; - this.oleDbInsertCommand1.Connection = this.oleDbConnection1; this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("quClose", System.Data.OleDb.OleDbType.Single, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(7)), ((System.Byte)(0)), "quClose", System.Data.DataRowVersion.Current, null)); this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("quDate", System.Data.OleDb.OleDbType.DBDate, 0, "quDate")); --- 142,145 ---- *************** *** 154,163 **** // this.oleDbSelectCommand1.CommandText = "SELECT quClose, quDate, quHigh, quLow, quOpen, quTicker FROM quotes"; - this.oleDbSelectCommand1.Connection = this.oleDbConnection1; // // oleDbUpdateCommand1 // this.oleDbUpdateCommand1.CommandText = @"UPDATE quotes SET quClose = ?, quDate = ?, quHigh = ?, quLow = ?, quOpen = ?, quTicker = ? WHERE (quId = ?) AND (quClose = ? OR ? IS NULL AND quClose IS NULL) AND (quDate = ? OR ? IS NULL AND quDate IS NULL) AND (quHigh = ? OR ? IS NULL AND quHigh IS NULL) AND (quLow = ? OR ? IS NULL AND quLow IS NULL) AND (quOpen = ? OR ? IS NULL AND quOpen IS NULL) AND (quTicker = ? OR ? IS NULL AND quTicker IS NULL)"; - this.oleDbUpdateCommand1.Connection = this.oleDbConnection1; this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("quClose", System.Data.OleDb.OleDbType.Single, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(7)), ((System.Byte)(0)), "quClose", System.Data.DataRowVersion.Current, null)); this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("quDate", System.Data.OleDb.OleDbType.DBDate, 0, "quDate")); --- 153,160 ---- *************** *** 182,189 **** // oleDbCommand1 // ! this.oleDbCommand1.CommandText = "DELETE FROM quotes"; ! this.oleDbCommand1.Connection = this.oleDbConnection1; // ! // Form1 // this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); --- 179,186 ---- // oleDbCommand1 // ! this.oleDbCommand1.CommandText = "DELETE quotes.* FROM quotes INNER JOIN tickers ON quotes.quTicker = tickers.tiTic" + ! "ker"; // ! // WebDownloader // this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); *************** *** 192,196 **** this.dataGrid1, this.button1}); ! this.Name = "Form1"; this.Text = "Form1"; this.Load += new System.EventHandler(this.Form1_Load); --- 189,193 ---- this.dataGrid1, this.button1}); ! this.Name = "WebDownloader"; this.Text = "Form1"; this.Load += new System.EventHandler(this.Form1_Load); *************** *** 256,260 **** private void downloadQuotes_createTickerDataSet( DataSet ds ) { ! System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1=new OleDbDataAdapter( "select * from tickers" , this.oleDbConnection1); oleDbDataAdapter1.Fill(ds); } --- 253,257 ---- private void downloadQuotes_createTickerDataSet( DataSet ds ) { ! System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1=new OleDbDataAdapter( "select * from tickers" , this.OleDbConnection1); oleDbDataAdapter1.Fill(ds); } *************** *** 322,332 **** private void downloadQuotes_withTickerDataSet_create_dsTickerCurrentlyDownloaded( DataTable dt ) { ! if (!this.dsTickerCurrentlyDownloaded.Tables.Contains( "Tickers" )) { ! this.dsTickerCurrentlyDownloaded.Tables.Add( "Tickers" ); ! this.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Columns.Add( new DataColumn( dt.Columns[ "tiTicker" ].ColumnName , dt.Columns[ "tiTicker" ].DataType ) ); ! this.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Columns.Add( "currentState" , System.Type.GetType( "System.String" ) ); ! this.dataGrid1.DataSource = this.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ]; } --- 319,329 ---- private void downloadQuotes_withTickerDataSet_create_dsTickerCurrentlyDownloaded( DataTable dt ) { ! if (!this.DsTickerCurrentlyDownloaded.Tables.Contains( "Tickers" )) { ! this.DsTickerCurrentlyDownloaded.Tables.Add( "Tickers" ); ! this.DsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Columns.Add( new DataColumn( dt.Columns[ "tiTicker" ].ColumnName , dt.Columns[ "tiTicker" ].DataType ) ); ! this.DsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Columns.Add( "currentState" , System.Type.GetType( "System.String" ) ); ! this.dataGrid1.DataSource = this.DsTickerCurrentlyDownloaded.Tables[ "Tickers" ]; } *************** *** 348,352 **** //qd.downloadTicker(); } ! ImportQuotesCsv iqc = new ImportQuotesCsv(); } --- 345,349 ---- //qd.downloadTicker(); } ! //ImportQuotesCsv iqc = new ImportQuotesCsv(); } *************** *** 356,366 **** downloadQuotes_deleteFrom_quotes(); downloadQuotes_createTickerDataSet( ds ); ! //this.oleDbConnection1.Open(); downloadQuotes_withTickerDataSet( ds ); ! this.oleDbConnection1.Close(); } private void button1_Click(object sender, System.EventArgs e) { this.oleDbCommand1.ExecuteNonQuery(); downloadQuotes(); --- 353,364 ---- downloadQuotes_deleteFrom_quotes(); downloadQuotes_createTickerDataSet( ds ); ! //this.OleDbConnection1.Open(); downloadQuotes_withTickerDataSet( ds ); ! this.OleDbConnection1.Close(); } private void button1_Click(object sender, System.EventArgs e) { + oleDbCommand1.Connection = this.OleDbConnection1; this.oleDbCommand1.ExecuteNonQuery(); downloadQuotes(); *************** *** 372,546 **** } } - public class TickerDownloader - { - private WebDownloader p_myForm; - private DataRow p_currentDataTickerRow; - private string p_quTicker; - private int p_numRows; - private DateTime startDate = new DateTime( 2000 , 1 , 1 ); - private DateTime endDate = DateTime.Today; - private int endDay = DateTime.Now.Day; - private int endMonth = DateTime.Now.Month; - private int endYear = DateTime.Now.Year; - public TickerDownloader( WebDownloader myForm, DataRow currentDataTickerRow, string quTicker , int numRows ) - { - p_myForm = myForm; - p_currentDataTickerRow = currentDataTickerRow; - p_quTicker = quTicker; - p_numRows = numRows; - } - private void addTickerTo_gridDataSet() - { - - DataRow newRow = p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ].NewRow(); - newRow[ "tiTicker" ] = p_quTicker; - newRow[ "currentState" ] = "Start"; - //lock( p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Rows ) - //{ - try - { - //MessageBox.Show( (p_myForm == null).ToString() ); - //MessageBox.Show( (p_myForm.dsTickerCurrentlyDownloaded == null).ToString() ); - //MessageBox.Show( (p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ] == null).ToString() ); - //MessageBox.Show( (p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Rows == null).ToString() ); - //MessageBox.Show( (newRow == null).ToString() ); - p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Rows.Add( newRow ); - } - catch (Exception ex) - { - MessageBox.Show( ex.ToString() ); - } - //} - p_myForm.dataGrid1.Refresh(); - } - - private void removeTickerFrom_gridDataSet() - { - DataRow[] myRows = p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Select( "tiTicker='" + p_quTicker + "'" ); - p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Rows.Remove( myRows[ 0 ] ); - DataRow newRow = p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ].NewRow(); - newRow[ "tiTicker" ] = p_quTicker; - newRow[ "currentState" ] = "Start"; - p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Rows.Add( newRow ); - p_myForm.dataGrid1.Refresh(); - } - private void updateCurrentStatus( string newState ) - { - lock( p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ] ) - { - DataRow[] myRows = p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ].Select( "tiTicker='" + p_quTicker + "'" ); - myRows[ 0 ][ "currentState" ] = newState; - p_myForm.dataGrid1.Refresh(); - } - } - - private void addTickerToFaultyTickers() - { - System.Data.OleDb.OleDbCommand odc = new System.Data.OleDb.OleDbCommand(); - odc.CommandText = "insert into faultyTickers ( ftTicker , ftDateTime ) " + - "values ( '" + p_quTicker + "' , #" + - DateTime.Now.Month + "/" + - DateTime.Now.Day + "/" + - DateTime.Now.Year + " " + - DateTime.Now.Hour + "." + - DateTime.Now.Minute + "." + - DateTime.Now.Second + "# )"; - odc.Connection = this.p_myForm.oleDbConnection1; - odc.ExecuteNonQuery(); - } - private void writeFile_tickerCsv_forNextTimeFrame( DateTime currBeginDate , DateTime currEndDate ) - { - int a = currBeginDate.Month - 1; - int b = currBeginDate.Day; - int c = currBeginDate.Year; - int d = currEndDate.Month - 1; - int e = currEndDate.Day; - int f = currEndDate.Year; - int numTrials = 1; - - //updateCurrentStatus( " 1 " ); - while (numTrials < 5) - { - this.p_myForm.Refresh(); - try - { - HttpWebRequest Req = (HttpWebRequest)WebRequest.Create("http:" + "//table.finance.yahoo.com/table.csv?a=" - + a + "&b=" + b + "&c=" + c +"&d=" + d + "&e=" + e + "&f=" + f + "&s=" + p_quTicker + "&y=0&g=d&ignore=.csv"); - - Req.Method = "GET"; - Req.Timeout = 10000; - - HttpWebResponse hwr = (HttpWebResponse)Req.GetResponse(); - - //updateCurrentStatus( " 2 " ); - Stream strm = hwr.GetResponseStream(); - //updateCurrentStatus( " 3 " ); - StreamReader sr = new StreamReader(strm); - StreamWriter sw=new StreamWriter( "C:\\Documents and Settings\\Glauco\\My Documents\\Visual Studio Projects\\QuantProject\\csvFiles\\" + p_quTicker + ".csv"); - string myString = sr.ReadToEnd(); - sw.Write(myString); - sw.Close(); - sr.Close(); - strm.Close(); - import_tickerCsv_into_quotesCsv(); - updateCurrentStatus( d + "/" + e + "/" + f ); - numTrials = 6 ; - //updateCurrentStatus( " scritto file! " ); - } - catch - { - //MessageBox.Show( "Son qui" ); - updateCurrentStatus( "Tentativo: " + numTrials ); - numTrials++; - if (numTrials > 5) - addTickerToFaultyTickers(); - } - } - } - private void writeFile_tickerCsv() - { - DateTime currBeginDate = new DateTime(); - currBeginDate = startDate; - while ( currBeginDate < endDate ) - { - DateTime currEndDate = new DateTime(); - if ( DateTime.Compare( DateTime.Today , currBeginDate.AddDays( 200 ) ) < 0 ) - currEndDate = DateTime.Today; - else - currEndDate = currBeginDate.AddDays( 200 ); - writeFile_tickerCsv_forNextTimeFrame( currBeginDate , currEndDate ); - currBeginDate = currEndDate.AddDays( 1 ); - } - } - private void import_tickerCsv_into_quotesCsv() - { - string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + - "C:\\Documents and Settings\\Glauco\\My Documents\\Visual Studio Projects\\QuantProject\\csvFiles\\" + - ";Extended Properties=\"Text;HDR=YES;FMT=Delimited\""; - - System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(sConnectionString); - objConn.Open(); - System.Data.OleDb.OleDbCommand odCommand = new System.Data.OleDb.OleDbCommand(); - odCommand.Connection = objConn; - try - { - odCommand.CommandText = "insert into quotes.csv SELECT '" + p_quTicker + "' as Ticker, * FROM " + p_quTicker + ".csv"; - odCommand.ExecuteNonQuery(); - } - catch (Exception ex) - { - MessageBox.Show( ex.ToString() ); - } - objConn.Close(); - } - public void downloadTicker() - { - { - addTickerTo_gridDataSet(); - writeFile_tickerCsv(); - //Monitor.Pulse( p_myForm.dsTickerCurrentlyDownloaded.Tables[ "Tickers" ] ); - } - } - } public class ImportQuotesCsv { --- 370,373 ---- |
