如何在SQLDataSource updateCommand中设置会话变量 [英] how to set Session Variable in SQLDataSource updatecommand

问题描述

Is it possible to insert a session variable in an SQLDataSource like so:

="SELECT * FROM Companies WHERE CompanyID = <%session(CurrentCompID)%>"

推荐答案

这不是DataSource，它是SqlCommand.
而且由于存储在Session中的所有值的对象类型，这种解决方案也不好.

试试这个:

This is not DataSource, its a SqlCommand.
And because of Object-type of all values that stores in Session this kind of solution is not good.

Try this:

string query = "SELECT * FROM Companies WHERE CompanyID = @companyID";
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(query);
cmd.Parameters.Add(new SqlParameter("@companyID", Session["CurrentCompID"]));
using(SqlConnection conn = new SqlConnection("Here Is Your Connection String"))
{
    cmd.Connection = conn;
    SqlDataAdapter da = new SqlDataAdapter(cmd);

    try
    {
        da.Fill(dt);
    }
    catch(Exception ex)
    {
    }
}

而且我建议检查Session中是否有值，当然还有类型匹配.
因此，当您知道会话中值的确切类型时，可以使用SqlParameter类的其他(更可取的)构造函数.

And i recommend to check value in Session for existing, and of course on Type matching.
So when you will know exact type of value in session, then you can use other (more preferable) constructor of SqlParameter class.

if(Session["CurrentCompID"] != null)
{
    int compId;
    if(int.TryParse(Session["CurrentCompID"].ToString(), out compId)
    {
        // And here paste above code, but with changing of SqlParameter initialization
        SqlParameter par = new SqlParameter("@companyID", SqlDbType.Int);
        par.Value = compId;
        cmd.Parameters.Add(par);
    }
}

对于Update查询，它看起来是一样的:

For Update query it will looks the same:

string query = "UPDATE [Data] SET [Companyname ]= @Companyname, [UpdatedBy] = @updatedBy, [UpdatedDate]= @updatedDate WHERE [CompanyID] = @CompanyID";
SqlCommand cmd = new SqlCommand(query);
cmd.Parameters.Add(new SqlParameter("@companyID", Session["CurrentCompID"]));
// And here add additional parameters
cmd.Parameters.Add(new SqlParameter("@updatedBy", Session["UpdatedBy"]));
cmd.Parameters.Add(new SqlParameter("@updatedDate", Session["UpdatedDate"]));
using(SqlConnection conn = new SqlConnection("Here Is Your Connection String"))
{
    cmd.Connection = conn;

    try
    {
        conn.Open();
        // Because no need in data returning
        cmd.ExecuteNonQuery();
    }
    catch(Exception ex)
    {
    }
}

这篇关于如何在SQLDataSource updateCommand中设置会话变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！