Executing a SQL query with C#

Question

I would like to add some information to my database. I searched for some tutorials, but none of them work.

NonQuery can do what he needs to do, because the messagebox returns "Success" (1). But it does not update my database. If I put the same query to "Add New Query", directly to my database, it works.

Can someone help me?

My class code at the moment:

namespace BurnThatFat
{
    class databaseconnection
    {
        //fields
        SqlConnection connection;
        string connectionstring;

        public databaseconnection()
        {
            // fields waarde toewijzen
            connectionstring = @"Data Source=(LocalDB)\MSSQLLocalDB;" +
                @"AttachDbFilename=|DataDirectory|\Database2.mdf;Integrated Security=True";
            connection = new SqlConnection(connectionstring);
            OpenConnection();
            CloseConnection();
        }

        public List<Object> getObjectsFromDatabase()
        {
            try
            {
                OpenConnection();
                // sql query
                // Datareader
                // sqlcommand
                // return list van objecten , objecten veranderd naar jouw wens van data.
                CloseConnection();
            }
            catch (Exception)
            {
                throw;
            }
            return new List<object>();
        }

        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 0:
                        MessageBox.Show("Cannot connect to server.  Contact administrator");
                        break;
                    case 1045:
                        MessageBox.Show("Invalid username/password, please try again");
                        break;
                }
                return false;
            }
        }

        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        public void AddGebruiker()
        {
            string query = "insert into Gebruiker VALUES(3, 'Cihan', 'Kurt', 18, 'Man', 85, 75, 'Admin1', 'Test123', 'testen');";
            using (connection)
            {
                SqlCommand command = new SqlCommand(query, connection);
                OpenConnection();
                int resultaat = command.ExecuteNonQuery();
                if (resultaat == 1)
                {
                    MessageBox.Show("succes");
                }
                else
                {
                    MessageBox.Show("fail");
                }
            }
        }
    }
}

Edit:

And this is the code for my buttons etc:

   using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

// voor sql connectie.
using System.Data.SqlClient;



namespace BurnThatFat
{
    public partial class SignUp : Form
    {

        databaseconnection db = new databaseconnection();

        public SignUp()
        {
            InitializeComponent();
            gb_login.Visible = false;
            gb_Voornaam.Visible = false;
            gb_Achternaam.Visible = false;
            gb_leeftijdgeslacht.Visible = false;
            gb_gewicht.Visible = false;
            gb_email.Visible = false;
            gb_Start.Visible = true;


        }


        private void btn_SignUp_Click(object sender, EventArgs e)
        {
            gb_Start.Visible = false;
            gb_Voornaam.Visible = true;
        }

        private void btn_login_Click(object sender, EventArgs e)
        {
            gb_Start.Visible = false;
            gb_login.Visible = true;
        }

        private void btn_loginvolgende_Click(object sender, EventArgs e)
        {
            gb_login.Visible = false;
            // hier moet nog een GB!!!!!!
        }

        private void btn_voornaamvolgende_Click(object sender, EventArgs e)
        {


            gb_Voornaam.Visible = false;
            gb_Achternaam.Visible = true;
        }

        private void btn_achternaamvolgende_Click(object sender, EventArgs e)
        {
            gb_Achternaam.Visible = false;
            gb_leeftijdgeslacht.Visible = true;
        }

        private void btn_leeftijdvolgende_Click(object sender, EventArgs e)
        {
            gb_leeftijdgeslacht.Visible = false;
            gb_gewicht.Visible = true;
        }


        // einde registratie
        // opslaan van gegevens in database
        private void btn_emailvolgende_Click(object sender, EventArgs e)
        {
            // gebruiker = new Gebruikerklasse();
           // gebruiker.Naam = Convert.ToString(tb_voornaam.Text);
           //// gebruiker.Achternaam = Convert.ToString(tb_achternaam.Text);
          //  gebruiker.Leeftijd = Convert.ToInt32(nud_leeftijd.Value);
          ///  gebruiker.Geslacht = Convert.ToString(cb_geslacht.Text);
          //  gebruiker.Huidig_gewicht = Convert.ToInt32(nud_huidiggewicht.Value);
          //  gebruiker.Streef_gewicht = Convert.ToInt32(nud_streefgewicht.Value);
          ///  gebruiker.Gebruikersnaam = Convert.ToString(tb_gebruikersnaam2.Text);
          //  gebruiker.Email = Convert.ToString(tb_email.Text);
         //   gebruiker.Wachtwoord = Convert.ToString(tb_wachtwoordsignup.Text);

            db.AddGebruiker();
            gb_email.Visible = false;
            // hier moet nog een GB!!!!!

        }

        private void btn_gewichtvolgende_Click(object sender, EventArgs e)
        {
            gb_gewicht.Visible = false;
            gb_email.Visible = true;
        }
    }
}

