How to connect c# to remote mysql database

In this quick tutorial I’m going to show you how you can connect your c# application to a remote mysql database. There’s no additional coding involve in this tutorial all you need to do is to configure your connection string a little bit and configure the privileges on your mysql database.

 

Requirements

  • phpmyadmin

 

Privileges

First you have to access your mysql database using phpmyadmin.

http://localhost:8080/phpmyadmin/

Click on the privileges tab then change the host to Any host.

image

 

Connection String

Finally you just have to change the server you’re connecting to. This time its not localhost since you’re not connecting to the database on your local computer.

string con_string = "Server=192.168.218.10;

Database=cake_beginner;Uid=root;Pwd=1234";

In the example above I used the internal ip address. If you don’t know your internal ip address. Just go to cmd and type ipconfig/all and copy the ip version 4 preferred address. Well that’s just internal ip address, which means that only the people who are inside your home network can access. If you want to connect to a remote mysql database you have to determine your external ip address, you can do so by going to cmyip.com. But remember that the ip address of the computer you’re trying to access should be static so that you won’t need to change your connection string every time you need to connect to the remote mysql database.

I have written a tutorial before on how to connect to a remote computer. You might want to read it if you don’t know how to configure your router to make remote access possible.

How to convert html tables to sql files

I know why you’re here, you want to convert a table such as the one below into full-fledged database table which can be manipulated using queries.

image

The image above is from w3schools of course.

Requirements

  • phpmyadmin / heidisql
  • Microsoft Excel or any spreadsheet application

 

Procedure

Converting an html table into an sql file can be done in 3 steps. First you need to have a source. Where are you getting the data from? In this case we are getting it from the fruits tables at w3schools. Highlight the whole table and paste it on Microsoft Excel, just right-click on the first cell of the sheet and select paste(Match Destination Formatting). After that, go ahead and save the file as an excel 2003 file(xls) or excel 2007 file(xlsx). Finally you just have to import the excel file using phpmyadmin or heidisql. Once you’re done you’ll end up with this.

image

Just remember that the name of the table depends on the name of the sheet. Sheet1 is the default so you have to rename the sheet if you’re planning to import multiple excel files on a single database. But you can just import it all at once, just use a different sheet for a different table structure.

Access denied error in phpmyadmin

 

image

Did you ever got this error in phpmyadmin? Maybe, that’s why you’re reading this right now.
I won’t spoil the moment even further. Here’s the solution:

 

First you need to go to the directory below. It’s not necessary that we have the same version of phpmyadmin. What’s important is that you go to a directory similar to this address:

C:wampappsphpmyadmin3.3.9

Then open up the config.inc.php file. Edit this line:

$cfg['Servers'][$i]['password'] = '';

Make sure that it matches the mysql password that you have. If you have forgotten they mysql password as well. Then you might want to download another version of mysql from wampserver (add-on) and use it as your primary server by going to the wampserver try icon. Left click it and go to mysql –>version, and check the newly installed version.

Select restart all services from wampserver tray icon.

Converting ms sql to mysql database

I’m really in a blogging mode yesterday and today. Because this is the last day of vacation and I don’t think I’ll be able to write things for a while that’s why I’m doing the best I can to add up entries to my blog.

Okay, enough with my life story. This time I’m going to show you the easiest way on how to convert an existing ms sql database into a mysql database. People call this database migrations (Yup there’s also database migrations in Rails but that’s a different one).

Before I proceed, let me tell you that I have found an article from the mysql site which discusses about this thing:

http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

I recommend that you go and check it out first. So that you’ll understand the basic concepts on database migrations.

Requirements

  • Existing ms sql database
  • DRPU Database converter – MS SQL to MySQL
  • Wampserver

Steps

First, you have to give a password to your root user using phpmyadmin. Because DRPU database converter doesn’t allow you to go any further if there is no password. We need it to be the root user because it has the most privileges to all the databases that is on the mysql database server. You might get some errors in the DRPU database converter if you don’t use the root user.

Next, install DRPU database converter. You can use Sandboxie if you just want to try this software. Here are the things that you need to input:

  • MS SQL Server – the computer name
  • MySQL Server – default would be localhost
  • Login Name – the user that I told you to create earlier
  • Password – the password of the user that you created

image

If you don’t know the name of your computer, just go to start->right click computer-> properties.  It looks something like the one below if you’re on windows 7 or vista.

image

Before you click on next, make sure that the sql service is up and running. Together with wampserver. Make sure that it is online.

Then you’ll see this screen if it has successfully connected. Just click on select all if you are not on the mood to click the tables one by one.

