• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

ASP.Net syntax with SQL Stored procedures

I'm trying to find the best way to get a parameter into a stored procedure being called when a page loads up. My BindData code looks like this:
private void BindData()
{
SqlCommand cm = new SqlCommand("MyStoredProcedure");
cm.CommandType = CommandType.StoredProcedure;
cm.Parameters.Add(new SqlParameter("@UserID", Session["id"]));
MyGrid.DataSource = GetData(cm);
MyGrid.DataBind();
}
The userid is what I want to use as the parameter. It is a session variable I want to capture on login. Any idea the best way to do this? I'm using VS 2008/C#/SQL Server 2005.
0
dodgerfan
Asked:
dodgerfan
  • 7
  • 5
1 Solution
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, I recommend you to use a pattern like this:
private void BindData()
{
    using (SqlConnection cn = new SqlConnection("Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"))
    {
        SqlCommand cm = new SqlCommand("MyStoredProcedure", cn);
        cm.CommandType = CommandType.StoredProcedure;
        cm.Parameters.Add("@UserID", SqlDbType.Int).Value = Session["id"];
        cn.Open();
        using (SqlDataReader dr = cm.ExecuteReader())
        {
            MyGrid.DataSource = dr;
            MyGrid.DataBind();
        }
    }
}

Open in new window


The previous code require these namespaces:
System.Data;
System.Data.SqlClient;

Open in new window


Also the connection string that I have used is for demo purposes, you must think in a common place where to store your connection string (you can use the web.config file), and I'm assuming that your UserID parameters is a Integer value, otherwise change SqlDbType.Int to the correct type.
0
 
dodgerfanAuthor Commented:
yv989c,
Ok, I seemed to have done this wrong. I'm getting the error stating that the stored procedure expects a parameter and it was not supplied. That means I am not actually storing the UserID in a session variable as I thought I was doing. I'm using asp.net membership, however I am using another table to store UserId (they are legacy IDs). In this same page, in the OnLoad event, I am pulling the UserID from the correct table. How could I refer to that UserID for the above code? Or is it better to grab it at login and store it in a session?
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
I have adapted the previous code to use a connection string saved in your web.config.

This is an example of how must appear your connection string in your web.config file:
<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="MyConnection"
         connectionString="Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"
         providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
    <compilation debug="false"/>
  </system.web>
</configuration>

Open in new window

(You must change the connection parameters with the real ones, like your server name, DB name, user, etc...)

Then your can get your connection string from your code by using:
System.Configuration.ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString

Open in new window


The next example retrieve the connection string from the web.config:
private void BindData()
{
    using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString))
    {
        SqlCommand cm = new SqlCommand("MyStoredProcedure", cn);
        cm.CommandType = CommandType.StoredProcedure;
        cm.Parameters.Add("@UserID", SqlDbType.Int).Value = Session["id"];
        cn.Open();
        using (SqlDataReader dr = cm.ExecuteReader())
        {
            MyGrid.DataSource = dr;
            MyGrid.DataBind();
        }
    }
}

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Carlos VillegasFull Stack .NET DeveloperCommented:
Ok, you can write a method that retrieve the legacy UserId your the current authenticated user, store it to a session variable and return it, the next time that you call that method it can check first the session variable, if it is not null then return it's value instead of make DB query, can you provide the code of how you retrieve this value? I can help you to write this method.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
the legacy UserId your the current authenticated user
must read:
the legacy UserId for the current authenticated user
0
 
dodgerfanAuthor Commented:
yv989c,
Ok, here is the code behind to the page. I changed my BindData code to utilize your recommendations. All that is missing is getting the correct userid. Here it is:

protected void Page_Load(object sender, EventArgs e)
{
string userLogin = User.Identity.Name;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["MyProject"].ConnectionString;
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select UserID, LName, FName from vwUserInfo where UserName = '" + userLogin + "'";
cmd.Connection = conn;

conn.Open();
SqlDataReader reader = default(SqlDataReader);
reader = cmd.ExecuteReader():

using (reader)
{
while (reader.Read())
  int i = 0;
  for (i=0, i <= reader.FieldCount - 1; i++)
  {
      HttpContext.Current.Session[reader.GetName(i)] = reader.GetValue(i);
   }
}
}
if (User.IsInRole("Admin"))
{
      MyGrid.Visible = true;
}
else if (User.IsInRole("Testing"))
{
      MyGrid.Visible = true;
}

