Simple Sudoku solver

This Simple Sudoku Solver web page project is based on my article ‘Solving Sudoku with SQL’, published in the April 2006 edition of the VSJ. The tools used are Microsoft Visual Web Developer 2005 Express Edition, VB.NET Programming Language, and SQL Server 2005 Express. It is called ‘simple’ for two reasons: first it has a very basic user interface, and second the real business of solving the Sudoku is done by a single SQL stored procedure (pSolveSudoku) which was developed as a separate project; so we already have the engine, all we are doing is putting a user interface, written in ASP.NET 2.0, on top of it. The key ideas that this interface demonstrates are:

1. How to programmatically navigate the cells of an ASP:TABLE object
2. How to use CSS to format a table into a grid with different internal and external cell borders
3. How to use ASP.NET validation controls
4. How to use the Regex object
5. How to set up and use SQL Server database from Visual Studio 2005
6. How to call SQL Stored Procedure from ASP.NET
7. How to programmatically access web.config to get the connection string
8. How to use temporary tables for processes which must have separate tables for each session
9. How to deploy an ASP.NET Project
10. How to set up IIS to recognise ASP.NET 2.0 when it still thinks it is dealing with the older version.

The layout

Figure 1 shows the layout of the ASP.NET page for the Simple Sudoku Solver as seen in Visual Web Developer (or Visual Studio 2005).

Figure 1: The layout for the Simple Sudoku Solver

The first thing to note is that an HTML Table (<TABLE>) has been used for the overall outline. That is Controls/objects and texts have been placed in the cells (<TD>) of the table to create a standard grid layout.

The main objects on the page are the Input box (txtInput), the Solution Grid (tblSolutionGrid) and three command buttons ‘Display’ (btnDisplay), ‘Clear’ (btnClear) and ‘Solve’ (btnSolve).

Other objects are the validation Controls: a Required Validation control (valRqdInputBox), a Custom Validation control (valCustomInputBox) and the Validation Summary control (valSummary).

Figure 2: The Elements (or files) of the project as seen in the Solution Explorer

Figure 3: Grid Cell Borders

Visual Web Developer 2005 Express Edition makes the writing of ASP.NET web pages very easy. Start it up and choose ‘ASP.NET Web Site’ from the templates, specify a folder for your project and use Visual Basic as your preferred language. These steps generate most of the elements we need for the project. The style sheet gridLayout.css and the database ssudoku.mdf are to be added later. The files appear in the Solution Explorer, see Figure 3. The most important among them is the Default.aspx file, which we rename to ssudoku.aspx. This is where we place the controls (textboxes, buttons etc) and it forms the only page for our simple project. The final list of files is as shown in Figure 2.

The Visual Web Developer allows you to switch between ‘Design’ and ‘Source’ modes. In the design mode controls can be placed on the page by drag and drop, from the ‘Tool Box’.

In the ‘Source’ mode what you see is the straight ASP/ASP.NET source code where you have the ultimate flexibility to manually add and edit the tags. In most cases it is simpler to customise the tags using the property window. The complete HTML listing of the page is available from www.vsj.co.uk, and in most cases the changes needed to make the page look like Figure 1are fairly obvious.

The Display Grid

The display grid (tblSolutionGrid) is made out of a table server control (ASP:TABLE) which has 9 rows (ASP:TABLEROW), each row consisting of 9 cells (ASP:TABLECELL). To get the border lines in proper Sudoku style a CSS style sheet has been used. This contains named sections (CellTop, CellBottom etc. See Figure 3) that determine borders to be applied to cells within the ASP:TABLE. Notice that the CssClass property on each of the TableCells (<ASP:CELL>) is set to one of the named sections.

Look at the top of the ASP source ssudoku.aspx and you will find the following code.

<link href="gridLayout.css"
	rel="stylesheet" type="text/css" />

This tells the program to use the style sheet gridlayout.css for the page.

Part of the content of the CSS file, to demonstrate how cell styles are set, is shown below:

