CODEWITHSUNDEEP
asp.netsharepointsharepoint-2007
user1048083
user1048083

Reputation: 93

How to pass server variables as SqlDataSource parameter for sharepoint dataformwebpart?

I'm trying to execute a stored procedure via Sharepoint's DataFormWebPart by passing it the currently logged in sharepoint username (a server variable essentially), however, I'm getting stuck on how to pass server variables.

Here is what I have as code

<asp:SqlDataSource runat="server" ProviderName="System.Data.SqlClient" ID="SqlDataSource7" SelectCommandType="StoredProcedure" ConnectionString="xxx;" SelectCommand="xxx" __designer:customcommand="true">
    <SelectParameters>
         <!-- Not sure what to do here -->
    </SelectParameters>
</asp:SqlDataSource>

I know I want to do something like

<ParameterBinding Name="UserID" Location="CAMLVariable" DefaultValue="CurrentUserName"/>

But it seems I can only use asp parameter tags...

Upvotes: 2

Views: 1807

Answers (2)

Borian
Borian

Reputation: 26


better late than never :)
I'm working in 2010, but I guess it should work in 2007 too:

  1. bind appropriate server variable to your DVWP:

    <ParameterBindings>
  ...
  <ParameterBinding Name="LOGON_USER" Location="ServerVariable(LOGON_USER)"/>
</ParameterBindings>

  2. now you can push this parameter into data source:

    <SelectParameters>
  ...
  <WebPartPages:DataFormParameter PropertyName="ParameterValues" ParameterKey="LOGON_USER" Name="Context"/>
</SelectParameters>

Now you have current IIS user (i.e. DOMAIN\USER) in the SqlDataSource parameter @Context :)

Upvotes: 1

AnandMohanAwasthi
AnandMohanAwasthi

Reputation: 839

I think you should never use the sqldatasource in your page.

Try this way

  1. Firstly we will create a Class Lib. In this Class Lib, we will create different class file which will improve the Reusability.

1.1 Add the Class Lib (To go File -> New Project -> Add ClassLibrary)

  1. Now we will create the db connection class

2.1 Add the class file in the class lib project as'Classbll'

 public class DBConnection
{
    #region"private variables"

    private string _sConnectionString = ConfigurationManager.AppSettings["DBConnectString"].ToString();
    private static string _sErrorMessage = string.Empty;
    #endregion

    #region "Public Properties"

    public string DataConnectionString
    {
        get
        {
            return _sConnectionString;
        }
    }
    public string ErrorMessage
    {
        get
        {
            return _sErrorMessage;
        }
        set
        {
            _sErrorMessage = value;
        }
    }

    #endregion

}

2.2 Define the connection string in the web.config in the main project

<appSettings>
    <add key="DBConnectString" value="SERVER=XYZSERVER;UID=XXYY;Password=ZZZXXX;Database=DBXXYYZZ;"/>

</appSettings>

3 Now create another class file in the same class lib project..

Let us say File named as'FillCommon.cs'

    using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using System.Web.UI.WebControls;

namespace Clssbll
{
    public class FillCommon : DBConnection
    {


        #region "Private variables"
        private string _smRoleMasterRoleID;

        #endregion
        #region "Public variables"

        public string mRoleMasterRoleID
        {
            get { return _smRoleMasterRoleID; }
            set { _smRoleMasterRoleID = value; }
        }

        #endregion

        #region "Public Methods"

        public DataSet GetmRoleMaster_infoSingle()
        {

            try
            {
                DataSet oDS = new DataSet();
                SqlParameter[] oParam = new SqlParameter[1];

                oParam[0] = new SqlParameter("@m_RoleID", _smRoleMasterRoleID);


                oDS = SqlHelper.ExecuteDataset( DataConnectionString, CommandType.StoredProcedure, "SelectmRoleMaster", oParam);
                return oDS;
            }
            catch (Exception e)
            {
                ErrorMessage = e.Message;
                return null;
            }
        }

  1. Now add Classbll(Class Lib) in the main project reference or bin.

  2. In the main project class file You can use this anywhere you want'Using Classbll' namespace

If you find it useful, please mark it as your answer else let me know...

Upvotes: 0

Related Questions