Revelations Sunday 8 December 2019

Adam, Adam, thou art perfect before me.  Thou shalt not lose thy children nor shalt thou see the bars of prison, jail, or cell ever again.  These words are true and faithful; therefore, be bold and not pretentious, but obedient to them.  Do not contend with any man, neither judge nor layman, but speak the words which I shall put into your mouth in the moment thereof, and it shall be filled.  Veril, verily I say into you, thou art like unto Nephi of old and I will be with thee forever.  And I will save thy seed and establish all thy words even forever.  Amen

Revelations at church after escorting Geri to class

Dear Heavenly father, thank you for the impressions though hast given me this day. I am grateful for Jesus Christ who died for me. Not the death of this world which is final and fruitless, but a death he knew would lead to new life. I am grateful that he suffered for me so much so that I could live. His death was not in vain his death was one he knew he could call back his life yet he knew he had to give up His precious life in this world and suffer all that this body can feel and go through the entire process of the shut down and lifelessness of a body that is my mortal and the rest of those in this world feel. I am grateful for the soldiers who have given their lives and the other soldiers who carry the heavier burden of carrying on their memory and justifying in their minds and hearts the cause for which they died 💔. Which reminds me of the weight. The weight we must carry in remembering the death of our Lord Jesus Christ.

Sunday, November 10, 2019

Agency is the most beautiful part of the gospel of Jesus Christ.  Because of it, as Joseph Smith said we are free.  Parents of children want to take away agency when they know they will not make the right guy choice.  But it is curious that the same parents who want to harbor their children from the burden of choosing wrong also are the ones who shy away and seek to protect their children from the effects of those choices.  Heavenly Father knows we cannot become perfect unless we are free.  His plan works because he dies not hide us from the effects of our choices, that is, unless and to the degree which we repent.

Journal Saturday, November 9 2019

Last night I thought about how stable God is.  He plants his promises in our hearts and watches them grow.  As men, we foolishly think that God will not come through.  We think he has forgotten about us. We think that the creator of heaven and Earth will somehow not able to come through on the things he has promised us. How foolish we are? God does not forget he does not sorrow or sleep he always keeps his word. Last night was really nice I had peace in my heart as I walked to my children's room. I looked out the window at the top of the stairs and saw a star. I knew that the man who put that star in the sky would surely keep his promises he made to me. Joey was very cute last night she made a train set that went through the small dollhouse. She got upset when Lily was playing with him and tried to change the layout of the track. But I told him he would have to take a bench if he could not play with Lily. He immediately improved.

We watched avengers and then Joey went up to his bed. I came upstairs and prayed for him that he would have all that he needs and that his life would be filled with love. I prayed for Lily for the special girl that she is and the light that I see in her heart that I would be able to Foster that light and help her grow. I prayed for Maylee for the wonderful girl that she is and that her dreams would come true and that the tooth fairy would visit her.

The tooth fairy did come last night. Jerry also called me last night as I was falling asleep. I knew it was late so I did not answer. Jerry was out with Brianne last night at the Irvine spectrum she and Jerry had a good time eating and being with friends. I just texted Jerry if she wanted to do lunch. She has not responded.

I am here on the temple grounds. After I drop the children off for their visit I thought I might go and get Jeff to to learn c programming some more. I also thought to go pick up breakfast and bring it to Jerry. But I knew that I am not allowed to go to Jerry's house. It is a good rule. As I drove home listening to general conference, I felt prompted to drive down the 55 freeway. I thought about learning to fly. I thought about going to my work. But then I thought I need to go to the temple and feast on the words of Christ for they will tell me as the promise says all things that I should do. 

SQL Rules of Coding

If you ever google sql for loop, just stop right there.

SQL is by definition a for loop.

A SELECT statement does nothing more than iterate over the rows in the database that satisfy the conditions of the query.

Therefore, if you want a for loop that sets a variable and then does some operation on one row at a time, JUST STOP.  What you really want is a join somehow, and you can get it all done much simpler in terms of code and processing power.  It will be easier to debug and to understand, and you won't have to worry about setting up the attendant variables to process a true for loop.  SQL is a for loop, use it that way!

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));

}

