the usage of procedure between asp.net and sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

the usage of procedure between asp.net and sql

Author

Topic

ying
Starting Member

4 Posts

Posted - 2004-05-29 : 04:36:02

CREATE PROCEDURE news123.glbl_Login
(
@username VarChar(100),
@password VarChar(20)
)
AS
select * from dbo.Login where username=@username and userpass=@password
RETURN
GO

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Web.Security;

namespace secure.Components
{
public class CCommonDB : CSql
{
public CCommonDB() : base() { }

public string AuthenticateUser(
System.Web.SessionState.HttpSessionState objSession, // Session Variable
System.Web.HttpResponse objResponse, // Response Variable
string email, // Login
string password, // Password
bool bPersist // Persist login
)
{
int nLoginID = 0;
int nLoginType = 0;

// Log the user in
Login(email, password, ref nLoginID, ref nLoginType);

if(nLoginID != 0) // Success
{
// Log the user in
System.Web.Security.FormsAuthentication.SetAuthCookie(nLoginID.ToString(), bPersist);

// Set the session varaibles
objSession["loginID"] = nLoginID.ToString();
objSession["loginType"] = nLoginType.ToString();

// Set cookie information incase they made it persistant
System.Web.HttpCookie wrapperCookie = new System.Web.HttpCookie("wrapper");
wrapperCookie.Value = objSession["wrapper"].ToString();
wrapperCookie.Expires = DateTime.Now.AddDays(30);

System.Web.HttpCookie lgnTypeCookie = new System.Web.HttpCookie("loginType");
lgnTypeCookie.Value = objSession["loginType"].ToString();
lgnTypeCookie.Expires = DateTime.Now.AddDays(30);

// Add the cookie to the response
objResponse.Cookies.Add(wrapperCookie);
objResponse.Cookies.Add(lgnTypeCookie);
switch(nLoginID)
{ case 1:
{
return "../wandlsort.aspx";
}
case 2: // Admin Login
{
return "../wandlgx10.aspx";
}
case 3: // Reporting Login
{
return "../wandlcr.aspx";
}
default:
{
return string.Empty;
}
}
}
else
{
return string.Empty;
}
}

/// <summary>
/// Verifies the login and password that were given
/// </summary>
/// <param name="email">the login</param>
/// <param name="password">the password</param>
/// <param name="nLoginID">returns the login id</param>
/// <param name="nLoginType">returns the login type</param>
public void Login(string email, string password, ref int nLoginID, ref int nLoginType)
{
ResetSql();

DataSet ds = new DataSet();

// Set our parameters
SqlParameter paramLogin = new SqlParameter("@username", SqlDbType.VarChar, 100);
paramLogin.Value = email;

SqlParameter paramPassword = new SqlParameter("@password", SqlDbType.VarChar, 20);
paramPassword.Value = password;

Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = "glbl_Login";
Command.Parameters.Add(paramLogin);
Command.Parameters.Add(paramPassword);

Adapter.TableMappings.Add("Table", "Login");
Adapter.SelectCommand = Command;
Adapter.Fill(ds);

if(ds.Tables.Count != 0)
{
DataRow row = ds.Tables[0].Rows[0];

// Get the login id and the login type
nLoginID = Convert.ToInt32(row["Login_ID"].ToString());
nLoginType = Convert.ToInt32(row["Login_Type"].ToString());
}
else
{
nLoginID = 0;
nLoginType = 0;
}
}
}

abstract public class CSql
{
private SqlConnection sqlConnection; // Connection string
private SqlCommand sqlCommand; // Command
private SqlDataAdapter sqlDataAdapter; // Data Adapter
private DataSet sqlDataSet; // Data Set

public CSql()
{
sqlConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
sqlCommand = new SqlCommand();
sqlDataAdapter = new SqlDataAdapter();
sqlDataSet = new DataSet();

sqlCommand.Connection = sqlConnection;
}

/// <summary>
/// Access to our sql command
/// </summary>
protected SqlCommand Command
{
get { return sqlCommand; }
}

/// <summary>
/// Access to our data adapter
/// </summary>
protected SqlDataAdapter Adapter
{
get { return sqlDataAdapter; }
}

/// <summary>
/// Makes sure that everything is clear and ready for a new query
/// </summary>
protected void ResetSql()
{
if(sqlCommand != null)
{
sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
}
if(sqlDataAdapter != null)
sqlDataAdapter = new SqlDataAdapter();

if(sqlDataSet != null)
sqlDataSet = new DataSet();
}

/// <summary>
/// Runs our command and returns the dataset
/// </summary>
/// <returns>the data set</returns>
protected DataSet RunQuery()
{
sqlDataAdapter.SelectCommand = Command;

sqlConnection.Open();
sqlConnection.Close();

sqlDataAdapter.Fill(sqlDataSet);

return sqlDataSet;
}
}
}
error:System.IndexOutOfRangeException

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources