New to C# - DB question

[hedbonker]

I am used to using old school ADO where I might create things like:

objConn.Open( sConnectString);

objRecordSet.Open ("Select * from users where UID=' + sUserID + "'",
objConn);

Then I have a recordset collection that I can filter on or step through
etc. based on a specific SQL query.

Is there a similar methodology employed in .NET? I see items like

DataSource
TableAdapter
DataSet

But I do not see a specific method for executing SQL against a specific
table or set of tables. I have my data source defined to my project
and I can drag and drop grid or field views to my form but I do not
want that type of functionality. I want user inputs that I then do a
lookup in my database with.

Can someone point me in the right direction?

Thanks in advance!

[Steven Nagy]

Hi there,

Yes you are along the right track.
Firstly, you are interested in the System.Data namespace, also known as
ADO.NET
In there you will find a few sub namespaces for specific database
connections, and also some generic classes useful for all database
types.

..NET implements a disconnected architecture, where you will essentially
connect to a DB temporarily, perform some action (select, delete,
update, create), and put the results into some object, then disconnect.
There is a class called "Dataset" which can contain a collection of
"DataTable"s
A dataTable can store the result of a "select" against a database. We
generally use a "DataAdapter" to manage the SQL for us for simple table
operations (ie. a single table CRUD). A DataAdapter (such as the
System.Data.SqlClient.SqlDataAdapter) can take a DataConnection (such
as a System.Data.SqlClient.SqlConnection) object, along with some
select statement (eg Select * From sysobjects) as a string, and fill a
data table.

If you want to perform simple database statements that don't return a
whole result set, you can use the DbCommand objects (such as a
System.Data.SqlClient.SqlCommand).
The command object also takes a connection object, and a string which
is your sql statement. With the command object you have 3 methods, of
which you should concern yourself with just 2 as a beginner:
ExecuteNonQuery() and ExecuteScalar()
The former allows you to just execute some SQL, and it will return the
number of rows affected. The latter option lets you execute some sql
where you expect a single result being returned, known as a 'Scalar'.
Example of this is sql such as "Select Count(*) FROM sysobjects" which
you expect a single integer result, rather than tables with rows.

Anyways, pick which database type you are using and check out the
relative namespace. Note that you can also do quite a lot in design
mode with data adapters and datasets, and this is all very well
documented in MSDN.

Hope this helps,
Steven

[Steven Nagy]

Oh also, if you are in a rush and need to complete some DB
functionality, you could always add a reference to the old adodb com
stuff and just use the recordsets you are familiar with. But if you
have time, definately learn the new architecture in .NET.

I remember when I did a .NET subject at uni in 4 years ago when I was
first introduced to it. I came from ASP background and I knew about COM
but not ADO.NET so I did exactly that on my first assignment: completed
the requirements using an ADO recordset! Luckily I was asked to
resubmit, otherwise I'd be cleaning windows for a living now...

[heddy]

Thanks a ton, Steve. What I ended up doing for this little test was:

private void btnSubmit_Click(object sender, EventArgs e)
{
int Privelege;
string SQL;
OleDbDataReader Reader;
string sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source = G:\\Media Manager.mdb";
OleDbConnection objConnection = new OleDbConnection
(sConnect);

if (this.txtPassWd.Text.Length < 1 ||
this.txtUserID.Text.Length < 1)
{
MessageBox.Show("You MUST fill both fields in!");
return;
}

objConnection.Open();

try
{
SQL = "Select * from Users where username='" +
this.txtUserID.Text + "' AND password='" + this.txtPassWd.Text + "';";
// Find USER ID in users table
// if we find it, validate the password and set the
privledge.

OleDbCommand oMyCommand = new OleDbCommand(SQL,
objConnection);
Reader = oMyCommand.ExecuteReader();

if (Reader.HasRows)
{
// Login is good here
MessageBox.Show("Login is good");
Reader.Read();
Privelege = Reader.GetInt32(3);
}
else
{
MessageBox.Show("Login is bad");
}
}
finally
{
objConnection.Close();
//Reader.Close();
}
}

The docs said to ensure I closed the reader but the compiler complained
about it...Not sure why.

[Steven Nagy]

Close the reader before you close the connection.

What you have done is used ADO.NET, but instead of SQL server
(System.Data.SqlClient) you have used OleDb for Access
(System.Data.OleDb).
There is also: (System.Data.OracleClient) and (System.Data.Odbc).
They all have Data readers, data connections, data adapters and command
objects, and they all work pretty much the same way, just differrent
connection strings of course.

[Marc Gravell]

Some security advice;

