ASP.NET


My friend asked me that he was using web service to fetch data from a list/Document library consisting of 500 items, but the result fetched data only for the first page i.e 100 items.

The list I am querying has pagination, with each page showing 100 items. So, it my list has 500 items, there are 5 pages. But on using the code Lists.GetListItems method, only first 100 records(first page data) is only returned.

The code was as shown below:
Please note: here ibjview is the web reference.

string DocLibName = “Demo Doc Lib”;
XmlNode ndAllView = ibjview.GetViewCollection(DocLibName.Trim());

XmlDocument xmlDoc = new System.Xml.XmlDocument();
XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, “Query”, “”);
XmlNode ndViewFields =xmlDoc.CreateNode(XmlNodeType.Element, “ViewFields”, “”);
XmlNode ndQueryOptions = xmlDoc.CreateElement(“QueryOptions”);
ndQueryOptions.InnerXml = “-1”;
ndViewFields.InnerXml = “”;

System.Xml.XmlNode nListView= ibjlists.GetListItems(ndLists.Attributes[“ID”].Value, null, ndQuery, ndViewFields, null, ndQueryOptions, string.Empty);

The xml returned result only for the first page. He was not able to loop the code to get to the next page.
After googling, we found from msdn that,

The XML data returned by this method includes a ListItemCollectionPositionNext attribute inside the rs:Data element that contains the information to support paging.
This string contains data for the fields in the sort and for other items needed for paging.

We debugged, and found the xml was something like:
 
“<rs:Data ListItemCollectionPositionNext=”
Paged=TRUE&p_ID=100&View=
      %7bC68F4A6A%2d9AFD%2d406C%2dB624%2d2CF8D729901E%7d&PageFirstRow=
      101″ Count=1000 >
   <z:row ows_FirstName=”Nancy” ows_LastName=”Name” ….. />”

So, we needed to modify the p_ID to go to the next page.
This is how, we modified our code.

