We help IT Professionals succeed at work.

Database Help (250 pts)

onestar
onestar asked
on
432 Views
Last Modified: 2008-02-01
Hi, I am totally new to ASP.NET 2.0 using C# and MS SQL 2005. I am coming from a PHP MySQL background. I just started a new job and now I have to do everything in asp. I am having some issues with MSSQL. It just seem very confusing on how to do the database side of things. There just seems many ways of doing things with data readers and data sets and I am not sure what is the best way or the best way to start. Can someone tell me the best way and give me some complete working sample code in C# to get me started. I know this is pretty simple but a little time consuming. I have put up 250 pts.

This is the way I am used to doing things in php and mysql.

I am using a free php class ezsql http://www.jvmultimedia.com/portal/node/6 but this is the jist of it.

// connect to database;
$db = new ezSQL_mysql('db_user','db_password','db_name','db_host');

// get single record (set variable with result)
$userid = $db->get_var("SELECT userid FROM users WHERE userid = 1");
echo "$userid"; // print userid

// get row (return object)
$user = $db->get_row("SELECT * FROM users WHERE userid = 1");
echo "$user->username"; // print username

// get results (return object)
$users = $db->get_results("SELECT * FROM users");
// print out all users
foreach ($users as $myrow)
{
echo "$myrow->username<br />";
}

// execute and insert or update or delete
$sql= "UPDATE `schedule` SET `results` = '$result',`winner` = '$winner' WHERE `game_id` = $game_id LIMIT 1";
$db->query("$sql");

// Class variable that I used all the time as well
$db->num_rows – Number of rows that were returned (by the database) for the last query (if any)
$db->insert_id -- ID generated from the AUTO_INCRIMENT of the previous INSERT operation (if any)
$db->rows_affected -- Number of rows affected (in the database) by the last INSERT, UPDATE or DELETE (if any)

So I need to know how to do all this in ASP.NET 2.0 using C# connecting to MS SQL
I would like full code on how to do this. If you have any good links I should read let me know also.

This is one example I got to work, I am not sure if this is a good way or not.

public void insertUser(string username, string email)
    {
        string con_string = "Data Source=.\\SQLEXPRESS;AttachDbFilename=\"C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\helpdesk.mdf\";Integrated Security=True;Connect Timeout=30;User Instance=True";
        string sql = string.Format(
        "INSERT INTO users (username, email) VALUES ('{0}', '{1}')",username, email);

        SqlCommand cm = new SqlCommand(sql, new SqlConnection(con_string));
        cm.Connection.Open();
        cm.ExecuteNonQuery();
        cm.Connection.Close();
    }

I really appericate any help you can give.

Thanks Onestar
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
When you do with parameters.add does that prevent SQL injection? In php I use mysql_escape function on each var to escape the strings properly from a user.

I still would like to know how to return a single var, a row and result set to an object and display it. Like I said I am totally new to ASP.

Thanks
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>When you do with parameters.add does that prevent SQL injection?
yes, as the .net command will handle the stuff.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.