Could anyone point me into the right direction on how to make a C# SQL Datalogin?
GAME -5 Junior Poster
mcriscolo 47 Posting Whiz in Training
edgias 0 Newbie Poster
using System.Data.SqlClient;
SqlConnection conn=new Sqlconnection("YourConnectionString");
conn.Open();
string qry="SELECT * FROM Tablename";
Sqlcommand cmd=new SqlCommand(qry,conn);
bool userFound=false;
SqlDatareader dr;
dr=cmd.ExecuteReader();
while(dr.Read())
{
if(user==user.Text&&pass=pass.Text)
{
userFound=true;
break;
}
}
if(userFound)
{
//Do Something e.g Response.Redirect("ToSomePage.aspx");
}
else
{
}
Singlem 0 Light Poster
Code above is very unsafe and has many possibilities to fail. Better to use try/catch/finally block for connection to the database and use a more "Where statement query"
using System.Data.SqlClient;
private void btnCompile_Click(object sender, EventArgs e)
{
string qry = "SELECT Password FROM Tablename WHERE User=" + edtUserName.Text;
using (SqlConnection conn = new SqlConnection("YourConnectionString"))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(qry, conn);
SqlDataReader reader;
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
if (edtPassword.Text == reader["Passwrod"].ToString())
{
//what needs to happen if after this
}
}
}
else
{
MessageBox.Show("Username was not found", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error Opening Database", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
finally
{
conn.Close();
}
}
}
jugosoft 25 Junior Poster in Training
Your code in vulnerabile with SQL injection. Always use parametarised queries:
public void checkUsername() {
string qry = "SELECT Password FROM Tablename WHERE User=@username";
using (SqlConnection conn = new SqlConnection("YourConnectionString"))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(qry, conn);
cmd.Parameters.Add(new SqlParameter("username", userName.Text));
SqlDataReader reader;
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
if (edtPassword.Text == reader["Passwrod"].ToString())
{
//what needs to happen if after this
}
}
}
else
{
MessageBox.Show("Username was not found", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error Opening Database", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
finally
{
conn.Close();
}
}
}
Edited by jugosoft because: n/a
Singlem 0 Light Poster
Thanks jugosoft, I always use stored procedures due to the injection fact, only been doing C# 3 months now...parametarised queries is better to use in all cases:)
Mitja Bonca 557 Nearly a Posting Maven
This is my code (I actually use it as well):
public static bool LogingInMethod(string userName, string password)
{
bool result = false;
string sqlQuery = "SELECT UserName, Password FROM Users WHERE UserName ='" + userName + "'";
using (SqlConnection sqlConn = new SqlConnection(p))
{
SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn);
SqlDataReader reader;
bool HasRows = false;
try
{
sqlConn.Open();
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
if (password == reader["Password"].ToString().Trim())
{
result = true;
break;
}
HasRows = true;
}
reader.Close();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
sqlConn.Close();
}
finally
{
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
cmd = null;
reader = null;
GC.Collect();
}
if (result == true)
{
result = true;
}
else if (HasRows == false)
{
MessageBox.Show("Wrong userName.", "Eror", MessageBoxButtons.OK, MessageBoxIcon.Error);
result = false;
}
else
{
MessageBox.Show("Wrong password.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
result = false;
}
}
return result;
}
GAME -5 Junior Poster
This is my code (I actually use it as well):
public static bool LogingInMethod(string userName, string password) { bool result = false; string sqlQuery = "SELECT UserName, Password FROM Users WHERE UserName ='" + userName + "'"; using (SqlConnection sqlConn = new SqlConnection(p)) { SqlCommand cmd = new SqlCommand(sqlQuery, sqlConn); SqlDataReader reader; bool HasRows = false; try { sqlConn.Open(); reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) if (password == reader["Password"].ToString().Trim()) { result = true; break; } HasRows = true; } reader.Close(); } catch (System.Exception ex) { MessageBox.Show(ex.Message); sqlConn.Close(); } finally { if (sqlConn.State == ConnectionState.Open) { sqlConn.Close(); } cmd = null; reader = null; GC.Collect(); } if (result == true) { result = true; } else if (HasRows == false) { MessageBox.Show("Wrong userName.", "Eror", MessageBoxButtons.OK, MessageBoxIcon.Error); result = false; } else { MessageBox.Show("Wrong password.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); result = false; } } return result; }
Would you happen to know how to set up a public sql database, so anyuser would be able to access it?
Mitja Bonca 557 Nearly a Posting Maven
GAME -5 Junior Poster
I still don't get what to do.
Singlem 0 Light Poster
The connection is handled in here
#
using (SqlConnection conn = new SqlConnection("YourConnectionString"))
So you just need to set that up
public string YourConnectionString = "SERVER = localhost; DATABASE = DatabaseName; User ID = root; PASSWORD = password;";
GAME -5 Junior Poster
How would I set up an online database?
Singlem 0 Light Poster
Google I found has many answers regarding this in the world
http://msdn.microsoft.com/en-us/library/dd857537(v=vs.85).aspx
Don't be afraid to use google
shajis001 -1 Newbie Poster
http://en.csharp-online.net/Working_...s_using_OLE_DB
refer this u will get the basdic idea and then start...
shajis001 -1 Newbie Poster
using System;
using System.Data;
using System.Data.OleDb;
namespace csbook.ch15 {
class Example15_2 {
static void Main(string[] args) {
string conString =
@"Provider=Microsoft.JET.OLEDB.4.0;"
+ @"data source=c:\data\Northwind.mdb";
// create an open the connection
OleDbConnection conn = new OleDbConnection(conString);
conn.Open();
// create the DataSet
DataSet ds = new DataSet();
// create the adapter and fill the DataSet
OleDbDataAdapter adapter =
new OleDbDataAdapter("Select * from Customers", conn);
adapter.Fill(ds);
// close the connection
conn.Close();
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows) {
Console.WriteLine(dr["CompanyName"].ToString());
}
}
}
}
The listing first creates a connection string that provides information to the OleDbConnection class. Specifically, the connection string contains the provider for the database engine that we want to connect to and the data source, which in this case is a Microsoft Access file. If the file were password protected, we would also specify a User ID and Password in the connection string. After creating the connection string, the program creates an OleDbConnection object, passing the connection string as the argument.
Next, the listing opens the database connection by calling the connection’s Open method. It also creates an empty DataSet that will later be filled from the database.
The OleDbAdapter class encapsulates the command to fill the DataSet. We create the adapter by passing a SQL query string and the database connection to its constructor. This query string will select all fields of all rows from the Customers table. The adapter’s Fill method will execute the query through the connection and load the results into the DataSet that we pass to it. The results include the data from the query as well as the metadata, defining its structure. When the call is complete, the DataSet will contain a table with all the Customer records from the database. At that point, we can close the database connection so that it can be used by some other application.
The tables within the DataSet are represented by the DataTable class. Each DataTable has a collection of DataRow objects storing the rows from the query. The foreach loop iterates through the DataRows, displaying the CompanyName field. The DataRow object uses the string "CompanyName" as an index to find the field of that name and retrieve its value. The value is returned as an object type. Calling the virtual ToString method will result in displaying the string value of the field
Edited by mike_2000_17 because: Fixed formatting
GAME -5 Junior Poster
Does anyone have a method to insert a username, password and a email?
Mitja Bonca 557 Nearly a Posting Maven
What do you mean? A method in Win form to insert userName, password and an email to sql dataBase?
YOu have to pass the data from textBoxes to sql command parameters, end execute the procedure.
Try this code:
private void InsertingData()
{
string connString = @"server=x;uid=y;pwd=z;database=xyz"; //this is an example, you need your own
using (SqlConnection sqlConn = new SqlConnection(connString))
{
string query = String.Format(@"INSERT INTO Users VALUES (@id, @name, @password, @email)");
using (SqlCommand cmd = new SqlCommand(query, sqlConn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1; //FIND YOUR NEW ID IF YOU HAVE THIS COLUMN IN DATABASE
cmd.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = textBox1.Text;
cmd.Parameters.Add("@password", SqlDbType.VarChar, 50).Value = textBox2.Text;
cmd.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = textBox3.Text;
cmd.Connection.Open();
try { cmd.ExecuteNonQuery(); }
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally { cmd.Connection.Close(); }
}
}
}
GAME commented: Sweet, It worked... +0
GAME -5 Junior Poster
It worked. THANK YOU!!!
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.