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>
        /// <returns></returns>
        public static DataTable Get_fund_list_by_company(string companyId)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_dd_get_fund_list_by_company", connection);

            MySqlParameter i_company_id = new MySqlParameter("i_company_id", companyId);

            ad.SelectCommand.Parameters.Add(i_company_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;
        }


        public static DataTable Get_dd_personnel_info(string personnelId, string companyId)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_dd_get_personnel_info", connection);

            MySqlParameter i_personnel_id = new MySqlParameter("i_personnel_id", personnelId);
            MySqlParameter i_company_id = new MySqlParameter("i_company_id", companyId);

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

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

            connection.Close();

            return dt;

        }

        public static int Set_dd_personnel_info(string personnelId, DateTime effectiveDate, string companyId, 
            string name, int? role, string title, int? companyStartYear, int? industryStartYear, string bio,
            int status, int isValid, int userId, out string personnel_id)
        {
            int ret = -1;

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

                connection.Open();

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

                MySqlParameter i_personnel_id = new MySqlParameter("i_personnel_id", personnelId);
                MySqlParameter i_effective_date = new MySqlParameter("i_effective_date", effectiveDate);
                MySqlParameter i_company_id = new MySqlParameter("i_company_id", companyId);
                MySqlParameter i_name = new MySqlParameter("i_name", name);
                MySqlParameter i_role = new MySqlParameter("i_role", role);
                MySqlParameter i_title = new MySqlParameter("i_title", title);
                MySqlParameter i_company_start_year = new MySqlParameter("i_company_start_year", companyStartYear);
                MySqlParameter i_industry_start_year = new MySqlParameter("i_industry_start_year", industryStartYear);
                MySqlParameter i_bio = new MySqlParameter("i_bio", bio);
                MySqlParameter i_status = new MySqlParameter("i_status", status);
                MySqlParameter i_isvalid = new MySqlParameter("i_isvalid", isValid);
                MySqlParameter i_userid = new MySqlParameter("i_userid", userId);
                
                MySqlParameter o_personnel_id = new MySqlParameter("o_personnel_id", MySqlDbType.String);
                o_personnel_id.Direction = ParameterDirection.Output;


                cmd.Parameters.Add(i_personnel_id);
                cmd.Parameters.Add(i_effective_date);
                cmd.Parameters.Add(i_company_id);
                cmd.Parameters.Add(i_name);
                cmd.Parameters.Add(i_role);
                cmd.Parameters.Add(i_title);
                cmd.Parameters.Add(i_industry_start_year);
                cmd.Parameters.Add(i_company_start_year);
                cmd.Parameters.Add(i_bio);
                cmd.Parameters.Add(i_status);
                cmd.Parameters.Add(i_isvalid);
                cmd.Parameters.Add(i_userid);
                cmd.Parameters.Add(o_personnel_id);

                cmd.CommandType = CommandType.StoredProcedure;

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

                personnel_id = cmd.Parameters["o_personnel_id"].Value.ToString();

                ret = 1;
            }

            return ret;
        }

        public static DataTable Get_dd_company_info(string companyId, DateTime? effectiveDate, int isLatest)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);
            MySqlDataAdapter ad = new MySqlDataAdapter("sp_dd_get_company_info", connection);

            MySqlParameter i_company_id = new MySqlParameter("i_company_id", companyId);
            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_company_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_company_info(string companyId, 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_company_info", connection);

                MySqlParameter i_company_id = new MySqlParameter("i_company_id", companyId);
                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_company_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;
        }

    }
}