Accessing tables from database

This tutorial will show you how to access tables in database. Use the same database as we used in previous tutorial. You can also download the database script here

Problem statement – Put a simple login box on page containing username & password. Once you click submit, it will either display ‘successful’ msg, or failed (if the entry does not contain in the connected database table)

C# Code - using System;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

public partial class DB_Connection : System.Web.UI.Page
{
public SqlConnection connection;
public SqlCommand command; public SqlDataAdapter adapter; public SqlDataReader reader;
public DataSet ds;
public int recordsAffected;
public string connectionString = "Data Source=.\\SQLEXPRESS; Initial Catalog=Student; Integrated Security=True";
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnLogin_Click(object sender, EventArgs e)
{
int success=connectDB();
if (success == 1)
{
try
{
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
string username = txtUsername.Text;
string password = txtPassword.Text;
string query = "select * from login_credentials where user_name='" + username + "' and password='" + password + "'";//"Insert into login_credentials values('" + username + "','" + password + "')";
command = new SqlCommand(query, connection);
reader = command.ExecuteReader();
bool isSelected = reader.HasRows;
if (isSelected)
{
lblMessage.Text = "Login Successful";
}
else
{ lblMessage.Text = "Login Failed"; }

}
catch (Exception ex)
{
lblMessage.Text = ex.Message;
}
finally
{
connection.Close();
command.Dispose();
}
}
else
{
lblMessage.Text = "Can't Connect to Database";
}
}
public int connectDB()
{
try
{
connection = new SqlConnection(connectionString);
return (1);
}
catch (Exception ex)
{
return (0);
}
}

}

Here we are inputting username & password and checking across the database if they are valid or not with the help of query.
Download complete code here