.cellLeft
{
	border-left-width:3px;
	border-right-width:1px;
	border-bottom-width:1px;
	border-top-width:1px;
	border-color:Black;
	border-style:solid;
	width:15px;
	height:15px
}
.cellRight
{
	border-left-width:1px;
	border-right-width:3px;
	border-bottom-width:1px;
	border-top-width:1px;
	border-color:Black;
	border-style:solid;
	width:15px;
	height:15px
}

The Code File

All the serious programming for the web page goes into the Code file ssudoku.aspx.vb. This contains all the event subroutines as well as all the other Subs and Functions. Separating code from HTML is one of the strong points of ASP.NET.

The Required Field Validation, valInputRqd was added to prevent the user from submitting blank entry in the input textbox. Setting up this validation requires no coding. Just drag the Required Validation control from the Tool box unto the page, set it to the control to validate and what error messages to display when the validation is violated.

The Custom Validation valInputCustom ensures that the right types of characters and the right number of characters have been typed into the input box before it is submitted. This validation does require some coding and is carried out in the Sub validateInputBox which consists of two processes:

• One of the requirements is that only the characters 0–9 should be used. This validation is done with the RegEx Object which is quite versatile in the way it allows texts to be compared and if you haven’t learned about regular expressions it is worth finding out more. For instance “[0-9]{81}” says ‘The first 81 characters must be 0,1,2…or 9’
• Another requirement is that the basic Sudoku rules must be obeyed by the contents of the input box (i.e. only one occurrence of a number in each row, column and box). This validation is achieved by passing the data to a stored procedure pCheckConsistency in the database. The article ‘Solving Sudoku with SQL’ explains how this works in detail.

The complete subroutine including the call to the stored procedure is:

Sub validateInputBox(ByVal source As Object,
	ByVal args As ServerValidateEventArgs)
		Handles valCustomInputBox.ServerValidate
	tableVal = Replace(args.Value, " ", "")
	tableVal = Replace(tableVal, Chr(13), "")
	tableVal = Replace(tableVal, Chr(10), "")
	tableValIsValid = False

'Ensure correct number of chracters and only 0-9
	Dim rgxpr As Regex = New Regex("[0-9]{81}")
	If Not rgxpr.IsMatch(
		Trim(tableVal)) Then
		valCustomInputBox.ErrorMessage =
			"What you have entered in
			the input box is not
			correct. Please ensure that
			it consists only of the
			numbers 0-9 and there is a
			total of 81 numbers. See
			example."
		args.IsValid = False
	Else
		args.IsValid = True
		tableValIsValid = True
	End If

'Check the consistency of the data
	Dim constr As String
	constr =
ConfigurationManager.ConnectionStrings(
		"DatabaseConnectionString1"
		).ConnectionString
	Dim conn As New
		 Data.SqlClient.SqlConnection(
		constr)
	conn.Open()
	Dim cmd As New
		Data.SqlClient.SqlCommand()
	cmd.CommandType =
		Data.CommandType.Text
	cmd.Connection = conn
	cmd.CommandTimeout = 66
	cmd.CommandText =
		"EXEC pCheckConsistency '" +
		tableVal + "'"
	Dim strCheckConsistencyResult As
		String =
		cmd.ExecuteScalar()

	If strCheckConsistencyResult =
		"NOT OK" Then
		valCustomInputBox.ErrorMessage =
		"The same number has been
		repeated along the same row,
		column or block"
		tableValIsValid = False
		args.IsValid = False
	End If
End Sub

The database

Setting up a database for the project is greatly simplified by Microsoft Visual Web Developer 2005. It is only a matter of adding a new SQL Database item to the project, in the Solution Explorer and voilà! A self-contained database file is attached to the project with the flexibility of an Access Database and all the power of a full RDBMS; the entire panoply of stored procedures, triggers, indexes, cursors etc all at your beck and call! You can view all of these in the Database Explorer Window.