A: don't ever store passwords (even cyphered) unless you **really** nead to.
Perhaps hash them and compare hashes... and use a known hash (MD5 or
similar), not the CLR GetHashCode(), as that is implementation specific.

B: don't use string concatenation to place parareters into a SQL string -
you should be using the parameters collection instead (along with whichever
syntax is suitable in your SQL, for instance "?" or "@SomeParam" etc - refer
to documentation).

Firstly, you will run into trouble with the O'Neil's of this world. More
importantly, the reason for this is the simple trick of SQL-injection: for
instance, I could enter (as my user id on the form):
Fred' SELECT * FROM sysobjects --

or about 1000 other things (DROP TABLE... SELECT username, password...); you
can waste a lot of time trying to protect yourself by escaping characters
such as quotes, but the parameters approach deals with it all *much* more
efficiently, robustly and safely.

Best of luck,

Marc

[heddy]

Thanks a ton guys. This is very helpful. I am going to get SQL
express installed and setup a stored proc for the login stuff using
parms. My inital code was just to see if I could connect to a DB and
do something. Is there a standard algorythm for hashing other then the
CLI one?

[heddy]

heddy wrote:

Thanks a ton guys. This is very helpful. I am going to get SQL
express installed and setup a stored proc for the login stuff using
parms. My inital code was just to see if I could connect to a DB and
do something. Is there a standard algorythm for hashing other then the
CLI one?

Also, I need to have an object stay in scope throughout my application.
Should I instantiate the object in main to do this?

[heddy]

As for the security concern, I switched the login to use SQL Server
auth like this:

private void btnSubmit_Click(object sender, EventArgs e)
{
if (this.txtPassWd.Text.Length < 1 ||
this.txtUserID.Text.Length < 1)
{
MessageBox.Show("You MUST fill both fields in!");
return;
}

SqlConnection Conn;
SqlDataReader reader = null;

Conn = new SqlConnection("user id=" + this.txtUserID.Text +
";" +
"password=" +
this.txtPassWd.Text + ";" +
"server=MIKIE\\SQLEXPRESS;" +
"database=Media Manager;" +
"connection timeout=30");
try
{
Conn.Open();
}
catch
{
MessageBox.Show("Login Failed for user: " +
this.txtUserID.Text);
return;
}

try
{
SqlCommand cmd = new SqlCommand("spGetAuthority",
Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserName",
this.txtUserID.Text));
reader = cmd.ExecuteReader();

if (reader.HasRows)
{
// Login is good here
reader.Read();
oLocalUser.iAuthority = reader.GetInt32(0);
oLocalUser.bLoggedIn = true;
MessageBox.Show("Login is good for User: " +
this.txtUserID.Text + " Auth code = " +
oLocalUser.iAuthority.ToString());

}
else
{
MessageBox.Show("Login Failed");
}
}
finally
{
reader.Close();
Conn.Close();

}

}

[heddy]

And for my next question...

After my login form has finished it's processing (Login.frm), I do the
following to load the next form:

ProjectManager formProjects = new
ProjectManager(oLocalUser);
formProjects.Show();
formProjects.BringToFront();
MessageBox.Show(this.Name);
this.Hide();
this.SendToBack();

This all works wonderfully well. But, on the Projects form, when the
user exits the form, I need to show the Login form again. If I try to
reference the Login object, I do not get methods for show etc. How do
I reference back to the Login form so that I can show it again?

Thanks!

[Marc Gravell]

As long as you stay *within* the CLR (preferably within 1 AppDomain)
then the CLR GetHashCode() is fine for hashing (or as fine as the type
in question defines it ;-p). The problem is when you persist that to
e.g. a DB; several of the GetHashCode() algorithms changed between 1.1
and 2.0; result: broken logins if used incorrectly.

For this type of things, you need to use a known hash such as MD5 or
SHA1 (or similar); since the implementation of such is pinned, it
cannot change, regardless of whether one client is MS CLR 1.1, one MS
CLR 2.0, and one "Mono" (open source CLR variant) or similar,

Marc

[heddy]

Marc Gravell wrote:

As long as you stay *within* the CLR (preferably within 1 AppDomain)
then the CLR GetHashCode() is fine for hashing (or as fine as the type
in question defines it ;-p). The problem is when you persist that to
e.g. a DB; several of the GetHashCode() algorithms changed between 1.1
and 2.0; result: broken logins if used incorrectly.

For this type of things, you need to use a known hash such as MD5 or
SHA1 (or similar); since the implementation of such is pinned, it
cannot change, regardless of whether one client is MS CLR 1.1, one MS
CLR 2.0, and one "Mono" (open source CLR variant) or similar,

Marc

Thanks, Marc!

Can anyone else address my other questions perchance?