Working with SqlDataSource: MySQL and LIKE clause
We could have used SQL Express too, but hey, it is cool to be versatile ;)
Since it is a small project, I though we could use ASP.NET controls to display data. I've been working for years with ASP.NET but never worked with the SqlDataSource, for example, just with the ObjectDataSource which were bounded to Business Objects. But I knew SqlDataSource capabilities, since I studied it for certification exams :)
One mistake I found very common while googling is to think that the SqlDataSource objects doesnt work with other databases than Microsoft SQL Server. SqlDataSource works with every database that has a ADO.NET provider implemented, including MySQL. MySQL has the marvelous MySQL Net/Connector, which is at version 6 and can be downloaded here.
MySQL Net/Connector has alot of "gotchas", some things works different with the MySQL database as a data source for the SqlDataSource, specially the SELECT querys with "LIKE" clauses, and that's what this post is all about.
First things first, let's make the MySQL Net/Connector work. Close all yours Visual Studio instances, then you install the MySQL Net/Connector, which is next, next, next, finish.
Fires up your Visual Studio again and you can add a SqlDataSource to a Web Form.
Right click the SqlDataSource object and click "Configure Data Source", a Wizard
is going to start. Click "New connection" in the first screen and you are going
to see an option to connect to a MySQL database, like this:
Choose "MySQL Database", click "Ok" and fulfill the connection
information to your database:
Click "Next", choose to save connection string in the web.config file (we all know this isn't safe, but this is an example) and click "Next" again.
The query statement wizard starts, and this is where things starts to get interesting,
specially if you need to add a LIKE clause to your query:
Click the "WHERE" button and add a LIKE clause, like in the screen below:
Click "Add" and then "OK".
Back to the query statement wizard, click "Next" and watch as the wizard mounted
the query in the "SELECT statement" box:
If you click the "Test Query" button and add a value to the parameter you added
two steps ago, a error is going to occur:
After hours bumping my head against the wall, I found out that the query statement
that Visual Studio generated isn't MySQL compliant:
The query's syntax demonstrated above works with SQL Server, but not with MySQL,
which should be:
SELECT * FROM conteudo WHERE Titulo LIKE Concat('%', ?Texto, '%')
The "Test Query" will still not work and I don't know why, but it works at runtime.
I appreciate your comments ;)
Updates:
- I found out that the images I posted were too large, so I decreased its size.
- I don't even know how my post was submited to DotNetShoutOut. Whoever made it, thank you. From now on I'm going to post the "Shout it" in every post! If you like the content, please shout it!