this.BindData();
}

private void BindData()
and this is what you already helped with.

The grid is set to visible = false. Only members of those two groups can see it. The vwUserInfo view is  view in SQL Server. It combines the aspnet_Users table with my own UserInfo table. The only thing from the aspnet_Users table in the query is the UserName field. The other fields are in my custom table. I hope this makes sense.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hello, Ok, I have created a new class named SessionHelper, you must place it on your App_Code folder:
using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;

public static class SessionHelper
{
    private static HttpContext GetContext()
    {
        HttpContext context = HttpContext.Current;
        if (context.Session["UserID"] != null)
            return context;

        // Initialize Session values if required.
        using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyProject"].ConnectionString))
        {
            SqlCommand cm = new SqlCommand("SELECT UserID, LName, FName FROM vwUserInfo WHERE UserName = @pUserName", cn);
            // You must see if this has the correct data type and length of the UserName column (i'm assuming VarChar(50)).
            cm.Parameters.Add("@pUserName", SqlDbType.VarChar, 50).Value = context.User.Identity.Name;
            cn.Open();
            using (SqlDataReader dr = cm.ExecuteReader(CommandBehavior.SingleRow))
            {
                if (dr.Read())
                {
                    context.Session["UserID"] = Convert.ToInt32(dr["UserID"]);
                    context.Session["LName"] = dr["LName"].ToString();
                    context.Session["FName"] = dr["FName"].ToString();
                }
            }
        }

        return context;
    }

    public static int GetUserId()
    {
        return Convert.ToInt32(GetContext().Session["UserID"]);
    }

    public static string GetFirstName()
    {
        return Convert.ToString(GetContext().Session["FName"]);
    }

    public static string GetLastName()
    {
        return Convert.ToString(GetContext().Session["LName"]);
    }
}

Open in new window

Here the file: SessionHelper.cs

Now, from your pages you can use it in this way:
protected void Page_Load(object sender, EventArgs e)
{
    if (User.IsInRole("Admin"))
    {
        MyGrid.Visible = true;
    }
    else if (User.IsInRole("Testing"))
    {
        MyGrid.Visible = true;
    }

    this.BindData();
}

private void BindData()
{
    using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyProject"].ConnectionString))
    {
        SqlCommand cm = new SqlCommand("MyStoredProcedure", cn);
        cm.CommandType = CommandType.StoredProcedure;
        cm.Parameters.Add("@UserID", SqlDbType.Int).Value = SessionHelper.GetUserId();
        cn.Open();
        using (SqlDataReader dr = cm.ExecuteReader())
        {
            MyGrid.DataSource = dr;
            MyGrid.DataBind();
        }
    }
}

Open in new window


Please test it, I have not tested the code, tell me about any error.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Also, for your page, I think that is better if you check if the current request is not a PostBack:
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        if (User.IsInRole("Admin"))
        {
            MyGrid.Visible = true;
        }
        else if (User.IsInRole("Testing"))
        {
            MyGrid.Visible = true;
        }

        this.BindData();
    }
}

Open in new window

0
 
dodgerfanAuthor Commented:
yv989c,
Thanks again for the help. I had to leave before I could try it, but I'm sure it will work. If I have a problem I'll post back up here. This will help me a great deal. I have to grab the UserID all over the place depending on what I'm bringing up. I may ask another question regarding creating new users in the next day or so if I can't get it figured out fast enough, Again, thanks.
0
 
dodgerfanAuthor Commented:
Fantiastic!
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
No problem, you're welcome.
0
 
dodgerfanAuthor Commented:
yv989c:
I just posted a related question, which I think is pretty simple.
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_27405009.html
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now