【C#】28. 操作Wind Reference Data

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Diagnostics;

namespace TestWindReferenceData
{
    class Program
    {
        static void Main(string[] args)
        {
            //成员列表
            string strconn;
            string strCmd1,strCmd2;
            SqlConnection Sconn;
            SqlCommand Scmd;
            SqlDataReader reader;

            //读取数据库连接信息
            strconn = File.ReadAllText(@"E:\长江证券实习\底层数据库\连接信息.txt",Encoding.Default);
            Console.WriteLine("登录信息：{0}",strconn);
            //读取命令：strCmd1
            strCmd1 = File.ReadAllText(@"E:\长江证券实习\底层数据库\SQL命令.txt",Encoding.Default);    //预处理命令
            //建立连接实例
            Sconn = new SqlConnection(strconn);
            Sconn.Open();
            Console.WriteLine("连接状态：{0}", Sconn.State);
            //载入命令
            Scmd = Sconn.CreateCommand();
            Scmd.CommandText = strCmd1 + "SELECT * FROM ##Trading_Dates" ;  //输出日期
            //执行并且读取查询结果：把日期存储到CSV中
            reader = Scmd.ExecuteReader();
            DataTable dt = new DataTable("Trading_Dates");
            dt.Load(reader);
            MyFunctions.DataTable2CSV(dt, @"E:\长江证券实习\8月21日\" + dt.TableName + @".csv");
            reader.Close();
            //CSV转换为数组
            List<string[]> listStrDates = MyFunctions.CSV2List(@"E:\长江证券实习\8月21日\" + dt.TableName + @".csv");
             DateTime curDate, curDateOneYearBefore ;
            for (int i = 1; i < listStrDates.Count; i++)
            {
                curDate = DateTime.ParseExact(listStrDates[i][0].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                curDateOneYearBefore = curDate.AddYears(-1);
                //读取命令：strCmd2
                strCmd2 = File.ReadAllText(@"E:\长江证券实习\底层数据库\SQL命令2.txt", Encoding.Default);    //预处理命令
                strCmd2 = strCmd2.Replace("20100129", curDate.ToString("yyyyMMdd"));    //替换1
                strCmd2 = strCmd2.Replace("20090129", curDateOneYearBefore.ToString("yyyyMMdd"));   //替换2
                Scmd.CommandText =  strCmd2;
                reader = Scmd.ExecuteReader();
                dt = new DataTable(curDate.ToString("yyyyMMdd"));
                dt.Load(reader);
                MyFunctions.DataTable2CSV(dt, @"E:\长江证券实习\8月21日\" + dt.TableName + @".csv");
                reader.Close();
            }
            


            生成交易日数组
            //DateTime[] DTs = new DateTime[67];
            //int n = 0;
            //while (reader.Read())
            //{
            //    for (int i = 0; i < reader.FieldCount; i++)
            //    {
            //        DTs[n] = DateTime.ParseExact(reader.GetValue(i).ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
            //    }
            //    n += 1;
            //}
            //reader.Close();

            //执行查找SQL命令
            //foreach (DateTime curDate in DTs)
            {
                

                //StringBuilder sbcmd2 = new StringBuilder();
                //sbcmd2.Append(File.ReadAllText(@"E:\长江证券实习\底层数据库\SQL命令2.txt", System.Text.Encoding.GetEncoding("GB2312")));
                //sbcmd = new StringBuilder("");
                //sbcmd.Append(MyFunctions.InputCurDate(curDate, curDateOneYearBefore).ToString());
                //string temp = sbcmd2.ToString().Replace("20100101", curDate.ToString("yyyyMMdd"));
                //temp = temp.Replace("20090101", curDateOneYearBefore.ToString("yyyyMMdd"));
                //string temp = sbcmd2.ToString();
                //Console.WriteLine(temp);
                
                //Console.WriteLine("查询操作成功！");
                //Console.WriteLine("受影响的行数：{0}", reader.RecordsAffected);
               
            }

            //FileStream fs = new FileStream(@"E:\长江证券实习\8月21日\Result.csv", System.IO.FileMode.Create, System.IO.FileAccess.Write);
            //StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.Default);
            //StringBuilder data = new StringBuilder();
            //for (int i = 0; i < 67; i++)
            //{
            //    sbcmd = new StringBuilder();
            //    sbcmd.Append(MyFunctions.InputCurDate(DTs[i], DTs[i].AddYears(-1)).ToString());
            //    scmd.CommandText = sbcmd.ToString();
            //    reader = scmd.ExecuteReader();
            //    DataTable dt = new DataTable(DTs[i].ToString("yyyyMMdd"));
            //    dt.Load(reader);

            //    //写出各行数据
            //    for (int k = 0; k < dt.Rows.Count; k++)
            //    {
            //        //清空数据
            //        data.Length = 0;
            //        for (int j = 0; j < dt.Columns.Count; j++)
            //        {
            //            data.Append(dt.Rows[k][j].ToString());
            //            if (j < dt.Columns.Count - 1)
            //            {
            //                data.Append(",");
            //            }
            //        }
            //        sw.WriteLine(data.ToString());
            //    }
            //    reader.Close();
            //}
            //sw.Close();
            //fs.Close();
            //Console.WriteLine("数据已经成功写入！");

           
            Sconn.Dispose();
            Sconn.Close();
            Console.Read();
        }
    }
}