I am going to use MySQL as an example, since I don't have SQL installed. However, if you're set on using SQL, just remove
My
from
MySQL
and It should work for SQL providing you use a connection string built for SQL server with your own credentials. This example code was pulled from a static class in a working MySQL project. For your sanity, I will remove the static modifiers and the classes, and assume you know how to structure the code yourself in a custom database class.
Your connection string would look
Similar to this ::
public const string cString = "server=localhost;user id=root;password=root;persistsecurityinfo=True;database=visuals_db;port=8889";
I think the connection string is self explanatory, so I won't bother elaborating on it; suffice to say you can compare them at ConnectionStrings.Com -
MySQL -
SQLServer - Notice the difference with SQL, you need to qualify an instance name for your SQL installation as where with MySQL, you can provide a server address.
In order to begin executing statements, you will need some which look like this ::
public const string execSelectUnameQuery = "SELECT username from users WHERE email=@email";
public const string execSelectIDQuery = "SELECT id from users WHERE email=@email";
public const string execUpdateNameQuery = "UPDATE users SET name=@Fname WHERE email=@Email AND password=@Pass;";
public const string execInsertUserQuery = "INSERT INTO users (name,username,email,password) VALUES (@Fname,@Uname,@Email,@Pass)";
public const string execDeleteUserQuery = "DELETE FROM users WHERE id=@id";
Next step it to declare a connection with the connection string ::
public MySqlConnection Con = new MySqlConnection(cString);
Then you will want to build up a method to execute your statements. I prefer to pass in my statements to the method. At this point, you're dealing with pseudo code, as I explained, I've pulled this from a working project. It's up to you to jigsaw it back together.
//The insert method :: Each method takes one argument, this will be the statement declared above. Just pass in the variable name for the insert statement.
public bool Insert(string Statement)
{
try //Do not nest try catch blocks, except, declare more than one catch exception in one try block. Each catch can be added for the different exceptions that may arise.
{
if (Con != null)
//Check connection reachable and open it if its closed.
{
DoConnection(); //While the connection is closed, open it
using (MySqlCommand cmd = new MySqlCommand(Statement, Con)) //Build the command, and pass in the statement with the connection.
{ /* Use using blocks for they are self disposing when done using.
The below parameters takes the parameter name, and then the value of that parameter
So for the first parameter, we are setting the Name of the person for Fname parameter name */
cmd.Parameters.AddWithValue("@Fname", myAccount.Name); //Where myAccount would be the class holding your clients user details
cmd.Parameters.AddWithValue("@Uname", myAccount.Username);
cmd.Parameters.AddWithValue("@Email", myAccount.Email);
cmd.Parameters.AddWithValue("@Pass", myAccount.Password);
//Notice Fname, Uname, Email, Pass are the placeholders for the values and they are found in the executing statement above. This is how parameters should be used or you risk [URL='https://en.wikipedia.org/wiki/SQL_injection']SQL Injection[/URL] attacks.
//Always use parameterized command queries!! And use add with value since add is deprecated
cmd.ExecuteNonQuery(); //Execute the query
}
DoConnection(); //Close the connection.
return true; //Tell the calling code success
}
return false; //Statement wont execute
}
catch (Exception ex)
{ //Then catch all errors you might miss
MessageBox.Show(string.Concat("Tell the developer there is an error at ", ex.StackTrace));
return false;
}
I prefer to handle my open connections with a switch.
DoConnection()
method being called above opens and closes the connection based on its state.
public bool DoConnection() //Open and close the connection with this method
{
//We devised a switch statement to open and close the connection depending on its current state
switch (Con.State == ConnectionState.Closed)
{
case true:
Con.Open();
return true;
case false:
Con.Close();
return false;
}
return false;
}
You will need to get it working yourself. Your database table should look something like this ::
If you want to use the Select/Delete queries, you need to add an ID field to the table and make it AI. Normally I don't provide code snipped off the bat without seeing what you've attempted first. But in a nutshell, It should look something like that. If you have any questions, be sure to post back with your own implementation of how you implemented this example. Hope it helps.