CODEWITHSUNDEEP
c#asp.netmysqldatabaseinsert
Tarasov
Tarasov

Reputation: 3695

How I can Insert Data in the MySQL Database?

I have a ASP.NET Application and a MySQL Database. I want write a Class to insert,delete and show the Data from the database. I have a Connection to the Database but I can't insert data in the database.

My Class insert method: 

public string CreateEntry(string Connectionstring, string mitarbeiter)
{
    connection = new MySqlConnection(Connectionstring);
    try
    {
        var command = connection.CreateCommand();
        command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname) VALUES ('tom')";
        connection.Open();
        return "Mitarbeiter wurde angelegt";
    }
    catch (Exception ex)
    {
        return ex.Message;
    }
    finally
    {
        connection.Close();
    }
}

The Connectionstring is correct. I don't get a error but there is no data in the database.

My tablename: tb_mitarbeiter columns: ID and Vorname

Upvotes: 6

Views: 57962

Answers (7)

Ramgy Borja
Ramgy Borja

Reputation: 2458

You can also used Sql parameter to prevent Sql Injection

try
{
    MySqlCommand command = connection.CreateCommand();

    command.CommandText = @"INSERT INTO `tb_mitarbeiter` (`Vorname`) VALUES (@tom)";
     command.Parameters.AddWithValue("@tom", tom);
     connection.Open();
     command.ExecuteNonQuery();

     return "Mitarbeiter wurde angelegt";
}
catch (Exception ex)
{
     return ex.Message;
}
finally
{
    command.Dispose();
    command.Close(); 
    connection.Close();
}

Upvotes: 0

T.A.Chathura Priyahsad
T.A.Chathura Priyahsad

Reputation: 111

{
string MyConnection2 = "datasource=localhost;port=3306;username=root;password=1234"; 

        string Query = "insert into DBname.TableName(id,Name,First_Name,Age,Address) values('" +this.IdTextBox.Text+ "','" +this.NameTextBox.Text+ "','" +this.FirstnameTextBox.Text+ "','" +this.AgeTextBox.Text+ "','" +this.AddressTextBox.Text+ "');";  

        MySqlConnection MyConn2 = new MySqlConnection(MyConnection2);  

        MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);  
        MySqlDataReader MyReader2;  
        MyConn2.Open();  
        MyReader2 = MyCommand2.ExecuteReader();     
        MessageBox.Show("Save Data");  
        while (MyReader2.Read())  
        {                     
        }  
        MyConn2.Close();  
    }  
    catch (Exception ex)  
    {   
        MessageBox.Show(ex.Message);  
  }

}

Upvotes: 3

cubski
cubski

Reputation: 3258

You forgot to execute the command by calling command.ExecuteNonQuery(). This is how I would typically do it:

public string CreateEntry(string connectionString, string valueToInsert)
{
    var stringToReturn = "";

    try
    {
        using(var connection = new MySqlConnection(connectionString))
        {
            //Open connection
            connection.Open();

            //Compose query using sql parameters
            var sqlCommand = "INSERT INTO table_name (field_name) VALUES (@valueToInsert)";

            //Create mysql command and pass sql query
            using(var command = new MySqlCommand(sqlCommand, connection))
            {
                command.Parameters.AddWithValue("@valueToInsert", valueToInsert);
                command.ExecuteNonQuery();
            }           

            stringToReturn ="Success Message";
        }
    }
    catch(exception ex)
    {
        stringToReturn = "Error Message: " + ex.Message;
    }

    return stringToReturn;
}

There are a few key things to keep in mind:

  1. Wrap disposable objects with a using. In the case of MySqlConnection, it will properly close and dispose the connection when its out of scope.
  2. Use SQL parameters when passing values inside your query. This will avoid SQL injection and its much more easier to maintain.
  3. Personally, I like to have one exit point in a function. In this example, the "stringToReturn" variable holds the value to return once the function is done executing both successfully or in case of a failure.

Upvotes: 8

shaouari
shaouari

Reputation: 236

To do a Insert / Update / Delete u should add 

connection.Open();
command.ExecuteNonQuery();

For select ()to show data from database use:

connection.Open();
command.ExecuteReader();

Upvotes: 6

Rahul Tripathi
Rahul Tripathi

Reputation: 172608

You missed to write this:-

 ....
 connection.Open();
 command.ExecuteNonQuery();
 ....

Upvotes: 1

Adil
Adil

Reputation: 148180

You are not executing the command use SqlCommand.ExecuteNonQuery 

try
{
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname) VALUES ('tom')";
        connection.Open();
        command.ExecuteNonQuery();
        return "Mitarbeiter wurde angelegt";
 }
 catch (Exception ex)
 {
      return ex.Message;
 }
 finally
 {
       connection.Close();
 }

Upvotes: 1

Steve
Steve

Reputation: 216343

You should simply execute the command 

....
MySqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname) VALUES ('tom')";
connection.Open();
command.ExecuteNonQuery();
....

I suppose that mitarbeiter is the real value that should be set in the database.
If this is the case remember to use parameters to insert/update your data

MySqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname) VALUES (?name)";
command.Parameters.AddWithValue("?name", mitarbeiter);
connection.Open();
command.ExecuteNonQuery();

Upvotes: 12

Related Questions