Extract email address with SQL UDF

April 15, 2006 1 comment
This is a nice little user defined function that can extract email addresses from a block of text. I got the split function from planet-source-code, just to give credit where due.
 
CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
    BEGIN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)
    — HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
    —     ERO FIRST TIME IN LOOP
    SELECT @INDEX = 1
    — following line added 10/06/04 as null
    —      values cause issues
    IF @String IS NULL RETURN
    WHILE @INDEX !=0
        BEGIN 
         — GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
         SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
         — NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
         IF @INDEX !=0
          SELECT @SLICE = LEFT(@STRING,@INDEX – 1)
         ELSE
          SELECT @SLICE = @STRING
         — PUT THE ITEM INTO THE RESULTS SET
         INSERT INTO @Results(Items) VALUES(@SLICE)
         — CHOP THE ITEM REMOVED OFF THE MAIN STRING
         SELECT @STRING = RIGHT(@STRING,LEN(@STRING) – @INDEX)
         — BREAK OUT IF WE ARE DONE
         IF LEN(@STRING) = 0 BREAK
    END
    RETURN
END
CREATE FUNCTION get_email
 (  @TextContainingEmail varchar(1000)  )
RETURNS varchar(1000)
AS
BEGIN
 declare @retval varchar(1000)
 select top 1 @retval=items from dbo.split(@TextContainingEmail,’ ‘)
 where items like ‘%@%’
 return @retval
END
 
Then to use it, you can call
 
select dbo.get_email(‘my email address is bob@microsoft.com’‘)
 
 
Categories: Uncategorized

Export Google Search Results to CSV

April 15, 2006 Leave a comment
I just wrote a handly function which uses the Google API to do a search, then export the results in CSV, for easy import into a database for further processing. Be aware, that this will probably max out your quota with one call, so be careful with it.
 

public void GoogleSearchToCSV(string SearchString, string FileName)

{

GoogleSearchService.GoogleSearchService gss = new GoogleSearchService.GoogleSearchService();

string CSV = "";

try

{

for(int i=170;i<10000;i+=10)

{

GoogleSearchService.GoogleSearchResult gsr = gss.doGoogleSearch("L3Pyyh5QFHKU740wXUu0/aBu17loGATB",SearchString,0,10,

false,"",false,"","","");

foreach(GoogleSearchService.ResultElement reSearch in gsr.resultElements)

{

CSV += reSearch.title.Replace(",","").Replace("n","") + ",";

CSV += reSearch.snippet.Replace(",","").Replace("n","") + ",";

CSV += reSearch.URL.Replace(",","").Replace("n","") + "n";

}

Debug.Write("Progress:" + i.ToString());

}

}

catch(Exception ex)

{

Debug.Write(ex.ToString());

}

FileStream fsOut = new FileStream(FileName,FileMode.Create);

StreamWriter swOut = new StreamWriter(fsOut);

swOut.Write(CSV);

fsOut.Close();

}

 
Categories: Uncategorized

Writing a C# Application without a user interface

April 12, 2006 Leave a comment
I was looking to write a C# program which had no particular need for a user interface. My first stop was to develop a windows service, however, this proved too difficult to debug, so I decided to re-implement it as a windows forms app, with the form removed.
 
This application needed to poll a connection, every 5 seconds, and run idefinitely.
 

public System.Timers.Timer tmrPoll = new System.Timers.Timer();

[STAThread]

static void Main()

{

VoiceMailMain vmmInstance =

new VoiceMailMain();

vmmInstance.tmrPoll.Enabled =

true;

vmmInstance.tmrPoll.Interval = 5000;

vmmInstance.tmrPoll.Elapsed += new

System.Timers.ElapsedEventHandler(vmmInstance.tmrPoll_Tick);

System.Threading.Thread.CurrentThread.Suspend();

}

public

void tmrPoll_Tick(object sender, System.Timers.ElapsedEventArgs e)

{ … and magic happens … }

 

Simple when you know how!

 
Categories: Uncategorized

No mapping between account names and security IDs

April 12, 2006 7 comments
When installing a windows service, using installutil, it prompted for a username, and password, and
since my Administrator user didn’t have a password, I got the following error:
 
System.ComponentModel.Win32Exception: No mapping between account names and security IDs was done
 
After reading a few posts, I found a handy tip to solve this
 
Add this to the ProjectInstaller.cs file (in InitializeComponent)
this.serviceProcessInstaller1.Account = System.ServiceProcess.ServiceAccount.LocalSystem;
 
Et viola, it installs without prompting for a password.
 
 
 
Categories: Uncategorized

Uploading a file via a web service

April 11, 2006 Leave a comment
When you think uploading files with a web service, you may immediately think about using DIME and WSE 2.0 etc. However, I believe there is an easier way – depending on your application, I presume, but this approach may be useful.
 