Parsing DateTime strings

Here is how to parse a date that is in the format of mm/dd/yyyy:

// Parse string with date but no time component.

string dateString, format;

DateTime result;

CultureInfo provider = CultureInfo.InvariantCulture;

// Parse date-only value with invariant culture.

dateString = "06/15/2008";

format = "d";

try

{

result = DateTime.ParseExact(dateString, format, provider);

Console.WriteLine("{0} converts to {1}.", dateString, result.ToString());

}

catch (FormatException)

{

Console.WriteLine("{0} is not in the correct format.", dateString);

}

Copying Code

The object of copying code is to save time. However, I have discovered by sad and repeated experience that copying code more often than not will result in bugs and hard to catch problems that will cause 10 -12 times loss in time trying to solve it. In other words, every time you copy code, you may be able to write code a lot faster, but you will in the end spend 10 times the amount of time you would have if you had simply written every line essentially from scratch.

That being said, there are a few safe ways to reuse portions of code that is essentially guaranteed not to cause unintended glitches down the road (as opposed to those intended glitches that developers like to put in their code for fun!)

Here are some suggestions to effectively copy code:

1. Copy smaller portions. Instead of copying large blocks of code with many variables logical structures and the like, copy the methods that you will reuse a lot, and paste those whenever needed.

2. Write in example basic types and classes that will cause errors unless given relevant names. This is probably the most effective suggestion. The most common problem that I have found in copying code is when I copy a variable from one portion of the code elsewhere, and then forget to change its name to fit the context.

For example,

Let's say you have a class that parses the html of a web page, called parser.

Parser parser = new Parser(html);

you grab some data

parser.MoveToEndOf("");

