Copyright Fergal Grimes, March 18th 2002
In January of 2002, Manning released Microsoft .net for Programmers. In it, I walk the reader through several implementations of a Video Poker program. Several readers have expressed an interest in deploying the video poker machine on their own Web sites. However, there are two obstacles to overcome:
The solution to the first problem is simple and requires no changes to the underlying poker engine (DLL). All we need is a new front end. Figure 1 presents a more functional front end which I recently developed for my own amusement.
This version implements payout control and provides a real-time view of the machine's overall performance against all players. In a future article, I hope to walk through the development of this new front end, and present the markup and C# code (behind). For now, I leave it as an exercise.
Finding SQL Server space is a more difficult obstacle to overcome. While free SQL Server space may be difficult to arrange, many ISPs do allow the use of DSNless Microsoft Access databases, which are based on Microsoft's Jet database technology. Brinkster (http://www.brinkster.com) is one such provider. So let's see what's involved in downsizing the poker database to Jet.
If you have a copy of Microsoft Access, you probably already know how to create a new database and create tables, queries, and so forth. However, if you do not have Microsoft Access, you'll need to write a C# program to create the necessary database objects. But first you need to create an empty database.
Creating a New Jet Database (Without Using Microsoft Access)
At the taskbar, select Start | Settings | Control Panel | Administrative Tools | Data Sources (ODBC) to launch the ODBC administration utility. (These are the steps for Windows 2000 users.) Then select the File DSN tab and click the Add button to create a new data source. Select the Microsoft Access (*.mdb) driver and click Next. Call the DSN file vpoker.dsn. (We won't be using this file.) Then click the Next and Finish buttons. You should see the window shown in figure 2.
At this point, you can click the Create... button, call the new database vpoker.mdb, and save it to your chosen directory. This gives us an empty Microsoft Access database.
Before we move on to create the tables, note that we could have created an empty database programmatically by using COM interop and ADOX, as follows:
// use ADOX to create new database...
string jetStr =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\vpoker.MDB";
ADOX.Catalog cat = new ADOX.CatalogClass();
cat.Create(jetStr);
However, the manual approach shown above will do fine for our purposes here.
The next steps are to create the tables and insert some test data. In the book, we used osql.exe to execute the pokdb.sql script, which created these objects on SQL Server. We'll need a different approach to create the Microsoft Access database. Since we don't have a copy of Microsoft Access, we'll write a program to create the corresponding Access objects. Listing 1 presents a simple C# program to do the job. (I've sacrificed error handling for the sake of brevity.)
// file : createDatabase.cs
// compile : csc createDatabase.cs
// description : Creates a Jet version of the poker database
//
// Supplement to the book "Microsoft .NET for Programmers"
using System;
using System.Data.OleDb;
public class MainClass {
public static void Main () {
// Before running this program, use the ODBC Data
// Source Administrator to create an empty MDB file
string jetConn =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\vpoker.MDB";
OleDbConnection accessConn = new OleDbConnection(jetConn);
string[] sql = new string[20];
int i = 0;
// create games table...
sql[i++] =
"CREATE TABLE games ( " +
"id COUNTER PRIMARY KEY, " +
"date_time TIMESTAMP, " +
"hand CHAR(15) NOT NULL, " +
"score INTEGER NOT NULL, " +
"bet INTEGER NOT NULL)";
// insert test data into games table...
sql[i++] = "INSERT INTO games(hand, score, bet) " +
"VALUES ('QC 7C QH KS QS ', 4, 1)";
sql[i++] = "INSERT INTO games(hand, score, bet) " +
"VALUES ('QC JD 6H 5C KH ', 0, 1)";
sql[i++] = "INSERT INTO games(hand, score, bet) " +
"VALUES ('KC 2C KD JD 6C ', 2, 1)";
// create integers table...
sql[i++] = "CREATE TABLE integers (" +
"int_name CHAR(30) PRIMARY KEY, " +
"int_value INTEGER NOT NULL)";
// insert test data into integers table...
sql[i++] = "INSERT INTO integers(int_name, int_value) " +
"VALUES ('MinBet', 1)";
sql[i++] = "INSERT INTO integers(int_name, int_value) " +
"VALUES ('MaxBet', 5)";
sql[i++] = "INSERT INTO integers(int_name, int_value) " +
"VALUES ('StartCredits', 100)";
sql[i++] = "INSERT INTO integers(int_name, int_value) " +
"VALUES ('TargetMargin', 25)";
int num = i;
// build the objects...
Console.WriteLine("starting...");
accessConn.Open();
for (i = 0; i < num; i++) {
Console.WriteLine(
"executing : " + sql[i].Substring(0, 60) + "...."
);
OleDbCommand cmd = new OleDbCommand(sql[i], accessConn);
cmd.ExecuteNonQuery();
}
accessConn.Close();
Console.WriteLine("Done!");
}
}
If you are familiar with the case study in the book, you'll notice that I've used some new classes in the program above.
System.Data.OleDb namespace and the OleDbConnection class to talk OLEDB to Jet. The System.Data.Sql namespace, which we used in the book, is for use with MS SQL Server only.Microsoft.Jet.OLEDB.4.0, in the connection string.name and value as column names, so I used int_name and int_value instead. (I assume the former must be reserved words.)CREATE TABLE syntax differs from SQL Server's Transact-SQL equivalent.Compiling and running the program produces the output shown in figure 3.
| Note | |
The createDatabase.cs program in listing 1 could serve as the genesis for your own OSQL-like utility to process SQL scripts for OLEDB providers. Just amend it to read the SQL statements, and connection string, from a file, and add some error checking. |
|
Porting the Poker Data Layer to Jet
As you'll recall, all the data access code in the case study is confined to the Bank class. For the most part, the changes necessary here involve replacing SQL Server class names with their OLEDB equivalents, as follows:
System.Data.SqlClient becomes System.Data.OleDbSqlConnection becomes OleDbConnectionSqlCommand becomes OleDbCommandSqlDataAdapter becomes OleDbDataAdapterNote that the method calls on these objects are unchanged.
Next, you should change the setConnectString method to reflect a suitable default connection string for Jet:
connectString =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\vpoker.mdb";
Recall that the actual connection string is retrieved from the application configuration file, as follows:
connectString = ConfigurationSettings.AppSettings["dsn"];
For Web applications, ConfigurationSettings.AppSettings looks in the Web.Config file for application settings. Since the connection string will be different on the ISP's server than it will be locally, create two different Web.Config files, one for local use, and one for your ISP-hosted site. (You can execute Server.MapPath("vpoker.mdb") on the ISP's site to find the correct absolute path. Then hardcode that path into the remote Web.Config file.) Don't forget to copy the database to the server too. There is no need to copy the vpoker.dsn file created earlier.
Finally, remember to change the SQL names of the columns used in the query in GetParm:
string sql = "SELECT int_value FROM integers " +
"WHERE int_name='" + parmName + "'";
That should be the end of it, but it's not. During testing I was surprised by some invalid cast errors generated by lines such as:
takenIn = (int) dr[0];
A quick and dirty change fixed the problem:
takenIn = Int32.Parse(dr[0].ToString());
There is a more elegant way to do this using OLEDB data types, but we won't go into it here. You'll have replace four int casts in this way.
The full Jet version of the Bank class is presented in listing 2 below.
using System;
using System.Configuration;
using System.IO;
using System.Data;
using System.Data.OleDb;
namespace Poker {
public class Bank {
public Bank() {
setConnectString();
TargetMargin = GetParm("TargetMargin", 25);
refresh();
}
public readonly int TargetMargin;
public int TakenIn { get { return takenIn; } }
public int PaidOut { get { return paidOut; } }
public int Profit { get { return profit; } }
public double HouseMargin { get {
if (takenIn == 0) return TargetMargin;
return (double) profit * 100.0 / takenIn;
} }
public double Delta { get {
return HouseMargin - TargetMargin;
} }
public int Bias { get {
if (Delta >= 0.0) return 0;
int bias = (int) Math.Round(Math.Abs(Delta));
if (bias > 10) return 10;
return bias;
} }
public string Status { get {
return status;
} }
public string Text { get {
return "\n" +
status + "\n" +
"===========================\n" +
"Taken In : " + takenIn + "\n" +
"Paid Out : " + paidOut + "\n" +
"Profit : " + profit + "\n" +
"House Margin % : " +
String.Format("{0:00.00}", HouseMargin) + "\n" +
"Target Margin % : " +
String.Format("{0:00.00}", TargetMargin) + "\n" +
"Delta : " +
String.Format("{0:00.00}", Delta) + "\n" +
"Bias : " + Bias + "\n";
} }
public override string ToString() {
return Text;
}
public void SaveGame(string hand, int score, int bet) {
if (connectString == "") return;
OleDbConnection conn = null;
try {
conn = new OleDbConnection(connectString);
} catch (Exception e) {
new MsgLog(String.Format( "Bank.SaveGame(): {0} - {1}",
"Cannot create OleDbConnection",
e.Message));
return;
}
string sql =
"INSERT INTO games(hand, score, bet) VALUES " +
"('" + hand + "'," + score + "," + bet + ")";
OleDbCommand comm = null;
try {
comm = new OleDbCommand(sql, conn);
} catch (Exception e) {
new MsgLog(String.Format( "Bank.SaveGame(): {0} - {1}",
"Cannot create OleDbCommand",
e.Message));
return;
}
try {
conn.Open();
} catch (Exception e) {
new MsgLog(String.Format( "Bank.SaveGame(): {0} - {1}",
"Cannot open OleDbConnection",
e.Message));
return;
}
try {
comm.ExecuteNonQuery();
} catch (Exception e) {
new MsgLog(String.Format( "Bank.SaveGame(): {0} - {1}",
"Cannot execute OleDbCommand",
e.Message));
return;
}
finally {
if (conn.State == ConnectionState.Open) conn.Close();
}
refresh();
}
public int GetParm(string parmName, int defaultValue) {
int parmValue = defaultValue;
if (connectString == "") return parmValue;
//*** SQL Server name column becomes Jet int_name column
//*** SQL Server value column becomes Jet int_value column
string sql = "SELECT int_value FROM integers " +
"WHERE int_name='" + parmName + "'";
DataSet ds = new DataSet("PokerParm");
OleDbDataAdapter sda =
new OleDbDataAdapter(sql, connectString);
try {
sda.Fill(ds, "result");
// *** removed cast below...
// parmValue = (int) ds.Tables["result"].Rows[0][0];
parmValue =
Int32.Parse(ds.Tables["result"].Rows[0][0].ToString());
} catch (Exception e) {
connectString = "";
new MsgLog(
String.Format("Bank.GetParm(): {0}", e.Message));
}
return parmValue;
}
private void setConnectString() {
connectString = ConfigurationSettings.AppSettings["dsn"];
if (connectString == null) connectString = "";
if (connectString == "")
connectString =
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=.\vpoker.MDB";
}
private void refresh() {
if (connectString == "") return;
string sql =
"SELECT " +
"SUM(bet) AS taken_in, " +
"SUM(score * bet) AS paid_out, " +
"SUM(bet) - SUM(score * bet) as profit " +
"FROM games";
OleDbDataAdapter sda = null;
try {
sda = new OleDbDataAdapter(sql, connectString);
DataSet ds = new DataSet("PokerProfit");
sda.Fill(ds, "stats");
DataRow dr = ds.Tables[0].Rows[0];
// *** removed casts below...
// takenIn = (int) dr[0];
takenIn = Int32.Parse(dr[0].ToString());
// paidOut = (int) dr[1];
paidOut = Int32.Parse(dr[1].ToString());
// profit = (int) dr[2];
profit = Int32.Parse(dr[2].ToString());
status = "Machine Stats (All Players)";
} catch (Exception e) {
new MsgLog(
String.Format("Bank.refresh(): {0}", e.Message));
}
}
// private static Bank bank = null;
private string connectString = "";
private string status = "Machine Stats Unavailable";
private int takenIn = 0;
private int paidOut = 0;
private int profit = 0;
}
}
In this article we saw how to use C# and OLEDB to programmatically create a Microsoft Access database. We also explored the changes needed to run the video poker machine using Jet as the underlying database engine.
If you do decide to deploy the video poker machine on your Web site, please remember that it is intended for fun, not profit. In any case, as we have discussed on the Author Online forum, the payout algorithm would likely be illegal in most jurisdictions.
Finally, if you deploy a public Web version of the game, please acknowledge Microsoft .NET for Programmers on your Web page. Every little helps.
