Introduction
The article covers the start implements a simple search engine in C.
The first version released on the 08/22/2013 performs only Queries. The new version implements CRUD functions, which means, you can create new record, read records, update records and delete records. The new versions utilize the jQuery plugins DataTables and editable. The code from version 1 was cleaned up, and null values are better handled.
I normally use SQL-Server and C#. But the friend I teach C/C++ programming to wants to stay away from Microsoft. In the past, MySQL wasn't a database for me, because in the standard installation, it lacked the support of transactions, but it's getting mature. I use MySQL 5.6 64 bit with an innodb as Database and Unicode(utf8) encoding, which is the default for new databases.
Freetext is a fresh new feature for innodb, it was first introduced with version 5.6 of MySQL.
I generally prefer C++ over C, even in small projects: it's nice not to know all the functionnames, but have some classes with the needed operations bundled and nice intellisense support. In C++, you too have the STL or Boost for collections and strings.
The C++ interface of MySql is rather small, while there's a huge matured C-Interface, so I decided to use the C-Interface.
The C-dll is published with WCF for Ajax requests. In Visual Studio Ultimate 2012, I used the C# "WCF Service Application" Template. I searched for ways to host the WebService in C++, but only found some samples for calling WebServices in managed C++.
The userinterface for the query only step is an HTML Page which uses JQuery and autocomplete from Jquery-UI, the page is added to the Site of the "WCF Service Application", the project is named VisionWeb. The page is named "DefaultQueryOnly.html".
The Webpage looks like this:
For version 2, I used the file "default.html". Pages look like this:
I configured the projects for framework 4.0, 64 bit and debug. If you use a 32 bit MySql server, you have to change this. You must leave the option UNICODE on its default value true.
Configuring for MySQL
You probably will open The VisionDAL project from the VisionSmall Solution, perhaps you have to modify the linkage of the MySQL C-Interface. Here, I describe how to setup the MySQL-Interface in an new project: check if these settings are right for you, in special the path to the mysql.lib file and VisionDAL.dll.
In Visual Studio, add a project VisionDAL, using the template "Other Languages/Visual C++/Empty Project". In the wizard, you only need to change the "Application type" to DLL. Rename VisionDAL.cpp to VisionDAL.c, this is enough to tell Visual Studio to switch from compiling C++ to C. Add a header file named "VisionDAL.h" to the project.
In the Solution Explorer, right click on the VisionDAL project and choose Properties. Under "Configuration Properties"/Linker/Input, choose "Additional Dependencies" and add libmysql.lib to the path, don't forget the separating ";".
Under "Configuration Properties"/Linker/General, choose "Additional Library Directories" for me, add C:\Program Files\MySQL\MySQL Server 5.6\lib>. Now we have linked in the C-Interface, but the DLL implementing the calls in libmysql.lib must be in the system search path for excecutables: from the Control Panel choose System, click "Advanced system settings", "click Environment Variables" under "System Variables" choose Path and add the libmysql.lib path (DLL is in the same folder with the lib file): ;C:\Program Files\MySQL\MySQL Server 5.6\lib.
We need to have VisionDal.dll in our Path too, IIS won't find it when you put the DLL into its bin folder of the website. Add <path to the solution>/x64/debug to the path. I needed to reboot to get this setting effective, eventually helps restarting the Application Pool, by toggling the setting "Managed Pipeline Mode" - you can do this in IIS Manager. When the website gets its first request, it will load VisionDAL.dll, when you now rebuild the project, you will get a write error on the VisionDAL.dll: to fix it, reset the Application Pool or use a tool like unlocker.
Than we specify the include properties for VisionDAL. Under "Configuration Properties"/"C/C++", add your MySQL header file path, for example: C:\Program Files\MySQL\MySQL Server 5.6\include.
Next, we switch "Precompiled Headers" to "Not Using Precompiled Headers" which is under "C/C++"/"Precompiled Headers" . To avoid error messages when using strcpy and fopen, set the Preprocessor definitions: USE_STANDARD_FILE_FUNCTIONS and _CRT_SECURE_NO_WARNINGS under "C/C++"/Preprocessor/"Preprocessor Definitions".
When you link now the mysqllib references are not resolved, because they are 64 bit procedures. Fix this by opening the project properties from VisionDAL, choose the button "Configuration Manager" and set the platform to x64.
Now We Create Our Sample Database Named Vision
Open the MySql Workbench under SQL Development, open your instance. A new window "SQL File 1" appears. Double Click on the Sql.txt file in the VisionDAL project. Copy all the content to the clipboard, paste in into the "SQL File 1" window in the workbench. Click on the flash icon (the third icon on the left) to create the sample database.
Next We Need the Usual Information for a Database Login
We have a configuration file for this: <installation director>VisionSmall\x64\Debug\VisionConfiguration.txt, which looks like this:
Host: localhost
User: root
Password: frob4frob
Database: vision
Port: 3306
Modify the values to match your MySQL-Configuration.
The Vision Database
There is only one table in the database:
CREATE TABLE `document` (
`DocumentID` int(11) NOT NULL AUTO_INCREMENT,
`Title` varchar(255) DEFAULT NULL,
`Text` text,
PRIMARY KEY (`DocumentID`),
FULLTEXT KEY `ft` (`Title`,`Text`),
FULLTEXT KEY `ftTitle` (`Title`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
When we search, we use the fulltext index named `ft`, when looking for autocomplete words, we use the fulltext named 'ftTitle'.
If you have a fulltext index including many fields, you can choose in Microsoft SQL Server when querying, which fields should be included in the search. In MySql, always all fields from the fulltext index are searched, so we have to specify the additional fulltext index 'ftTitle'.
Querying MySQL with the C-Interface
First, we need to connect to the database and get a MYSQL pointer for further access:
MYSQL *Connect(){
MYSQL *conn;
conn = mysql_init(NULL);
if(mysql_real_connect(
conn, Configuration.Host, Configuration.User, Configuration.Password,
Configuration.Database, Configuration.Port, NULL, 0) == NULL) {
fprintf(stderr, "sorry, no database connection ...\n");
return NULL;
}
return conn;
}
At startup, we fill the global Configuration struct with the values from the configuration file VisionConfiguration.txt, which should be in the same directory as VisionDAL.dll. The routine to read the settings is ConfigurationRead. To get the path of the currently executing module, it uses GetModuleFileName from the Win32-Api:
TCHAR *GetExecutablePath(){
HMODULE hMods[1024];
HANDLE hProcess;
DWORD cbNeeded;
unsigned int i, j;
DWORD processID = 0L;
TCHAR fileName[MAX_PATH];
TCHAR *szModName=(TCHAR *) malloc(MAX_PATH*2);
int size=MAX_PATH*2;
processID = GetCurrentProcessId();
hProcess = OpenProcess( PROCESS_QUERY_INFORMATION |
PROCESS_VM_READ,
FALSE, processID );
if (NULL == hProcess)
return NULL;
if( EnumProcessModules(hProcess, hMods, sizeof(hMods), &cbNeeded)){
for ( i = 0; i < (cbNeeded / sizeof(HMODULE)); i++ ){
szModName[0] = 0;
fileName[0]=0;
if ( GetModuleFileNameEx( hProcess, hMods[i], szModName,
size )){
int lastBackSlash=0;
int k=0;
for(k=0; k <(int) wcslen(szModName) -1; k++){
if(szModName[k] == '\\') lastBackSlash = k;
}
if(lastBackSlash == 0) continue;
szModName[lastBackSlash]=0;
for(k=lastBackSlash + 1, j=0; szModName[k] != 0; k++, j++){
fileName[j]=szModName[k];
}
fileName[j]=0;
wStrLower(fileName);
if(!wcscmp(L"visiondal.dll", fileName)){
return szModName;
}
}
}
}
return NULL;
}
There is only one routine we want to expose: GetDocuments. Definition in the header file:
#define FORMAT_TEXT 0
#define FORMAT_JSON 1>
#define FORMAT_DATATABLE 2
__declspec(dllexport) wchar_t* __cdecl GetDocuments(TCHAR *search, int format, int forAutocomplete);
Definition in the source file:
__declspec(dllexport) wchar_t* GetDocuments(TCHAR *search, int format, int forAutocomplete)
__declspec(dllexport) on the declaration and definition effectuate that the call is added to the file VisionDAL.lib and exported in the VisionDAL.dll file. __cdecl defines how to call the procedure, here we use C-Style calling conventions. TCHAR is a define which is the same as WCHAR when the UNICODE definition is set otherwise it's a simple char, in our case UNICODE is turned on.
- Note that there are different Unicode-Formats
- In C code, we use a 2 byte value to denote a
char value - In MySql and the .NET Framework, the format is UTF-8, which means one byte is used for each character and only on demand more than one byte are used
- In Console Applications, you usually use one byte for each
char and use Codepage 850 for the values greater than 127.
The Parameter format is FORMAT_TEXT or FORMAT_JSON, to toggle the output between text and JSON. Version 2 adds the format FORMAT_DATATABLE, which formats the output in the standard format for DataTable.
If forAutocomplete is true, only the Title is searched and returned.
VisionDALClientConsole
VisionDALClientConsole is a tiny Windows Console Application, to test our GetDocuments procedure.
It has a reference to the VisionDAL project set. Its outputfiles go to VisionSmall\x64\Debug together with the output from VisionDAL.
VisionDALClientConsole asks for the search string, the wildcard is "*", it searches the columns title and text and outputs the text from the GetDocuments call.
A sample run:
The main routine:
int _tmain(int argc,TCHAR* argv[])
{
char c;
TCHAR *result;
TCHAR *search = (TCHAR *)malloc(1000*2);
char *searchA = (char *)malloc(1000);
int retval = 1;
char buffer[32000];
buffer[0]=0;
printf("Search for: ");
if(scanf("%[^\n]", searchA) <= 0){
printf("Could not read input - retrieving all Documents \n");
*search=0;
}else{
MultiByteToWideChar(850,0,searchA, -1,search, 999);
}
result=GetDocuments(search, FORMAT_TEXT, 0);
if(result == NULL){
retval = 0;
}else{
WideCharToMultiByte(850,0,result, -1,buffer, 32000,NULL,NULL);
printf("%s", buffer);
}
fflush(stdin);
printf("Press RETURN Key to Exit\n");
getchar();
return retval;
}
In Microsoft C V. 12, you have routines to deal with Unicode-16 strings. The have a starting additional w or replace str with wcs, for example: wscanf, wprintf instead of scanf and printf and wcslen instead of strlen. Using wscanf did not get the umlauts right. I used MultiByteToWideChar using codepage 850 to get the wide chars and WideCharToMultiByte to convert back to chars.
Querying the MySQL Database
Above, I showed how to connect to the database and get a MYSQL pointer named conn.
Next, we build the SQL-Query, here is the code for generating the list for autocomplete:
mysql_query(conn, "SET NAMES 'utf8'");
if(forAutocomplete){
if(search == NULL || wcslen(search) ==0){
WideCharToMultiByte(CP_UTF8,0,L"SELECT Title from Document LIMIT 20",-1,
sql,1000,NULL,NULL);
}else{
wsprintf(lbuffer, L"SELECT Title, match(Title) against('%ls' IN BOOLEAN MODE)
as Score from Document where match(Title) against('%ls' IN BOOLEAN MODE) > 0.001
order by Score Desc LIMIT 20",
search, search);
WideCharToMultiByte(CP_UTF8,0,lbuffer,-1,sql,1000,NULL,NULL);
}
}
match(Title, Text) against('%ls' IN BOOLEAN MODE) searches for the search string in the columns Title and Text which returns a value how good the match is. Only documents with a Score greater 0.001 are displayed, the result is ordered by the score.
IN BOOLEAN MODE effectuate that the search for the multiple words are interpreted as or.
In the search string, you can use "*" as wildchar, which will match 0 to n characters. For example, "as*" will find ASP. The search is not case-sensitive. Some things are annoying: "as**" won't find anything; "*sp" or "*" won't match anything - in Microsoft SQl Server, you can match wildcards at the beginning of a string.
Getting the Data
...
if(mysql_query(conn, sql)) {
fprintf(stderr, "%s\n", mysql_error(conn));
fprintf(stderr, "%s\n", sql);
return NULL;
}
...
result = mysql_store_result(conn);
...
while((row = mysql_fetch_row(result)) != NULL) {
...
}else if(format == FORMAT_JSON){
if(!forAutocomplete){
MultiByteToWideChar(CP_UTF8,0,row[0], -1,buffer, 255);
wsprintf(resultBufferp,L"{\"DocumentID\": %s, ", buffer);
resultBufferp+=wcslen(buffer)+wcslen(L"{\"DocumentID\": , ");
if(row[1]){
MultiByteToWideChar(CP_UTF8,0,row[1], -1,buffer, 255);
wsprintf(resultBufferp,L"\"Title\": \"%s\", ", buffer);
resultBufferp+=wcslen(buffer)+wcslen(L"\"Title\": \"\", ");
}else{
wsprintf(resultBufferp,L"\"Title\": null, ");
resultBufferp+=wcslen(L"\"Title\": null, ");
}
if(row[2]){
MultiByteToWideChar(CP_UTF8,0,row[2], -1,buffer, 32000);
wsprintf(resultBufferp,L"\"Text\": \"%s\"},", buffer);
resultBufferp+=wcslen(buffer)+wcslen(L"\"Text\": \"\"},");
}else{
wsprintf(resultBufferp,L"\"Text\": null},");
resultBufferp+=wcslen(L"\"Text\": null},");
}
}
...
}
...
}
mysql_query sends the query to the server. mysql_store_result prepares the result as a collection, which you iterate using mysql_fetch_row(result). The row is an array of strings, ignoring which datatype the column has. I prefer the typed columns which you have in ADO.NET. In .NET, we probably would use a StringBuilder to agreggate the result string, here we allocate a char[] with malloc and increment the pointer resultBufferp, to which we write. I use MultiByteToWideChar to convert to WCHAR.
The JSON Format
I decided to use the lightweight JSON-Format, for communication from the Webpage using Ajax to the Webservice, instead from XML.
The JSON-output when using FORMAT_DATATABLE looks like this:
[{"DocumentID": 1, "Title": "ASP MVC 4", "Text": "Was für Profis"},
{"DocumentID": 2, "Title": "JQuery", "Text":
"Hat Ajax Support"},{"DocumentID": 3, "Title": "
WebServices", "Text": "Visual C++ kanns nicht"},
{"DocumentID": 4, "Title": "Boost", "Text": "Muss Extra installiert werden"}]
The JSON-output looks like this when the parameter autocomplete is true:
["ASP MVC 4","JQuery","WebServices","Boost"]
DataTable expects a 2 dimensional array which look like this:
[[1, "ASP MVC 4", "Was für Profis"],[2, "JQuery", "Hat Ajax Support"]]
But it's possible to use an array of objects, as shown above, for format FORMAT_DATATABLE.
The "[]" denotes the start and end of array, "{}" marks the beginning and end of an object. In an object, the part before the ":" is the property name, the part after is the value. This is nearly the same, as when you code the values in JavaScript. With the JavaScript-Command JSON.parse you get a full fledged object, whose properties are accessible using the usual "." notation.
Hosting the Webservice for the GetDocuments Method
I created the project VisionWeb using the template "Visual C#/WCF/WCF Service Application", which added the needed references like System.ServiceModel.
Next, we use NuGet to add the needed JavaScript libraries. Choose "Tools/Library Packet Manager/Package Manager Console" and issue the commands:
Install-Package jQuery
Install-Package jQuery.UI.Combined
Next We Define the Service Contract in the File "App-Code/IVisionService.cs"
namespace VisionServices
{
[ServiceContract(SessionMode = SessionMode.Allowed)]
public interface IVisionService
{
[OperationContract]
[WebInvoke(
Method = "POST",
BodyStyle = WebMessageBodyStyle.WrappedRequest,
RequestFormat = WebMessageFormat.Json,
ResponseFormat = WebMessageFormat.Json)]
string GetDocuments(string search, int format, int forautocomplete);
}
}
The WebInvoke attribute ensures that the service can be called by Ajax calls. As method, I chose POST which sends the parameter in the body of the HTTP-Request. The alternative GET, would encode and unveil the parameters in the URL.
We specify that request and response are sent in JSON-Format. BodyStyle = WebMessageBodyStyle.WrappedRequest must be used when more than one parameter is used. You can use WebMessageBodyStyle.Bare if you have zero or one parameter.
Implementation of the Webservice
We define the implementation of GetDocuments in "App-Code/VisionService.cs":
namespace VisionServices
{
public class PInvoke
{
[DllImport("VisionDAL.dll", CharSet = CharSet.Unicode)]
[return: MarshalAs(UnmanagedType.LPWStr)]
public static extern string GetDocuments(string search, int format, int forAutocomplete);
}
public class VisionService : IVisionService
{
public string GetDocuments(string search, int format, int forautocomplete)
{
string result = PInvoke.GetDocuments(search, format, forautocomplete).ToString();
return result;
}
}
}
Implementation of VisionService.svc
<%@ ServiceHost Language="C#" Debug="true" Service="VisionServices.VisionService"
CodeBehind="App_Code\VisionService.cs" %>
This defines the endpoint on which our service can be called to "http://<your webserver>:<your port>VisionService.svc", the URL to call our GetDocuments function is "http://<your webserver>:<your port>/VisionService.svc/GetDocuments".
The Web.config
="1.0"
<configuration>
<appSettings/>
<system.web>
<httpRuntime/>
<compilation debug="true"/>
</system.web>
<system.serviceModel>
<services>
<service name="VisionServices.VisionService">
<endpoint address="" binding="webHttpBinding"
contract="VisionServices.IVisionService" behaviorConfiguration="webHttpEndpoint"/>
<endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>
</service>
</services>
<behaviors>
<endpointBehaviors>
<behavior name="webHttpEndpoint">
<webHttp helpEnabled="true"/>
</behavior>
</endpointBehaviors>
<serviceBehaviors>
<behavior>
<serviceMetadata httpGetEnabled="true" httpsGetEnabled="true"/>
<serviceDebug includeExceptionDetailInFaults="true"/>
</behavior>
</serviceBehaviors>
</behaviors>
<serviceHostingEnvironment aspNetCompatibilityEnabled="false"
multipleSiteBindingsEnabled="true"/>
</system.serviceModel>
<system.webServer>
<modules runAllManagedModulesForAllRequests="true"/>
<directoryBrowse enabled="true"/>
</system.webServer>
</configuration>
These are the settings to allow Ajax-calls. There are quite a lot options for configuring WCF. You might look at further documentation like [2], which is on Safari.
<endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/> defines the endpoint for metada exchange. From the metadata, you can automatically generate code to get a proxy for the Webservice, for example, with svcutil. Choose "Programs/Microsoft Visual Studio 2012/Visual Studio Tools/Developer Command Prompt for VS2012". Enter svcutil http://localhost:8001/VisionService.svc. A file VisionService.cs is generated, in other cases, a file containing the configuration of the Webservice is generated too.
Note that Visual Studio has a graphical Editor for the Webservice- Configuration. It's under "Tools/WCF Service Configuration Editor".
Hosting the Website
Start "Settings/ControlPanel/Administrative Tools/Internet Information Services (IIS) Manager". When it's missing install IIS. Navigate to "Application Pools", grab the name of a pool running .NET Framework Version 4.0*, or add a new Application Pool. Navigate to the "Sites" node, right click it and choose "Add Website...". Use Vision as Site name, for the Application Pool, choose a pool running .NET Framework 4, as physical path use <vision installdir>/VisionWeb, set the port to 8001. Choose Properties on the VisionWeb project, choose Web, check "Use Custom Web Server", enter http://localhost:8001 as Server URL. You can host the site with other options, for example in IIS Express, but if you don't want to change the files Default.html and DefaultQueryOnly.html, you have to set the port to 8001.
The HTML/JQuery Page
In the VisionWeb version 1 project is a single HTML-Page named "DefaultQueryOnly.html", which contains the HTML and JavaScript content. In version 2, the solution has "Default.html" as startpage.
Here's again what the webpage looks like:
<html>
<head>
<title>Search</title>
<script src="Scripts/jquery-2.0.2.js"></script>
<script src="Scripts/jquery-ui-1.10.3.js"></script>
<link href="Content/themes/base/jquery.ui.autocomplete.css" rel="stylesheet" />
<style type=text/css>
.ui-menu-item {
background: White;
}
.ui-helper-hidden-accessible { display:none; }
</style>
</head>
´The <code>html</code> states that this has a doctype of HTML 5. Then, we include the necessary JavaScript files. From JQuery-UI, we use only the autocomplete widget, for which we include its CSS file.
For the autocomplete object, which contains the class .ui-menu-item, we set the background to white, otherwise it's transparent and the content of the table shows through..ui-helper-hidden-accessible { display:none; } removes an annoying help-message from the autocomplete-widget.
<form>
<label for="search" >Search:</label>
<input type="text" id="search", name="search" />
<input type="button" id="update" name="update" value="Update" />
<div id="result"></div>
</form>
The elements in the form are given ids, so that you can retrieve them with JQuery like $('#result'). Instead of the shortcut $, you can use jQuery, for example [__em all="[object HTMLCollection]"__] jQuery('#result'). The JavaScript call document.getElementById('result'), has the same effect, but JQuery supports all kinds of css-selectors and more.
I use unobtrusive JavaScript, which means the html-code is not mixed with JavaScript. The eventhandler is set in the function $(document).ready(function () which is called when the page has loaded and is ready.
function GetDocuments(e) {
var searchstring = AddWildcards($('#search').val());
$.ajax({
type: 'POST',
url: 'http://localhost:8001/VisionService.svc/GetDocuments',
dataType: 'json',
crossDomain: true,
data: JSON.stringify({ search: searchstring, format: 1, forautocomplete: 0 }),
processData: true,
contentType: "application/json ; charset=utf-8",
success: function (json, textStatus) {
var result = JSON.parse(json);
var display;
display = "";
display += "<table id='mytable' border=2>
<thead><tr><th style='text-align:left' >ID</th>
<th style='text-align:left' >Title</th><th style='text-align:left' >Text</th>
</tr></thead>";
display += "<tbody>";
$.each(result, function (index, value) {
display += "<tr>";
display += "<td>" + value.DocumentID + "</td>";
display += "<td>" + ((value.Title != null)?value.Title:"") + "</td>";
display += "<td>" + ((value.Text != null)?value.Text:"") + "</td>";
display += "</tr>";
});
display += "</tbody></table>";
$('#result').empty()
$('#result').html(display);
},
error: function (xhr, textStatus, errorThrown) {
alert('An error occurred! ' + (errorThrown ? errorThrown : xhr.status) +
" xhr: " + xhr + " textStatus: " + textStatus);
}
});
}
The GetDocuments call executes, when you click on the Update-Button or press enter in the search fields. It performs a fulltext query and displays the result in an HTML-Table:
We get into the variable searchstring the value of our search form field, and add the wildcard * to each of its words in AddWildcards, when it is not present. JQuery has AJAX support, for example the $.ajax call.
You can find a reference for this call here: jQuery.ajax().
The url: points to the address we configured in our WCF-application. As set in our WCF-Application, we use JSON as dataformat. In the function success:which is asynchronous called when the ajax call suceeds, we get in the variable json, the output from our GetDocuments call. A simple call to JSON.parse(json), gives us a full fledged JavaScript object, from which we generate an HTML-Table. The variable result is an array of JavaScript objects. The jQuery $.each call iterates over the array and calls the given function with the index of the current array element and the object at the current position. With $('#result').html(display), we display our html string display as the html from our result div. Under data:, we specify the parameters for the call as JavaScript object which we prepare for the transport with the JSON.stringify call. When an error occurs, the code after error: gets executed.
How Autocomplete Works
At the beginning of our JavaScript code, we declare a global variable to hold the words for autocomplete in an array: var Documents = [];. The function GetAutocomplete fills the Documents array. The Autocomplete function:
function GetAutocomplete(e) {
var searchstring = $('#search').val();
if (searchstring.length > 0) {
if (searchstring[searchstring.length - 1] != "*") {
searchstring += "*";
}
}
$.ajax({
type: 'POST',
url: 'http://localhost:8001/VisionService.svc/GetDocuments',
dataType: 'json',
data: JSON.stringify({ search: searchstring, format: 1, forautocomplete: 1}),
processData: true,
async: false,
contentType: "application/json ; charset=utf-8",
success: function (json, textStatus) {
Documents = JSON.parse(json);
},
error: function (xhr, textStatus, errorThrown) {
alert('An error occurred! ' + (errorThrown ? errorThrown : xhr.status) +
" xhr: " + xhr + " textStatus: " + textStatus);
}
});
}
This looks quite similar to the GetDocuments function.The success function only updates the Documents variable, with a JSON.parse over the output of our Webservice. Note the async: false, which causes the call to be synchronized. The autocomplete widget will call our GetAutocomplete function and immediately display the Documents.
Initialization from the autocomplete widget in the $(document).ready(function ():
$("#search").autocomplete({
source: function (request, callback) {
GetAutocomplete();
callback(Documents);
},
open: function (event) {
var $ul = $(this).autocomplete("widget");
}
});
You can find information about autocomplete here.
Handling from the [RETURN] key in the search box:
$('#search').bind("keydown", GetInput);
function GetInput(e) {
if (e.keyCode == 13) {
e.preventDefault();
GetDocuments(e);
$('#search').autocomplete("close");
}
}
The e.preventDefault(); stops the processing of the current event.
Debugging
You can enter the URL http://localhost:8001/VisionService.svc in your web browser. If there's a service activation error, a message will be displayed, for example that the VisionDAL.dll could not be loaded, or WCF is not installed, which will cause a message about a missing handler You can examine the http-Communication with a tool like Fiddler.
Debugging VisionDAL, when running VisionWeb doesn't work. Start Vision without debugging and attach to w3wp.exe.
VisionSmall Version 2 - Implementing CRUD Operations
Until now, I showed you DefaultQuery, we switch over to Default.html.
We click "Tools/Library Packet Manager/Manage NuGet Packages for Solution...", search for jquery.datatables, and add the result package.
We add a global variable, called oTable to hold a reference for the created table. We change GetDocuments to use DataTable.
function GetDocuments(e) {
var searchstring = AddWildcards($('#search').val());
$.ajax({
type: 'POST',
url: 'http://localhost:8001/VisionService.svc/GetDocuments',
dataType: 'json',
data: JSON.stringify({ search: searchstring, format: 2, forautocomplete: 0 }),
contentType: "application/json ; charset=utf-8",
success: function (json, textStatus) {
var result = JSON.parse(json);
var display;
display = "";
display += "<table id='mytable' border=2>
<thead><tr><th style='text-align:left' >ID</th>
<th style='text-align:left' >Title</th>
<th style='text-align:left' >Text</th></tr></thead>";
display += "<tbody>";
display += "</tbody>";
display += "</table>";
if (oTable) {
$('#mytable').remove();
oTable = null;
}
$('#result').empty()
$('#result').html(display);
oTable = $('#mytable').dataTable({
"aaData": result,
"bPaginate": false,
"bLengthChange": false,
"bFilter": false,
"bSort": true,
"bInfo": false,
"bAutoWidth": false
});
...
}
...
}
oTable = $('#mytable').dataTable( converts the HTML-Table to a DataTable. The contents of the table are passed by the "aaData": result, property. The table header is generated the some way as in version 1 by emitting HTML-Code. Instead, you specify columns for the DataTable.
function makeSelectable(e) {
if ($(this).hasClass('row_selected')) {
$(this).removeClass('row_selected');
}
else {
$(this).addClass('row_selected');
}
};
Implementing delete
First, we make the tablerows selectable by calling oTable.$("tbody tr").click(makeSelectable) in our GetDocuments function. Later, only the first column is selectable, because we apply editable on all columns, despite the first "ID" column.
function fnGetSelected(oTableLocal) {
var aReturn = new Array();
var aTrs = oTableLocal.fnGetNodes();
for (var i = 0 ; i < aTrs.length ; i++) {
if ($(aTrs[i]).hasClass('row_selected')) {
aReturn.push(aTrs[i]);
}
}
return aReturn;
}
...
$('#delete').click(function () {
if(!oTable)return;
var ids = new Array();
var anSelected = fnGetSelected(oTable);
if (anSelected.length == 0) return;
for (i = anSelected.length - 1; i >= 0; i--) {
if (anSelected[i].cells[0].innerHTML != "" && anSelected[i].cells[0].innerHTML != null) {
ids.push(parseInt(anSelected[i].cells[0].innerHTML));
oTable.fnDeleteRow(anSelected[i], null, false);
}
}
oTable.fnDraw();
$.ajax({
type: 'POST',
url: 'http://localhost:8001/VisionService.svc/DeleteDocuments',
dataType: 'json',
data: JSON.stringify({"ids": ids}),
contentType: "application/json ; charset=utf-8",
asyn: false,
success: function (json, textStatus) {
var i = JSON.parse(json);
if(i != ids.length) alert("Error: should delete " + ids.length +
" but deleted " + i + " records");
},
error: function (xhr, textStatus, errorThrown) {
alert('An error occurred! ' + (errorThrown ? errorThrown : xhr.status) +
" xhr: " + xhr + " textStatus: " + textStatus);
}
});
});
...
We iterate over the selected table rows from oTable and call the DataTable function fnDeleteRow which removes the given row from the table.
The Ajax call is handled by DeleteDcouments in the WCF project:
public int DeleteDocuments(int[] ids)
{
int retval = -1;
IntPtr intPtr = Marshal.AllocHGlobal(ids.Length * sizeof(int));
Marshal.Copy(ids, 0, intPtr, ids.Length);
retval = PInvoke.DeleteDocuments(intPtr, ids.Length);
Marshal.FreeHGlobal(intPtr);
return retval;
}
Note that WCF is smart enough to automatically convert the JSON-Array into a C# int Array. But to call our C-routine, we have to prepare with the Marshal-functions a raw C array.
The C-Routine from the DAL:
...
if(mysql_query(conn, buffer)) {
...
printf("%i records found\n", retval=mysql_affected_rows(conn));
The routine first generates a SQL-string like:
Delete from Document where DocumentID in (2,4)
. mysql_query sends the SQL-string to the server. Note that mysql_query is used without regards if the call is a read, update, delete or new statment. mysql_affected_rows returns the number of rows, which were affected by the last mysql_query statement, in this case the number of deleted rows.
Implementing new
...
$("#new").unbind('click');
$('#new').click(function ()
{
if(!oTable)return;
var id=-1;
var Title=null;
var Text=null;
$.ajax({
type: 'POST',
url: 'http://localhost:8001/VisionService.svc/NewDocument',
dataType: 'json',
data: JSON.stringify({"title": Title, "text": Text }),
contentType: "application/json ; charset=utf-8",
aysnc: false,
success: function (json, textStatus) {
id = JSON.parse(json);
if (id == -1) {
alert("Error: inserting a new record failed");
return -1;
}
var values = new Array(id, Title, Text);
oTable.fnAddData(values);
$("#mytable tr:last-child td:gt(0)").addClass("editable").editable(makeEditable,
{ "placeholder": "" });
oTable.$("tbody tr:last-child").click(makeSelectable);
$("#mytable tr:last-child td:eq(1)").click();
},
error: function (xhr, textStatus, errorThrown) {
alert('An error occurred! ' + (errorThrown ? errorThrown : xhr.status) +
" xhr: " + xhr + " textStatus: " + textStatus);
}
});
});
...
Currently Title and Text are unused. We generate in the database an empty record, which causes that a new DocumentID is generated, set in the new record and returned to the calling ajax function. The submitted SQL looks like this:
Insert Document(Title) values(NULL)
We add the new record to the table with the DataTablecalloTable.fnAddData(values). Then we need to make the row selectable and editable.
Implementing Update
We download jQuery editable from editable.
In GetDocuments function, we bind the click even from our New-button.
...
oTable.$('td:gt(0)').addClass("editable").editable
(makeEditable,{ "placeholder": "" });
...
function makeEditable(value, settings) {
var columnName = this.parentNode.parentNode.parentNode.rows[0].cells[this.cellIndex].innerHTML;
var id = parseInt(this.parentNode.cells[0].innerHTML);
$.ajax({
type: 'POST',
url: 'http://localhost:8001/VisionService.svc/UpdateDocument',
dataType: 'json',
data: JSON.stringify({ "id": id, "columnName": columnName, "newValue": value }),
async: false,
contentType: "application/json ; charset=utf-8",
success: function (json, textStatus) {
var i = JSON.parse(json);
if (i != 1) alert("Error: should update " + ids.length + " but updated " + i + " records");
},
error: function (xhr, textStatus, errorThrown) {
alert('An error occurred! ' + (errorThrown ? errorThrown : xhr.status) +
" xhr: " + xhr + " textStatus: " + textStatus);
}
});
return (value);
};
When an HTML-Element is made editable, it edits the object, when you click it once. The new data is submitted. So every update modifies a single column.
The generated SQL looks like this:
Update Document set Title='new value' where DocumentID=4
References
- [1]Michael Kofler;MySQL 5: Einführung, Programmierung, Referenz; Addison-Wesley Verlag 2009
- [2]Steven Cheng; Microsoft Windows Communication Foundation 4.0 Cookbook for Developing SOA Applications; Packt Publishing 2010
- [3]DataTables
- [4]editable
