Monthly Archives: August 2009

Import a MySQL 5 database into SQL Server 2005

*Update* 8-28-2009

After trying this out, I’ve found that this does not work… at all. MSSQL compatibility mode must of been built back before 2000. I will continue searching the internet for a solution, but my feeling is that I will have manually create the tables and then add the insert statements into the database.

This is how I imported a database using MySQL 5 running on Ubuntu 8 LTS to MS SQL Server 2005.

First export the database by typing in a Ubuntu shell:

‘mysqldump -p –compatible=mssql databasename > mssql.sql’

The ‘-p’ switch prompts for a password, while the ‘mssql.sql’ file is the exported database file. If you need to specify a username, add ‘-u username’ as the first parameter or before the -p switch.

Transfer the file over to the Windows machine and  create a database for the import. Make sure you have permissions to transfer data via Windows Authentication. If you don’t have permissions, you can specify credentials using the -U and -P switches.

Run the following command line:

“C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe” -S localhost -d databasename -i “mssql.sql”

Before running, verify that the ‘sqlcmd.exe’ file path is correct. The -S switch is the host name, server name, or location. You may have to use the machine name depending on the name of your instance. The -d switch specifies the database to import the data.

ASP.NET: Modifying DetailsView Select and Updating Events

Recently I was tasked with encrypting/decrypting some data that could be edited using ASP.NET’s DetailsView control.  Along with the details view control, the legacy code used a SQL Data Source to select and update everything. This means the data access layer was bypassed completely, so any business logic that dealt with encryption on this page had to use a different execution path. So, how would you encrypt and decrypt data?

The problem could be divided into two tasks:

  1. Create a filter to decrypt data in any bound columns.
  2. Edit the DetailsView event, ItemUpdating and figure out which columns needed to be updated during the event.

The first task was the easier of the two, as it just involved referencing the encryption library from the code behind page and calling the function inline in the aspx page. So, the method in the code behind page would look like something like this:

protected string Decrypt(object input)
{
return EncryptionHelper.Decrypt(input.ToString());
}

While calling the decrypt function in the DetailsView child  Fields tags on the aspx page:

<asp:templatefield headertext="Decrypted Data" sortexpression="EncryptedData>
<edititemtemplate>
<asp:TextBox ID="TextBox1" runat="Server" Text='<%#Decrypt(Eval("EncryptedData"))%>' />
</edititemtemplate>
<itemtemplate>
<asp:label runat="server" text='<%# Decrypt(Eval("EncryptedData")) %>' id="Label1"></asp:label>
 </itemtemplate>
</asp:templatefield>

However, decrypting the data using a templatefield tag posed another problem, extracting new values from the ‘TextBox1’ control. This will be solved when calling the ItemUpdating event.

After setting the DetailsView’s ItemUpdating method, you will need to modify the SQL Data Source’s UpdateCommand, UpdateCommandType, and UpdateParameters. The legacy code in the SQL data source used a update statement with parameters. Normally the details view control automatically detects whether to pass a null in the parameter. Instead when  setting the UpdateParameters in the codebehind, the control will update the database with the parameter names entered. For example ‘@Parameter’, will be sent in the update to the record instead of null. To solve this, you would need to automatically detect whether a value is null and then add it to the UpdateParameters list. This was done using the ‘IsFieldNull’ method [shown below].

Initializing the SQL data source also had another problem – because certain data needed to be re-encrypted before being sent back to the database. You will need to single out any columns that need to be encrypted. On top of that, the table being updated had over 50 columns, so typing in 50+ lines just to initialize the parameters was required, but I decided to do something else instead.

The parameters being sent to the stored procedure were identically named to the column names. Using this protocol, I decided to simply grab all the field names, which were identical to the column names and add the ‘@’ character next during initialization. So, the method to get the column names looked like the following:

private List<string> GetColumnNameList()
{
List<string> Ret = new List<string>();

for (int i = 0; i < DetailsView.Rows.Count; i++)
{
if (!String.IsNullOrEmpty(DetailsView.Rows[i].Cells[0].Text))
{
Ret.Add(DetailsView.Rows[i].Cells[0].Text);
}
}
return Ret;
}

Initializing the update parameters looked like the following:

private void InitSqlDataSource()
{
SQLDataSource.UpdateCommand = "Update";
SQLDataSource.UpdateParameters.Clear();

List<string> paramList = GetColumnNameList();

foreach (string col in paramList)
{
if (!IsFieldNull(col))
{
if (col != "EncryptedData")
{
SQLDataSource.UpdateParameters.Add(col, "@" + col);
}
else
{
SQLDataSource.UpdateParameters.Add("EncrptedData",
EncryptionHeper.Encrypt(GetDecryptedValue()));
}
}
}
}

The method checks to make sure the data being sent isn’t blank or null by executing the ‘IsFieldNull’ method. This method uses the ‘ExtractValuesFromCell’ method to grab values from the DetailsView control. This method isn’t exactly well documented on MSDN, so it’s not obvious at first that the passed parameter, IOrderedDictionary dictionary, is being used as a referenced parameter, not as a copy. [another weird quirk about the .NET framework] The method, GetValues, used is identical to what is covered at the article written at David Fowler’s blog.

private bool IsFieldNull(string fieldName)
{
OrderedDictionary vals = GetValues(DetailsView) as OrderedDictionary;

if (vals[fieldName] == null)
{
return true;
}

return string.IsNullOrEmpty(vals[fieldName].ToString());
}

public IDictionary GetValues(DetailsView detailsView)
{
IOrderedDictionary values = new OrderedDictionary();

foreach (DetailsViewRow row in detailsView.Rows)
{
if (row.RowType != DataControlRowType.DataRow)
{
continue;
}

DataControlFieldCell dataCell = (DataControlFieldCell)row.Cells[0];

if (dataCell.ContainingField.ShowHeader)
{
dataCell = (DataControlFieldCell)row.Cells[1];
}

dataCell.ContainingField.ExtractValuesFromCell(values, dataCell, row.RowState, true);
}

return values;
}

Angelo’s BBQ in Ft. Worth

It’s hard to find decent BBQ, especially in larges cities. Dallas, TX has its share of franchise BBQ restaurants. If you’ve lived in any of the surrounding DFW areas for a few months, places like ‘Sonny Bryans’, ‘Spring Creek BBQ’, and ‘Dickey’s BBQ Pit’ can be found easily. Each place has its own specials and caters towards a specific taste, however, I think they all fail to capture BBQ that’s comparable to what you find at cook offs at rodeos or competitions in the locale. I thought this would be a problem that would never be solved, until recently.

A few weeks ago, I was invited to dine in at Angelo’s BBQ in Ft. Worth. Don’t let the location or the decor of the restaurant fool you, the food in this place is extremely good. I got to try the beef brisket and the chopped beef, both were tasty and extremely tender. A quality that seems to have all but disappeared in all the franchise restaurants I’ve stepped into so far.

No place is perfect though. The place could use some fresh decor and maybe upgrade the serving ware from styrofoam to dishes.  I also advise to get there before nightfall. The location isn’t exactly in the most tourist friendly area. The restaurant location is near an industrial area, so I’m not sure how safe it is in the evenings.

Pricing isn’t bad for what you get, expect to pay around $10 for a dinner platter or $5 for a sandwich. Alcohol is also served there.

All in all, if you’re looking for BBQ that doesn’t taste dry or like it was reheated after being stored in the freezer, I recommend trying Angelo’s BBQ.