Oracle: extract data from Oracle in Window Server 2008 (64 bit)

I need to create an application which is hosted in IIS under 64 bit Window Servers 2008. There are several steps needed to prepare and they are shown as below:

1. download 64 bit Oracle Client and install administration type
     win64_11gR2_client.zip

2. download 32 bit Oracle Client and install administration type
    win32_11gR2_client.zip

3. use Net Configuration Assistant to name a database into tnsname.ora

4. log off the window so that the system can update the environment.

C#: Hosting an WCF application in IIS 7

I need to host an WCF application in IIS 7 under Window Servers 2008 R2. There are several steps needed to follow.

Create an WCF project in Visual Studio 2010

After creating a project WcfService, there are IWCFService.cs, WCFService.svc, WCFService.svc.cs and web.config in the solution.

For IWCFService.cs, you need to declare methods and customer data type.

namespace WcfService
{
     [ServiceContract]
     public interface IWCFService
     {
           [OperationContract]
           People SayHello();
     }

     [DataContract]
     public class People
     {
           [DataMember]
           public String id {get; set;}
           public String name {get; set;}
      }
}

For WCFService.svc.cs, you need to create the method declared in Operation Contract.

namespace WcfService
{
     public class WCFService: IWCFService
     {
           public People SayHello()
           {
                 return new People() {
                          id = "1",
                          name = "name"
                 };
            }
      }
}

For web.config, in order to host in IIS 7, it needs some configurations.

<?xml version="1.0"?>
<configuration>
 <system.serviceModel>
  <services>
   <service behaviorConfiguration="CustomerServiceBehavior" name="WcfService.WCFService">
    <endpoint address="" binding="wsHttpBinding" contract="WcfService.IWCFService"/>
    <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>
   </service>
  </services>
  <behaviors>
   <serviceBehaviors>
    <behavior name="CustomerServiceBehavior">
     <serviceMetadata httpGetEnabled="true"/>
     <serviceDebug includeExceptionDetailInFaults="true"/>
    </behavior>
   </serviceBehaviors>
  </behaviors>
 </system.serviceModel>
 <system.webServer>
  <modules runAllManagedModulesForAllRequests="true"/>
  <directoryBrowse enabled="true"/>
 </system.webServer>
 <system.web>
  <compilation debug="true"/></system.web></configuration>

For WCFService.svc, by default, you need not modify it.

<%@ ServiceHost Language="C#" Debug="true" Service="WcfService.WCFService" CodeBehind="WCFSevice.svc.cs" %>

Debugging Tool
It is quite difficult to do debugging in WCF application. Visual Studio 2010 provides a WCF Testclient interface for developer to invoke menthods.

Configure the file permission for IIS

In order to host the project in IIS, it require to access information inside. You need to select the project folder and then right click "Properties". Under tab "Security", add IIS_IUSERS and assign read permission to it.

Hosting the project in IIS 7
Under IIS, right click folder "Sites" and select "Add Web Site..."

If your application runs under .Net 4.0 version, you need to go to application. Click the application and then click "Advanced Settings..."  to update ".Net Framework Version" to v4.0

Last but not least, you need to restart the web site.

C#: Read and Query Data from Excel File

The following code will show a simple example which reads and queries data in the file.

       protected static String connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                                  "Data Source=" + "C:\\sharedsupport.xls;" +
                                  "Extended Properties=Excel 8.0;";
        //

        //"Select * from [" + DropDownList1.SelectedValue + "$A5:I" + rowNum + "]"
        //

        public Product findValue(Product item, DataTable table, ArrayList fieldList)
        {
            var query = from r in table.AsEnumerable()
                        where r.Field<String>("Product Part Number") == item.name
                        select new{family = r.Field<Object>(fieldList[0].ToString())};

            foreach (var q in query){
                retun new Product() {family = q.family.ToString()};
            }
            return item;
        }
        //

        public DataTable getQueryTable(String cmd)
        {
            OleDbConnection conn = new OleDbConnection(connectionString);
            conn.Open();

            OleDbCommand dbCommand = new OleDbCommand(cmd, conn);
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            adapter.SelectCommand = dbCommand;
            DataSet dataSet = new DataSet();
            adapter.Fill(dataSet);

            conn.Close();
            return dataSet.Tables[0];
        }

C#: The Microsoft.ACE.OLEDB.14.0 provider is not registered on the local machine

I need to create an application which reads data from a excel file in 64 bit environment and an driver is needed.

Microsoft Access database Engine 2010 Redistributable

After I install the 64bit version, I run the following code to create a connection to an excel file

String connectionString = "Provider=Microsoft.ACE.OLEDB.14.0;" +
                                    "Data Source=" + "C:\\Downloads\\haha.xls;" +
                                    "Extended Properties=Excel 8.0;HDR=YES;";

OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();

When I runs the above code,  conn.Open() throws an InvalidOperationException was unhandled by user code. The exception shows "The Microsoft.ACE.OLEDB.14.0" provider is not registered on the local machine.

In order to solved this problem, I create a new VM and install the AccessdatabaseEngine.exe in a 64 bit environment. After that, the above code runs fine.

SQL Server: TCP Connection Opening

In order to remote access a SQL Server, I need to open a TCP port for listening.There are several steps are needed to prepared.

(1) Go to "Sql Server Configuration Manager" and expand "SQL Server Network Configuration"

