MS SQLServer ISNUMBER()/NOT EXSITS 替换方案、

WITH                                                                                          
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),                                                  
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),                                     
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),                                     
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),                                     
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),                                     
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS num FROM L4)      
           
SELECT TOP (9999) MIN(d.num) FROM Nums AS d 
WHERE NOT EXISTS
( 
	SELECT sir.serial 
	FROM( 
		SELECT DISTINCT  SUBSTRING(LX_IMA01, LEN('095-0009-') + 1, 4) AS serial,lx_ima01
		FROM    innovator.PART 
		LEFT OUTER JOIN innovator.lx_notcode ON ima01=lx_ima01						   ---2.1：改用LEFT OUTER JOIN 替换 NOT EXISTS，速度快
		WHERE   LX_IMA01 LIKE '095-0009-%'                                                  
			--AND ISNUMERIC(SUBSTRING(LX_IMA01, LEN('095-0009-') + 1, 4 )) = 1         ---1.0：0'0d'会被判断为数值
			AND PATINDEX( '%[^0-9]%',SUBSTRING(LX_IMA01, LEN('095-0009-') + 1, 4 ))=0  ---1.1：改为使用PATINDEX()匹配不是数字的位置，匹配不到就认为是全数字。
			AND LEN(LX_IMA01) = 14
						--AND  not exists (select 1 from innovator.lx_notcode a where ima01=lx_ima01) --2.0：NOT EXISTS太耗时
 	) AS sir 
	WHERE sir.serial =  D.num
) AND d.num< 9999

WITH

L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),

L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),

L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),

L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),

Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS num FROM L4)

SELECT TOP (9999) MIN(d.num) FROM Nums AS d

WHERE NOT EXISTS

(

SELECT sir.serial

FROM(

SELECT DISTINCT SUBSTRING(LX_IMA01, LEN('095-0009-') + 1, 4) AS serial,lx_ima01

FROM innovator.PART

LEFT OUTER JOIN innovator.lx_notcode ON ima01=lx_ima01 ---2.1：改用LEFT OUTER JOIN 替换 NOT EXISTS，速度快

WHERE LX_IMA01 LIKE '095-0009-%'

--AND ISNUMERIC(SUBSTRING(LX_IMA01, LEN('095-0009-') + 1, 4 )) = 1 ---1.0：0'0d'会被判断为数值

AND PATINDEX( '%[^0-9]%',SUBSTRING(LX_IMA01, LEN('095-0009-') + 1, 4 ))=0 ---1.1：改为使用PATINDEX()匹配不是数字的位置，匹配不到就认为是全数字。

AND LEN(LX_IMA01) = 14

--AND not exists (select 1 from innovator.lx_notcode a where ima01=lx_ima01) --2.0：NOT EXISTS太耗时

) AS sir

WHERE sir.serial = D.num

) AND d.num< 9999

Sqlserver 中文全角符号like查询

