提交

王 垚

2022-12-07 ee9f4215d0750a15031bfb89f0f347dadc8c1b70

提交

 Pub_Class/CustomerCls/ClsSqlHelper.cs

@@ -7,23 +7,23 @@

namespace Pub_Class
{
    public class ClsSqlHelper:IDisposable
    public class ClsSqlHelper : IDisposable
    {
        private SqlConnection MainCn;//创建连接对象
        public string strCon;
        SqlTransaction MainTran;
        public string sServer;
        public string sDataBase ;
        public string sDataBase;
        public string sUser;
        public string sPassword;
        //public string sTextBox = "HXErp_Test.ini";
        public string sTextBox = "HXErp.ini";
        //开始事务
        public void  BeginTran()
        public void BeginTran()
        {
            if (!this.CnOpen())
                return;
            MainTran=MainCn.BeginTransaction();
            MainTran = MainCn.BeginTransaction();
        }
        //结束事务
        public void Commit()
@@ -45,7 +45,7 @@
                ClsPub.AppPath = System.Environment.CurrentDirectory;
                //ClsPub.AppPath = @"C:\Windows\System32";
            }
            sServer = ClsIni.ReadIni("Erp", "Server", ClsPub.AppPath + @"\"+sTextBox);
            sServer = ClsIni.ReadIni("Erp", "Server", ClsPub.AppPath + @"\" + sTextBox);
            sDataBase = ClsIni.ReadIni("Erp", "DataBase", ClsPub.AppPath + @"\" + sTextBox);
            sUser = ClsIni.ReadIni("Erp", "UserName", ClsPub.AppPath + @"\" + sTextBox);
            sPassword = ClsIni.ReadIni("Erp", "PassWord", ClsPub.AppPath + @"\" + sTextBox);
@@ -54,13 +54,13 @@

        public bool CheckOpen()
        {
            if (sServer == "" || sServer == "没有找到！" )
            if (sServer == "" || sServer == "没有找到！")
            {
                throw new Exception("错误的服务器名!!" + System.Environment.CurrentDirectory);
            }
            if (sDataBase == "" || sDataBase == "没有找到！")
            {
                throw new Exception("错误的数据库名!!"  +System.Environment.CurrentDirectory);
                throw new Exception("错误的数据库名!!" + System.Environment.CurrentDirectory);
            }
            SqlConnection oCn = new SqlConnection("Data Source=" + sServer + ";DataBase=master;User ID=" + sUser + ";PWD=" + sPassword + ";max pool size=32767");
            try
@@ -78,7 +78,7 @@
            }
        }

        private bool  CnOpen()//OPEN数据库连接
        private bool CnOpen()//OPEN数据库连接
        {
            if (sServer == "" || sServer == "没有找到！")
            {
@@ -88,7 +88,7 @@
            {
                throw new Exception("错误的数据库名!");
            }
            if (MainCn==null)
            if (MainCn == null)
            {
                MainCn = new SqlConnection("Data Source=" + sServer + ";DataBase=" + sDataBase + ";User ID=" + sUser + ";PWD=" + sPassword + ";max pool size=32767");
            }
@@ -117,10 +117,10 @@

        public void CnDispose()//释放资源
        {
            if(MainCn!=null)
            { 
            if (MainCn != null)
            {
                MainCn.Dispose();
                MainCn=null;
                MainCn = null;
            }
        }
        /// <summary>
@@ -133,7 +133,7 @@
        /// <returns>新的 parameter å¯¹è±¡</returns>
        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
        {
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value); 
            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
        }
        /// <summary>
        /// åˆå§‹åŒ–参数值
@@ -192,13 +192,13 @@
        /// <param name="procName"></param>
        /// <param name="sErrMessage"></param>
        /// <returns></returns>
        public void RunProc(string procName,ref string sErr)
        public void RunProc(string procName, ref string sErr)
        {
            if (!this.CnOpen())
            {
                sErr = "连接数据库失败！";
                return;
                

            }
            try
            {
@@ -211,7 +211,7 @@
            }
            catch (Exception e)
            {
                sErr =e.Message;
                sErr = e.Message;
                ClsPub.sErrInfo = e.Message;
                throw (e);
                //return;
@@ -226,7 +226,7 @@
        /// <param name="tbName">表名</param>
        /// <param name="sErrMessage">异常信息</param>
        /// <returns></returns>
        public DataSet RunProcReturn(string procName, SqlParameter[] prams, string tbName )
        public DataSet RunProcReturn(string procName, SqlParameter[] prams, string tbName)
        {
            SqlDataAdapter dap = CreateDataAdaper(procName, prams);
            if (dap == null)
@@ -252,7 +252,7 @@
        /// <param name="tbName">表名</param>
        /// <param name="sErrMessage">异常信息</param>
        /// <returns></returns>
        public DataSet RunProcReturn(string procName, string tbName )
        public DataSet RunProcReturn(string procName, string tbName)
        {
            SqlDataAdapter dap = CreateDataAdaper(procName, null);
            DataSet ds = new DataSet();
@@ -276,7 +276,7 @@
        /// <param name="tbName">表名</param>
        /// <param name="sErrMessage">异常信息</param>
        /// <returns></returns>
        public DataSet RunProcReturn(string procName, string tbName,ref string sErr)   
        public DataSet RunProcReturn(string procName, string tbName, ref string sErr)
        {
            SqlDataAdapter dap = CreateDataAdaper(procName, null);
            DataSet ds = new DataSet();
@@ -366,7 +366,7 @@

        }

        public SqlDataReader RunReader(string sSQL,string tbName,ref string sErr)
        public SqlDataReader RunReader(string sSQL, string tbName, ref string sErr)
        {
            if (!this.CnOpen())
            {
@@ -384,12 +384,42 @@
            catch (Exception e)
            {
                ClsPub.sErrInfo = e.Message;
                sErr = e.Message; 
                sErr = e.Message;
                return null;
            }
        }


        /// <summary>
        /// æ‰¹é‡æ’入数据库
        /// </summary>
        /// <param name="TableName">目标表</param>
        /// <param name="dt">源数据</param>
        public bool SqlBulkCopyByDatatable(string TableName, DataTable dt)
        {
            string connectionString = $"Data Source={sServer};DataBase={sDataBase};User ID={sUser};PWD={sPassword}";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
                {
                    try
                    {
                        sqlbulkcopy.DestinationTableName = TableName;
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                        }
                        sqlbulkcopy.WriteToServer(dt);
                        return true;
                    }
                    catch (System.Exception ex)
                    {
                        //ex.Message;
                        return false;
                    }
                }
            }
        }
        #region IDisposable æˆå‘˜

        public void Dispose()

 WebAPI/Controllers/BaseSet/Gy_UnitController.cs

@@ -1,5 +1,7 @@
using Newtonsoft.Json;
using Model;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NPOI.SS.Formula.Functions;
using Pub_Class;
using System;
using System.Collections;
@@ -7,6 +9,7 @@
using System.Data;
using System.Data.SqlClient;
using System.Web.Http;
using WebAPI.Controllers.SCGL.日计划管理;
using WebAPI.Models;
using WebAPI.Service;


 WebAPI/Controllers/BaseSet/Gy_WarehouseController.cs

@@ -1,4 +1,5 @@
using Newtonsoft.Json;
using Model;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Pub_Class;
using System;
@@ -7,6 +8,7 @@
using System.Data;
using System.Data.SqlClient;
using System.Web.Http;
using WebAPI.Controllers.SCGL.日计划管理;
using WebAPI.Models;
namespace WebAPI.Controllers
{
@@ -221,7 +223,7 @@
                long HParentID = list[0].HParentID;
                long HLevel = list[0].HLevel;
                bool HEndFlag = list[0].HEndFlag;
                bool HStopflag = list[0].HStopflag;
                bool HStopflag = list[0].HStopFlag;
                string HRemark = list[0].HRemark;
                string HBarCode = list[0].HBarCode;
                long HEmpID = list[0].HEmpID;
@@ -304,7 +306,7 @@
                long HParentID = list[0].HParentID;
                long HLevel = list[0].HLevel;
                bool HEndFlag = list[0].HEndFlag;
                bool HStopflag = list[0].HStopflag;
                bool HStopflag = list[0].HStopFlag;
                string HRemark = list[0].HRemark;
                string HBarCode = list[0].HBarCode;
                long HEmpID = list[0].HEmpID;
@@ -411,6 +413,8 @@
                _value = "[" + _value.ToString() + "]";
                List<Warehouse> list = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Warehouse>>(_value);
                string sql = string.Empty;
                //DataTable dt = DataHelper.ListToDataTable<Warehouse>(list);
                //oCN.SqlBulkCopyByDatatable("Gy_Warehouse", dt);
                //保存
                oCN.BeginTran();
                sql = $"delete Gy_Warehouse where HItemID = {list[0].HItemID}";
@@ -421,7 +425,7 @@
                       " (HItemID,HNumber,HName,HHelpCode,HShortNumber,HParentID" +
                       ",HLevel,HEndFlag,HStopflag,HRemark,HEmpID,HAddress,HPhone,HUSEORGID) " +
                       " Values('" + list[0].HItemID + "','" + list[0].HNumber + "','" +list[0].HName + "','" + list[0].HHelpCode + "','" + list[0].HShortNumber + "'," + list[0].HParentID.ToString() +
                       "," + list[0].HLevel.ToString() + "," + Convert.ToString(list[0].HEndFlag ? 1 : 0) + "," + Convert.ToString(list[0].HStopflag ? 1 : 0) + ",'" + list[0].HRemark + "'," + list[0].HEmpID.ToString() + ",'" + list[0].HAddress + "','" + list[0].HPhone + "','" + list[0].HUSEORGID + "')", ref DBUtility.ClsPub.sExeReturnInfo);
                       "," + list[0].HLevel.ToString() + "," + Convert.ToString(list[0].HEndFlag ? 1 : 0) + "," + Convert.ToString(list[0].HStopFlag ? 1 : 0) + ",'" + list[0].HRemark + "'," + list[0].HEmpID.ToString() + ",'" + list[0].HAddress + "','" + list[0].HPhone + "','" + list[0].HUSEORGID + "')", ref DBUtility.ClsPub.sExeReturnInfo);
                //修改上级为非末级代码
                sql = "set identity_insert Gy_Warehouse off";
                oCN.RunProc(sql);

 WebAPI/Controllers/BaseSet/Gy_WorkDayController.cs

@@ -95,16 +95,14 @@
                //反序列化
                msg2 = "[" + msg2.ToString() + "]";
                List<WorkDay> list = Newtonsoft.Json.JsonConvert.DeserializeObject<List<WorkDay>>(msg2);

                long HItemID = list[0].HItemID;

                //保存
                //保存完毕后处理
                if (HItemID == 0)
                {
                    oCN.BeginTran();
                    string sql = string.Format(@"insert into Gy_WorkDay (HDate,HWorkBegin,HWorkEnd,HNoonbreakBegin,HNoonbreakEnd,HNightbreakBegin,HNightbreakEnd)
values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", list[0].HDate, list[0].HWorkBegin, list[0].HWorkEnd, list[0].HNoonbreakBegin, list[0].HNoonbreakEnd, list[0].HNightbreakBegin, list[0].HNightbreakEnd);
                    string sql = string.Format(@"insert into Gy_WorkDay (HDate,HWorkBegin,HWorkEnd,HNoonbreakBegin,HNoonbreakEnd,HNightbreakBegin,HNightbreakEnd,HNoonbreakBegin2,HNoonbreakEnd2,HNightbreakBegin2,HNightbreakEnd2)
values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')", list[0].HDate, list[0].HWorkBegin, list[0].HWorkEnd, list[0].HNoonbreakBegin, list[0].HNoonbreakEnd, list[0].HNightbreakBegin, list[0].HNightbreakEnd, list[0].HNoonbreakBegin2, list[0].HNoonbreakEnd2, list[0].HNightbreakBegin2, list[0].HNightbreakEnd2);
                    oCN.RunProc(sql);
                    oCN.Commit();
                }
@@ -118,9 +116,13 @@
                        ",HWorkBegin='" + list[0].HWorkBegin + "'" +
                        ",HWorkEnd='" + list[0].HWorkEnd + "'" +
                        ",HNoonbreakBegin='" + list[0].HNoonbreakBegin + "'" +
                        ",HNoonbreakEnd=" + list[0].HNoonbreakEnd +
                        ",HNightbreakBegin=" + list[0].HNightbreakBegin +
                        ",HNightbreakEnd=" + list[0].HNightbreakEnd +
                        ",HNoonbreakEnd='" + list[0].HNoonbreakEnd + "'" +
                        ",HNightbreakBegin='" + list[0].HNightbreakBegin + "'" +
                        ",HNightbreakEnd='" + list[0].HNightbreakEnd + "'" +
                        ",HNoonbreakBegin2='" + list[0].HNoonbreakBegin2 + "'" +
                        ",HNoonbreakEnd2='" + list[0].HNoonbreakEnd2 + "'" +
                        ",HNightbreakBegin2='" + list[0].HNightbreakBegin2 + "'" +
                        ",HNightbreakEnd2='" + list[0].HNightbreakEnd2 + "'" +
                        " Where HItemID=" + HItemID, ref DBUtility.ClsPub.sExeReturnInfo);
                    //
                    oCN.Commit();

 WebAPI/Controllers/SCGL/Èռƻ®¹ÜÀí/DataHelper.cs

@@ -1,5 +1,8 @@
using System;
using Newtonsoft.Json;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;
@@ -19,7 +22,7 @@
                List<T> list = new List<T>();
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow row in ds.Tables[0].Rows) 
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        list.Add(row.Put(new T()));
                    }
@@ -167,5 +170,77 @@
            }
            return $"update {table} set {strSet} where 1=1 {condition}";
        }




        /// <summary>
        /// å°†List转换成DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(this IList<T> data)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable dt = new DataTable();
            for (int i = 0; i < properties.Count; i++)
            {
                PropertyDescriptor property = properties[i];
                dt.Columns.Add(property.Name, property.PropertyType);
            }
            object[] values = new object[properties.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = properties[i].GetValue(item);
                }
                dt.Rows.Add(values);
            }
            return dt;
        }


        /// <summary>
        /// å°†List转换为DataTable
        /// </summary>
        /// <param name="list">请求数据</param>
        /// <returns></returns>
        public static DataTable ListToDataTable<T>(List<T> list)
        {
            //创建一个名为"tableName"的空表
            DataTable dt = new DataTable("tableName");

            //创建传入对象名称的列
            foreach (var item in list.FirstOrDefault().GetType().GetProperties())
            {
                dt.Columns.Add(item.Name);
            }
            //循环存储
            foreach (var item in list)
            {
                //新加行
                DataRow value = dt.NewRow();
                //根据DataTable中的值，进行对应的赋值
                foreach (DataColumn dtColumn in dt.Columns)
                {
                    int i = dt.Columns.IndexOf(dtColumn);
                    //基元元素，直接复制，对象类型等，进行序列化
                    if (value.GetType().IsPrimitive)
                    {
                        value[i] = item.GetType().GetProperty(dtColumn.ColumnName).GetValue(item);
                    }
                    else
                    {
                        value[i] = JsonConvert.SerializeObject(item.GetType().GetProperty(dtColumn.ColumnName).GetValue(item));
                    }
                }
                dt.Rows.Add(value);
            }
            return dt;
        }

    }

}

 WebAPI/Models/»ù´¡×ÊÁÏ/Warehouse.cs

@@ -16,12 +16,11 @@
        public long HParentID { get; set; }
        public string HRemark { get; set; }
        public string HShortNumber { get; set; }
        public bool HStopflag { get; set; }
        public bool HStopFlag { get; set; }
        public string HUseFlag { get; set; }
        public Int64 HEmpID { get; set; }
        public string HAddress { get; set; }
        public string HPhone { get; set; }
        public bool HIsStockMgr { get; set; }
        public Int64 HSPGroupID { get; set; }
        public string HBarCode { get; set; }
        public Int64 HUSEORGID { get; set; }

 WebAPI/Models/»ù´¡×ÊÁÏ/WorkDay.cs

@@ -15,5 +15,9 @@
        public DateTime HNoonbreakEnd { get; set; }
        public DateTime HNightbreakBegin { get; set; }
        public DateTime HNightbreakEnd { get; set; }
        public DateTime HNoonbreakBegin2 { get; set; }
        public DateTime HNoonbreakEnd2 { get; set; }
        public DateTime HNightbreakBegin2 { get; set; }
        public DateTime HNightbreakEnd2 { get; set; }
    }
}

 WebAPI/WebAPI.csproj.user

@@ -18,7 +18,7 @@
    <IISExpressWindowsAuthentication />
    <IISExpressUseClassicPipelineMode />
    <UseGlobalApplicationHostFile />
    <ProjectView>ShowAllFiles</ProjectView>
    <ProjectView>ProjectFiles</ProjectView>
    <Controller_SelectedScaffolderID>MvcControllerEmptyScaffolder</Controller_SelectedScaffolderID>
    <Controller_SelectedScaffolderCategoryPath>root/Common/MVC/Controller</Controller_SelectedScaffolderCategoryPath>
  </PropertyGroup>