I wanted to be able to allow people use standard File upload from a webpage
<input type="file"> – and then have the output of this passed to a webservice. – Also, I wanted to do it generically, so that if an application were to consume the webservice, the developer would not have to figure out how to cast a stream to a HttpPostedFile class.
 
Therefore, I used a very simple technique, use a byte array as a parameter to the webMethod.
– I then wanted to save the byte array as a file, with a random name, in the same path as the webservice –

string strPath = HttpContext.Current.Request.ServerVariables["APPL_PHYSICAL_PATH"].ToString();

string strFileName = (Guid.NewGuid()).ToString()+".wav";

FileStream fsWave =

new FileStream(strPath + strFileName,FileMode.Create);

fsWave.Write(WaveData,0,WaveData.Length);

fsWave.Close();

 

Where WaveData was my Byte array.

 

Then when consuming this service, from an ASP.NET page, I used this code

HttpPostedFile hpfWave = this.AudioFile.PostedFile;

int intFileLen = hpfWave.ContentLength;

byte[] bWaveData = new byte[intFileLen];

hpfWave.InputStream.Read(bWaveData, 0, intFileLen);

strMessage = vmWebService.UploadAudio(bWaveData);

 

Where AudioFile is my Input type=file on the web page, and vmWebService was my webservice.

 

If you’re interested in knowing where this is leading, check out www.sms-txt.co.uk in a few days!

 

Categories: Uncategorized

SQL performance: Nested select statements.

March 24, 2006 Leave a comment
This is a tip for restructuring nested select statements to make them run faster in SQL server. I can’t vouch for this in all situations, but It shortened a long SQL query for me from 1 hour to 7 minutes.
 
Where you have
Select * from xyz where id in
 (
   Select * from abc where id in
   (
    select id from def
   }
 )
 
I recommend re-writing as
 
   Select * into #abcdef from abc where id in
   (
    select id from def
   }
 
Select * from xyz where id in
 (
   select id from #abcdef
 )
 
 
Categories: Uncategorized

Charset reverting to Windows standard

March 23, 2006 Leave a comment
On Internet eplorer 6 SP1, If you perform a Response.Flush() before any content goes to the page, then the charset will revert to Windows. Regardless if the charset is set in the HTML.
 
If you put this line in Page_load, it should fix the problem
Response.Charset = "UTF-8";
Categories: Uncategorized

Differences between CHOICE command in Batch files

March 22, 2006 Leave a comment
Batch files may be arcane, but they serve well for quick & nasty build scripts. One point to note that I fell into a trap today is the difference in the implementation of the CHOICE command (prompting the user for input) between Windows 9x and Windows 2000/XP. – Alot of documentation on Batch files is quite old, and thus doesn’t mark any difference
 
Windows 9x:
CHOICE /C:123 /N Please choose a menu option.
IF ERRORLEVEL == 3 GOTO LIVE
IF ERRORLEVEL == 2 GOTO BACKUP
IF ERRORLEVEL == 1 GOTO LOCAL
EXIT
 
Windows 2000/XP:
set choice=
set /p choice=Please choose a menu option.
if not ‘%choice%’==” set choice=%choice:~0,1%
if ‘%choice%’==’1’ goto LOCAL
if ‘%choice%’==’2’ goto BACKUP
if ‘%choice%’==’3’ goto LIVE
EXIT
 
Categories: Uncategorized

Google Toolbar buttons… second attempt

March 19, 2006 Leave a comment
Categories: Uncategorized

Transparent Localisation

March 18, 2006 Leave a comment
I’ve just launched one website, in four different languages all in the same path, with the same files & database under IIS.
    www.sms-txt.co.uk (english)
   www.handyspruche.com (german)
   www.envoyezsms.com (french)
  www.mensajetexto.com (Spanish).
 
How did I do it?
 

public static string getGetSiteCulture()

{

string strUrl = System.Web.HttpContext.Current.Request.Url.ToString().ToLower();

string strCulture = "en-GB";

if (strUrl.IndexOf("mensajetexto")!=-1) {strCulture = "es-ES";}

if (strUrl.IndexOf("handyspruche")!=-1) {strCulture = "de-DE";}

if (strUrl.IndexOf("envoyezsms")!=-1){strCulture = "fr-FR";}

return strCulture;

}

 

Then I have 4 different Resource files, strings.es-ES.resx, strings.en-GB.resx etc.

public static string Translate(string ID)

{

rmTranslations.IgnoreCase =

true;

string strTranslation ="";

Thread.CurrentThread.CurrentCulture =

new CultureInfo(getGetSiteCulture());

Thread.CurrentThread.CurrentUICulture =

new CultureInfo(getGetSiteCulture());

try

{strTranslation= rmTranslations.GetString(ID);}

catch{

throw new Exception(ID + " not found");

}

return strTranslation;

}

 

Then in place of static text, I put Translations.Translate("Whatever")

 
Categories: Uncategorized