项目中遇到的问题及技术

最新推荐文章于 2024-08-06 04:12:21 发布

taoaxiang520

最新推荐文章于 2024-08-06 04:12:21 发布

阅读量551

点赞数

文章标签：项目问题及技术

本文链接：https://blog.csdn.net/taoaxiang520/article/details/43154497

版权

跨数据库操作数据（webService+xml）

前提：项目A，DBA；项目B，DBB

要求：项目B需要从项目A中的数据库（DBA）取得数据放到自己的数据库（DBB）中

解决方案：webService+xml

具体流程：

项目A中创建一个webService：ImportFromCloundServer.asmx，

ImportFromCloundServer.asmx

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Services;

namespace Web.CloudServer
{
    /// <summary>
    /// ImportFromCloundServer 的摘要说明
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消注释以下行。 
    // [System.Web.Script.Services.ScriptService]
    public class ImportFromCloundServer : System.Web.Services.WebService
    {
        //用到的方法
        [WebMethod]
        public string import(string year)
        {
            return Biz.BizLegalHolidays.import(year);
        }

        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }
    }
}

创建成功后，在浏览器打开webService，可以看到有两个方法，

在项目B中添加服务引用，成功之后出现Service References文件夹，里面有自动生成的webService相关代码（方法也在里面）

项目B：Controller

using EAS.ImportFromCloundServerService;

 ImportFromCloundServerSoapClient client = new ImportFromCloundServerSoapClient();
            string xml = client.import(year);

PS：DataTable与XML的互转（非文件）

DataTable转XML

DataTable dt = new DataTable();
            Dictionary<string, object> fieldValues = new Dictionary<string, object>();
            fieldValues.Add("year", year);
            BizCenter.DataAccessor.GetData("getHolidaysByImport", fieldValues, ref dt);
            System.IO.StringWriter writer = new System.IO.StringWriter();

            if(dt.Rows.Count==0){
                return "";
            }
            dt.TableName = "HOLIDAYS";
            dt.WriteXml(writer, XmlWriteMode.WriteSchema, true);
            return writer.ToString();

XML转DataTable（先转成DataSet）

DataSet ds = new DataSet();
                StringReader sr = new StringReader(xmlStr);
                ds.ReadXml(sr, XmlReadMode.ReadSchema);
                DataTable dt = ds.Tables[0];
                BizCenter.DataAccessor.UpdateData("addLegalHolidays", dt);

                return "{'result':1,'msg':'导入成功'}";

导出Excel（Aspose.Cells）

json转DataTable

        //json转换为datatable（导出Excel）
        public static DataTable jsonToDataTable(string strJson)
        {
            //将json转换为dataTable
            // 取出表名   
            var rg = new Regex(@"(?<={)[^:]+(?=:\[)", RegexOptions.IgnoreCase);
            string strName = rg.Match(strJson).Value;
            DataTable tb = null;

            // 去除表名   
            strJson = strJson.Substring(strJson.IndexOf("[") + 1);
            strJson = strJson.Substring(0, strJson.IndexOf("]"));

            // 获取数据   
            rg = new Regex(@"(?<={)[^}]+(?=})");
            MatchCollection mc = rg.Matches(strJson);
            for (int i = 0; i < mc.Count; i++)
            {
                string strRow = mc[i].Value;
                string[] strRows = strRow.Split(',');
                // 创建表   
                if (tb == null)
                {
                    tb = new DataTable();
                    tb.TableName = strName;
                    foreach (string str in strRows)
                    {
                        var dc = new DataColumn();
                        string[] strCell = str.Split(':');
                        dc.ColumnName = strCell[0].Replace("\"", "");
                        tb.Columns.Add(dc);
                    }
                    tb.AcceptChanges();
                }
                // 增加内容   
                DataRow dr = tb.NewRow();
                Regex xx = new Regex(@"^\w+:");
                for (int j = 0; j < strRows.Length; j++)
                {
                    //strRows[j].Replace
                    dr[j] = xx.Replace(strRows[j], "").Replace("\"", "");
                }
                tb.Rows.Add(dr);
                tb.AcceptChanges();
            }
            return tb;
        }

服务器生成Excel

        public static void WorkingHoursToExcel(string StrJson)
        {
            DataTable tb = BizCommon.jsonToDataTable(StrJson);
            //导出Excle文件
            WorkbookDesigner wd = new WorkbookDesigner();
            //以SmartMarker_Designer.xlsx为模版（样式）
            string path = System.AppDomain.CurrentDomain.BaseDirectory;
            wd.Open(path + "Download/工作时间设置模版.xlsx");
            //将dataTable中的数据配置到wd中
            wd.SetDataSource(tb);
            wd.Process(true);
            //生成新的Excle文件（会覆盖以前的文件）
            wd.Workbook.Save(path+"Download/工作时间设置记录.xlsx");
        }

