Building session-variables from a SQL stored procedure Calling code and attempt at building session variables:

DataTable dtServerVars = clCommonFunctions.TryAutoLogin("europe\\MrTest");
Session["CablingUserID"]= dtServerVars.Rows[0]["CablingUserID"].ToString();
Session["CablingUseremail"] = dtServerVars.Rows[0]["CablingUseremail"].ToString();
Session["CablingLogin"] = dtServerVars.Rows[0]["CablingLogin"].ToString();
Session["CablingPassword"] = dtServerVars.Rows[0]["CablingPassword"].ToString();
Session["CablingPersonnel"] = dtServerVars.Rows[0]["CablingPersonnel"].ToString();
Session["CablingSurname"] = dtServerVars.Rows[0]["CablingSurname"].ToString();
Session["CablingFirstName"] = dtServerVars.Rows[0]["CablingFirstName"].ToString();
Session["CablingSuperUser"] = dtServerVars.Rows[0]["CablingSuperUser"].ToString();
Session["CablingDateAdded"] = dtServerVars.Rows[0]["CablingDateAdded"].ToString();
Session["CablingContact"] = dtServerVars.Rows[0]["CablingContact"].ToString();
Session["CablingApprovalAuthority"] = dtServerVars.Rows[0]["CablingApprovalAuthority"].ToString();
Session["CablingAdminUser"] = dtServerVars.Rows[0]["CablingAdminUser"].ToString();
Session["SharedInfoID"] = dtServerVars.Rows[0]["SharedInfoID"].ToString();
Session["SharedInfousername"] = dtServerVars.Rows[0]["SharedInfousername"].ToString();
Session["SharedInfopassword"] = dtServerVars.Rows[0]["SharedInfopassword"].ToString();
Session["SharedInfoname"] = dtServerVars.Rows[0]["SharedInfoname"].ToString();
Session["SharedInfoemail"] = dtServerVars.Rows[0]["SharedInfoemail"].ToString();
Session["SharedInfoICLlocation"] = dtServerVars.Rows[0]["SharedInfoID"].ToString();
Session["SharedInfoPhone"] = dtServerVars.Rows[0]["SharedInfoPhone"].ToString();
Session["SharedInfoSecLevel"] = dtServerVars.Rows[0]["SharedInfoSecLevel"].ToString();
Session["IMSUserID"] = dtServerVars.Rows[0]["IMSUserID"].ToString();
Session["IMSUserName"] = dtServerVars.Rows[0]["IMSUserName"].ToString();
Session["IMSIsAnonymous"] = dtServerVars.Rows[0]["IMSIsAnonymous"].ToString();
Session["IMSLastActivityDate"] = dtServerVars.Rows[0]["IMSLastActivityDate"].ToString();
Session["loggedin"] = "unknown";


Code being called:

public static DataTable TryAutoLogin(string strREMOTE_USER)
{
    SqlConnection siConnection = new SqlConnection();
    siConnection.ConnectionString = Databases.getDbConnectionString("csSharedInfo");
    siConnection.Open();
    SqlCommand seCmd = new SqlCommand("GetSignOnDetails", siConnection);
    seCmd.CommandType = CommandType.StoredProcedure;
    seCmd.Parameters.Add(new SqlParameter("@DomainAccount", SqlDbType.NVarChar, 300));
    seCmd.Parameters["@DomainAccount"].Value = strREMOTE_USER;
    seCmd.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter sda = new SqlDataAdapter();
    seCmd.Connection = siConnection;
    sda.SelectCommand = seCmd;
    DataTable dtServerVars = new DataTable();
    sda.Fill(dtServerVars);
    siConnection.Close();
    if (dtServerVars != null)
    {
        if (dtServerVars.Rows.Count &gt; 0)
    {
        return dtServerVars;
    }
}
    return null;
}
First your TryAutoLogin:


A SqlConnection is IDisposable. Use it in combination with using.
You have some duplicate lines of code, like seCmd.CommandType = CommandType.StoredProcedure;.
dtServerVars can never be null.
You do not have to open the connection, SqlDataAdapter will do that for you.
Since you are using only ONE datarow, just return one datarow.


Resulting in:

 public static DataRow TryAutoLogin(string strREMOTE_USER)
 {
      using(SqlConnection siConnection = new SqlConnection(Databases.getDbConnectionString("csSharedInfo")))
       {
           SqlCommand seCmd = new SqlCommand("GetSignOnDetails", siConnection);
           seCmd.CommandType = CommandType.StoredProcedure;
           seCmd.Parameters.AddWithValue("@DomainAccount", strREMOTE_USER);
           SqlDataAdapter sda = new SqlDataAdapter(seCmd);
           DataTable dtServerVars = new DataTable();
           sda.Fill(dtServerVars);
           if (dtServerVars.Rows.Count &gt; 0)
                return dtServerVars.Rows[0];
           return null;
      }
 }


Having done this, the next piece of code will also be a lot easier:

DataRow drServerVars = clCommonFunctions.TryAutoLogin("europe\\MrTest");
Session["CablingUserID"]= drServerVars["CablingUserID"].ToString();
Session["CablingUseremail"] = drServerVars["CablingUseremail"].ToString();
Session["CablingLogin"] = drServerVars["CablingLogin"].ToString();
...