Help
cancel
Showing results for 
Search instead for 
Did you mean: 
Copilot Lvl 3
Message 1 of 5

WebService for MySQL database

Solved! Go to Solution.

Hi,

I want to create a WebService which communicates with my MySQL database, the data of the database should be displayed on a Xamarin App later on. But at first I got stuck at a point of my WebService and don´t really know how to get further (I´m kinda new to programming and MySQL). Here is a piece of my code (the code is from personPersistance class):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using SimpleRESTServer.Models;
using MySql.Data;


namespace SimpleRESTServer
{
    public class PersonPersistance
    {

        private MySql.Data.MySqlClient.MySqlConnection conn;

        public PersonPersistance()
        {
            string myConnectionString;
            myConnectionString = "server=127.0.0.1;uid=Local Instance MySQL80; pwd=;database=employeedb";
            try
            {
                conn = new MySql.Data.MySqlClient.MySqlConnection();
                conn.ConnectionString = myConnectionString;
                conn.Open();
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {

            }

        }
        public long savePerson(Person personToSave)
        {
            String sqlString = " INSERT INTO tblpersonnel (FirstName, LastName) VALUES ('"+ personToSave.FirstName + "','" + personToSave.LastName + "')";}
            MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlConnection(sqlString, conn);
            cmd.ExecuteNonQuery();
            long id = cmd.LastInsertedId;
            return id;
        }

    }
}

I used this tutorial for my WebService: https://youtu.be/LpySuvYPMZQ

Everthing works as it should till I come to the savePerson function. In my database I insert the ID manually, in the tutorial it gets inserted automatically (I think). So my problem is that I don´t really know how to go on at this point.

Here is a picture of my database columns it may help:

StackOverflow.PNG

Thanks in advance!

4 Replies
Highlighted
Solution
Commander Lvl 2
Message 2 of 5

Re: WebService for MySQL database

Numeric ids can often be "auto generated" in databases. In the case of MySQL, you define the column as AUTO_INCREMENT to get this capability. [This would be my personal choice.]

 

If you want to continue using the manual method, you have two choices:

1. Track the person id in the caller of your web service, and provide the id to use to the SavePerson function.

2. In SavePerson, query the database first to find out what the next id would be, then provide that id value when you write to the database. E.g. if you did a "select max(ID) from tblPersonnel" you get the largest ID used, then add one and include it in the INSERT statement. Note that you may end up re-using IDs [e.g. add person A with id 10; delete person A; add person B - person B will end up with ID 10].

 

Please follow-up to let us know how you made out. For good karma, mark a reply as the answer if it helped!

Highlighted
Copilot Lvl 3
Message 3 of 5

Re: WebService for MySQL database

Thank you I will go for the auto increment option. I think it has more advantages (didn´t knew how to use it before). The code I mentioned above should work if I use auto increment in the DB, or?

Highlighted
Copilot Lvl 3
Message 4 of 5

Re: WebService for MySQL database

Managed to do it thanks for your help!

Highlighted
Commander Lvl 2
Message 5 of 5

Re: WebService for MySQL database

Glad to hear it! If you could use the "mark as solution", it would be appreciated by myself and future users ...

 

Please follow-up to let us know how you made out. For good karma, mark a reply as the answer if it helped!