Use C # To read the dbf quote File

Source: Internet
Author: User

For historical reasons, the Shanghai Stock Exchange and Shenzhen Stock Exchange in China use dbf documents to distribute market data. For details about the satellite offer system, refer: http://maltig.itpub.net/post/12165/195151 this blog on the securities company information written in the article is still quite good. Shanghai Stock Exchange uses the show2003.dbf file, while Shenzhen Stock Exchange uses SJSHQ. DBF, which can be opened directly using Visual FoxPro to view its content. Next, let's talk about how to use C # to read the data.

C # is used to read dbf Files. It is recommended to use ole db for reading (ODBC is not recommended for Microsoft). First, download and install Microsoft ole db Provider for Visual FoxPro 9.0, next, you can use C # To operate ole db. The connection string is:

Provider = vfpoledb; Data Source = C: \ vfp; Collating Sequence = machine;

Here C: \ vfp is a folder path, and the dbf file to be read is under this folder. Of course, if dbf is in a shared network location, you can also use the network folder path. If you want to read all the data from the Shenzhen Stock Exchange and return a DataSet, the corresponding function is:

 

Public static DataSet ReadSJSHQ ()
{
String strConn = @ "Provider = vfpoledb; Data Source = C: \ vfp; Collating Sequence = machine ;";
Using (OleDbConnection myConnection = new OleDbConnection (strConn ))
{
OleDbDataAdapter adpt = new OleDbDataAdapter ("select * from SJSHQ. DBF", myConnection );
DataSet mySet = new DataSet ();
Adpt. Fill (mySet );
MyConnection. Close ();
Return mySet;
}
}

 

This function can read all the data from the Shenzhen Stock Exchange. If you want to read the data from the Shanghai Stock Exchange, it will not be so simple, although you can use this method to change the query to select * from show2003.dbf, you can also read and return a dataset. However, the returned dataset is incomplete, and the first 200 rows of data are not read. If you use Visual FoxPro to open the show2003.dbf file, you can view all the data content. The reason why the first 200 rows are not returned is that these rows are deleted in the dbf file. Therefore, if you want to read all data rows, you need to set the currently read command to remove the delete tag. For example, to read all S1 columns in the dbf File of Shanghai Stock Exchange, the corresponding code should be:

 

Public static DataSet ReadShow2003S1 ()
{
String strConn = @ "Provider = vfpoledb; Data Source = C: \ vfp; Collating Sequence = machine ;";
Using (OleDbConnection myConnection = new OleDbConnection (strConn ))
{
MyConnection. Open ();
OleDbCommand cmd = new OleDbCommand ();
Cmd. Connection = myConnection;
Cmd. CommandText = "set deleted off"; // remove the delete tag to retrieve all records.
Cmd. CommandType = CommandType. Text;
Cmd. ExecuteNonQuery ();
OleDbDataAdapter adpt = new OleDbDataAdapter ("select s1 from show2003.dbf", myConnection );
DataSet mySet = new DataSet ();
Adpt. Fill (mySet );
MyConnection. Close ();
Return mySet;
}
}

 

If we want to obtain the data of all rows and columns in show2003, the SQL command "select * from show2003.dbf" will throw an exception:

The provider cannot determine the Decimal value. For example, if this row is just created, the default value of the Decimal column is not provided, and the user has not set a new Decimal value.

When you do not read the rows with the delete tag, no error is reported. This is because of some column values in the previous row. Use Visual Foxpro to open the show2003.dbf file, we can see that the first row S1 is 000000 of the Data row, and its column S6 is the date of the current dbf file data generation, for example, my current S6 value is 20101209, the data in this column is defined as "numeric, 8 in width, and 3 in decimal places". It is said to be a design Bug. For details, refer to: Numeric. In fact, the first line needs special processing, because the first line is not the actual data. The first line mainly describes the date and time of the file, all I use is a simple and crude method. I use two DataSet to return the data. The specific function code is:

 

Public static DataSet ReadShow2003 (DataSet mySet0)
{
String strConn = @ "Provider = vfpoledb; Data Source = C: \ vfp; Collating Sequence = machine ;";
Using (OleDbConnection myConnection = new OleDbConnection (strConn ))
{
MyConnection. Open ();
OleDbCommand cmd = new OleDbCommand ();
Cmd. Connection = myConnection;
Cmd. CommandText = "set deleted off"; // remove the delete tag to retrieve all records.
Cmd. CommandType = CommandType. Text;
Cmd. ExecuteNonQuery ();
// Read the first line separately
OleDbDataAdapter adpt0 = new OleDbDataAdapter ("SELECT s1, s2, s3, s4, s5, str (s6) as s6, s11, s13, s15, s17 from show2003 where s1 = \ "000000 \" ", myConnection); // only these fields have values
Adpt0.Fill (mySet0 );
// Read other rows
OleDbDataAdapter adpt = new OleDbDataAdapter ("SELECT * from show2003 where s1 <> \" 000000 \ "", myConnection );
DataSet mySet = new DataSet ();
Adpt. Fill (mySet );

MyConnection. Close ();
Return mySet;
}
}

 

So far, we have read the quote files of the Shenzhen Stock Exchange and Shanghai Stock Exchange. Next we will perform other logic processing ~~~~~~

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.