Any C# devs here?

Are there any C# devs here? I am having a small issue and Stack Overflow isn't helping This is for the a3bans api.

Here is the stack overflow link. so I don't have to copy a bunch of stuff:

http://stackoverflow.com/questions/40328739/c-sharp-asp-net-mysql-either-null-or-input-string-was-not-in-a-correct-format

Any C# devs here?

Null Checks
I'm guessing it is probably something to do with the dbReader.GetString(). If any of the columns have a null value it will fail conversion to string. Look at using IsDBNull (http://stackoverflow.com/questions/4739641/how-to-check-for-null-in-mysqldatareader-by-the-columns-name).

For example if you are calling...
searchBan.Proof = dbReader.GetString("Proof");

That would throw an exception if Proof is null for a particular record.

Reader vs NonQuery
Also you would not want to use both of these on the same command.
1. dbReader = selectCommand.ExecuteReader();
2. selectCommand.ExecuteNonQuery();

ExecuteNonQuery implies you are not returning any data from a select clause, this is for executing update/inserts, not for selects.

WebAPI Serialization
Also, WebAPI handles serialization/deserialization automatically. If you wanted to return a list of bans for a grid you would change...
public string oSearchBan(tBan ban)
to
public List<tBan> oSearchBan(tBan ban)

For a .Net client side app you would need to deserialize json manually though.

Client side deserialization might look something like this...
var listOfBans = JsonConvert.DeserializeObject<List<tBan>>(ResponseStringContent);

I'm returning it through a single value and not a list I've changed the code

Code: [Select]

MySqlCommand selectCommand = new MySqlCommand("select * from a3bans.bans where GUID = @prmGuid", conDataBase); // Returning a null value?!
               
                selectCommand.Parameters.AddWithValue("@prmGuid", ban.GuidOrIP);
     

                selectCommand.ExecuteNonQuery();
                selectCommand.Dispose();

                dbReader = selectCommand.ExecuteReader();
               
                try
                {
                    while (dbReader.Read())
                    {
                        tBan searchBan = new tBan();
                        //searchBan.BanID = dbReader.GetString("BanID");
                        searchBan.GuidOrIP = dbReader.GetString("GUID");
                        searchBan.BanType = dbReader.GetString("BanType");
                        searchBan.BanReason = dbReader.GetString("Reason");
                        searchBan.Proof = dbReader.GetString("Proof");
                        bans = searchBan;
                    }
                   
                }
                catch (Exception ex)
                {

                }
                finally
                {
                    dbReader.Close();
                    conDataBase.Close();
                }
                return bans.Proof;
            }
I'm also using Postman to post directly to the controller via Json, it works with an int... however the value always returns null with a string or in this case the "GUID"

I've even made a new class with a constructor and tried it that way, however I feel my constructor is wrong.

I'm trying to get the returns to go to each text block so the information can populate so later after a user searches, a server owner can simply click a button to remove the exact ban they just searched.

But as of now I am just trying to get the GUID to populate the return. and like I said, if I use int it populates both clientside and controller side in the proper way. But as soon as I try to search via a string it always returns null.

Can I see the code where you are posting an int? I only see the WebAPI methods accepting a tBan object, returning a string.

For serializable objects(Like tBan) you never want to use overloaded constructors or private get/set, and the class and properties should be public. (Or at least keep an empty constructor) This is because on deserialization the deserializer attempts to instance the class by a default empty constructor with no parameters. If it doesn't exist then deserialization will fail.


Also there is a problem with two lines of code...
1. selectCommand.ExecuteNonQuery();
This should only be used by updates/inserts. It might still execute without error, but it won't do anything on a select clause.

2. selectCommand.Dispose();
This shouldn't be called until after you are done with the selectCommand. It tells the garbage collector to release memory used by selectCommand. Since the next line calls ExecuteReader() you still need it. This would be better placed in the finally block right after "dbReader.Close();".

I'm using postman to post Json to the Controller.

Client side results are the same

still returning a Null Value... It's on the controller side.. everything I do is Null. Or can't convert to a string... when it is a string.

This Works:

Code: [Select]

[HttpPost]
        [Route("A3Bans/searchBan")]
        public string oSearchBan(tBan ban)
        {
            {
                tBan bans = new tBan();
                string dbConnection = "datasource=127.0.0.1;port=3306;username=admin;password=00000";
                MySqlConnection conDataBase = new MySqlConnection(dbConnection);
                MySqlDataReader dbReader;

                MySqlCommand selectCommand = new MySqlCommand("SELECT `BanID`, `GUID`, `BanTime`, `Reason`, `BanType`, `Proof` FROM `a3bans`.`bans` WHERE  `GUID`= 'e7af78997ef220a557c97a1a4c11e0c2'", conDataBase);  //@prmGuid, conDataBase); // Returning a null value?!

               // selectCommand.Parameters.AddWithValue("@prmGuid", new tID(ban.GuidOrIP));

                conDataBase.Open();
                dbReader = selectCommand.ExecuteReader();
                try
                {

                    while (dbReader.Read())
                    {
                        tBan searchBan = new tBan();
                        searchBan.BanID = dbReader.GetString("BanID");
                        searchBan.GuidOrIP = dbReader.GetString("GUID");
                        searchBan.BanTime = dbReader.GetString("BanTime");
                        searchBan.BanType = dbReader.GetString("BanType");
                        searchBan.BanReason = dbReader.GetString("Reason");
                        searchBan.BanType = dbReader.GetString("BanType");
                        searchBan.Proof = dbReader.GetString("Proof");
                        bans = searchBan;
                    }
 
                }
                catch (Exception ex)
                {

                }
                finally
                {
                    dbReader.Close();
                    conDataBase.Close();
                }
                return bans.Proof;
            }
        }
However, as soon as I try to make it an input from another source it's null.

So my issue is here:

Code: [Select]

MySqlCommand selectCommand = new MySqlCommand("SELECT `BanID`, `GUID`, `BanTime`, `Reason`, `BanType`, `Proof` FROM `a3bans`.`bans` WHERE  `GUID`= '@prmGuid'", conDataBase);

selectCommand.Parameters.AddWithValue("@prmGuid", new tID(ban.GuidOrIP));
The Class with Constructor:
Which is probably completley wrong.
tID

Code: [Select]

namespace A3Bans.Schemas
{
    public class tID
    {
        public tID(string guidOrIP)
        {
            GuidOrIP = guidOrIP;
        }

        public string GuidOrIP { get; set; }
        public string BanID { get; set; }

    }
}


Any C# devs here?

It either inserts a blank param or it's literally inserting the param

Ohhh noes, I got something to work... but... it's not refining the search. it's literally returning the same value now... sigh... I hate my life .

Code: [Select]

                MySqlConnection con = new MySqlConnection(dbConnection);
                MySqlCommand cmd;
                con.Open();
                MySqlDataReader dbReader;
                string cmdText = "SELECT `BanID`, `GUID`, `BanTime`, `Reason`, `BanType`, `Proof` FROM `a3bans`.`bans` WHERE  `GUID` LIKE @pGUID";


                cmd = new MySqlCommand(cmdText, con);

                cmd.Parameters.AddWithValue("@pGUID", "%" + bans.GuidOrIP + "%");