CODEWITHSUNDEEP
c#databaseconnectiondatabase-connection
Matteo
Matteo

Reputation: 346

Database connection doesn't work C#

I'm working on a program in C#. I created a database with MySQL on phpMyAdmin and I want to connect this database with my program. After the connection I have to insert, update, delete and view all the data, but I have a problem: the connection doesn't work.

I post here my code for the connection:

public static string StringaConnessione = "Data Source=localhost;Database=agility;userid=root;password='';";
public static MySqlConnection Connessione = new MySqlConnection(StringaConnessione);

When I write the code for the insert button I have another problem (is certainly for the database)

Connessione.Open();
SQLDataAdapter SDA=new SqlDataAdapter("INSERT INTO GARA(nome_gara,giudice,località,data,tpsopm,tpmopm,tpstot,tpmtot)VALUES('"+textBox1.Text+"','"+textBox2.Text+"','"+textBox3.Text+"','"+textBox4.Text+"','"+textBox5.Text+"','"+textBox6.Text+"','"+textBox7.Text+"','"+textBox8.Text+"')",Connessione);
SDA.SelectCommand.ExecuteNonQuery();
Connessione.Close();
MessageBox.Show("Dati salvati correttamente!");

May you help me, please? Thank you!

Upvotes: 1

Views: 312

Answers (3)

SKLTFZ
SKLTFZ

Reputation: 950

i think you should discard all your code. and find a valid one to start

for example this http://roboardgod.blogspot.hk/2013/08/cmysql.html

you may need to add the reference MySql.Data.MySqlClient manually. check this post to add the reference How do I add a reference to the MySQL connector for .NET?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
namespace MySQLtest
{
    class Program
    {
        static void Main(string[] args)
        {   string dbHost = "";//db address, for example localhost
            string dbUser = "";//dbusername
            string dbPass = "";//dbpassword
            string dbName = "";//db name
            string connStr = "server=" + dbHost + ";uid=" + dbUser + ";pwd=" + dbPass + ";database=" + dbName;
            MySqlConnection conn = new MySqlConnection(connStr);
            MySqlCommand command = conn.CreateCommand();
            conn.Open();

            String cmdText = "SELECT * FROM member WHERE level < 50";
            MySqlCommand cmd = new MySqlCommand(cmdText, conn);
            MySqlDataReader reader = cmd.ExecuteReader(); //execure the reader
            while (reader.Read())
            {
                for (int i = 0; i < 4; i++)
                {
                    String s = reader.GetString(i);
                    Console.Write(s + "\t");
                }
                Console.Write("\n");
            }


            Console.ReadLine();
            conn.Close();
        }
    }
}

Upvotes: 0

mybirthname
mybirthname

Reputation: 18127

You have so many problems with your code:

1) You are using static connection, there is Connection pool and it is your friend.

2) You are not using your connection in using block or in try/catch/finally/block to ensure closing of connection on exception.

3) Blocker problem: You are using SqlDataAdapter instead of MySqlDataAdapter

4) Blocker problem You should define your Insert query in InsertCommand of the DataAdapter, it will not work with SelectCommand. Even better just use MySqlCommand and ExecuteNonQuery on it.

5) You are not protected from Sql Injection(Use MySqlCommand.Parameters)

6) Bad formatting of your variables, textboxes and db fields.

How your code will look optimally:

public static string connetionString= "Data Source=localhost;Database=agility;userid=root;password='';";

public void SomeMethod()
{

    using(MySqlConnection conn = new MySqlConnection(connetionString));
    {
         conn.Open();

         string query = @"INSERT INTO GARA
                             (nome_gara, giudice, località, data, tpsopm, tpmopm, tpstot, tpmtot)
                          VALUES
                             (@Param1, @Param2, @Param3, @Param4, @Param5, @Param6, @Param7, @Param8)";

         MySqlCommand cmd = new MySqlCommand(@"query", conn);
         cmd.Parameters.AddWithValue("@Param1", textBox1.Text);
         cmd.Parameters.AddWithValue("@Param2", textBox2.Text);
         cmd.Parameters.AddWithValue("@Param3", textBox3.Text);
         cmd.Parameters.AddWithValue("@Param4", textBox4.Text);
         cmd.Parameters.AddWithValue("@Param5", textBox5.Text);
         cmd.Parameters.AddWithValue("@Param6", textBox6.Text);
         cmd.Parameters.AddWithValue("@Param7", textBox7.Text);
         cmd.Parameters.AddWithValue("@Param8", textBox8.Text);

         cmd.ExecuteNonQuery();
    }

}

Upvotes: 1

stuartd
stuartd

Reputation: 73253

You can't use a SqlDataAdapter to talk to MySQL as that class is designed for use with Sql Server.

Use the MySqlDataAdapter instead.

Upvotes: 4

Related Questions