--1,全角符号匹配查询
SELECT DISTINCT B.LX_BMB13,REPLACE(REPLACE(B.LX_BMB13,'''',''),'，',','), B.*
FROM PART_BOM AS B
JOIN PART AS A ON A.ID = B.SOURCE_ID
JOIN LX_EC_PART_REL AS C ON C.RELATED_ID = A.ID
JOIN ECN AS D ON C.SOURCE_ID = D.ID
WHERE D.ITEM_NUMBER='ECN19-0000029656'
AND (
B.LX_BMB13 LIKE '%''%' 
OR B.LX_BMB13 COLLATE Chinese_PRC_CS_AS_WS LIKE '%，%'--COLLATE Chinese_PRC_CS_AS_WS用作全角查询，否则职匹配‘，’会匹配成‘,’。
)


--SELECT REPLACE(REPLACE('''AB，C,DE，''FGH','''',''),'，',',')
--2，全角替换
BEGIN TRAN
UPDATE B SET B.LX_BMB13 =REPLACE(REPLACE(B.LX_BMB13,'''',''),'，',',')
FROM PART_BOM AS B
JOIN PART AS A ON A.ID = B.SOURCE_ID
JOIN LX_EC_PART_REL AS C ON C.RELATED_ID = A.ID
JOIN ECN AS D ON C.SOURCE_ID = D.ID
WHERE D.ITEM_NUMBER='ECN19-0000029656'
AND (B.LX_BMB13 LIKE '%''%' OR B.LX_BMB13 COLLATE Chinese_PRC_CS_AS_WS LIKE '%，%')

ROLLBACK
COMMIT

--1,全角符号匹配查询

SELECT DISTINCT B.LX_BMB13,REPLACE(REPLACE(B.LX_BMB13,'''',''),'，',','), B.*

FROM PART_BOM AS B

JOIN PART AS A ON A.ID = B.SOURCE_ID

JOIN LX_EC_PART_REL AS C ON C.RELATED_ID = A.ID

JOIN ECN AS D ON C.SOURCE_ID = D.ID

WHERE D.ITEM_NUMBER='ECN19-0000029656'

AND (

B.LX_BMB13 LIKE '%''%'

OR B.LX_BMB13 COLLATE Chinese_PRC_CS_AS_WS LIKE '%，%'--COLLATE Chinese_PRC_CS_AS_WS用作全角查询，否则职匹配‘，’会匹配成‘,’。

)

--SELECT REPLACE(REPLACE('''AB，C,DE，''FGH','''',''),'，',',')

--2，全角替换

BEGIN TRAN

UPDATE B SET B.LX_BMB13 =REPLACE(REPLACE(B.LX_BMB13,'''',''),'，',',')

FROM PART_BOM AS B

JOIN PART AS A ON A.ID = B.SOURCE_ID

JOIN LX_EC_PART_REL AS C ON C.RELATED_ID = A.ID

JOIN ECN AS D ON C.SOURCE_ID = D.ID

WHERE D.ITEM_NUMBER='ECN19-0000029656'

AND (B.LX_BMB13 LIKE '%''%' OR B.LX_BMB13 COLLATE Chinese_PRC_CS_AS_WS LIKE '%，%')

ROLLBACK

COMMIT

C# Split字符串分隔

//换行符分隔
string[] lines = theText.Split(
    new[] { Environment.NewLine },
    StringSplitOptions.None
);
//多个字符分隔
string[] lines = theText.Split(
    new[] { "\r\n", "\r", "\n" },
    StringSplitOptions.None
);

//换行符分隔

string[] lines = theText.Split(

new[] { Environment.NewLine },

StringSplitOptions.None

);

//多个字符分隔

string[] lines = theText.Split(

new[] { "\r\n", "\r", "\n" },

StringSplitOptions.None

);

VS 2019离线安装

参考链接：VS2019 离线安装方法详解

官网链接：创建 Visual Studio 的脱机安装

1，下载Visual Studio 引导程序。

2，命令行切换到VS引导程序下载目录，查找组件对应ID，编写命令脚本(下图红框选择项vs2017截图)：

mkdir vs2019_layout
vs_professional.exe --layout .\vs2019_layout --add Microsoft.VisualStudio.Workload.NetWeb --add Microsoft.VisualStudio.Workload.ManagedDesktop --add Microsoft.VisualStudio.Workload.NetCoreTools --add Microsoft.VisualStudio.Workload.Office --includeRecommended --lang en-US zh-CN

1 2	mkdir vs2019_layout vs_professional.exe --layout .\vs2019_layout --add Microsoft.VisualStudio.Workload.NetWeb --add Microsoft.VisualStudio.Workload.ManagedDesktop --add Microsoft.VisualStudio.Workload.NetCoreTools --add Microsoft.VisualStudio.Workload.Office --includeRecommended --lang en-US zh-CN

3，独立组件安装：

vs_professional.exe --layout .\vs2019_layout --add Microsoft.Component.HelpViewer --lang en-US zh-CN

1	vs_professional.exe --layout .\vs2019_layout --add Microsoft.Component.HelpViewer --lang en-US zh-CN

4，切换到vs2019_layout 目录，执行vs_setup.exe 进行安装。

去除emoji特殊字符–Unicode码

Regex基于UTF-16工作的，Emoji是Unicode，不能被全部替换。参考

using System;
using System.Text.RegularExpressions;

class Test
{
    static void Main(string[] args)
    {
        string text = "x\U0001F310y";
        Console.WriteLine(text.Length); // 4
        string result = Regex.Replace(text, @"\p{Cs}", "");
        Console.WriteLine(result); // 2
    }
}

using System;

using System.Text.RegularExpressions;

class Test

{

static void Main(string[] args)

{

string text = "x\U0001F310y";

Console.WriteLine(text.Length); // 4

string result = Regex.Replace(text, @"\p{Cs}", "");

Console.WriteLine(result); // 2

}

编译器失败，错误代码为 -2146232576。

罪魁禍首是Microsoft.Net.Compilers包，用於在您的項目中支持現代C＃語法/功能（版本6.0,7.0），特別是在Razor視圖中。根據其版本的不同，該軟件包需要在相關機器上安裝安裝的完整.NET框架的特定最低版本。

例如，2.2.0包需要.NET 4.6+。即使你的項目的目標是.NET 4.5.2，你可能在你的開發機器上安裝了最新的.NET，而且一切都很順利。遠程部署機器只安裝了.NET 4.5.2，當您的ASP.NET應用程序嘗試在運行時編譯資源（例如視圖）時，您會收到錯誤-2146232576。

您的選擇：

如果您確實需要使用新望C＃功能在您的項目 – 安裝最新的.NET Framework到遠程計算機。
如果您對僅提供更多或更少的現代C＃功能感到滿意 – 將Microsoft.Net.Compilers軟件包降級爲僅需要您的遠程機器上的框架的版本。例如，版本1.3.2只需要.NET 4.5。
如果你並不需要在所有上述功能，只需從你的項目中刪除Microsoft.Net.Compilers和Microsoft.CodeDom.Providers.DotNetCompilerPlatform（羅斯林CodeDom中提供）封裝。

解决方案：

解決方法：

我面臨這個問題升級後一些NuGets並通過下面的步驟來解決。

步驟1：從NuGet Package Console移除這些的NuGet包

PM&gt; Uninstall-<span class="hljs-keyword">package</span> Microsoft.CodeDom.Providers.DotNetCompilerPlatform 
PM&gt; Uninstall-<span class="hljs-keyword">package</span> Microsoft.Net.Compilers

PM> Uninstall-package Microsoft.CodeDom.Providers.DotNetCompilerPlatform

PM> Uninstall-package Microsoft.Net.Compilers

步驟2：後：在Web.config

<system.codedom> 
     <compilers> 
      <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:6 /nowarn:1659;1699;1701"></compiler> 
      <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:14 /nowarn:41008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+"></compiler> 
     </compilers> 
</system.codedom>

<system.codedom>

</compilers>

</system.codedom>

再发布就可以了。

参考链接

端口查询与spid终止

netstat -aon|findstr 5000
tasklist /fi “pid eq 22924”
taskkill /pid 22924 /f

XML格式化输出

private void button49_Click(object sender, EventArgs e)
{
    txt_Item_Number.BackColor = Color.White;
    txt_RequestId.BackColor = Color.White;
    if (string.IsNullOrEmpty(txt_Item_Number.Text))
    {
        MessageBox.Show("单号未填写！");
        txt_Item_Number.BackColor = Color.Red;
        return;
    }
    string EZUrlresult =  LUX_getStrEZGPURL(txt_Item_Number.Text.Trim());
    if (string.Compare(EZUrlresult.Split('|').FirstOrDefault(),Boolean.TrueString,true)!=0)
    {
        MessageBox.Show(EZUrlresult.Split('|')[1]);
        return;
    }
    if (string.IsNullOrEmpty(txt_RequestId.Text))
    {
        MessageBox.Show("RequestId为空！");
        txt_RequestId.BackColor = Color.Red;
        return;
    }
    //获取josn数据
    string url = $"http://oa.abcenter.xyz/luxshare/workflow/itfw/it001/it001_nodeoperation.jsp?operation=checkPLMxml&requestid={txt_RequestId.Text}";
    lnk_OAXmlLink.Text = url;
    HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
    HttpWebResponse response = (HttpWebResponse)request.GetResponse();
    OAXmlObj oAXmlObj = new OAXmlObj();
    using (Stream resStream = response.GetResponseStream())
    {
        StreamReader reader = new StreamReader(resStream, Encoding.UTF8);
        string respText = reader.ReadToEnd();
        oAXmlObj = Newtonsoft.Json.JsonConvert.DeserializeObject<OAXmlObj>(respText);                
        resStream.Close();
    }
    if (string.IsNullOrEmpty(oAXmlObj.result))
    {
        oAXmlObj.result = "未获取到xml。";
        return;
    }

    System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
    doc.LoadXml(System.Net.WebUtility.HtmlDecode(oAXmlObj.result));//html实体解码

    //格式化
    System.IO.StringWriter sw = new System.IO.StringWriter();
    using (System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(sw))
    {
        writer.Indentation = 2;  // the Indentation
        writer.Formatting = System.Xml.Formatting.Indented;
        doc.WriteContentTo(writer);
        writer.Close();
    }
    rtxt_OAXml.Text = System.Net.WebUtility.HtmlDecode(sw.ToString());//输出格式化xml字符串

}
private void lnk_OAXmlLink_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
    System.Diagnostics.Process.Start(lnk_OAXmlLink.Text);
}
public class OAXmlObj
{
    public string result { get; set; }
}

private void button49_Click(object sender, EventArgs e)

{

txt_Item_Number.BackColor = Color.White;

txt_RequestId.BackColor = Color.White;

if (string.IsNullOrEmpty(txt_Item_Number.Text))

{

MessageBox.Show("单号未填写！");

txt_Item_Number.BackColor = Color.Red;

return;

}

string EZUrlresult = LUX_getStrEZGPURL(txt_Item_Number.Text.Trim());

if (string.Compare(EZUrlresult.Split('|').FirstOrDefault(),Boolean.TrueString,true)!=0)

{

MessageBox.Show(EZUrlresult.Split('|')[1]);

return;

}

if (string.IsNullOrEmpty(txt_RequestId.Text))

{

MessageBox.Show("RequestId为空！");

txt_RequestId.BackColor = Color.Red;

return;

}

//获取josn数据

string url = $"http://oa.abcenter.xyz/luxshare/workflow/itfw/it001/it001_nodeoperation.jsp?operation=checkPLMxml&requestid={txt_RequestId.Text}";

lnk_OAXmlLink.Text = url;

HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);

HttpWebResponse response = (HttpWebResponse)request.GetResponse();

OAXmlObj oAXmlObj = new OAXmlObj();

using (Stream resStream = response.GetResponseStream())

{

StreamReader reader = new StreamReader(resStream, Encoding.UTF8);

string respText = reader.ReadToEnd();

oAXmlObj = Newtonsoft.Json.JsonConvert.DeserializeObject<OAXmlObj>(respText);

resStream.Close();

}

if (string.IsNullOrEmpty(oAXmlObj.result))

{

oAXmlObj.result = "未获取到xml。";

return;

}

System.Xml.XmlDocument doc = new System.Xml.XmlDocument();

doc.LoadXml(System.Net.WebUtility.HtmlDecode(oAXmlObj.result));//html实体解码

//格式化

System.IO.StringWriter sw = new System.IO.StringWriter();

using (System.Xml.XmlTextWriter writer = new System.Xml.XmlTextWriter(sw))

{

writer.Indentation = 2; // the Indentation

writer.Formatting = System.Xml.Formatting.Indented;

doc.WriteContentTo(writer);

writer.Close();

}

rtxt_OAXml.Text = System.Net.WebUtility.HtmlDecode(sw.ToString());//输出格式化xml字符串

}

private void lnk_OAXmlLink_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)

{

System.Diagnostics.Process.Start(lnk_OAXmlLink.Text);

}

public class OAXmlObj

{

public string result { get; set; }

}

IdentityServer笔记

1，修改C#版本

c-sharpCorner

<!--.<em>csproj</em> 文件-->
<PropertyGroup>  
    <OutputType>Exe</OutputType>  
    <TargetFramework>netcoreapp2.0</TargetFramework>  
    <LangVersion>7.1</LangVersion>  
</PropertyGroup>

<TargetFramework>netcoreapp2.0</TargetFramework>

</PropertyGroup>

2，“keyset is missing”

Stack OverFlow

CSDN

3，通常每个套接字地址(协议/网络地址/端口)只允许使用一次。

端口查找与终止

我的部落格

OAuth2四种模式使用总结

MS SQLServer ISNUMBER()/NOT EXSITS 替换方案、

Sqlserver 中文全角符号like查询

C# Split字符串分隔

VS 2019离线安装

去除emoji特殊字符–Unicode码

编译器失败，错误代码为 -2146232576。

解決方法：

端口查询与spid终止

XML格式化输出

IdentityServer笔记