<<< These scripts were tested on MS SQL Server  2019 >>> From time to time we need to generate huge data for testing. Without any data generating tool we can achieve it by using some SQL Scripts. these scripts are different from regular query scripts. Generate list of data CREATE TABLE #accounts ( fname VARCHAR(20), lname VARCHAR(20)) GO INSERT #accounts VALUES ('Fred', 'Flintstone') GO 1000 SELECT TOP 10 * FROM #accounts fname lname Fred Flintstone Fred Flintstone Fred Flintstone Fred Flintstone Fred Flintstone Fred Flintstone Fred Flintstone Fred Flintstone Fred Flintstone Fred Flintstone ALTER TABLE #accounts ADD id INT IDENTITY(1,1) SELECT TOP 10 * FROM #accounts GO fname lname     id Fred Flintstone 1 Fred Flintstone 2 Fred Flintstone 3 Fred Flintstone 4 Fred Flintstone 5 Fred Flintstone 6 Fred Flintstone 7 Fred Flintstone 8 Fred Flintstone 9 Fred Flintstone 10 ALTER TABLE #accounts ADD id INT GO DECLARE @id INT SET @id = 0 UPDATE #accounts SET @id = id = @id + 1 OPTION ( MAXDOP 1 ) GO SELECT * FROM #accounts GO fname lname     id Fred Flintstone 1 Fred Flintstone 2 Fred Flintstone 3 Fred Flintstone 4 Fred Flintstone 5 Fred Flintstone 6 Fred Flintstone 7 Fred Flintstone 8 Fred Flintstone 9 Fred Flintstone 10 Note: MAXDOP is max degree of parallelism, set it to 1 to avoid duplicate value if running on multiple processors machine. Read article 1 for detail. Generate sequence numbers Using sys.all_columns table SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns Generate huge Rows using CROSS JOIN SELECT…

Split array column into rows Use OPENJSON feature in sql server (2016+) to parse json array string into rows. Let's first look this: select * from openjson('["small.jpg","mid.jpg","big.jpg"]') select * from openjson('{"A":"small1.jpg","B":"mid1.jpg","C":"big1.jpg"}') Result: key value type 0 small.jpg 1 1 mid.jpg 1 2 big.jpg 1 key value type A small1.jpg 1 B mid1.jpg 1 C big1.jpg 1 Now look at more complehensive example: create table parts (id int, name varchar(255), images varchar(max)) insert into parts (id,name,images) values (1, 'nail','["small.jpg","mid.jpg","big.jpg"]'), (2, 'screw','{"A":"small1.jpg","B":"mid1.jpg","C":"big1.jpg"}') --split array column to rows declare @tmp table(id int, img varchar(4000)) declare @id int,@img varchar(max) declare csr cursor for select id,images from parts open csr fetch next from csr into @id,@img while @@fetch_status=0 begin insert into @tmp (id,img) select @id,value from openjson(@img) fetch next from csr into @id,@img end close csr deallocate csr select * from @tmp Result: Concat rows to a column Using OPENXML feature in sql server to concat rows into a column. Reuse to script above: --concat rows to a column --using above example select id, '_product_image_gallery', stuff((select ','+img from @tmp where id=t.id for xml path('')),1,1,'') from @tmp t group by id Result:

Add some useful extension methods for string manipulations in C#. Make coding more elegent, interesting and productive. string.ToBoolean(); string.ToInt(); string.RemoveSufix(string suffix); string.RemovePrefix(string prefix); string.Remove(string toBeRemoved); string.RemoveSpace(); string.RemoveWhiteSpace();   /// <summary> /// Accept string value: "true", "t", "1" as true, all others will be evaluated as false. /// </summary> /// <returns></returns> public static bool ToBoolean(this string value) { if (string.IsNullOrEmpty(value)) return false; switch (value.Trim().ToLower()) { case "true": return true; case "t": return true; case "1": return true; case "0": return false; case "false": return false; case "f": return false; case "": return false; default: return false; //throw new InvalidCastException("You can't cast that value to a bool!"); } } /// <summary> /// Convert a string to int with default value /// </summary> /// <returns></returns> public static int ToInt(this string value) { if (string.IsNullOrEmpty(value)) return 0; int.TryParse(value, out int i); return i; } /// <summary> /// Remove string suffix /// </summary> /// <param name="suffix"></param> /// <returns></returns> public static string RemoveSufix(this string value, string suffix) { if (value.EndsWith(suffix)) { value.Substring(0, value.Length - suffix.Length); } return value; } /// <summary> /// Remove string prefix /// </summary> /// <param name="prefix"></param> /// <returns></returns> public static string RemovePrefix(this string value, string prefix) { if (value.StartsWith(prefix)) { value.Substring(prefix.Length); } return value; } /// <summary> /// Remove all spaces from a string /// </summary> /// <returns></returns> public static string RemoveSpace(this string value) { return value.Replace(" ", string.Empty); } /// <summary> /// Remove all white spaces from a string, including " ", \t, \r, \n /// </summary> /// <returns></returns> public static string RemoveWhiteSpace(this string value) { return value.Replace(" ", string.Empty) .Replace("\t",…