XmlNode xmlPosition = nListView.SelectSingleNode(“//@ListItemCollectionPositionNext“);

XmlTextReader reader = new XmlTextReader(nListView.OuterXml, XmlNodeType.Element, null);

 

reader = new XmlTextReader(nodes.OuterXml, XmlNodeType.Element, null);
XmlDocument xmldoc = new XmlDocument();
 xmldoc.LoadXml(nodes.InnerXml.Replace(“z:”, “”).Replace(“rs:”, “”));
 nodelist1 = xmldoc.SelectNodes(“/data/row”);                                                                                                                                                                                                                                                                                                                          

  for (int i = 0; i < nodelist1.Count; i++)
 { foreach (XmlAttribute attr in nodelist1[i].Attributes) {/*write your code*/}}

  if (xmlPosition != null)
  {
  ndQueryOptions.InnerXml = “<Paging ListItemCollectionPositionNext='” + xmlPosition.InnerXml + “‘ /><MeetingInstanceID>-1</MeetingInstanceID><ViewAttributes Scope=’RecursiveAll’  IncludeRootFolder=’True’ />”;            

}                     

The above code will run until xmlposition is null, which will only occur at the last page.

Advertisements

I had VS2008,when trying to create setup project , while trying to add prerequisite of  3.5 SP1, was getting lot of errors like “The install location for prerequisites has not been set to ‘component vendor’s web site’ and the file ‘DotNetFX35SP1\dotNetFX20\aspnet.msp’ in item ‘.NET Framework 3.5 SP1’ can not be located on disk. ”

The solution of this can be found at http://download.microsoft.com/download/A/2/8/A2807F78-C861-4B66-9B31-9205C3F22252/VS2008SP1Readme.htm

When I performed the steps shown in the above link,my problem was solved. Stating the steps mentioned in the link once more here,

If the .NET Framework 3.5 SP1 bootstrapper package is selected in the Prerequisite dialog box for a Setup project or in ClickOnce publishing, and also the “Download prerequisites from the same location as my application” option is selected, the following build error is shown: 

The install location for prerequisites has not been set to ‘component vendor’s web site’ and the file ‘dotNetFx35setup.exe’ in item ‘Microsoft.Net.Framework.3.5.SP1’ cannot be located on disk.

To resolve this issue:

Update the Package Data

  1. Open the [Program Files]\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\DotNetFx35SP1 folder or %ProgramFiles(x86)%\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\DotNetFx35SP1 on x64 operating systems
  2. Edit the Product.xml file in Notepad.
  3. Paste the following into the < PackageFiles > element:
    <PackageFile Name=”TOOLS\clwireg.exe”/>
    <PackageFile Name=”TOOLS\clwireg_x64.exe”/>
    <PackageFile Name=”TOOLS\clwireg_ia64.exe”/>
  4. Find the element for < PackageFile Name=”dotNetFX30\XPSEPSC-x86-en-US.exe” and change the PublicKey value to: 3082010A0282010100A2DB0A8DCFC2C1499BCDAA3A34AD23596BDB6CBE2122B794C8EAAEBFC6D526C232118BBCDA5D2CFB36561E152BAE8F0DDD14A36E284C7F163F41AC8D40B146880DD98194AD9706D05744765CEAF1FC0EE27F74A333CB74E5EFE361A17E03B745FFD53E12D5B0CA5E0DD07BF2B7130DFC606A2885758CB7ADBC85E817B490BEF516B6625DED11DF3AEE215B8BAF8073C345E3958977609BE7AD77C1378D33142F13DB62C9AE1AA94F9867ADD420393071E08D6746E2C61CF40D5074412FE805246A216B49B092C4B239C742A56D5C184AAB8FD78E833E780A47D8A4B28423C3E2F27B66B14A74BD26414B9C6114604E30C882F3D00B707CEE554D77D2085576810203010001
  5. Find the element for < PackageFile Name=”dotNetFX30\XPSEPSC-amd64-en-US.exe” and change the PublicKey value to the same as in step 4 above
  6. Save the product.xml file

 

Download and Extract the Core Installation Files

  1. Navigate to the following URL: http://go.microsoft.com/fwlink?LinkID=118080
  2. Download the dotNetFx35.exe file to your local disk.
  3. Open a Command Prompt window and change to the directory to which you downloaded dotNetFx35.exe.
  4. At the command prompt, type:
    dotNetFx35.exe /x:.
    This will extract the Framework files to a folder named “WCU” in the current directory.
  5. Copy the contents of the WCU\dotNetFramework folder and paste them in the %Program Files%\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\DotNetFx35SP1 folder (%ProgramFiles(x86)%\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\DotNetFx35SP1 on x64 operating systems). Note: Do not copy the WCU\dotNetFramework folder itself. There should be 5 folders under the WCU folder, and each of these should now appear in the DotNetFx35SP1 folder. The folder structure should resemble the following:
    o DotNetFx35SP1 (folder)

    • dotNetFX20 (folder
    • dotNetFX30 (folder)
    • dotNetFX35 (folder)
    • dotNetMSP (folder)
    • TOOLS folder)
    • en (or some other localized folder)
    • dotNetFx35setup.exe (file)

You may now delete the files and folders you downloaded and extracted in steps 2 and 4.

 I had to convert an excel file with different languages into a xml/dataset.

Below is the sample excel sheet I had

English French Spanish Portugese
Guinea Guinéee Guinea Guiné
Côte d’Ívoire Côte d’Ívoire Costa de Marfil Costa do Marfim
South Africa Afrique du Sud Sudáfrica África do Sul

For Excel 97-2003 format Microsoft Jet OLEDB Driver 4.0 is used. A sample connection string as follows.For Excel 2007 format the new Microsoft Ace OLEDB Driver 4.0 is used.

Code to convert is shown below:

//Connection String for Excel 97-2003 Format (.XLS)

String strExcelConn = “Provider=Microsoft.Jet.OLEDB.4.0;”

+ “Data Source=C:\\Gitolekha\\test.xls;

+ “Extended Properties=’Excel 8.0;HDR=Yes’“;

//Connect to excel sheet

OleDbConnection connExcel = new OleDbConnection(strExcelConn);

OleDbDataAdapter da = new OleDbDataAdapter();

OleDbCommand cmdExcel = new OleDbCommand();

cmdExcel.Connection = connExcel;

//Access the sheet

connExcel.Open();

DataTable dtExcelSchema;

dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

DataSet ds = new DataSet();

string SheetName = dtExcelSchema.Rows[0][“TABLE_NAME“].ToString();//It fetches the first sheet Sheet1$

cmdExcel.CommandText = “SELECT * From [” + SheetName + “]”;

da.SelectCommand = cmdExcel;

da.Fill(ds);

connExcel.Close();

 StringWriter sw = new StringWriter();

ds.WriteXml(sw, XmlWriteMode.IgnoreSchema);

 string textToConvert = sw.ToString();

//Encoding xml as it contains non ASCII characters of different languages 

Encoding latin = Encoding.GetEncoding(28591);

Encoding iso8= Encoding.GetEncoding(“iso-8859-8“);

Byte[] latinBytes= latin.GetBytes(textToConvert);

Byte[] iso8bytes = Encoding.Convert(latin, iso8, latinBytes);

string str = Encoding.UTF8.GetString(iso8bytes);

return str;

There is one point to remember if you are running this on a 64-bit platform:

I kept on getting eror “‘Microsoft.Jet.OLEDB.4.0′ provider is not registered on the local machine“, after much searching on the net, I dicovered :

  • there is no Jet OLEDB provider to use on a 64bit platform

However, we can configure the IIS to run 32-bit application on 64-bit Windows(IIS 6.0), for details, one can reffer the below url’s

http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/405f5bb5-87a3-43d2-8138-54b75db73aa1.mspx?mfr=true

http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/13f991a5-45eb-496c-8618-2179c3753bb0.mspx?mfr=true

Suppose I have a months enum like the below:

enum MonthName{Jan = 1,Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec }

My objective is to get the month name(MMM) from integer value,
e.g If I have 1 then Jan should be returned from the above enum.

string strTemp = “1”;
MonthName mnthName = (MonthName)Convert.ToInt32(strTemp);
string strname= Enum.GetName(typeof(MonthName), mnthName);

So by using GetName, it retrieves the name of the constant in the specified enumeration that has the specified value.

So, strname returns Jan as the input value was 1.

In ASp.NET grids, doing this is very easy, suppose you want to display the date as ‘Apr – 2009’, you just need to modify the DataFormatString property of the grid as 
DataFormatString=”{0:MMM – yyyy}”

Suppose we have a asp menu like the one shown below, and we want to hide all items except the first item, the code is as follows:

<asp:Menu
        id=”MnuApplication”
        Orientation=”Horizontal”
        StaticMenuItemStyle-CssClass=”tab”
        StaticSelectedStyle-CssClass=”selectedTab”
        CssClass=”tabs”
        OnMenuItemClick=”MnuApplication_MenuItemClick”
        Runat=”server”>
        <Items>
        <asp:MenuItem Text=”Application” Value=”0″ Selected=”true” />
        <asp:MenuItem Text=”Environment” Value=”3″ />
        <asp:MenuItem Text=”Focal Point” Value=”2″ />
        <asp:MenuItem Text=”Vendor” Value=”1″ />
        </Items>   
    </asp:Menu>

There is no option to hide menu items, we have to remove them through coding, so, if we want to hide all items except the first one, that is “Application”
for (int i = 0; i < MnuApplication.Items.Count; i++)
{
    if (MnuApplication.Items.Count > 1)
    {
       if (!MnuApplication.Items[i].Text.Equals(“Application”))
        {
            MnuApplication.Items.Remove(MnuApplication.Items[i]);
            i = 0;
         }
      }
}

To add items to a menu we do the following,

MenuItem mnuVendor = new MenuItem();
mnuVendor.Text = “Vendor”;
mnuVendor.Value = “1”;
MnuApplication.Items.Add(mnuVendor);

We use views within a   multiview to display the content of each menu. we can set the index of a particular view using ActiveViewIndex on click of a menu  :

protected void MnuApplication_MenuItemClick(object sender, MenuEventArgs e)
{
  int index = Int32.Parse(e.Item.Value);
  MultiView1.ActiveViewIndex = index;
}

 

Sometimes we might just need to add values to a dropdownlist which does not change frequently, hence listitem can solve the problem. Below is an example of populating dropdown list using listitem through coding.

Suppose name of th dropdownlist is drpChooseStatus

List<System.Web.UI.WebControls.ListItem> status = new List<System.Web.UI.WebControls.ListItem>(); status.Add(new ListItem(“In Progress”, “1”)); status.Add(new ListItem(“To be added”, “2”)); status.Add(new ListItem(“Completed”, “3”));
drpChooseStatus.DataSource = status;
drpChooseStatus.DataBind();

Sometimes if a table has many columns, inserting data can be tiresome as you have to write many input parameters from your  application, a smart way to handle this would be to pass the values as xml and use openxml to save data, below is an example:

Define a datatable with all the columns you need to save and add it to a Dataset, something like the one showed below

 DataSet dsAddServerDetails = new DataSet();
DataTable dtServer = new DataTable(“Server”);
dtServer.Columns.Add(“Server_Name”, Type.GetType(“System.String”));
dtServer.Columns.Add(“OS”, Type.GetType(“System.String”));
dtServer.Columns.Add(“Server_Type”, Type.GetType(“System.String”));
dtServer.Columns.Add(“CreatedBy”, Type.GetType(“System.String”));
dtServer.Rows.Add(//Insert logic to add row…);
dsAddServerDetails.Tables.Add(dtServer);

//While Saving
ArrayList arrList = BusinessLayer.GetData(dsAddServerDetails);
if (arrList.Count > 0)
{
 string strServerId = arrList[0].ToString();
}

//BusinessLogic and DataAccess Layer logic

public static class BusinessLayer
    {
        public static ArrayList GetData(DataSet ds)
        {
            int intRetValue;
            ArrayList arrRetVal = new ArrayList();
            try
            {
                arrRetVal = DataAccessLayer.GetData(ds);
            }
            catch (Exception ex)
            {
                throw ex;            }
            return arrRetVal;
        }
}

public static class DataAccessLayer
{
public static ArrayList GetData(DataSet ds)
        {
            int intRetValue;
            ArrayList arrRetVal = new ArrayList();
            try
            {
                Database db = DatabaseFactory.CreateDatabase();
                DbCommand command = db.GetStoredProcCommand(“SaveServer”);
                db.AddInParameter(command, “@ServerDetails”, DbType.Xml, ds.GetXml());
                db.AddOutParameter(command, “@ServerId”, DbType.Int32, 4);

                intRetValue = db.ExecuteNonQuery(command);
                if (intRetValue == -1)
                {
                    int intServerId = Int32.Parse(db.GetParameterValue(command, “@ServerId”).ToString());
                    arrRetVal.Add(intServerId);
                }
            }
            catch (Exception ex)
            {
                throw ex;            }
            return arrRetVal;
        }
}

//Stored Procedure

ALTER PROCEDURE [dbo].[SaveServer]
 — Add the parameters for the stored procedure here
 @ServerDetails  xml,
 @ServerId int OUTPUT
AS
BEGIN
EXEC sp_xml_preparedocument @hDoc OUTPUT,@ServerDetails
BEGIN TRANSACTION
 INSERT INTO tblServer(
 Server_Name,
 OS,
 Server_Type,
 CreatedBy)
 SELECT
 Server_Name,
 OS,
 Server_Type,
 CreatedBy
 FROM OPENXML(@hDoc, ‘NewDataSet/Server’,2)
 WITH (
 Server_Name varchar(50),
 OS varchar(50),
 Server_Type varchar(50),
 CreatedBy datetime)
 SET @ServerId = @@IDENTITY
 SELECT @ServerId
 IF @@ERROR <>0
  BEGIN
   ROLLBACK TRANSACTION
   RAISERROR(‘Error Inserting Server items!’,11,1) with NOWAIT, SETERROR;
   RETURN
  END
 ELSE
  BEGIN

Next Page »