So many things you should do differently here. First, your connection string belongs in a config file, not hard coded. You should wrap your connection in a USING statement and not have separate methods to open and close connections. You have very brittle architecture here that can very easily clog up your connection pool due to poorly handling connections. — Sean Lange
– Sean Lange, Commented Jan 5, 2017 at 20:29
Why are you catching MySqlException if you're using MS SQL Server? — mason
– mason, Commented Jan 5, 2017 at 20:31
And why is this tagged as sql server but you are catching mysql exceptions? If this is actually sql server those exceptions will never happen. Look at the catch block in OpenConnection. If you actually catch the exception and the number is anything other than 0 or 1045 your catch block will do nothing other than return false rendering you helpless to debug this. — Sean Lange
– Sean Lange, Commented Jan 5, 2017 at 20:31
The INSERT statement doesn't list the columns before the values. — user47589
– user47589, Commented Jan 5, 2017 at 20:35
@Gigitex While it's not required to list the column names, you should always list them out. This avoids issues with the queries no longer working if the table structure changes, and helps protect against inserting data into the wrong column. School assignment or not, it's a good habit to get into early. — Siyual
– Siyual, Commented Jan 5, 2017 at 20:43

marc_s · Accepted Answer · 2017-01-05 21:36:45Z

9

The simplest way to insert into a SQL Server database:

string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database2.mdf;Integrated Security=True";

string commandText = "INSERT INTO MyTable (ID, Name, Address) VALUES (10, 'Bob', '123 Main Street');";

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(commandText, conn))
{
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close();
}

As long as commandText is a working query, it should insert a row. It would be better to use parameters for your values instead of hard coding them like I did here - that avoids SQL injection attacks and other potential problems. You can search Google for that (or the question you are asking now) and find tons of resources to help you.

If you need more specific help, post details such as what is actually happening when you try to run your code - are you getting an exception?

edited Jan 5, 2017 at 21:36

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 5, 2017 at 20:37

Dave Smash

3,0211 gold badge22 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Cihan Kurt Over a year ago

I have this now: prnt.sc/drz4b5 prnt.sc/drz4ok Still does not upload anything to my database.

Dave Smash Over a year ago

Try setting a break point on the line "cmd.ExecuteNonQuery();" to make sure that it is being run. If not, make sure that AddGebruiker() is being called by something - it looks like your button click should make it run, but only if you are handling the "Click" event properly.

Cihan Kurt Over a year ago

If i press on 'send ' it should update the database. But that doesnt happen. It happens to be a bit laggy, but that means that the connection is opened and closed... if I refresh my database, nothing is added.

Dave Smash Over a year ago

Set a break point to make sure that your button is doing what you think it is. If the insert does not work, it should be throwing an Exception and crashing your program. If it is running, check your connection string - it looks different than any connection string that I normally use. You can use Server Explorer in Visual Studio to find your connection string - click on the database that you want and it should appear in the properties window.

Cihan Kurt Over a year ago

It does exactly the same thing with a breakpoint. I will try to change the connectionstring and come back to you.

TripWire · Accepted Answer · 2019-10-13 22:47:19Z

1

I'd clean up a bunch of things before doing anything else.

First, get rid of the openconnection and closeconnection methods all together. And don't keep an instance property for the connection in your class. Create the connection ondemand with a using statement, because at the end of the using statement the compiler will insert a call to the Dispose method on the connection's IDisposable interface implementation and it will close the connection automatically for you.

So after cleaning up all the unnecessary code all you really should have in this class is an implementation of your Addgebrukier method which would look like this

public void AddGebruiker()
{
        string query = "insert into Gebruiker VALUES(3, 'Cihan', 'Kurt', 18, 'Man', 85, 75, 'Admin1', 'Test123', 'testen');";
        using (SqlConnection connection = new SqlConnection(connectionstring))
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                connection.Open();
                int resultaat = command.ExecuteNonQuery();
                if (resultaat == 1)
                {
                    MessageBox.Show("succes");
                }
                else
                {
                    MessageBox.Show("fail");
                }
            }
        }
    }

You should also load your connection string from the section in the app/web.config, but you can do that later after you get it running.

edited Oct 13, 2019 at 22:47

TripWire

5821 gold badge7 silver badges18 bronze badges

answered Jan 5, 2017 at 20:37

jeff.eynon

1,3263 gold badges11 silver badges30 bronze badges

3 Comments

Cihan Kurt Over a year ago

I haven't tried it yet, but if I run your code it will give me an error. I'll need to have an open connection for ExecuteNonQuery or else it won't work

H.G. Sandhagen Over a year ago

The connection variable and the connection.Open(); is missing.

jeff.eynon Over a year ago

You're right I forgot the connection.Open. Editing it now.

ASH · Accepted Answer · 2017-01-31 16:10:12Z

Here is a simple concept that should work perfectly fine for you. Just change the ServerName, DatabaseName, etc.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connetionString = null;
            SqlConnection connection ;
            SqlDataAdapter adapter = new SqlDataAdapter();
            string sql = null;
            connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";
            connection = new SqlConnection(connetionString);
            sql = "insert into product (Product_id,Product_name,Product_price) values(6,'Product6',600)";
            try
            {
                connection.Open();
                adapter.InsertCommand = new SqlCommand(sql, connection);
                adapter.InsertCommand.ExecuteNonQuery();
                MessageBox.Show ("Row inserted !! ");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }
}

Collectives™ on Stack Overflow

Executing a SQL query with C#

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related