DDQ/DataAccess.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Text.Json;

using MySql.Data.MySqlClient;

namespace ddq
{
    public class DataAccess
    {
        private static String connectionString = ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString;


        /// <summary>
        /// 取基金基本信息
        /// </summary>
        /// <param name="fundId"></param>
        /// <returns></returns>
        public static DataTable Get_fund_info(string fundIds, int? userId)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_tamp_get_fund_info", connection);

            MySqlParameter i_fundIds = new MySqlParameter("i_fund_ids", fundIds);
            MySqlParameter i_userId = new MySqlParameter("i_userid", userId);

            ad.SelectCommand.Parameters.Add(i_fundIds);
            ad.SelectCommand.Parameters.Add(i_userId);
            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;

        }


        /// <summary>
        /// 根据公司搜索基金
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="pageNum"></param>
        /// <param name="pageSize"</param>
        /// <returns></returns>
        public static DataTable Get_fund_list_by_company(string companyId, int pageNum, int pageSize)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_tamp_get_fund_list_of_companay", connection);

            MySqlParameter i_company_id = new MySqlParameter("i_company_id", companyId);
            MySqlParameter i_page_num = new MySqlParameter("i_page_num", pageNum);
            MySqlParameter i_page_size = new MySqlParameter("i_page_size", pageSize);

            ad.SelectCommand.Parameters.Add(i_company_id);
            ad.SelectCommand.Parameters.Add(i_page_num);
            ad.SelectCommand.Parameters.Add(i_page_size);
            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;

        }

        /// <summary>
        /// 从 mfdb 里取NAV
        /// </summary>
        /// <param name="fundId"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public static DataTable Get_Nav(string fundId, DateTime? startDate, DateTime? endDate)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_get_nav", connection);

            MySqlParameter i_fund_id = new MySqlParameter("i_fund_id", fundId);
            MySqlParameter i_start_date = new MySqlParameter("i_start_date", startDate);
            MySqlParameter i_end_date = new MySqlParameter("i_end_date", endDate);

            ad.SelectCommand.Parameters.Add(i_fund_id);
            ad.SelectCommand.Parameters.Add(i_start_date);
            ad.SelectCommand.Parameters.Add(i_end_date);

            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;
        }

        /// <summary>
        /// 取基金指数，benchmark_type: 0-主基准、1-分类平均
        /// </summary>
        /// <param name="fundId"></param>
        /// <returns></returns>
        public static DataTable Get_dm_fund_benchmark(string fundId)
        {

            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_get_dm_fund_benchmark", connection);

            MySqlParameter i_fund_id = new MySqlParameter("i_fund_id", fundId);

            ad.SelectCommand.Parameters.Add(i_fund_id);

            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;
        }


        public static DataTable Get_dd_fund_info(string fundId, DateTime? effectiveDate, int isLatest)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_dd_get_fund_info", connection);

            MySqlParameter i_fund_id = new MySqlParameter("i_fund_id", fundId);
            MySqlParameter i_effective_date = new MySqlParameter("i_effective_date", effectiveDate);
            MySqlParameter i_is_latest = new MySqlParameter("i_is_latest", isLatest);

            ad.SelectCommand.Parameters.Add(i_fund_id);
            ad.SelectCommand.Parameters.Add(i_effective_date);
            ad.SelectCommand.Parameters.Add(i_is_latest);
            ad.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable dt = new DataTable();
            ad.Fill(dt);

            connection.Close();

            return dt;

        }


        public static int Set_dd_fund_info(string fundId, DateTime effectiveDate, string info, int status, int isValid, int userId)
        {
            int ret = -1;

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {

                connection.Open();

                MySqlCommand cmd = new MySqlCommand("sp_dd_set_fund_info", connection);

                MySqlParameter i_fund_id = new MySqlParameter("i_fund_id", fundId);
                MySqlParameter i_effective_date = new MySqlParameter("i_effective_date", effectiveDate);
                MySqlParameter i_info = new MySqlParameter("i_info", info);
                MySqlParameter i_status = new MySqlParameter("i_status", status);
                MySqlParameter i_isvalid = new MySqlParameter("i_isvalid", isValid);
                MySqlParameter i_userid = new MySqlParameter("i_userid", userId);

                cmd.Parameters.Add(i_fund_id);
                cmd.Parameters.Add(i_effective_date);
                cmd.Parameters.Add(i_info);
                cmd.Parameters.Add(i_status);
                cmd.Parameters.Add(i_isvalid);
                cmd.Parameters.Add(i_userid);

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Connection = connection;
                cmd.ExecuteNonQuery();

                ret = 1;
            }

            return ret;
        }

    }
}