成功后在js中调用Ctroller中的DownLoadFile()

        public void DownLoadFile()
        {
            string fileName = "工作时间设置记录.xlsx";//客户端保存的文件名  
            string filePath = Server.MapPath("../Download/工作时间设置记录.xlsx");//路径  

            FileStream fs = new FileStream(filePath, FileMode.Open);
            byte[] bytes = new byte[(int)fs.Length];
            fs.Read(bytes, 0, bytes.Length);
            fs.Close();
            Response.Charset = "UTF-8";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
            Response.ContentType = "application/octet-stream";

            Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
            Response.BinaryWrite(bytes);
            Response.Flush();
            Response.End();
        }

批量修改（json转DataTable）

json格式：

{RID:65.001,OPER_STATUS:"edit",STATUS:"D"},

{RID:64.001,OPER_STATUS:"edit",STATUS:"D"},

{RID:63.001,OPER_STATUS:"edit",STATUS:"D"},

{RID:62.001,OPER_STATUS:"edit",STATUS:"D"},

{RID:61.001,OPER_STATUS:"edit",STATUS:"D"}

转DataTable

        //json转datatable（批量修改）
        public static bool ParseJsonToDataTable(string JsonData, string PrimaryKeyField, string StateField, ref DataTable dataTable)
        {
            bool result = false;
            bool hasStateField = false;
            bool isEditRow = false;
            string rowStatus = null;
            try
            {
                JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
                javaScriptSerializer.MaxJsonLength = Int32.MaxValue; //取得最大数值
                ArrayList arrayList = javaScriptSerializer.Deserialize<ArrayList>(JsonData);
                if (arrayList.Count > 0)
                {
                    foreach (Dictionary<string, object> dictionary in arrayList)
                    {
                        if (dataTable.Columns.Count == 0)   //如果表中没有字段
                        {
                            foreach (string current in dictionary.Keys) //把集合中的key加入数据表里面
                            {
                                dataTable.Columns.Add(current, dictionary[current].GetType());
                            }
                            hasStateField = !string.IsNullOrEmpty(StateField) && dictionary.ContainsKey(StateField);
                        }
                        if (hasStateField)
                            rowStatus = dictionary[StateField].ToString().ToLower();
                        isEditRow = hasStateField && rowStatus == "edit";

                        DataRow dataRow = dataTable.NewRow();    //datarow 与datatable有相同架构
                        dataTable.Rows.Add(dataRow); //循环添加行到DataTable中

                        dataRow[PrimaryKeyField] = dictionary[PrimaryKeyField];
                        if (rowStatus != "add")
                            dataRow.AcceptChanges();

                        foreach (string current in dictionary.Keys)
                        {
                            if (string.Compare(current, PrimaryKeyField, true) == 0)
                                continue;
                            dataRow[current] = dictionary[current];  //将dictionary中的值加入到datarow中
                        }

                        if (hasStateField)
                        {
                            switch (dataRow[StateField].ToString())
                            {
                                case "add":
                                    dataRow[PrimaryKeyField] = DBNull.Value;
                                    break;
                                case "edit":
                                    break;
                                case "del":
                                    dataTable.DefaultView[dataTable.DefaultView.Count - 1].Delete();
                                    break;
                                case "view":
                                    dataRow.AcceptChanges();
                                    break;
                            }
                        }
                    }
                }
                result = true;
            }
            catch (Exception ex)
            {
                //BizCenter.DataAccessor.AddSysLog(EasyWork.Utils.LogMessageType.Error, "ParseJsonToDataTable->", ex);
            }
            return result;
        }

DES加解密

using System.Security.Cryptography;

//测试des加密
        public string EncryptDES(string encryptString) {
            byte[] Keys = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF };
            string keyUse = "12345678";
            try
            {
                byte[] rgbKey = Encoding.UTF8.GetBytes(keyUse.Substring(0, 8));
                byte[] rgbIV = Keys;
                byte[] inputByteArray = Encoding.UTF8.GetBytes(encryptString);
                DESCryptoServiceProvider dCSP = new DESCryptoServiceProvider();
                MemoryStream mStream = new MemoryStream();
                CryptoStream cStream = new CryptoStream(mStream, dCSP.CreateEncryptor(rgbKey, rgbIV), CryptoStreamMode.Write);
                cStream.Write(inputByteArray, 0, inputByteArray.Length);
                cStream.FlushFinalBlock();
                string msg = Convert.ToBase64String(mStream.ToArray());
                return msg;
            }
            catch
            {
                return encryptString;
            }
        }

        //测试des解密
        public string DecryptDES(string decryptString)
        {
            byte[] Keys = { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF };
            string keyUse = "12345678";
            try
            {
                byte[] rgbKey = Encoding.UTF8.GetBytes(keyUse);
                byte[] rgbIV = Keys;
                byte[] inputByteArray = Convert.FromBase64String(decryptString);
                DESCryptoServiceProvider DCSP = new DESCryptoServiceProvider();
                MemoryStream mStream = new MemoryStream();
                CryptoStream cStream = new CryptoStream(mStream, DCSP.CreateDecryptor(rgbKey, rgbIV), CryptoStreamMode.Write);
                cStream.Write(inputByteArray, 0, inputByteArray.Length);
                cStream.FlushFinalBlock();
                string msg = Encoding.UTF8.GetString(mStream.ToArray());
                return msg;
            }
            catch
            {
                return decryptString;
            }
        }