image

Yeah my example was a bit exaggerated since the SBO-COMMON database is the database that is used by SAP Business One. This one might take forever, so I just terminated the process by deleting the sandbox.

It might just take less than 10 seconds if your database is not as big as the SBO-COMMON database.

And here’s the screenshot when I open it up on Phpmyadmin

image

You can see from here that the demo version can only export up to 5 records into a mysql database. Doesn’t really matter since it already took the trouble of rebuilding the database from scratch.

image

How to export ms sql database using sql server management studio

 

Requirements

 

Prerequisites

You already know the basics of using Server Management Studio. Like connecting to the ms sql server, browsing the different databases and tables, executing sql statements.

Steps

First you have to open up SSMSE. Right-click on your database and select task then generate scripts.

image

image

 

Select your database. You can also check the script all objects in the selected database. So that it will no longer ask you later on what object (tables, stored procedures, views, etc..) to include in the sql file that will be produced.

image

Just go ahead and press the next button right away if you see this screen. That is if you don’t really see something that makes sense.

image

Then select the script to file. Make sure that you have also selected single file and Unicode text. After that, just click on the browse button and select the location where you want to export the sql file. And then name it using the name of the database or any name you want.

image

This is just a summary of what it will do.

image

Then here’s the screen that will notify you if the operation was a success or not.

image

If you open up the sql file that has been produced:

USE [students]
GO
/****** Object:  Table [dbo].[student_info]    Script Date: 05/29/2011 15:20:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student_info](
	[idnum] [nchar](10) NULL,
	[fname] [nchar](10) NULL,
	[lname] [nchar](10) NULL,
	[course] [nchar](10) NULL,
	[address] [nchar](10) NULL
) ON [PRIMARY]
GO

Basically it looks like the sql file that’s been produced by mysql. But it won’t work if you try to import it using phpmyadmin. Since ms sql and mysql are very different databases.

So what you can do with this sql file? You can actually open it up on notepad, copy the contents and execute it on SSMSE to rebuild your table. As you can see from the code above, it doesn’t create the database, it just uses it. So you still have to create a database with the same name as the one specified in the sql file to make use of this sql file. Another thing, it doesn’t actually backup your data. Or dump statements as they call it.

So what’s the purpose of this tutorial? To create an sql file which describes an ms sql database. If you are planning to migrate from ms sql to mysql database. Then this file can serve as a basis for the database that you are going to create in mysql. All of the table structure are in one place.

Using auto-increment in mysql

This is a short guide that will teach you how you can auto-increment a field in a mysql table. What auto-increment does is to fill up a value for a field without user intervention. It automatically generates auto-incrementing values as you use an insert query. Auto-increment values are usually used as a primary key or unique key for a certain table in a database.

Here’s a screenshot that will give you an idea on how to auto-increment a field.

image

The value that would be inserted in this field would be 1 to n. Where n is the numeric value of the last record that you have inserted in the table.

When you perform an insert query, you don’t need to supply values on these fields. That’s very useful because the amount of time needed to complete a form is diminished because a unique number for the primary key is automatically generated.

How to automate Mysql database backup

In this guide I will show you how you can automate the backup process of Mysql database using a simple trick in Windows. This is the Windows equivalent of the Cron jobs that are mostly used in Linux systems.

I’m using Windows 7 at the time of this writing but the process is still the same for Windows XP and Windows Vista.

Before we start, you must have already a knowledge on how to backup mysql database using mysqldump the built-in tool for mysql which I mainly use for backups.

Here’s the link to the last tutorial that I have made which discusses about the ways on how to backup mysql database. You might want to check it out first if you don’t have an idea on how to use mysqldump utility.

Lets begin by opening up the task scheduler

image

From this screen, you need to click on create basic task

image

Input the name of the task together with the description then click next

image

In this case I selected when I log on. You can choose whatever you want. Then click on next again.

image

We will select start a program here since we will be executing a batch file. A batch file is similar to an executable file. And you can convert a batch file to an executable easily. Click next again.

image

Browse for the batch file that backups mysql database or just enter its location on the input box provided. Then click next.

image

Just click on finish when you see this screen. Its just a summary of what the task will do.

image

To check if it is really saved then just scroll down to the active task section and try to find the name of the task in the list. Or just press on your keyboard the first letter of the task name. In my case, it is ‘D’ for db backup

image

 

Conclusion

That’s a nice and easy way on how to do mysql database backups automatically using the built in windows tool, the task scheduler. You can also try to be creative by doing some other task with this neat windows tool.