提交

王 垚

2022-12-07 ee9f4215d0750a15031bfb89f0f347dadc8c1b70

提交

 Pub_Class/CustomerCls/ClsSqlHelper.cs

@@ -390,6 +390,36 @@
        }


        /// <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;
@@ -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>