Connection String - Using sqlDataReader

It's been awhile since my last post. Now I'm currently doing some asp .net projects again and I can't remember the connection string and commands I've previously used to manually connect and get data from my source, hence, I would post it here as a reference so I don't have to search again and again for the source code.

Assuming that i have my sql server connectionstring configured on my web.config file and my stored procedure named "valStudentProfile" with two varchar parameters "username" and "password" created on my SQL Server 2005.

VB Code

Imports System.Data.SqlClient

'-- this function will validate user and return true or false
'-- by calling stored procedure and passing parameters.

Function AuthenticateUser(ByVal Username As String, ByVal Password As String) As Boolean

Dim oConn As New SqlConnection

oConn.ConnectionString = ConfigurationManager.AppSettings("MyServerName")
oConn.Open()

Dim oComm As New SqlCommand
oComm.Connection = oConn

oComm.CommandType = CommandType.StoredProcedure
oComm.CommandText = "valStudentProfile"

Dim pUser As New SqlParameter("@username", SqlDbType.VarChar)
Dim pPass As New SqlParameter("@password", SqlDbType.VarChar)

pUser.Value = UserName.ToString()
pPass.Value = Password.ToString()

oComm.Parameters.Add(pUser)
oComm.Parameters.Add(pPass)

Dim rd As SqlDataReader = oComm.ExecuteReader()

rd.Read()

'- validate if have records
If rd.HasRows Then
'- initialize my session variable
Session("userid") = rd.Item("userid").ToString()
'return true if record exist in the database
Return True
Else
'- return false if no record found match
Return False
End If
-- disposing objects
oComm.Dispose()
oConn.Dispose()
End Function

C# Code


public authUser()
{
string username = txtUsername.ToString();
string password = txtPassword.ToString();

SqlConnection oConn = SqlConnection(ConfigurationManager.AppSettings["MyServerName"]);
conn.open();

SqlCommand oComm = new SqlCommand("valStudentProfile", oConn);

oComm.CommandType = CommandType.StoredProcedure;
oComm.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
oComm.Parameters.Add("@password", SqlDbType.VarChar).Value = password;

SqlDataReader rd = oComm.ExecuteReader();

if (rd.Read())
{
return true;
} else {
return false
}
}