WebService for MySQL database

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:

Thanks in advance!

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].

3 Likes

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?

Managed to do it thanks for your help!

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