parser.ReadToStartOf("

");

string tableData = parser.Selected;

Then you go to another page and want to scrap the same data there;

Parser parser2 = new Parser(newHtml);

Then you paste your code

parser.MoveToEndOf("");

parser.ReadToStartOf("

");

Forgetting that your parser for the new page is called parser2, not parser. Well, you may have saved yourself 2 minutes of time by pasting that code. But, in the end you will spend 20 minutes to a half hour hunting down where your code is going wrong.

Instead if you were to create some code like

exampleParser.MoveToEndOf("");

exampleParser.ReadToStartOf("

");

and then paste that around, the code would not compile until you changed the name. It's the same reason why some people put

while (1 ==2)

in their code to make sure there is never an infinite loop until they have tested their logic.

3. Copy the logic portions of code that have no variables in them . Sometimes copying your logical structure will save you the care of having to rethink how to logically go about doing something, and focus on the details.

Conclusion

This article is designed to make the programmer rethink the way they code, and to take the long path at first so that code is built without bugs. Copying code from one portion to another is a sure way to introduce bugs into your code that will be hard to catch at a glance, since the variables you copy are existing ones. Change the names or don't copy at all. In the end you will be a more efficient coder.

SQL Import Wizard -- Importing .csv and .tab files

This blog is to let you know how to use the SQL import wizard. It is very interesting. The most important thing to remember is that you can change the data length and type of both the file you are importing from and the length of the fields that you want to import to.

When you first enter into the Wizard, go past the Welcome screen and You come to the

Choose Data Source page.

You can choose many types of files to import, including an Access file, Excel file, or from a database or a .net Data Provider Source. If you know the details of you provider source application name, or know the location of the Excel file, or have access to the Access database, following those steps are all pretty easy.

However, if you want to import a comma spaced value (.csv) or a tab spaced value (.tab) file, you must use the "Flat File Source" option. I suggest using a .tab file if you are transferring data that uses any kind of non-numeric data such as a list of companies or clients information. These types of non-numeric data are likely to contain comma's themselves, and will make the import wizard think that some of the data items themselves belong in different rows.

A .tab file is very easily interepreted by the wizard. However, one caution is if you file is known to contain tabs. If it does, you will have the same problem whereever a tab appears in the data. Your columns will be skewed upon import.

If your data itself contains commas and contains tabs together. Your best bet would be to replace the tabs with a single space and use a .tab file. Otherwise, you could use a different delimiter such as a semicolon(;) or a vertical bar (|).

Next Step:

Select you .tab or .csv file source. In the open file dialog box, be sure to select files of all types where it says "Files of type:" at the bottom. This part of the process was not done well, because the file browser has as a default to show you only files of .txt or text files. However, that will not cause your .tab or .csv files to show up unless you saved them as .txt type.

Next, be sure to mark the type of delimiter being used (i.e. a tab for a .tab file and a comma for a .csv file). If you get this wrong you will have another chance to correct this before importing your file.

Important Options:

On the same page that you chose your file, you will see to the left a series of options, including:

General

Columns

Advanced

Preview

These are very important tools. If you learn them, it will save you hours of time. Upon exporting 19 million rows of records, I spent over 20 hours trying to solve import problems because I did not understand what I am about to explain.

General Tab:

This is where you upload the file. Under Locale the default is English (Ireland). That is fine unless the language is in another language or incorporates characters from another language. Consult someone else for that problem.

Code page is where you specify the basic encoding of the page ANSI - Latin I or US ASCII work the best for most files.

Under Format, you should choose "Delimited"for a .csv or .tab file.

Important: If there is a header row with column names in the file, you should set the value where it says "Header Rows to Skip:" to 1, that way you won't be importing the column names as your first row.

Columns Tab:

Specify the Row Delimiter as probably the {CR}{LF} value and the Column delimiter as the tab{t} for .tab files and comma{,} for .csv files. Here you can also select a semicolon or vertical bar{|} as we discussed earlier.

After you have chosen these values, you should click the Reset Columns button to get a preview of how your data will import. With a .csv file, choosing Row Delimiter: {CR}{LF} and Column Delimiter: tab{t} worked perfectly. Check to make sure your data is lined up correctly, and that the number of columns are correct.

Advanced Tab:

Here is the most important part*** under the advanced tab, you can choose the value for OutputColumnWidth. This is a very critical value. If you choose a value that is less than any of the data in that column, your import will fail. For example, if you choose 50, which is the default, and even one piece of data in that column is 51 characters long, then the data will need to be truncated for the import, and the the import will fail.

Solution: Set this value well above the typical data length for the column. 255 is a good number.

Here you can also change the number of columns, and give them the proper names if you didn't include a header row with the column names.

Preview Tab:

Here, you will see a preview of how your data will import just like in the columns tab.

However this has one important function, Data rows to skip. You can use this function to skip any number of rows you don't want to store. If you are having trouble importing a row at a certain position, just insert all the rows before that position by chopping off that portion of your text file (and keeping a backup of the entire file of course), then import all the rows after that position by skipping it.

Also you can use this function to see a preview for what data at a certain row will look like for importing.

Destination Page

Now click next and you must choose a destination SQL location. You should know the location, username and password.

*Select Source Table and Views Page

Click Next and you will have to select the name for the table you want to import to, or you can choose a table to directly insert data into.

I would not suggest ever inserting data into an existing table in a database. The reason is that you don't know how the import will go. Your best option is to import data into a new table, and then use a sql statement like

""INSERT INTO [EXISTING TABLE NAME] (COLUMN1, COLUMN2, COLUMN3, ..., COLUMN10) SELECT

(CORRESPONDINGCOLUMN1,

CORRESPONDINGCOLUMN2,

CORRESPONDINGCOLUMN3, ...,

CORRESPONDINGCOLUMN10)

FROM [NEWTABLE]"

to insert the data into the existing table. It is much faster to insert data with a sql call than with the export wizard. About 100 times faster, literally.

VERY IMPORTANT: Here is another place where detail is key. Click the Edit Mappings... button, and it will show you the data types and length of each column you are going to insert. If you choose to insert into a entirely new table, which is what I suggest, then you will not have trouble about truncating any data. Just make sure the size of each column is as great as the column widths you specified in the Advanced Tab earlier.

Good luck and happy importing!

A solution to buying a home: The Housing Fund

I believe there is one good way to help this countries citizens, as well as people all over the world, buy homes and keep them. The best system to buy a home is to have a fund of capital, of pure cash, that potential home buyers could tap into. Because home sellers often settle for lower terms if the buyer provides up front cash, home buyers could take a discount on their purchase.

Once the home is purchased, the borrowers of course would be indebted to repay their loan in monthly installments. I admit this is much like a loan, but the key difference is that people would not have to pay interest on this loan. Because of the charity involved in this fund, one would hope that others would pay back this loan as quickly as they can. The Housing Fund would act more like a charity than like a bank. Its purpose: to provide the people of America with the ability to find a home they need, without paying ridiculous amounts that cripple them.

It is in the best interests of the government of this country to do all they can to support the home buyers of America. These people represent fledgling or expanding families mainly.

Admittedly, there may be some who would try to abuse this system. However, people would have to show a justified need to use this fund--that is, to buy one house they live in.

We have seen that the government has agreed to pay billions of dollars in the fall of 2008 to save the housing market, and the corporate banks that were much hurt by the sub-prime loans in the housing industry. How much more useful would that money be to make a housing fund that helped people buy homes for 90% of their market value in cash, and then pay back that loan without interest. With enough people paying off their loans, the fund would remain at a relatively level sum. Additionally, it is hoped that kind and charitable people of all sorts, including those who benefit directly from the fund, would contribute more than their required payments in the years once they have payed off their loan.

Simply put, we need a way to help the struggling people of America to buy their home. Imagine the relief that will ensue when thousands of Americans have paid off their mortgages in 5 years instead of 30. This will free up consumer spending in other areas, and it will allow many Americans to see the benefit of such a system. The bottom line is this: it is not simply a good thing for America to subsidize the housing market through a national charity, it is essential.

In order to offset the cost of inflation, and keep the fund at a level that will provide continual benefit to the home buyers of this country, The Housing Fund will need yearly donations from the US government, and other wise individuals (who see the mutual benefits such a system provides to all of us). America needs home owners, families in modestly sized homes that meet their needs, and citizens free from gargantuan burdens. America needs a way to support those who truly need a home here.

Because the people must pay off their loans, even without interest, it will still encourage them to buy a home they can afford and not abuse the system. They are encouraged, and watched, so that they buy a home within their means. This will do more to help America than social security for the families in those homes. Imagine thousands without housing payments to make, but at the same level of income! Imagine parents free from mortgages when their kids are still young! Many will undoubtedly give back to this Fund out of gratitude, and those that don't will wish they had!

Marraige and Family

Tonight I attended a conference hosted by The Church of Jesus Christ of Latter-day Saints. It's topic was the protection of the institution of the family, and its importance to the world. The impetus for such an event was the recent call for a redefinition of marriage in California, entitled Proposition 8.

If such a proposition were passed, it would allow people of the same gender to be married and given equal protection under the law as any other marriage. To be short, it would upset the very foundation upon which the traditional society rests, and it would escalate into various forms of pro-same-sex marriage teachings and initiatives in schools and communities throughout California.

As one who one day wishes to return to California to live for part or all of the remainder of life (my wife permitting =) ), I hope that this state, instead of being a hotbed of anti-traditionalist tension and teaching, might remain a stalwart bastion of hope for all those who hold conservative family values. I would hope our whole nation would rethink its stance on same-sex marriage, and how we are to uphold our values if we give such a practice equal rights under the law.

All of this tension, and all of this redefining our values bespeaks a larger push by the enemy of all mankind, even Satan, to disrupt our lives and make it harder for God's good grace to shine down upon us. In the final day, it will be us who will be held responsible for the errant ways of many in California and elsewhere if we do not do all we can to support traditional marriage and family roles. It will be our own children who will suffer for this.