Mysql in c# --> Find ID where Name is

Question

I have a table in mysql called Users that has 4 rows. ID, Name, pass, company.

I try to make an application in c# WPF that does this : You add the user and give the values that you have to give, and you must also give the company that he belongs too.

Now, what i need to do is find a way to give the company's name and say : Where company's name is Google, place in the user in the field company the id 1

ID    Name

1     Google
2     Facebook
3     Instagram
4     etc_etc

And the code is :

public bool AddUser(string userid, string username, string password, int companyId)
        {
            return ExecQuery("insert into users (id, name, pass, companyid) values(@id, @name, @pass, @cid)",
                cmd =>
                {
                    cmd.Parameters.AddWithValue("@id", userid);
                    cmd.Parameters.AddWithValue("@name", username);
                    cmd.Parameters.AddWithValue("@pass", password);
                    cmd.Parameters.AddWithValue("@cid", companyId);
                    return cmd;
                });
        }

        public bool AddCompany(string name)
        {
            return ExecQuery("insert into companies (name) values(@name)",
                cmd =>
                {
                    cmd.Parameters.AddWithValue("@name", name);
                    return cmd;
                });
        }

So the question is how to get the ID of the inserted company ? — Zein Makki, Aug 10 '16 at 15:09
Yes, how to get the id of the inserted company when the user writes it's name. — Starlex M.A, Aug 10 '16 at 15:10
do a company lookup or else you risk messing up your data. "Acme Freight" could have many rows in Company table. Also, hash your [password](http://stackoverflow.com/a/38841137). — Drew, Aug 10 '16 at 15:12
Can you show me how to do it ? i do hash my password but i will changed the encryption code because it is really bad. public static string GenerateSHA512String(string inputString) { SHA512 sha512 = SHA512Managed.Create(); byte[] bytes = Encoding.UTF8.GetBytes(inputString); byte[] hash = sha512.ComputeHash(bytes); StringBuilder sb = new StringBuilder(); for (int i = 0; i < hash.Length; i++) { sb.Append(hash[i].ToString("X2")); } return sb.ToString(); } — Starlex M.A, Aug 10 '16 at 15:15
Just use the mysql built-in `Password()` function. Have you password column be like varchar(100) or text. — Drew, Aug 10 '16 at 15:19
`create table h123d ( id int auto_increment primary key, password VARCHAR(41) NOT NULL );` ... `insert h123d(password) values (password('Open^sesame87b'));` ... `select * from h123d;` — Drew, Aug 10 '16 at 15:22

score 1 · Answer 1 · answered Aug 10 '16 at 15:21

Assuming the company name is unique & has been selected from the values in the table, you could change the query to include a subquery to get the company id.

 "insert into users (id, name, pass, companyid) 
  values(@id, @name, @pass, (select id from company where Name=@cid))"

If the company name is not unique, this query will fail when multiple values are returned.

If the company name is not found, the query will fail if the companyid field is set to NOT NULL, otherwise null will be entered.

score 1 · Answer 2 · edited May 23 '17 at 12:08

1

You can get last inserted value by the pre-developed MySQL command LAST_INSERT_ID()

You can get last insert id like this in SP:

DECLARE LID int;
SET LID = LAST_INSERT_ID();

Ref: Here

edited May 23 '17 at 12:08

Community

1
1

answered Aug 10 '16 at 15:27

Heram Sharma

23
5

OP wants to insert the company ID value - if this isn't a newly added company, then this will not work. – PaulF Aug 10 '16 at 15:30

Mysql in c# --> Find ID where Name is

2 Answers2