Error handling in sql server was a pain before SQL Server 2005. Starting from  Sql Server 2005 try ... catch blocks was introduced. It was not only giving us a beauty of coding, but greatly improved our development performance. It comes with the following build-in functions: ERROR_NUMBER() ERROR_SEVERITY() ERROR_STATE() ERROR_PROCEDURE() ERROR_LINE() ERROR_MESSAGE() When these functions is called in a CATCH block, they will returns the error information. After working on many projects, I found a best pattern for error handling in Sql Server as the following steps. 1. Create a log table to store error information catch by try...catch blocks, CREATE table sys_log( id int identity primary key, procName varchar(255), essionID int, errorNumber int, severity smallint, errorState smallint, errorLine int, description nvarchar(255), info nvarchar(max), mslangid smallint default 1033, --1033 English, 2052 Chinese timeStemp datetime default getdate()) GO 2. create a stored procedure to catch and log error information: CREATE proc sys_LogError @procname sysname, @info nvarchar(max)='' AS INSERT INTO sys_log(ProcName,SessionID,ErrorNumber,Severity,ErrorState,ErrorLine,Description,info) values ( @procname, @@spid, ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_MESSAGE(), @info ) 3. in all stored procedures that need to handle exepctions in transaction using this template: CREATE proc proc_name AS BEGIN tran BEGIN try -- do something print 1/0 commit tran END try BEGIN catch rollback tran declare @info nvarchar(max)='@table='+@table+', @urls='+@urls declare @procname sysname = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID) exec sys_LogError @procname,@info ---- re-throw exeception if you need --declare @msg nvarchar(2048) = error_message() --declare @severity smallint = error_severity() --declare @state smallint = error_state() --raiserror (@msg, @severity, @state) END catch ERROR_MESSAGE() can be referenced with MASTER.dbo.sysmessages table, mslangid is for identifying…

