Creating a sql connection in c#

Question

I'm new to this site and also to programming. I am currently creating an inventory system via a point of sale. It uses modal and non-modal forms. My problem is tho, I'm working on the change password dialog which has to be connected to the database in order to overwrite the password field. The database i used is microsoft sql server management studio express. Here is what I have so far with the necessary comments. Please note that on the 'design' form, I have a combobox which is bounded to the database. Where did I go wrong?

private void ChangePwdButton_Click(object sender, EventArgs e)
{
  SqlConnection sqlconn = new SqlConnection();
  sqlconn.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Gerald-   dean Martin\Documents\SQL Server Management Studio Express\Projects\BodyMates.mdf;Integrated Security=True;User Instance=True";
     sqlconn.Open();
    string oldpwd = txtOldPwd.Text;
    string newpwd = txtNewPwd.Text;
    string confirmNewPwd = txtConfirmNewPwd.Text;
    string sqlquery = "UPDATE [Employee] SET Pwd=@newpass where EmployeeCode=@empcode";
    SqlCommand cmd = new SqlCommand(sqlquery, sqlconn);
    cmd.Parameters.AddWithValue("@newpass", txtConfirmNewPwd.Text);
    cmd.Parameters.AddWithValue("@empcode", comboEmpCode.SelectedValue);
    //cmd.Parameters.AddWithValue("@pwd", txtNewPwd.Text);
    cmd.Connection = sqlconn;
    cmd.ExecuteNonQuery();
    SqlDataReader dr = cmd.ExecuteReader();             
    while (dr.Read())
    {
        if(txtOldPwd.Text == dr["pwd"].ToString() && (txtNewPwd.Text == txtConfirmNewPwd.Text))
        {
            if (comboEmpCode.SelectedIndex == 0)
            {
               string query = "UPDATE [Employee] SET Pwd = '" + txtConfirmNewPwd.Text + "'";
            }
        }

       // if ((txtNewPwd.Text == dr["newpwd"].ToString()) & (txtConfirmNewPwd.Text == (dr["confirmNewPwd"].ToString()))) { }
    }
   // MessageBox.Show("Password was changed Successfully!", "Password Change", MessageBoxButtons.OK, MessageBoxIcon.Information); 
}

Why do you think that you can read from an update command? `cmd.ExecuteNonQuery(); SqlDataReader dr = cmd.ExecuteReader();`. Btw, `cmd.Connection = sqlconn;` is redundant. — Tim Schmelter, Dec 25 '12 at 22:13
@TimSchmelter especially when you create command via `new SqlCommand(sqlquery, sqlconn)` :) — Sergey Berezovskiy, Dec 25 '12 at 22:14
Are you storing the user passwords in clear text? Do they get encrypted on the back end in the database? Also, why are you mixing paradigms in your code here, with a parameterized query up top, and then an update query built using concatenated strings at the bottom? That second SQL string is a SQL injection attack waiting to happen, and if you're storing those passwords in clear text, it's a gaping security hole that someone will find and exploit. — Maurice Reeves, Dec 25 '12 at 22:21
Also, please, for the love of memory, wrap the database objects in `using` statements so they're properly disposed when they fall out of scope. — Maurice Reeves, Dec 25 '12 at 22:24
The part `Gerald-___dean Martin` in the file path seems very suspicious. Does the file name really contain 3 consecutive spaces (represented with `_` here)? — Olivier Jacot-Descombes, Dec 25 '12 at 22:55

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

1

You can use ExecuteNonQuery like cmd.ExecuteNonQuery(); It returns int value. Use it like this;

int i = cmd.ExecuteNonQuery();

And also ExecuteReader() works like this;

SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine(String.Format("{0}", reader[0]));
        }

You can read returning data's column. Like first column reader[0], second column reader[1] etc.

But before all this information, if you are new to programming, you can find a lot of book proposal and useful informations on Stackoverflow. Check these articles;

What is the single most influential book every programmer should read?
https://stackoverflow.com/questions/477748/what-are-the-best-c-sharp-books
https://stackoverflow.com/questions/2018/best-book-for-a-new-database-developer

edited May 23 '17 at 12:12

Community

1
1

answered Dec 25 '12 at 22:29

Soner Gönül

97,193
102
206
364

3

+1 for pointing him in the right direction at the bottom. Cheers! – Maurice Reeves Dec 25 '12 at 22:40
@Soner, Maurice and lazyberezovsky, thanks for your reply. To be honest I dont understand your replies as I said I'm new to this thing. Is there any book you could recommend that I could read that would definitely shed some light on the whole connection thing because I need to understand what each line of statement means. Thanks again. – GerryD Dec 26 '12 at 18:12
@GerryD For C# and database connections, I would recommend Beginning C# 2008 Databases From Novice to Professional looks best for you. Look at from [Amazon](http://www.amazon.com/Beginning-2008-Databases-Professional-Professionals/dp/1590599004/ref=sr_1_1?s=books&ie=UTF8&qid=1356545828&sr=1-1&keywords=Beginning+C+2008+Databases+From+Novice+to+Professional). – Soner Gönül Dec 26 '12 at 18:17
@GerryD If you find this answer usefull, you can select an answer. – Soner Gönül Dec 26 '12 at 18:38
@Soner, ok. I'm grateful. I'm now taking steps to acquire the book. Thanks! – GerryD Dec 26 '12 at 19:17

Creating a sql connection in c#

1 Answers1

Linked