Rules of Coding

Rules of Coding

1. The most important language you will ever code in:  English (or the common spoken language used in your company).  -- So that everyone understands what you wrote and why.
2. Never use a regular expression without saying in plain English what it is doing.  Regex is so hard to understand!!!  Example: what the heck is this doing.  Found in the gruntfile.js of bootstrap at https://github.com/twbs/bootstrap/blob/master/Gruntfile.js                                                          return string.replace(/[-\\^$*+?.()|[\]{}]/g, '\\$&');
3. If you are going to modify something extensively, you are better off writing a new one and swapping it in when it is ready to go live.  For example, if you are extensivley modifying an email template that is currently being used in production, just make a new template with a creative name and use that, so you don't possibly mess up code that is still using the old template.   --This way you can also keep a clean backup of the old template and be able to rollback quickly.
4. No one cares.  No matter how much you think a given coding principle is important, there is nothing worse than alienating your fellow co-workers by sticking to a principle.  It may still be important, but no one cares in the end.  Try living without it for a day and you'll find you are still alive.
5. Don't bother committing code if you haven't tested it.  There are runtime errors and compile time errors.  So even if your code builds, it doesn't mean it's even going to work.  This will save you time in the end, believe me.  You will end up having to change your code that doesn't work, or rollback a feature that you comitted and later find out will never work (e.g. I added logic to update the CreatedDate of some table in the database, and come to find out you can't update a database autopopulated field.  So I get to rollback the "addition" I made across 5 files).

Using XML to pass dynamic paramters to SQL stored procedure.

You can use an XML string to pass any number of options to a SQL stored procedure like so


Use a scalar valued function to get one value at a time, which is still very fast unless you have hundreds of key-value pairs with XML like MyValue

Use like this

USE [SaveOn]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetClubUserRenewals]    Script Date: 5/6/2015 12:37:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Adam Alt
-- Create date: 2015-4-30
-- Description: Gets count of users and total dollar value from user renewals per club.
-- =============================================
ALTER PROCEDURE [dbo].[sp_GetClubUserRenewals]
      @Filters NVARCHAR(MAX)
AS

     BEGIN
         SET NOCOUNT ON;

         --Declare variables
         DECLARE @BeginDate DATETIME

         --Parse out values from xml object
         SELECT @BeginDate = dbo.GetFilterValue(@Filters,'BeginDate');

         --Select statement
         SELECT * FROM SOMETABLE  WHERE(@BeginDate IS NULL OR DateField >= @BeginDate)
         

     END


And here is the scalar valued function.