Based OO principles the private fields should not be accessd by anything outside of the object. However, for some reason we have to access the private fields, a good example is unit testing. With dotnet reflection we could access any fields, members in an object. The accessibility is controled by BindingFlags, specify proper bindingFlags we can access all private fields in a class. To access the private fields in base class we need to add one more level of the type with BaseType. Here is the extension method to get the value of a public and private field in an instance. public static T GetFieldValue<T>(this object obj, string name) { var bindingFlags = BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance; var field = obj.GetType().GetField(name, bindingFlags); if (field == null) field = obj.GetType().BaseType.GetField(name, bindingFlags); //father else if (field == null) field = obj.GetType().BaseType.BaseType.GetField(name, bindingFlags); //granpa else if (field == null) field = obj.GetType().BaseType.BaseType.BaseType.GetField(name, bindingFlags); //father of granpa return (T)field?.GetValue(obj); }

Python Code import json import requests # 3rd party import jsonpath from pyecharts.charts import Map from pyecharts import options as opts from src.covid19_data import nameMap url = 'https://api.inews.qq.com/newsqa/v1/automation/foreign/country/ranklist' resp = requests.post(url).text data = json.loads(resp) # get contry names and confirmed cases $：outer {}， ..: fuzzy match + key name = jsonpath.jsonpath(data, "$..name") # ['美国', '巴西', '印度', '俄罗斯', ... ] confirm = jsonpath.jsonpath(data, "$..confirm") pairs = list(zip(name, confirm)) # [('美国', 5321520), ('巴西', 3109630), ('印度', 2329638), ...] # 2. data visualization map plotting - size,title,color,data map_ = Map(opts.InitOpts(width='1200px', height='600px')) .add(series_name="Global Covid-19 Confirmed Cases", data_pair=pairs, # data entry - country : confirmed maptype="world", # world map name_map=nameMap, # country name mapping is_map_symbol_show=False) # 不显示标记点 # set series options map_.set_series_opts(label_opts=opts.LabelOpts(is_show=False)) map_.set_global_opts(title_opts=opts.TitleOpts(title="Global Covid-19 Pandemic (Chinese)"), visualmap_opts=opts.VisualMapOpts(max_=6000000, is_piecewise=True)) map_.render('../result/covid19.html') covid19_data nameMap = { 'Singapore Rep.': '新加坡', 'Dominican Rep.': '多米尼加', 'Palestine': '巴勒斯坦', 'Bahamas': '巴哈马', 'Timor-Leste': '东帝汶', 'Afghanistan': '阿富汗', 'Guinea-Bissau': '几内亚比绍', "Côte d'Ivoire": '科特迪瓦', 'Siachen Glacier': '锡亚琴冰川', "Br. Indian Ocean Ter.": '英属印度洋领土', 'Angola': '安哥拉', 'Albania': '阿尔巴尼亚', 'United Arab Emirates': '阿联酋', 'Argentina': '阿根廷', 'Armenia': '亚美尼亚', 'French Southern and Antarctic Lands': '法属南半球和南极领地', 'Australia': '澳大利亚', 'Austria': '奥地利', 'Azerbaijan': '阿塞拜疆', 'Burundi': '布隆迪', 'Belgium': '比利时', 'Benin': '贝宁', 'Burkina Faso': '布基纳法索', 'Bangladesh': '孟加拉国', 'Bulgaria': '保加利亚', 'The Bahamas': '巴哈马', 'Bosnia and Herz.': '波斯尼亚和黑塞哥维那', 'Belarus': '白俄罗斯', 'Belize': '伯利兹', 'Bermuda': '百慕大', 'Bolivia': '玻利维亚', 'Brazil': '巴西', 'Brunei': '文莱', 'Bhutan': '不丹', 'Botswana': '博茨瓦纳', 'Central African Rep.': '中非共和国', 'Canada': '加拿大', 'Switzerland': '瑞士', 'Chile': '智利', 'China': '中国', 'Ivory Coast': '象牙海岸', 'Cameroon': '喀麦隆', 'Dem. Rep. Congo': '刚果（金）', 'Congo': '刚果（布）', 'Colombia': '哥伦比亚', 'Costa Rica': '哥斯达黎加', 'Cuba': '古巴', 'N. Cyprus': '北塞浦路斯', 'Cyprus': '塞浦路斯', 'Czech Rep.': '捷克', 'Germany': '德国', 'Djibouti': '吉布提', 'Denmark': '丹麦', 'Algeria': '阿尔及利亚',…

条件语句的作用是更改控制流，任何程序都离不开条件判断。但条件判断会增加程序的复杂度，过多的条件判断会导致循环复杂度（Cyclomatic Complexity）。 这种复杂度取决于3个因素： 分支变量数 分支数 条件嵌套深度 if else if else if else if switch case 1: ... case 2: ... end switch ...... end if end if end if end if 这种形状也叫 Arrow Anti Pattern。它不仅降低程序的可读性，难以扩展和重用，还会轻易地隐藏许多bug。是我们必须避免的代码。 设计模式注重于软件的重用性和扩展性，但大多数模式都可以用来降低循环复杂度。设计模式可以让不同参与者分担这些条件语句，每个参与者解决一部分问题，这样控制流程就更简单，从而降低他们的循环复杂性。 这里介绍一个用策略（Strategy）设计模式来降低循环复杂度的例子。 假设现在我们可以用 C# 写一个简单的计算器，代码如下： using System; public class Calculator { public double Calculate(double operand1, double operand2, char operater) { double result = 0.0; switch (operater) { case '+': result = operand1 + operand2; break; case '-': result = operand1 - operand2; break; case '*': result = operand1 * operand2; break; case '/': if (Math.Abs(operand2) > 0.0) { result = operand1 / operand2; } else { throw new ArithmeticException("分母为零。"); } break; default: throw new ArgumentException("未知运算符: " + operater); } return result; } public static void Main(String[] args) { args = new[] { "2", "*", "3" }; double operand1 = Double.Parse(args[0]); double operand2 = Double.Parse(args[2]); char operater = args[1][0]; double result = new Calculator().Calculate(operand1, operand2, operater); Console.WriteLine(operand1 + args[1] + operand2 + " = " + result); Console.ReadKey(); } } 现在用策略模式来重构这段代码： 模式角色： Strategy: IProcessor ConcreteStrategy: Adder, Subtractor, Multiplier, Divider Context: Calculator using System; using System.Collections.Generic; public interface IProcessor { double Process(double operand1, double operand2); } public class Adder : IProcessor { public double Process(double operand1, double operand2) { return operand1 + operand2; } } public class Subtractor : IProcessor { public double Process(double operand1, double operand2) { return operand1 - operand2; } } public class Multiplier : IProcessor { public double Process(double operand1, double operand2) { return operand1 * operand2; } } public class Divider : IProcessor { public double Process(double operand1, double operand2) { double…

Joining all column values into one string with concat function is a pain, especially for a table with large numbers of columns. This script concats all columns quickly. declare @sql varchar(max) SELECT @sql = ISNULL( @sql+ '' + '+'',''+', '') + 'convert(varchar(50), [' + c.name + '])' FROM sys.all_columns c join sys.tables t ON c.object_id = t.object_id WHERE t.name = 'herb' and c.name!='id' EXEC( 'SELECT ' + @sql + ' FROM herb') Result The script generates long string value of @sql as following: convert(varchar(50), [alias])+','+convert(varchar(50), [application])+','+convert(varchar(50), [attachment])+','+convert(varchar(50), [category])+','+convert(varchar(50), [caution])+','+convert(varchar(50), [chemical])+','+convert(varchar(50), [desc])+','+convert(varchar(50), [english])+','+convert(varchar(50), [enterdate])+','+convert(varchar(50), [extract])+','+convert(varchar(50), [form])+','+convert(varchar(50), [form1])+','+convert(varchar(50), [grow])+','+convert(varchar(50), [guijing])+','+convert(varchar(50), [image_all])+','+convert(varchar(50), [image_leaf])+','+convert(varchar(50), [image_root])+','+convert(varchar(50), [indication])+','+convert(varchar(50), [indication1])+','+convert(varchar(50), [locations])+','+convert(varchar(50), [medicalprop])+','+convert(varchar(50), [name])+','+convert(varchar(50), [notes])+','+convert(varchar(50), [origin])+','+convert(varchar(50), [pharmacology])+','+convert(varchar(50), [pinyin])+','+convert(varchar(50), [process])+','+convert(varchar(50), [source])+','+convert(varchar(50), [store])+','+convert(varchar(50), [url])+','+convert(varchar(50), [usage])

SQL Server 2019 Installation Download en_sql_server_2019_developer_x64_dvd_baea4195.iso file from MSDN Subscription. Mount iso file to file system. Run Setup.exe from the mounted CD-ROM. Don't worry about the warning, just ensure the ports are open if you need remote access. Now the installation is completed and ready to use. Let's see other options in the installation SSMS - SQL Server Management Studio Download SQL Server Management Studio (SSMS) SSAS,SSRS,SSIS Install SSDT Launch Visual Studio Installer Install SSAS, SSRS Project Templates Open Manage Extensions from Visual Studio menu. Search for ssdt. When you close Visual Studio the installation started automatically. Install SSIS Project templates Click on Download button, the linked browser will be open for download, or use this link to download Microsoft.DataTools.IntegrationServices.exe file. https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects Done Now open Visual Studio, the newly added project templates are shown in the list.

C# Using + operator Console.WriteLine("Hello" + " " + "String " + "!"); String Interpolation string author = "Mahesh Chand"; string book = "C# Programming"; string bookAuthor = $"{author} is the author of {book}."; String.Concatenate() method string fName = "Mahesh"; string lName = "Chand"; string Name = string.Concat(fName, lName); string[] authors = { "Mahesh Chand ", "Chris Love ", "Dave McCarter ", "Praveen Kumar "}; string arrayStr = string.Concat(authors); String.Join() method int[] intArray = { 1, 3, 5, 7, 9 }; String seperator = ", "; string result = "Int, "; result += String.Join(seperator, intArray); // Using String.Join(String, String[], int int) // Let's concatenate first two strings of the array String[] arr2 = { "Mahesh Chand ", "Chris Love ", "Dave McCarter ", "Praveen Kumar " }; String seperator2 = ", "; string result2 = "First Author, "; result2 += String.Join(seperator2, arr2, 1, 2); Console.WriteLine($"Result: {result2}"); String.Format() method string date = String.Format("Today's date is {0}", DateTime.Now); StringBuilder.Append() method builder.Append(", "); PHP Using . operator $b = "Hello " . "World!"; // slow String Interpolation $a = '3'; echo "qwe{$a}rty"; // double quote echo "Result: " . ($a + 3); // result 6 "{$str1}{$str2}{$str3}"; // one concat = fast   $str1. $str2. $str3;    // two concats = slow //Use double quotes to concat more than two strings instead of multiple '.' operators.  PHP is forced to re-concatenate with every '.' operator. $logMessage = "A {$user->type} with e-mailaddress {$user->email} has performed {$action} on {$subject}." Using printf() $logMessage = sprintf('A %s with email %s has performed %s on %s.', $user->type, $user->email, $action, $subject);