Joseph Michael Pesch
VP Programming

WCF Web Service XML Sample

by 24. September 2012 11:29
This is some sample code demonstrating a WCF web service with XML based class as an input parameter.
// Shared class...

public class MySampleData
{
  public string UserId { get; set; }
  public Guid RecordId { get; set; }
  public string RecordDesc { get; set; }
  public decimal DataAmount { get; set; }
  public DateTime EffectiveDate { get; set; }
}

// Sample usage...

using System.IO;
using System.Net;
using System.Xml;
using System.Xml.Serialization;

private void SampleUsage()
{
  MySampleData data = new MySampleData()
  {
    UserId = "myuser",
    RecordId = Guid.NewGuid(),
    RecordDesc = "This is a test",
    DataAmount = 100.99,
    EffectiveDate = System.DateTime.Now.AddDays(5)
  };
  string msg = SaveMySampleData(data);
  System.Diagnostics.Debug.WriteLine(msg);
}

// Caller initiating web service transaction...
private void SaveMySampleData(MySampleData data)
{
  XmlSerializer serializer = new XmlSerializer(data.GetType());
  MemoryStream ms = new MemoryStream();
  serializer.Serialize(ms, data);
  StreamReader rdr = new StreamReader(ms);
  ms.Position = 0;
  HttpWebRequest req = (HttpWebRequest)HttpWebRequest
    .Create("http://localhost:43653/MySampleService.svc/SaveMySampleData");
  req.Method = "POST";
  req.ContentType = "application/xml; charset=utf-8";
  byte[] reqBodyBytes = ms.ToArray();
  Stream reqStream = req.GetRequestStream();
  reqStream.Write(reqBodyBytes, 0, reqBodyBytes.Length);
  reqStream.Close();
  HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
  XmlDocument xml = new XmlDocument();
  xml.LoadXml(new StreamReader(resp.GetResponseStream()).ReadToEnd());
  resp.Close();
}

// Web service code (i.e. MySampleService.svc file)

using System;
using System.IO;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;
using System.Xml;
using System.Xml.Serialization;

[ServiceContract]
public class MySampleService
{
  [OperationContract(Name = "SaveMySampleData")]
  [WebInvoke(Method = "POST", UriTemplate = "SaveMySampleData",
              RequestFormat = WebMessageFormat.Xml,
              ResponseFormat = WebMessageFormat.Xml,
              BodyStyle = WebMessageBodyStyle.Bare)]
  public string SaveMySampleData(XmlElement input)
  {
    XmlSerializer serializer = new XmlSerializer(typeof(MySampleData));
    MySampleData data = (MySampleData)serializer.Deserialize
      (new MemoryStream(System.Text.Encoding.ASCII.GetBytes(input.OuterXml)));
    // Save to db here... just echoing data back for the sample...
    StringBuilder msg = new StringBuilder();
    msg.Append("You sent the following data.").Append(Environment.NewLine);
    msg.Append("UserId: ").Append(data.UserId).Append(Environment.NewLine);
    msg.Append("RecordId: ").Append(data.RecordId).Append(Environment.NewLine);
    msg.Append("RecordDesc: ").Append(data.RecordDesc).Append(Environment.NewLine);
    msg.Append("DataAmount: ").Append(data.DataAmount).Append(Environment.NewLine);
    msg.Append("EffectiveDate: ").Append(data.EffectiveDate.ToString()).Append(Environment.NewLine);
    return msg.ToString();
  }
}

Tags:

C# | Web Service | Windows Communication Foundation

AppFog Setup Application with MySQL Database and Connect to Database with MySQL Workbench

by 15. September 2012 18:01

This entry will walk through creating an AppFog application, linking a MySQL database to it and then connecting to the database with MySQL Workbench.  I will walk through creating the AppFog application and MySQL service, installing Ruby and the DevKit (toolkit that makes it easy to build and use native C/C++ extensions for Ruby on Windows).  In this case the extension we will be installing that requires the DevKit is Caldecott (which is used to create the MySQL TCP tunnel from the AppFog server to your Localhost).  My installation will focus on Windows 7 64bit operating system.

Step 0) Install MySQL Workbench from: http://www.mysql.com/downloads/workbench/ (the version I installed was: mysql-workbench-gpl-5.2.42-win32.msi).

Step 1) Create your AppFog account.  If you don't already have an AppFog account, signing up is quick an painless.  At the time of this post all they require to establish a new free account is an email address, password and company name.  My free account came with: Unlimited Apps (Java, .Net, Ruby, Node, Python, PHP, etc.) within 2GB RAM, Database services (MySQL, Mongo, PostgreSQL, etc.) with 1GB limit, 50 GB data transfer limit and Free Custom Domains.

Step 2) Create your AppFog application.  Simple click to create and deploy, pick you application framework and associated database service.  It's so easy I won't get into those details here, other than to say my first application was Node.js with MySQL database.

