Search

Loading...

Sunday, September 28, 2014

Working with the Google Reseller API

I spent a few days trying to get this to work so it’s worth a write-up of what it takes to use the API from .NET.

Setup

  1. Create an Google Apps account in you can use to impersonate the client and get the data. You do that by going to http://admin.google.com/reseller.domain.com for your Google Apps reseller account.
  2. Enable API Access by going to Security/API Reference and checking “Enable API access.”
  3. Create a new project in Google Developer Console. I called mine “ResellerAPI.”
  4. Configure the Reseller API to be available to your project by going to the “ResellerAPI” project/APIs and enabling “Google Apps Reseller API.”
  5. Create a service account credential under the “ResellerAPI” project/Credentials. You will need to download the P12 key, write down the client ID and the client email address.
  6. Back in the Google Apps admin console, enable client API access for the client ID you created in step 5. You do this by going to Advanced Settings/Mange API client access. You will add the client ID and the API scope “https://www.googleapis.com/auth/apps.order.


Project Setup

  1. Create a new .NET project. I called mine GoogleApi.
  2. Open the Nuget package manager and install Google.Apis.Reseller.v1 with the command: install-package Google.Apis.Reseller.v1
  3. Update the Microsoft.BCL.Async package to the latest version with: install-package Microsoft.Bcl.Async


Variable Setup

We will create all the above settings a local private constants:

private const string ClientSertificatePath = @"The path to the certificate file from step 5";
private const string ClientSertificatePassword = "notasecret";
private const string ClientAccountEmail = "The client email address from step 5";
private const string ClientImpersonateUser = "Your Google Apps user from step 1";
private const string ApplicationName = "Reseller Sample";
private const int ServiceMaxResults = 100;

Service Setup

We will use a separate function to setup the service:

/// <summary>
/// Creates the reseller service object
/// with the provided client settings and certificate
/// </summary>
/// <returns></returns>
private ResellerService Setup()
{
    // Create a new certificate from the client certificate file
    var certificate = new X509Certificate2(ClientSertificatePath, ClientSertificatePassword, X509KeyStorageFlags.Exportable);

    // Create new credentials based on the certificate and the client settings
    var credentials = new ServiceAccountCredential(
       new ServiceAccountCredential.Initializer(ClientAccountEmail)
       {
           // Set the scope of the request, AppOrderReadonly does not work here
           Scopes = new[] { ResellerService.Scope.AppsOrder },
           User = ClientImpersonateUser
       }.FromCertificate(certificate));

    // Return a new service with the client credentials
    return new ResellerService(new BaseClientService.Initializer()
    {
        HttpClientInitializer = credentials,
        ApplicationName = ApplicationName,
    });
}

Get Subscriptions

A separate function will be used recursively to get a list of subscriptions:

/// <summary>
/// Gets a list of subscriptions
/// </summary>
/// <param name="service">The ResellerService object</param>
/// <param name="listOfSubscriptions">A list of Subscription to store the returned data</param>
/// <param name="nextPageToken">The string containing the next page token</param>
private void GetSubscriptions(ref ResellerService service, ref List<subscription> listOfSubscriptions, string nextPageToken)
{
    listOfSubscriptions = listOfSubscriptions ?? new List<subscription>();

    // Create the subscriptions list request
    var listResults = service.Subscriptions.List();

    // Set the max results and page token
    listResults.MaxResults = ServiceMaxResults;
    listResults.PageToken = !string.IsNullOrEmpty(nextPageToken) ? nextPageToken : string.Empty;

    // Execute the request
    var result = listResults.Execute();

    if (result.SubscriptionsValue != null)
    {
        // Add all the subscriptions to the list
        listOfSubscriptions.AddRange(result.SubscriptionsValue);

        // If the next page token exists
        if (result.NextPageToken != null)
        {
            // Call yourself again passing the next page token
            GetSubscriptions(ref service, ref listOfSubscriptions, result.NextPageToken);
        }
    }
}

Putting it Together

The run function that will setup the service and get a list of subscriptions:

private void Run()
{
    // Setup the service
    var service = Setup();
    var listOfSubscriptions = new List<subscription>();

    Console.WriteLine("Getting a list of subscriptions...");
    
    GetSubscriptions(ref service, ref listOfSubscriptions, string.Empty);

    foreach (var s in listOfSubscriptions)
    {
        Console.WriteLine(s.SubscriptionId + " - " + s.CustomerId);
    }

    Console.WriteLine("Total: {0}", listOfSubscriptions.Count);
}

Downloads and Source Code

You can download all the working project from my Dropbox. Source code is on GitHub.

Wednesday, September 24, 2014

How to Execute a Sub-Query in SubSonic

Pretty simple but not easy to find:

listOfType = New SubSonic.Select() _
    .From(TableName.Schema) _
    .Where(TableName.Columns.Id).IsEqualTo( _
        New SubSonic.Select(TableName2.Columns.Id) _
        .From(TableName2.Schema) _
        .Where(TableName2.Columns.Id).IsEqualTo(someId) _
        .ExecuteScalar(Of Long)
).ExecuteTypedList(Of TypedList)()

Sunday, September 21, 2014

My Experience with Roger Beasley Mitsubishi South

Website: http://www.beasleymitsubishisouth.com/
Address: 1120 Shelby Lane, Austin TX 78745

So I decided I wanted to buy a Mitsubishi Lancer Evolution. I have been eyeing the car for a long time and since next year is going to be the last year they make it, now seems as good as time as any to get one. It’s a little beast of a car! All wheel drive, 4 cylinder, turbo-charged engine that makes driving this thing an awful lot of fun!

Looking around, I found that my local Mitsubishi dealer has two in stock (and here is the screenshot). And here is where the confusion begins:

  1. They are both listed as Lancer Evolution but the second is GSR and the first one is not. I know for a fact that there are only two models of this car. One is GSR and the other is MR.
  2. They are listed at $41,980 and $38,180 but the base MSRP (from the Mitsubishi web site)  is $34,995, so I assume they must have some extras but I cannot find anything in the listing except the default features and stock photos.

So I email the dealer and get a response from Debbie Morgan. She includes a copy of the sticker for each car and I can now clearly see the difference between the two cars. The cheaper ($38,180) car has the navigation package (MSRP $2375) on top of the base $34,995 + $810 destination fee, bringing the total to $38,180. However in her email she also tells me that the special sale price can not be combined with the special APR program that is currently running. I have no idea why that would be when the program on the Mitsubishi web site clearly does not exclude the Evolution.

Next step, I schedule a time to come-in and look at the car for Wednesday night (09/17/2014) at 7pm. I get a message from Rick King to confirm my appointment which I do promptly and show at the appointment at 7pm.

Wednesday

I get to the dealership, Rick walks out and without as much as a “Hi”, starts treating me like he has known me for years, but in fact we just met. He lets me look inside the car, and lets me start it but tells me I cannot take it for a test drive. At this point, I am starting to wonder how I am expected to spend $40K without test driving the car. He explains that some guy dropped the clutch at a previous test drive, and now the dealer does not let that car be test driven. Sounds unreasonable to me but I go along.

He takes me inside the dealership and we start talking about the numbers and what color I want. Rick seems to know very little about the car, and the available options. He leaves me alone to sit there for a while, talking to his manager I presume, without as much as offering some water or coffee. I am not happy with the poor customer service, but I put it with it. After maybe half an hour and some talk with his manager about prices and payments, I am now allowed to take the car on a test drive. I am guessing they didn’t think I was serious about buying it. I take the car on a 15 minute drive and I love it, but I am very careful how I drive it and we don’t go past 80 (even though I want to see what it can do). We get back, we talk about the color of the car, and that I want the black one from another dealer in San Antonio, and I also want several extra packages. Rick has no idea what is included in the packages I am talking about, he says he will look and get back to me. We finish the conversation with him saying he’ll find out about the packages, and me saying I have to sleep on it. I have not committed to buying the car at this point.

Thursday