On this occasion only stored procedures will be used. Moreover our stored procedures have already been developed and are fully discussed in the previous article. We will therefore not be dwelling upon them in any great detail except to point out minor modifications that have had to be made in order to adapt them for this project. Firstly, the two main tables in the previous scripts have been converted to temporary tables which are created in the main stored procedure pSolveSudoku (in SQL Server, temporary tables begin with ‘#’). Using temporary tables will make it possible to run the stored procedure in more than one session at a time. Using the physical tables would cause all sorts of concurrency issues. If you create a temporary table in a stored procedure, that table can be used by other stored procedures that are called by the original stored procedure. This is possible because, of course, they are all being used in the same session. Secondly, as already mentioned, a new stored procedure pCheckConsistency has been added for checking that the puzzle input by the user does not violate basic Sudoku rules.

For each of the required Stored procedures right click on the ‘Stored Procedure’ node in the Database Explorer and select ‘Add New Stored Procedure’ and then paste the script for the SP in the resulting window and save it. The files for the entire project as well as the scripts for all of the SP’s are available from the code bin.

The business of connecting to the database and executing the stored procedure is carried out with ADO.NET objects: SqlConnection, SqlCommand and SqlDataReader. Probably the most important property of the SqlConnection object is the connectionString which specifies the connection details for the database. It is normally something like:

constr = "Data Source=
	.\SQLEXPRESS;AttachDbFilename=
	""C: \SSudoKu\App_Data\
	Database.mdf"";Integrated
	Security=True;User Instance=True"

However, since the information is in the configuration file web.config we use:

constr = 
ConfigurationManager.ConnectionStrings(
	"DatabaseConnectionString1"
	).ConnectionString

As you can see, we are using the ConfigurationManager object to extract the information based on the identifier ‘DatabaseConnectionString1’ which is the key for the connection string in web.config:

<?xml version="1.0"?>
<configuration 
xmlns="http://schemas.microsoft.com/
	.NetConfiguration/v2.0">
<appSettings/>
<connectionStrings>
<add name="DatabaseConnectionString1"
	connectionString="Data
	Source=.\SQLEXPRESS;
	AttachDbFilename=|DataDirectory|\
	Database.mdf;
	Integrated Security=True;
	User Instance=True"
	providerName=
	"System.Data.SqlClient" />
</connectionStrings>
...
</configuration>

How the command buttons work

When the user clicks on any of the buttons on the page, the page is submitted to the Web Server. This causes the validations to take place. After successful validation the ‘Click’ event for that button is triggered, otherwise the validation error message is displayed. The ‘Click’ events for our three buttons are btnClear_Click, btnDisplay_Click and btnSolve_Click. They appear as Subs in the Code File ssudoku.aspx.vb.

When you click on the ‘Clear’ button it calls the ClearGrid() Subroutine which clears the grid:

Protected Sub btnClear_Click(
	ByVal sender As Object,
	ByVal e As System.EventArgs)
		Handles btnClear.Click
	ClearGrid()
End Sub

ClearGrid basically sets all the cells in the display grid to blank and resets the cell format.

Sub ClearGrid()
	Dim r As Control
	Dim c As Control
	Dim tblCell As TableCell
	For Each r In tblSolutionGrid.Rows
		For Each c In r.Controls
			tblCell = c
			tblCell.Text = ""
			tblCell.Font.Bold = False
			tblCell.ForeColor =
				Drawing.Color.Black
		Next
	Next
End Sub

Note the ease with which we are able to traverse and access the cells in tblSolutionGrid through the use of two nested ‘For Each…Next’ loops and the Control object, to identify rows, and the cells within each row.

Clicking the ‘Display’ button causes the Display Grid to be populated with the data in the input textbox. This is achieved with the VB routine FillGrid():

Protected Sub btnDisplay_Click(
	ByVal sender As Object,
	ByVal e As System.EventArgs)
		Handles btnDisplay.Click
	FillGrid(tableVal)
End Sub

FillGrid() first clears the display grid and then places the value of each character that is in the input string strData into each of the cells. We access each element of strData using the Mid function (still among VB6 virtuosos and still part of VB.NET) and assigning it to each cell in the grid as required:

Sub FillGrid(ByVal strData As String)
	ClearGrid()
	If Not tableValIsValid Then _
		Exit Sub
	Dim r As Control
	Dim c As Control
	Dim i As Integer
	Dim tblCell As TableCell
	For Each r In tblSolutionGrid.Rows
		For Each c In r.Controls
			i += 1
			tblCell = c
	If Mid(strData, i, 1) = "0" Then
				tblCell.Text = ""
			Else
	tblCell.Text = Mid(strData, i, 1)
			End If
		Next
	Next
	HilightGrid(tableVal)
End Sub

Finally, when you click on the ‘Solve’ button, the program goes to the database and executes the stored procedure pSolveSudoku. The result is extracted as a string which is similar to the input string and it is passed to FillGrid(), to populate the Display Grid:

Protected Sub btnSolve_Click(
	ByVal sender As Object,
	ByVal e As System.EventArgs)
			Handles btnSolve.Click
	If getGridString() = "" Then _
		Exit Sub
	'if there is no value the there is
	'nothing to solve!
	Dim constr As String
	constr = _
ConfigurationManager.ConnectionStrings(0). _
		ConnectionString
	Dim conn As New _
Data.SqlClient.SqlConnection(constr)
	conn.Open()
	Dim cmd As New _
		Data.SqlClient.SqlCommand()
	cmd.CommandType = _
		Data.CommandType.Text
	cmd.Connection = conn
	cmd.CommandTimeout = 60
	cmd.CommandText = _
		"EXEC pSolveSudoku '" + _
		 tableVal + "'"
	Dim myReader As
	Data.SqlClient.SqlDataReader = _
			cmd.ExecuteReader()
'return the result as a single string
	Dim returnstr As String = ""
	Dim i As Integer
'the expected resultset is 9 rows
'each row read as a string.
	While myReader.Read()
		For i = 0 To 8
			returnstr += _
				myReader.GetString(i)
		Next
	End While
	myReader.Close()
	FillGrid(returnstr)
End Sub

Note that at the beginning of the sub we check whether the input string is blank before going ahead with the process. The function getGridString() which is used to determine the string that is currently displayed on the grid, i.e. the content of the display grid, is:

Function getGridString() As String
	Dim r As Control
	Dim c As Control
	Dim tmpStr As String = ""
	Dim tblCell As TableCell
	For Each r In tblSolutionGrid.Rows
		For Each c In r.Controls
			tblCell = c
			tmpStr += tblCell.Text
		Next
	Next
	getGridString = tmpStr
End Function

Although the aim was to keep the project simple, there was just one extra that seems worthwhile: to distinguish the numbers provided by the user from the ones that were generated in the solution, the routine HilightGrid() paints all the original numbers red. This routine is called each time the FillGrid() routine is called.

Sub HilightGrid(ByVal _
	strData As String)
	If Not tableValIsValid Then _
		Exit Sub
	Dim r As Control
	Dim c As Control
	Dim i As Integer
	Dim tblCell As TableCell
	For Each r In tblSolutionGrid.Rows
		For Each c In r.Controls
			i += 1
			tblCell = c
If Not Mid(strData, i, 1) = "0" Then
				tblCell.Font.Bold = True
				tblCell.ForeColor = _
					Drawing.Color.Red
			End If
		Next
	Next
End Sub

Deployment

Once the project is completed within Microsoft Visual Web Developer/Visual Studio 2005, it can be deployed by simply copying the project directory to the desired internet folder, and applying the necessary virtual folder settings.

It is refreshingly simple given that a SQL Server database is involved!

Conclusion

This exercise has demonstrated how to attach a simple web-based user interface in ASP.NET 2.0 to a stored procedure, in SQL SERVER 2005.

It’s a good example of how a database can supply procedural logic as well as data.

---

Samuel Aina MIAP (Member of the Institution of Analysts and Programmers) is an IT Consultant working mainly for commercial banks and programming in Visual Basic and SQL Server, among other platforms.