Step 3) Install Ruby from http://rubyinstaller.org (the version I installed was: rubyinstaller-1.9.3-p194.exe).  This is a pretty basic Windows application install, so not much to say here other than the installation path I chose which was: C:\Programs\Ruby193.

Step 4) Install DevKit from http://rubyinstaller.org/downloads (the version I installed was: DevKit-tdm-32-4.5.2-20111229-1559-sfx.exe).  This was a self extracting EXE that simply extracted the contents to a directory.  I selected the extract to be inside my existing Ruby installtion folder: C:\Programs\Ruby193\DevKit.  After extraction was complete you need to run a couple commands to complete the installation:  1) ruby dk.rb init to generate the config.yml file. 2) ruby dk.rb install

Step 5) Launch Ruby and create the install the AppFog utility and the Caldecott utility.  Launch Ruby: Windows Button > All Programs > Ruby 1.9.3-p194 > Start Command Prompt with Ruby from the Ruby command window perform the following commands:

    gem update --system
    gem install af
    gem install caldecott

Step 6) Login to your AppFog account from the Ruby commandline: af login (this will prompt you for your email address and password).

Step 7) Create TCP tunnel between AppFog server and your localhost.  Caldecott is an extension that allows you to establish a tunnel by proxying TCP traffic on localhost via HTTPS.  With Caldecott, you can use the Ruby commandline (after you logged into your AppFog account) and create a tunnel that allows you to manage your MySQL database from your local desktop using MySQL Workbench.  From the Ruby commandline (after you are logged in using af login): af tunnel this will then prompt you with a list of options.  For example, in my case I had a MySQL and a MongoDB instance running on my AppFog account, so I got two options (as shown below), I selected option 2 which in my case was the MySQL database I was interested in.  Also, note the last prompt, I selected "None" which leaves the tunnel open for the MySQL Workbench UI to connect.  You will use the information shown in the username, password section to connect and the connection hostname/ip and port will be "localhost" on port "10000".

1: mongodb
2: mysql
Which service to tunnel to?: 2
Getting tunnel connection info: OK

Service connection info:
  username : xxxxxxxxxxxxx
  password : xxxxxxxxxxxxx
  name     : xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
  infra    : hp

Starting tunnel to mysql on port 10000.
1: none
2: mysql
3: mysqldump

Which client would you like to start?: 1
Open another shell to run command-line clients or
use a UI tool to connect using the displayed information.
Press Ctrl-C to exit...

NOTE: If you get an error installing caldecott please update your path... type message it usually means that you did not install the DevKit properly.



Tags:

SQL DB Table Record History Template

by 12. September 2012 10:21

Here is a template script to create a standard set of objects to track changes to table data.

/*
  drop trigger SchemaName.TableName_IUX
  drop table SchemaName.TableName
  drop view SchemaName.vxTableName
  drop table SchemaName.TableNameHistory
  drop view SchemaName.vxTableNameHistory
*/

/*
  drop table SchemaName.TableName
*/
create table SchemaName.TableName
(
  /* single record per modification instance */
    Timestamp timestamp not null
  , TableNameId uniqueidentifier not null
  , LastUpdateDate datetime not null default getdate()
  , LastUpdateByUserId uniqueidentifier not null
  /* add columns here */
)
go
alter table SchemaName.TableName
add constraint
  PK_SchemaName_TableName primary key clustered
  (
    TableNameId
  ) with ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
on [PRIMARY]
go
/*
  drop view SchemaName.vxTableName
*/
create view SchemaName.vxTableName
as
select
    TableNameId
  , LastUpdateDate
  , LastUpdateByUserId
  /* add columns here */
from 
  SchemaName.TableName
go
/*
  drop table SchemaName.TableNameHistory
*/
create table SchemaName.TableNameHistory
(
  /* single record per modification instance */
    HistoryId uniqueidentifier not null default newid()
  , TableNameId uniqueidentifier not null
  , LastUpdateDate datetime not null default getdate()
  , LastUpdateByUserId uniqueidentifier not null
  /* add columns here */
)
go
alter table SchemaName.TableNameHistory
add constraint
  PK_SchemaName_TableNameHistory primary key clustered
  (
    HistoryID
  ) with ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
on [PRIMARY]
go
/*
  drop view SchemaName.vxTableNameHistory
*/
create view SchemaName.vxTableNameHistory
as
select
    TableNameId
  , LastUpdateDate
  , LastUpdateByUserId
  /* add columns here */
from 
  SchemaName.TableNameHistory
go
/*
  drop trigger SchemaName.TableName_IUX
*/
create trigger SchemaName.TableName_IUX on SchemaName.TableName for insert, update
as begin
  insert into SchemaName.vxTableNameHistory
    select t1.* 
    from SchemaName.vxTableName t1
    inner join inserted t2 on t1.TableNameID = t2.TableNameID
end
go

Tags:

SQL Server