(2) Click the designated instance "SQLEXPRESS"

(3) Right Click "TCP/IP" and then select "Enable"

(4) Right Click "TCP/IP" and then select "Properties"

(5) Select tab "IP Address"

(6) Under "IPALL", enter port "1433" in "TCP Port"

(7) Click button "OK" 

(8) Under "SQL Server Services", right click the designated instance and then select "Restart"

SQL Server: Add a Read-Only Account using SQL authentication

I need to create a read-only account using SQL authentication to login SQL Server. There are several steps are needed to prepared and they are shown below:

1. Ensure the authentication of SQL Server

    (1) Right click the SQL Server and select "Properties"

    (2) Under page "Security", click "SQL Server and Windows Authentication mode" and then click button "OK"

2. Create a read-only account

    (1) Right click folder "Security" and select create a new login

    (2) Under page "General", type login name, password for SQL authentication and default database

    (3) Under page "User Mapping", click the designated database and set db_datareader in its role membership

    (4) Click button "OK"

ASP.NET: DropDownList do not fire onselectdindexchanged

I add a DropDownList in a web page and the setting is shown below:

<asp:DropDownList ID="DropDownList1" runat="server" Height="35px"
            onselectedindexchanged="DropDownList1_SelectedIndexChanged" Width="184px" 
            style="margin-right: 38px">

When I change the value in the DropDownList, it do not trigger the onselectedindexchange method. After some searching, i need to add attribute AutoPostBack

AutoPostBack="true"

Event Handler: Enable Error Message

In SharePoint, event handler is triggered before or after a specific event, A cancel update (properties.Cancel) is an unhandled exception for SharePoint and error message is shown as below:

The following code will be triggered before adding a field and an error message will be shown as below.

public override void FieldAdding(SPListEventProperties properties)

        {

            properties.ErrorMessage = "error”;

            properties.Cancel = true;

        }

By default, the error message will be shown as below when the attributue of CallStack is true in web.config (a xml document defines configuration of the web application). The error message will show the exception detail when admin enables the CallStack attribute

If I update the attribute of CallStack to “false”, the information for debugging will be totally hidden.

References:

http://msdn.microsoft.com/en-us/library/ff713710(office.12).aspx

http://httpcode.com/blogs/PermaLink,guid,c9b0f3e5-5d9a-433c-8197-6d195471a26f.aspx

Excel Service REST: authentication in C#

The service reference of excel service in SharePoint Server 2010 is

http://sp2010/_vti_bin/ExcelService.asmx

I add above service reference in a console application. The code in the application is shown below:

ExcelServiceSoapClient client = new ExcelServiceSoapClient();
client.ClientCredentials.Windows.AllowedImpersonationLevel =           System.Security.Principal.TokenImpersonationLevel.Impersonation;
String url = "http://sp2010/excelFiles/Book1.xlsx";
Status[] status;
String session = client.OpenWorkbook(url, "en-US", "en-US", out status);

When I runs the above code, method OpenWorkbook() throws an MessageSecurityException as below:

The HTTP request is unauthorized with client authentication scheme 'Anonymous'. The authentication header received from the server was 'Negotiate, NTLM'.

The message indicates the problem is related to the authentication method. Go to app.config and change the security settings.

<security mode="TransportCredentialOnly">
      <transport clientCredentialType="Ntlm" proxyCredentialType="Ntlm"  realm="" />
      <message clientCredentialType="UserName" algorithmSuite="Default" />
</security>

 If you do not want to use current window authentication, you can specify a username account.

client.ClientCredentials.Windows.ClientCredential = new System.Net.NetworkCredential(username, password, domain);
                client.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Delegation;    

C#: GridView

I create a GridView in an asp.net web apge. When it runs method DataBind(), an HttpException is shown as below:

The data source for GridView with id 'GridView1' did not have any properties or attributes from which to generate columns. Ensure that your data source has content.

When assigning a list of custom object to the data source of GridView,  .Net can read the property names of collection objects automatically. 

The above problem can be solved by the following code:

 public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Button1_Click(object sender, EventArgs e)
        {
            ArrayList list = new ArrayList();
            Car c1 = new Car() { Add = "1", Name = "2"};
            list.Add(c1);
            //data grid
            GridView1.AutoGenerateColumns = true;
            GridView1.DataSource = list;
            GridView1.DataBind();
        }
    }
    class Car
    {
        public String Name { get; set;}
        public String Add {get;  set;}
    }

For more detail about GridView in C#: http://www.dotnetperls.com/datagridview-tips

C#: Get Data from Clipboard

I write a application read data from clipboard using System.Windows.Froms. Then I discover the method Clipboard.getDataObject() always returns null.

Based on the description from msdn,

The Clipboard class can only be used in threads set to single thread apartment (STA) mode. To use this class, ensure that your Main method is marked with the STAThreadAttribute attribute.

The sample code is shown below:

class Program
    {
        static void Main(string[] args)
        {
            Thread thread = new Thread(new ThreadStart(CopyToClipboard));
            thread.TrySetApartmentState(ApartmentState.STA);
            thread.Start();
        }

        static void CopyToClipboard()
        {
            IDataObject clip = Clipboard.GetDataObject();

            if (clip.GetDataPresent(DataFormats.Text))
            {
                Console.WriteLine("" + clip.GetData(DataFormats.Text));
            }
        }
    }