I get a text message from Rick that he is picking my car up today. I am surprised because I have not yet committing to buying the car, and he has not gotten back to me about the packages I wanted. The packages I want are:

  1. Exterior Package ($1500) - FRONT, SIDE, AND REAR AIRDAMS, BRAKE AIR GUIDES, REAR SPOILER EXTENSION
  2. Interior Package ($425) - ALUMINUM 5MT SHIFT KNOB, ALUMINUM / LEATHER BRAKE GRIP
  3. Led Illumination Package ($335) - FLOOR ILLUMINATION (BLUE LED), INTERIOR LIGHTS
  4. Rear Park Assist Sensors ($295)

Package total MSRP is $2555.

Friday

Everything from here on happens over text messages, as I am at work. Rick does try to call me but I cannot talk on the phone.

I ask him about the packages and he gets back to me in a few hours with a price tag of $3999, and tells me that is just for parts, and that installation will be extra. I don’t know what to say. I send him to the Mitsubishi web site where the prices are listed (despite that they are the MSRP). He apologizes and tells me that I am smarter than their parts department. He then sends me another price of $1490 for installation. I tell him to find a way to drop the installation price and we’ll have a deal. I know this is possible since I have gotten it done at another dealer with my last car, and I was paying half of what the Evolution price tag is.

Eventually he gets back to me, and drops the installation price to $996 and $500 off the car price. Also tells me he has gotten me a $300 race track pass for the day – nice gesture without a doubt. However, that is still more than I want to spend, especially after the poor customer service and the several lies I caught them into. I tell them exactly what I want again, and that is to drop the installation fee and we’ll have a deal. Rick does not budge and tells me that his manager will be putting the car back on the lot in the morning. No love lost for me, I thank him for his time and consider the deal not happening.

What happens next is very unprofessional and makes me want to buy the car from Rick even less. He goes off on me about the 3 days he has spent on this, tries to guilt me into buying the car because he already got it from the other dealer, and also tells me that he has done everything I have asked for. On top of that, he tells me this has never happened to him in the past 8 years. I tell him that I love the car and I would love to have it, but I don’t have to buy it from him, and I don’t have to buy it today. I say that he has done very little to motivate me to buy it from him. I iterate one more time what I want and he keeps going about how it makes no business sense and how nobody will do that. I guess we are done. He sends me a few more angry messages, and I try to be as polite as possible.

Conclusion

I would have bought the car, I was 99% there. What it came down to was paying MSRP for the car, extra for the parts and still getting charged for installation. The poor customer service, the lack of knowledge about the car and packages, and the several things that turned out to be a lie, was what killed the deal. Roger Beasley Mitsubishi South, you failed to get my business, but what is worse than that is that I cannot recommend your dealership to anyone.

Monday, October 12, 2009

Useful Function for Type Casting Your Query String Values in ASP.NET

Whenever you want to get the query string value in ASP.NET, you usually use Request.QueryString. However, that always gives you a string value that is not type cast to the variable you want. So instead of an integer or a Guid, you always get a string. Here is little function that addresses that and gives you the proper type based on the variable type passed in. It’s used as follows:
' Declare a variable of Guid type
Dim assetID As Guid
' Get the value from the query string
getValue("id", assetID)

' Declare a variable of Integer type
Dim imageWidth As Integer

' Get the value from the query string
getValue("imageWidth", imageWidth)

And for the function itself:

''' 
''' Gets the value of the query string key specified
''' 
''' The data type of the value to be returned
''' The query string key''' The variable to store the value in
Public Shared Sub getValue(Of T)(ByVal key As String, ByRef value As T)
    If Not String.IsNullOrEmpty(Request.QueryString.Item(key)) Then
        'If the passed in type is Guid
        If GetType(T) Is GetType(Guid) Then
            ' Check the format of the query string for a valid Guid
            If isValidGuid(Convert.ToString(Request.QueryString.Item(key))) Then
                ' Type cast the value from a string to a Guid                 
                value = DirectCast(CType(New Guid(Convert.ToString(Request.QueryString.Item(key))), Object), T)
            Else
                ' Type cast the an empty guid for the value           
                value = DirectCast(CType(Guid.Empty, Object), T)
            End If
        Else
            ' Type cast query string value to the requested type     
            value = CType(Convert.ChangeType(Request.QueryString.Item(key).Trim(), GetType(T)), T)
        End If
    End If
