I'm new to C# and SQL so I would really appreciate your help and advises.
My idea is to connect to the DB with SQL connection, select the password and email, and send it to the person who wants to recover the account password via e-mail(SMTP connection).
My issue is that I'm trying to make a password recovery option for my C# project via SQL, but I get the following error: "Keyword not supported: 'provider'".
Here is my app.config file:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name="Sales_and_Inventory_System__Gadgets_Shop_.Properties.Settings.POS_DBConnectionString" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\bin\Debug\POS_DB.mdf;Integrated Security=True;Connect Timeout=30" providerName="System.Data.SqlClient" />
<add name="Restaurant_Management_System.Properties.Settings.SIS_DBConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\bin\Debug\SIS_DB.accdb" providerName="System.Data.OleDb" />
<add name="Restaurant_Management_System.Properties.Settings.POS_DBConnectionString" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\bin\Debug\POS_DB.mdf;Integrated Security=True;Connect Timeout=30" providerName="System.Data.SqlClient" />
<add name="Restaurant_Management_System.Properties.Settings.RMS_DBConnectionString" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\bin\Debug\RMS_DB.mdf;Integrated Security=True;Connect Timeout=30" providerName="System.Data.SqlClient" />
</connectionStrings>
<startup useLegacyV2RuntimeActivationPolicy="true">
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<system.data>
<DbProviderFactories>
<remove invariant="System.Data.SQLite" />
<add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
</DbProviderFactories>
</system.data></configuration>
And here is my Password recovery class file:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Net.Mail;
namespace Restaurant_Management_System
{
public partial class frmRecoveryPassword : MetroFramework.Forms.MetroForm
{
String cs = "Provider=Microsoft.ACE.Sql.12.0;Data Source=|DataDirectory|\\SIS_DB.accdb;";
public frmRecoveryPassword()
{
InitializeComponent();
}
private void RecoveryPassword_Load(object sender, EventArgs e)
{
txtEmail.Focus();
}
private void RecoveryPassword_FormClosing(object sender, FormClosingEventArgs e)
{
this.Hide();
frmLogin frm = new frmLogin();
frm.txtUserName.Text = "";
frm.txtPassword.Text = "";
frm.txtUserName.Focus();
frm.Show();
}
private void timer1_Tick(object sender, EventArgs e)
{
Cursor = Cursors.Default;
timer1.Enabled = false;
}
private void metroTile1_Click(object sender, EventArgs e)
{
if (txtEmail.Text == "")
{
MessageBox.Show("Enter your email", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
txtEmail.Focus();
return;
}
try
{
Cursor = Cursors.WaitCursor;
timer1.Enabled = true;
DataSet ds = new DataSet();
SqlConnection con = new SqlConnection(cs);
con.Open();
SqlCommand cmd = new SqlCommand("SELECT User_Password FROM Registration Where Email = '" + txtEmail.Text + "'", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
MailMessage Msg = new MailMessage();
// Sender e-mail address.
Msg.From = new MailAddress("[email protected]");
// Recipient e-mail address.
Msg.To.Add(txtEmail.Text);
Msg.Subject = "Your Password Details";
Msg.Body = "Your Password: " + Convert.ToString(ds.Tables[0].Rows[0]["user_Password"]) + "";
Msg.IsBodyHtml = true;
// your remote SMTP server IP.
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.gmail.com";
smtp.Port = 587;
smtp.Credentials = new System.Net.NetworkCredential("[email protected]", "abcd");
smtp.EnableSsl = true;
smtp.Send(Msg);
MessageBox.Show(("Password Successfully sent " + ("\r\n" + "Please check your mail")), "Thank you", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Hide();
frmLogin LoginForm1 = new frmLogin();
LoginForm1.Show();
LoginForm1.txtUserName.Text = "";
LoginForm1.txtPassword.Text = "";
LoginForm1.ProgressBar1.Visible = false;
LoginForm1.txtUserName.Focus();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void metroTile2_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
You have five (5!) connection strings in the body of your post. The only one you're using is the one that's hard-coded in the frmRecoveryPassword class as the cs field.
SqlConnection complained because it doesn't recognize at least one of the name/value pairs. That's because it's a connection string for OLEDB, not SQL Server.
Based on what you posted, it looked like you were trying to connect to an Access database. To do that, you would need to use OleDbConnection. Based on the comments, though, that's not the case. You really want to connect to a SQL Server database, so SqlConnection is correct and your connection string is wrong.
Starting with what you posted (and not the changes you made since then), the only thing you need to change is this line:
SqlConnection con = new SqlConnection(cs);
Change it to this:
// Pick one of the three names in app.config that refers to a SQL Server database.
// I chose the first one for this example.
string connectionStringName = "Sales_and_Inventory_System__Gadgets_Shop_.Properties.Settings.POS_DBConnectionString";
string connectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
This retrieves the connection by the name specified in connectionStringName and gives that to SqlConnection.
Make sure you're using the correct connection string for what this click handler is doing.
Now let's examine the connection strings.
The ones in app.config appear to be correct in that they have correct-looking values for the providerName attribute specified in each one. The second one points to an Access database and the other three point to SQL Server databases.
The one in the screenshot (from the comments) is wrong. It's a hybrid: you've specified an OleDb provider (for Access) and a SQL Server database file. It looks like you copied the path of the .mdf file from the first connection string in app.config and used that in the second one where the .accdb file was. SqlConnection will give you the same exception as the original question because it doesn't recognize the "Provider" key and OleDbConnection will give you the "unrecognized database format". It's wrong for either type of connection.
Whether you're using SQL Server or Access databases -- it looks like your application connects to both -- you need to make sure you use the correct connection type and connection string for that database.
Let's dig into some of the other problems now:
Do not concatenate strings to construct SQL.
Be sure to read about Little Bobby Tables. Avoiding SQL injection should not be an afterthought. Secure your application from the start. The way to do that is to parameterize your queries.
Ignoring the danger of this exact query for a moment, here's what you should do:
SqlCommand cmd = new SqlCommand("SELECT User_Password FROM Registration WHERE Email = @Email");
// ^^^^^^ parameter
cmd.Parameters.Add(new SqlParameter
{
Name = "@Email",
SqlDbType = SqlDbType.NVarChar,
Size = 100, // or whatever length your Email column is.
Value = txtEmail.Text
// ^^^^^^^^^^^^^ paramter's value
});
There are other Add overloads that you can use but I prefer this one because of how clear it is about which properties you're setting vs. the many other overloads. I've seen people use the wrong "DB Type" and get runtime errors instead of compilation errors, e.g. passing a SqlDbType value when they're using Oracle or MySql and it silently passes that value as the object value parameter. You'll encounter examples of those overloads in many of the answers on SO.
You'll also see AddWithValue used a lot -- don't use it. It's more trouble than it's worth, at least with SQL Server.
This is what I meant by the danger of that exact query...
Never store passwords in plain text.
You shouldn't ever send someone their password because you shouldn't ever be able to retrieve it. Instead, your database should contain a hash of the password. Your User_Password column should look like garbage not only to the human eye but also to any system used to retrieve it.
"Password recovery" is an inaccurate term when you're doing it right. Modern practices for dealing with forgotten passwords are really "access recovery" and they usually involve short-lived tokens that allow you to provide a new password.
Topics like password hashing and access recovery procedures are well documented so I won't go into them any further.