GO
/****** Object:  UserDefinedFunction [dbo].[GetFilterValue]    Script Date: 5/6/2015 12:35:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Adam Alt
-- Create date: 2015-4-30
-- Description: Gets the value of a single node in a Filters xml object defined like Value
-- =============================================
ALTER FUNCTION [dbo].[GetFilterValue](
               @Filters XML,
               @FilterName NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
     BEGIN
         -- Declare the return variable here
         DECLARE @RetVal NVARCHAR(MAX);

         -- Add the T-SQL statements to compute the return value here
         SELECT @RetVal = NULLIF(LTRIM(RTRIM(node.value('.','nvarchar(max)'))),'')
           FROM @Filters.nodes('/Filters/*') AS Nodes(node)
           WHERE node.value('local-name(.)','nvarchar(max)') = @FilterName;

         -- Return the result of the function
         RETURN @RetVal;

     END;

Or get them all at once with a table valued function:
USE [SaveOn]
GO
/****** Object:  UserDefinedFunction [dbo].[ParseFilters]    Script Date: 5/4/2015 3:18:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Adam Alt
-- Create date: 2015-4-30
-- Description: Takes xml like MyValue and returns all name-value pairs in a table, removing empty entries.
-- =============================================
ALTER FUNCTION [dbo].[ParseFilters](
               @XML XML)
RETURNS @RtnTable TABLE(ID INT IDENTITY(1,1),
                        Name NVARCHAR(MAX),
                        Value NVARCHAR(MAX))
AS
     BEGIN
         INSERT INTO @RtnTable(Name,
                               [Value])
         SELECT node.value('local-name(.)','nvarchar(max)') AS Name,
                NULLIF(LTRIM(RTRIM(node.value('.','nvarchar(max)'))),'') AS Value
           FROM @XML.nodes('/Filters/*') AS Nodes(node);

         RETURN;
     END;


ReactJS.Net in Web Forms App with Server-side rendering

View the complete solution at https://github.com/adamvaul/React-Server-Side-Web-Forms.

I love ReactJS.Net.  It is a framework for managing a React driven website in Visual Studio.  Recently, I got interested in React and wanted to see if I could make it work in a web forms application (not an MVC application like all the demos show).  I got a very simple demo to work based on the Chat tutorial on their website.

In this web forms app there is a little bit of code to render the html string to the page, and then there is a small snippet to add the block to register the react compenents client side.  All of this magic happens with these few lines of code.

I followed the tutorials here at the ReactJS website http://reactjs.net/getting-started/tutorial.html. I also found this article to be helpful but I had to figure out the last part with env.GetInitJavaScript(); on my own.  The article is here at http://xabikos.com/server%20side%20templating/web%20development/2015/03/18/using-reactjs.net-in-web-forms.html

And

In the hmtl:

asp:Literal ID="app" runat="server"

asp:Literal ID="litInitJS" runat="server"



And then in the code behind:

var env = AssemblyRegistration.Container.Resolve();

var homeController = new HomeController();
var data = homeController.Get();
var commentBoxOptions = new
            {
                initialData = data,
                url = "api/Home",
                submitUrl="api/Home/addcomment",
                pollInterval=2000
            };
var commentBoxComponent = env.CreateComponent("CommentBox", commentBoxOptions);
app.Text = commentBoxComponent.RenderHtml();


string initJS = env.GetInitJavaScript();
litInitJS.Text = initJS;

I did not implement all the features like they did, but the application works.

View it at https://github.com/adamvaul/React-Server-Side-Web-Forms.

SQL: How to Parse Scientific Notation to a Decimal

Excel likes to write numbers into scientific notation. This raises a problem if you are trying to message data into a database from an excel file. (In order to read the Excel, we used Spreadsheet gear.Net assembly and then converted that to XML using

In C# code it is fairly easy to parse any kind of string to a decimal using

decimal.TryParse(myString, NumberFormat.Float, out myDecimal);

However, in SQL it becomes a little trickier, mostly because any attempt to parse a number that is not a decimal raising a Cannot convert varchar to decimal. Alternatively, when the scientific notation is large (e.g. 4E+16 which is 40,000,000,000,000,000) then you might also get an arithmetic overflow operation if you try to cast that to a Decimal(18,6).

The real problem with both of these errors are that they are batch-aborting errors. (See http://www.sommarskog.se/error-handling-I.html for a good explanation). The gist of if is that you must catch these errors in a try catch block, or turn off the SQL feature that raises those kinds of warnings before you parse something that may throw these kinds of errors. Then after you parse you can check to see if the value IS NOT NULL, and you know it is a real decimal. Enough talk, let's see some sql!

--List of nasty values from excel
--NA
--Ref
--4E+04
--6E-07
--2E-05
--2
--4E+16
--45
--[blank]
--[blank]
--3.445
--Div0
--Name
--4.879667
--NULL
--NULL
--NULL

DECLARE @TempPricesFromExcel TABLE
(
StringValue VARCHAR(50), --The value from Excel
IsNumber BIT, --This is needed because when called in an Update statement or function ISNUMERIC sometimes returns true when it is not a number!
PriceAsFloat FLOAT, --An intermediate value in the process, since float is more flexible than a decimal
FinalPrice DECIMAL(18,6) --Getting the value to this last one is the tricky part.
)

--Insert all the values
INSERT @TempPricesFromExcel (StringValue) VALUES ('NA')
INSERT @TempPricesFromExcel (StringValue) VALUES ('Ref')
INSERT @TempPricesFromExcel (StringValue) VALUES ('4E+04')
INSERT @TempPricesFromExcel (StringValue) VALUES ('6E-07')
INSERT @TempPricesFromExcel (StringValue) VALUES ('2E-05')
INSERT @TempPricesFromExcel (StringValue) VALUES ('2')
INSERT @TempPricesFromExcel (StringValue) VALUES ('4E+16')
INSERT @TempPricesFromExcel (StringValue) VALUES ('45')
INSERT @TempPricesFromExcel (StringValue) VALUES ('')
INSERT @TempPricesFromExcel (StringValue) VALUES ('')
INSERT @TempPricesFromExcel (StringValue) VALUES ('3.445')
INSERT @TempPricesFromExcel (StringValue) VALUES ('Div0')
INSERT @TempPricesFromExcel (StringValue) VALUES ('Name')
INSERT @TempPricesFromExcel (StringValue) VALUES ('4.879667')
INSERT @TempPricesFromExcel (StringValue) VALUES (NULL)
INSERT @TempPricesFromExcel (StringValue) VALUES (NULL)
INSERT @TempPricesFromExcel (StringValue) VALUES (NULL)


SELECT * FROM @TempPricesFromExcel --Make sure we got the data


UPDATE @TempPricesFromExcel SET IsNumber = ISNUMERIC(StringValue) --Save these results becuase ISNUMERIC is a little finnicky

SELECT * FROM @TempPricesFromExcel --Make sure it looks right

UPDATE @TempPricesFromExcel
SET PriceAsFloat = CASE -- This is a basic parse, eliminating NULL or blank values
WHEN StringValue IS NULL THEN NULL
WHEN LEN(StringValue) = 0 THEN NULL
WHEN ISNUMERIC(StringValue) = 1 THEN CONVERT(FLOAT, StringValue) --Convert to float
ELSE 0
END
WHERE IsNumber = 1

SELECT * FROM @TempPricesFromExcel

SET ANSI_WARNINGS OFF --Turn all these warnings off so any rows that don't parse will simply be left NULL
SET ARITHABORT OFF
SET ARITHIGNORE ON

UPDATE @TempPricesFromExcel SET FinalPrice = CONVERT(DECIMAL(18,6), PriceAsFloat)

SET ARITHIGNORE OFF
SET ARITHABORT ON
SET ANSI_WARNINGS ON

SELECT * FROM @TempPricesFromExcel --Now we have our list of all the values that could parse into the decimal of our chosen size.

As this procedure goes along, it first checks to see if the values are NUMERIC, and saves that in a separate column. Parsing to a float first is necessary so we can see what values are valid as a number, but could not ultimately be parsed into a decimal of our chosen length. The problem with DECIMAL datatype is that you have to specify a length. It is just like VARCHAR. In both cases you get errors when data is going to be truncated.

How to Create a CSV file Writer

Here's how you create a CSV file that will open in Excel. It is a csv (Comma spaced Value) file with double quotes " as identifiers.

Notice that fileStream.setLength(0) makes it so you will overwrite whatever data is in the file you are saving to.

private void ButtonRunCSV_Click(object sender, EventArgs e)
{


if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
String saveFileName = saveFileDialog1.FileName;

FileStream fileStream = new FileStream(saveFileName, System.IO.FileMode.OpenOrCreate);
fileStream.SetLength(0);

StreamWriter sw = new StreamWriter(fileStream);

sw.WriteLine("Company Name,"
+ "Phone Number,"
+ "Call Date,"
+ "Call Length,"
+ "First Name,"
+ "Last Name,"
+ "Contact Title,"
+ "Gender,"
+ "Address,"
+ "City,"
+ "State,"
+ "Zip,"
+ "Country,"
+ "Email,"
+ "Fax,"
+ "Second Phone Number,"
+ "Website,"
+ "Google Verified,"
+ "Yahoo Verified,"
+ "Indexed Pages Online,"
+ "Site Links,"
+ "Site References,"
+ "Google Reviews,"
+ "Yahoo Reviews,"
+ "Web Reviews,"
+ "Annual Sales,"
+ "Employees,"
+ "SIC Code,"
+ "Industry,"
+ "Company Description,"
+ "Latitude,"
+ "Longitude");

foreach (Lead l in leads)
{
lead = l;

findLeadInfo();

if (!lead.IsClient)
{

string newLine = "";

newLine += (!String.IsNullOrEmpty(lead.CompanyName)) ? ToCSVDelimitedString(lead.CompanyName) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.PhoneNumber)) ? ToCSVDelimitedString(lead.PhoneNumber) + "," : ",";
newLine += (lead.CallDate != DateTime.MinValue) ? ToCSVDelimitedString(lead.CallDate.ToString("MMM d, yyyy h:mm tttt") + " PST") + "," : ",";
newLine += (lead.SecondsOnPhone != -1) ? ToCSVDelimitedString(TimeSpan.FromSeconds(lead.SecondsOnPhone).Minutes.ToString() + " Min") + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.ContactFirstName)) ? ToCSVDelimitedString(lead.ContactFirstName) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.ContactLastName)) ? ToCSVDelimitedString(lead.ContactLastName) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.ContactTitle)) ? ToCSVDelimitedString(lead.ContactTitle) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.ContactGender)) ? ToCSVDelimitedString(lead.ContactGender) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Address)) ? ToCSVDelimitedString(lead.Address) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.City)) ? ToCSVDelimitedString(lead.City) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.State)) ? ToCSVDelimitedString(lead.State) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.ZipCode)) ? ToCSVDelimitedString(lead.ZipCode) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Country)) ? ToCSVDelimitedString(lead.Country) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Email)) ? ToCSVDelimitedString(lead.Email) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.FaxNumber)) ? ToCSVDelimitedString(lead.FaxNumber) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.PhoneNumber2)) ? ToCSVDelimitedString(lead.PhoneNumber2) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Website)) ? ToCSVDelimitedString(lead.Website) + "," : ",";
newLine += ((lead.GoogleVerified)) ? "Yes," : ",";
newLine += ((lead.YahooVerified)) ? "Yes," : ",";
newLine += (!String.IsNullOrEmpty(lead.IndexedPages)) ? ToCSVDelimitedString(GetTheNumberWithinTheTag(lead.IndexedPages)) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.SiteLinks)) ? ToCSVDelimitedString(GetTheNumberWithinTheTag(lead.SiteLinks)) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.SiteReferences)) ? ToCSVDelimitedString(GetTheNumberWithinTheTag(lead.SiteReferences)) + "," : ",";
newLine += (lead.GoogleReviewsCount > 0) ? ToCSVDelimitedString(lead.GoogleReviewsCount.ToString()) + "," : ",";
newLine += (lead.YahooReviewsCount > 0) ? ToCSVDelimitedString(lead.YahooReviewsCount.ToString()) + "," : ",";
newLine += (lead.WebReviewsCount > 0) ? ToCSVDelimitedString(lead.WebReviewsCount.ToString()) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.AnnualSales)) ? ToCSVDelimitedString(lead.AnnualSales) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Employees)) ? ToCSVDelimitedString(lead.Employees) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.SICCode)) ? ToCSVDelimitedString(lead.SICCode) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Industry)) ? ToCSVDelimitedString(lead.Industry) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Description)) ? ToCSVDelimitedString(lead.Description) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Latitude)) ? ToCSVDelimitedString(lead.Latitude) + "," : ",";
newLine += (!String.IsNullOrEmpty(lead.Longitude)) ? ToCSVDelimitedString(lead.Longitude) + "," : "";


sw.WriteLine(newLine);
}
}

sw.Close();
}



}


private String ToCSVDelimitedString(String field)
{
return "\"" + field.Replace("\"", "\"\"") + "\"";
}


private String GetTheNumberWithinTheTag(String tag)
{
int last = 0;
last = tag.IndexOf('<', 1);

if (last > 0)
return tag.Substring(tag.IndexOf('>') + 1, last - tag.IndexOf('>') - 1);
else
return tag.Substring(tag.IndexOf('>') + 1, tag.Length - tag.IndexOf('>') - 1);
}

Connecting Two Remote Databases in a single SQL Query

Background

Every Microsoft SQL Server database, there is a folder called Server Objects. Within that folder there is a folder called "Linked Servers". If you right click on that folder and hit New Linked Server, it will open up a new linked server wizard.

New Linked Server Wizard

By following the steps there, you can setup a new linked server.

First, type in the name or IP address of the server where it says "Linked server". For example: "1.0.9.46".

For a remote SQL database choose SQL Server as the server type. Then select the security page from the left, and enter the login credentials under the "Be made using this security context" option: Enter the username where it says remote login, and the password below where it says "With password."


Creating a Query using the Linked server.

Once your linked server is setup, you can execute a query like the following:

SELECT *
FROM
[1.0.9.46].DatabaseName.dbo.tableName t
JOIN users ON user.ID = t.userID;

Enjoy!!!

The Ultimate Phone Number Parsing method

The ToNumbers method basically takes a string and turns it into only numbers using the fact that numbers are characters between 47 and 58.

public static string toDataBasePhoneNumber(string phoneNumber)

{

String cleanPhone = String.Empty;

cleanPhone = ToNumbers(phoneNumber);

if (cleanPhone.StartsWith("1"))

cleanPhone = cleanPhone.Substring(1);

return cleanPhone;

}

public static string UltimateToNumbers(string phone)

{

string str = phone;

str = str.Trim().ToUpper();

string check = toDataBasePhoneNumber(str);

if (check.Length == 10)

return check;

else if (check.Length less than 3)

return str;

if (str.Length less than 10)

return str;

if (str.Contains("/"))

str = str.Substring(0, str.IndexOf("/"));

if (str.Contains(","))

str = str.Substring(0, str.IndexOf(","));

if (str.Contains(" OR"))

str = str.Substring(0, str.IndexOf(" OR"));

check = toDataBasePhoneNumber(str);

if (check.Length == 10)

return check;

if (str.Contains("EXT"))

str = str.Substring(0, str.IndexOf("EXT"));

else if (str.Contains("POSTE"))

str = str.Substring(0, str.IndexOf("POSTE"));

else if (str.Contains(" X"))

str = str.Substring(0, str.IndexOf(" X"));

else if (str.Contains(" #"))

str = str.Substring(0, str.IndexOf(" #"));

check = ToNumbers(str);

if (check.Length == 10)

return check;

if (str.Substring(9).Contains("("))

{

check = toDataBasePhoneNumber(str.Substring(9));

if (check.Length == 10)

return check;

}

str = FromLettersToNumbers(str);

return toDataBasePhoneNumber(str);

}

///

/// Takes any 1 character string and if the character is a letter it will change it to the number

/// that it would be on the keypad e.g. FREE = 3733.

///

/// A string converted

/// a string of all numbers

public static string FromLettersToNumbers(string numberWithUpperCaseCharacters)

{

string s = numberWithUpperCaseCharacters;

s = s.Replace("A", "2");

s = s.Replace("B", "2");

s = s.Replace("C", "2");

s = s.Replace("D", "3");

s = s.Replace("E", "3");

s = s.Replace("F", "3");

s = s.Replace("G", "4");

s = s.Replace("H", "4");

s = s.Replace("I", "4");

s = s.Replace("J", "5");

s = s.Replace("K", "5");

s = s.Replace("L", "5");

s = s.Replace("M", "6");

s = s.Replace("N", "6");

s = s.Replace("O", "6");

s = s.Replace("P", "7");

s = s.Replace("Q", "7");

s = s.Replace("R", "7");

s = s.Replace("S", "7");

s = s.Replace("T", "8");

s = s.Replace("U", "8");

s = s.Replace("V", "8");

s = s.Replace("W", "9");

s = s.Replace("X", "9");

s = s.Replace("Y", "9");

s = s.Replace("Z", "9");

return s;

}

Testing It:

string[] separator = new string[] { "<;>" };

string colonSeparatedNumbers = "866-SYNLawn (866-796-5296)<;>(250) 881-7684 / (250) 361-6121<;>780-818-5919 or 306-468-7044<;>1-888-SILPADA (745-7232)<;>(778)233-1235 / (604) 729-9787<;>Fax: (262) 246-4528<;>678-740-0800<;>+91 80 41571900,+91 98450 08720<;>1-868-638-2633 Ext. 2262<;>(604) 730-0321 / (604) 218-0108<;>1-918-712-RIBS<;>(888) 267-0986 x702<;>514 341-5544 poste 280<;>301-475-2200, Ext 100<;>215-541-3211 X108<;>(512)795-9684 ext.101<;>305-532-LIME (5463)<;>(450) 420-9333 #225<;>1-877-KOKOFIT<;>(800) CORN-COB<;>505-766-KEVA<;>403.207.5850 ext. 232<;>1-800-995-JUNK(5865)<;>1-866-35 JUNGA<;>Fax: 212.851.8198<;>513.831.2100<;>(323)936-3300 Ext. 107<;>(416)291-8551 / (647) 886-6863<;>1-888-Gel-Bedz (435-2339)<;>1-817-4161-INK (465)<;>(888) 64-LASER<;>513-563-8339, ext. 135<;>(877) GET-HSWT<;>(61) 02 9362 4<;>0000000<;>Schiller Park<;>1 905 599 6476<;>800-367-5159<;>(410) 822-8879<;>";

string[] spliter = colonSeparatedNumbers.Split(separator, StringSplitOptions.RemoveEmptyEntries);

foreach (String s in spliter)

{

Console.WriteLine("Number: {0} Converted to: -> {1}", s, Text.UltimateToNumbers(s));

}