End Sub

Wednesday, November 05, 2008

SQL Server Scheduled Backups with NAnt

When installing and using production web applications, backup is always a must. I have written about SQL Server Backup before and this post would expend on that. The main goal here is to create a solution that periodically backs up your database. To do this, we’ll use the backup script from the previous post, employ NAnt to execute the script and zip the created backup. Last, we’ll schedule the execution of the NAnt build script through the Windows Task Scheduler or the “at” command. This procedure can backup a local or a remote SQL Server (as long as the remote server is on the same network).

Prerequisites

  • Installed and configured version of NAnt (see Getting Started with NAnt - .NET Build Tool)
  • Installed NAntContrib tasks (for the SQL task, see NAntContrib on SourceForge)
  • Task Scheduler Service enabled in Control Panel/Admin Tools/Services
  • SQL login with “dbo” rights to the database (to install the backupDatabase procedure)
  • SQL login that has “public” rights to the database (so it can execute the backup)

Backup SQL Procedure

The script below with create the SQL server stored procedure to create a backup file of a given database. It takes two parameters:

  1. databaseName – The SQL server database to be backed up
  2. backupDirectory – The directory where the backup file will be created

You need to execute it against the database that you will be backing up and give the “public” role execution permissions to the procedure.


Code
exec dbo.sp_executesql @statement = N'
/*
Created:
 07.18.2008 by Boyan Kostadinov (boyank@gmail.com)

Dependencies:
 None

Usage:
 exec dbo.backupDatabase ''ensembleVideo'', ''C:\Temp''

Parameters:
 @databaseName - varchar
 - The database to backup

 @backupDirectory - varchar
 - The path to where the database should be
 backed up. This should be an existing directory on
 the SQL Server where the database is located

Description:
 Backsup a given database to the specified directory
*/
create procedure dbo.backupDatabase
 @databaseName varchar(100),
 @backupDirectory varchar(1000)
as
declare @backupFileName varchar(100),
 @databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
 @databaseDataFile varchar(100), @databaseLogFile varchar(100),
 @execSql varchar(1000)

-- If the backup directory does not end with ''\'', append one
if charindex(''\'', reverse(@backupDirectory)) > 1
 set @backupDirectory = @backupDirectory + ''\''

-- Create the backup file name based on the backup directory, the database name and today''s date
set @backupFileName = @backupDirectory + @databaseName + ''-'' + replace(convert(varchar, getdate(), 110), ''-'', ''.'') + ''.bak''

set @execSql = ''
backup database ['' + @databaseName + '']
to disk = '''''' + @backupFileName + ''''''
with
  noformat,
  noinit,
  name = '''''' + @databaseName + '' backup'''',
  norewind,
  nounload,
  skip''

exec(@execSql)'
go

NAnt Backup Script

The script is somewhat complex. Here are the list of features:

  • Backup a local SQL Server instance or a SQL Server instance on the same network. The key is that the file system of the networked instance must be available through UNC shares to the machine executing the backup script.
  • Use a specified connection string or read the connection string from a .NET (or other type of XML)configuration file.

I will not dive into the script itself since it's properties and flow is documented fairly well.

Code

<project name="backupDatabase" default="run" xmlns="http://nant.sf.net/release/0.86-beta1/nant.xsd">
 <!-- Set the name of the backup file that will be created -->
 <property name="zipFileName" value="databaseBackup" overwrite="false" />

 <!-- Set the path where the backup file will be finally stored -->
 <property name="localBackupDirectory" value="C:\Temp" overwrite="false" />

 <!-- Set the local path (relative to the SQL Server instance) where SQL Server will write the backup file -->
 <!-- This has to always be a local path since SQL server can't write to network paths -->
 <property name="sqlServerLocalBackupDirectory" value="C:\Temp" overwrite="false" />

 <!-- Set the UNC path to the above "sqlServerLocalBackupDirectory" local path -->
 <!-- This is only needed if you are backing up SQL server on the network -->
 <property name="sqlServerUNCBackupDirectory" value="\\beehive\windows$\Temp" overwrite="false" />
 <!-- For backing up a local SQL Server instance, commented the first "sqlServerUNCBackupDirectory" property -->
 <!-- and uncommented the one below this line, or set the "sqlServerUNCBackupDirectory" property to "" -->
 <!--<property name="sqlServerUNCBackupDirectory" value="" overwrite="false" />-->

 <!-- Set the .NET connection string for connecting to the database -->
 <!-- This setting always takes precedence over the config file below -->
 <!-- To use a config file instead, set this property to "" (like so value="") -->
 <property name="connectionString" value="Data Source=beehive\sql2005;Initial Catalog=ensembleVideo;User ID=test;Password=test;" overwrite="false" />

 <!-- Alternatively set the path to where the build script should get the connection string from  -->
 <!-- This is usually a app.config or connectionString.config file -->
 <property name="connectionStringConfigFilePath" value="C:\Inetpub\wwwroot\myApp\config\connectionStrings.config" overwrite="false" />

 <!-- Set the XPath expression that will be used to grab the connection string from the config file -->
 <property name="connectionStringXPath" value="/connectionStrings/add[@name = 'sqlServerConnection']/@connectionString" overwrite="false" />

 <!-- Set the regular expression that's needed to get the database name from the connection string -->
 <property name="getDatabaseNameFromConnecionStringRegEx" value="Initial Catalog=(?'databaseName'.*?);" overwrite="false" />

 <property name="todaysDate" value="${string::substring(string::replace(datetime::to-string(datetime::now()), '/', '.'), 0, 10)}" />
 <property name="todaysLocalBackupDirectory" value="${path::combine(localBackupDirectory, todaysDate)}" />

   <target name="run">
  <!-- If the SQL Server UNC directory was not specified and the local backup directory exists -->
  <if test="${string::get-length(sqlServerUNCBackupDirectory) == 0 and directory::exists(localBackupDirectory)}">
   <!-- This is a back of a local SQL Server instance -->
   <mkdir dir="${todaysLocalBackupDirectory}" />

   <property name="todaysSqlServerLocalBackupDirectory" value="${todaysLocalBackupDirectory}" />
   <property name="todaysSqlServerUNCBackupDirectory" value="${todaysLocalBackupDirectory}" />

   <property name="localSqlServer" value="true" />
  </if>

  <if test="${string::get-length(sqlServerUNCBackupDirectory) > 0 and directory::exists(sqlServerUNCBackupDirectory) }" >
   <property name="todaysSqlServerLocalBackupDirectory" value="${path::combine(sqlServerLocalBackupDirectory, todaysDate)}" />
   <property name="todaysSqlServerUNCBackupDirectory" value="${path::combine(sqlServerUNCBackupDirectory, todaysDate)}" />

   <!-- This is a back of a networked SQL Server instance -->
   <mkdir dir="${todaysSqlServerUNCBackupDirectory}" />

   <property name="localSqlServer" value="false" />
  </if>

  <!-- If the connection string is empty,
  the connection string file exists and the XPath to find the connection string is not empty -->
  <if test="${string::get-length(connectionString) == 0 and file::exists(connectionStringConfigFilePath)
       and string::get-length(connectionStringXPath) > 0}" >
   <!-- Get the connection string to the database from the connection string config file -->
   <xmlpeek
    file="${connectionStringConfigFilePath}"
    xpath="${connectionStringXPath}"
    property="connectionString">
   </xmlpeek>
  </if>

  <!-- If the connectionString property is not empty and the regular expression to get the database name is not empty -->
  <if test="${string::get-length(connectionString) > 0 and string::get-length(getDatabaseNameFromConnecionStringRegEx) > 0}">
   <!-- Get the database name from the connection string -->
   <regex pattern="${getDatabaseNameFromConnecionStringRegEx}" input="${connectionString}" />
 
       <!-- Execute the stored procedure to bckup the database -->
   <sql connstring="Provider=SQLOLEDB;${connectionString}" transaction="false" delimiter=";" delimstyle="Normal">
   exec dbo.backupDatabase '${databaseName}', '${todaysSqlServerLocalBackupDirectory}';
   </sql>

   <!-- Zip up the created databse backup file -->
   <zip zipfile="${path::combine(todaysSqlServerUNCBackupDirectory, zipFileName + '-' + todaysDate + '.zip')}" ziplevel="9">
    <fileset basedir="${todaysSqlServerUNCBackupDirectory}">
     <exclude name="**/*.zip" />
     <include name="*.*" />
    </fileset>
   </zip>

   <!-- Delete all other files in the today's backup directory except for the created zip files -->
   <delete>
    <fileset basedir="${todaysSqlServerUNCBackupDirectory}">
     <exclude name="**/*.zip" />
     <include name="*.*" />
    </fileset>
   </delete>

   <!-- Move the contents of today's backup directory to the local backup directory -->
   <move todir="${localBackupDirectory}">
    <fileset basedir="${todaysSqlServerUNCBackupDirectory}">
     <include name="*.*" />
    </fileset>
   </move>

   <!-- Delete the "todays" directories -->
   <if test="${localSqlServer}">
    <delete dir="${todaysLocalBackupDirectory}" />
   </if>

   <if test="${not localSqlServer}">
    <delete dir="${todaysSqlServerUNCBackupDirectory}" />
   </if>
  </if>
  </target>
</project>

Setting Up Backup Script and Scheduling

Review the build script and set following properties to match your setup:
  • zipFileName – The name of the zip file that will be created for the database backup
  • localBackupDirectory – The local directory where the backup will be stored
  • sqlServerLocalBackupDirectory - The local path (relative to the SQL Server instance) where SQL Server will write the backup file. This has to always be a local path since SQL server can't write to network paths.
  • sqlServerUNCBackupDirectory – The UNC path to the above "sqlServerLocalBackupDirectory" local path. This is only needed if you are backing up SQL server on the network. For backing up a local SQL Server instance, set this "”.
  • connectionString - The .NET connection string for connecting to the database. This setting always takes precedence over the “connectionStringConfigFilePath“ setting. To use a configuration file instead, set this property to "".
  • connectionStringConfigFilePath - Alternatively set the path to where the build script should get the connection string from. This is usually a app.config or connectionString.config file.
  • connectionStringXPath - The XPath expression that will be used to grab the connection string from the configuration file.
  • getDatabaseNameFromConnecionStringRegEx - The regular expression that's needed to get the database name from the connection string.


The next step is to create the schedule with either the Task Scheduler or with “at” command.

To use the Task Scheduler:

  1. Create a .bat file with the following: “driveLetter:\path\to\nant.exe /f:pathToNAntBackupScript.build” and of course replace that with the actual path to nant.exe and to the NAnt build script you got here.
  2. Go to Control Panel / Scheduled Tasks / Add Scheduled Task
  3. Browse for the .bat file you created in #1
  4. Configure the schedule run as often as you would like

To use the “at” command:

  1. Do the same as #1 above.
  2. Open a command prompt and execute the “at” command:
    ”at 23:00 /every:M,T,W,Th,F pathToBatFileFromStep1.bat”

    That will schedule the task to execute every day of the week at 11:00pm. You can get more info on the “at” command from How To Use the AT Command to Schedule Tasks.

Bonus

You don’t have to hard code the values in the NAnt build script. You can pass them from the command line. In that manner you can reuse the same script for different database. You simply need to call the script with –D:propertyName=”value" like so:

path\to\nant.exe /f:pathToNAntBackupScript.build –D:zipFileName="myDatabase" –D:localBackupDirectory="D:\Temp"

Downloads

http://tech-cats.net/blog/downloads/sql/procedure-dbo.backupDatabase.txt
http://tech-cats.net/blog/nantScripts/backupDatabase.build
http://tech-cats.net/blog/nantScripts/backupDatabase.bat

// //]]>