Sharepoint 2010 connect to sql server and oracle database

1. Menu add connection strings in c:\inetpub\wwwroot\wss\VirtualDirectories\\web.config
Ex.
/* for sql server and oracle*/
＜connectionStrings＞
＜add name="AdventureWorksConnectionString" connectionString="Data Source=192.168.0.1;Initial Catalog=AdventureWorks;User Id=sa;Password=smpsqladm1" /＞
＜add name="OracleConnectionString" connectionString="Data Source=Ora_TEST;User ID=system;Password=system_pwd" providerName="System.Data.OracleClient" /＞
＜/connectionStrings>

2. In Sharepoint virtual web part ascx file .

＜asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px"
DataSourceID="SqlDataSource1" EnableModelValidation="True"＞
＜/asp:DetailsView＞

＜asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="＜%$ ConnectionStrings:AdventureWorksConnectionString%＞"
SelectCommand="select count(*) as intUnRead from yourtable"＞
＜/asp:SqlDataSource＞

＜asp:DetailsView ID="DetailsView2" runat="server" Height="50px" Width="125px"
DataSourceID="SqlDataSource2" EnableModelValidation="True"＞
＜/asp:DetailsView＞

＜asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="＜%$ ConnectionStrings:OracleConnectionString %＞"
ProviderName="System.Data.OracleClient"
SelectCommand="select count(*) as UnRead from testtable"＞
＜/asp:SqlDataSource＞

Using Visual Studio 2010 add connect string to Sharepoint 2010 to query database data

1. From VS2010 , add new sharepoint 2010 empty project as , and then add one new item "Virtual Web Part" name as "SQLWebPartTest"
2. In "SQLWebPartTest" ascx file , manual add codes for DetailsView and SqlDataSource .
-----------
＜asp:DetailsView ID="DetailsView1" runat="server"
DataSourceID="SqlDataSource1" EnableModelValidation="True"＞
＜/asp:DetailsView＞
＜asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="＜%$ ConnectionStrings:ChangebyYourConnectionString %＞"
SelectCommand="select * from ChangebyYourTable"＞
＜/asp:SqlDataSource＞

＜asp:DetailsView ID="DetailsView2" runat="server"
DataSourceID="SqlDataSource2" EnableModelValidation="True"＞
＜/asp:DetailsView＞
＜asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="＜%$ ConnectionStrings:ChangebyYourConnectionString2 %＞"
SelectCommand="select * from ChangebyYourTable"＞
＜/asp:SqlDataSource＞
--------------
3. In the project "Solution Explorer" , right click in "Feature1" to add one "Event Receiver" and name it as "Feature1.EventReceiver.cs"
--- In "Feature1.EventReceiver.cs"
#1 Add using
using System.Reflection;
using Microsoft.SharePoint.Administration;

#2 Add following code in "public class Feature1EventReceiver : SPFeatureReceiver"
private ModificationEntry[] entries =
｛
//Ensure there's a connectionStrings section.
new ModificationEntry(
"connectionStrings"
,"configuration"
,"＜connectionStrings/＞"
,SPWebConfigModification.SPWebConfigModificationType.EnsureChildNode
,true)
//Create the connectionstring.
,new ModificationEntry(
"add[@name='ChangebyYourConnectionString'][@connectionString='Data Source=192.168.0.1;Initial Catalog=ChangebyYourDB;User Id=ChangebyYourID;Password=ChangebyYourPWD']"
,"configuration/connectionStrings"
,"＜add name='ChangebyYourConnectionString' connectionString='Data Source=192.168.0.1;Initial Catalog=ChangebyYourDB;User Id=ChangebyYourID;Password=ChangebyYourPWD'/＞"
,SPWebConfigModification.SPWebConfigModificationType.EnsureChildNode
,false)
,new ModificationEntry(
"add[@name='ChangebyYourConnectionString2'][@connectionString='Data Source=192.168.0.100;Initial Catalog=AdventureWorks;User Id=ChangebyYourID;Password=ChangebyYourPWD']"
,"configuration/connectionStrings"
,"＜add name='ChangebyYourConnectionString2' connectionString='Data Source=192.168.2.100;Initial Catalog=AdventureWorks;User Id=ChangebyYourID;Password=ChangebyYourPWD'/＞"
,SPWebConfigModification.SPWebConfigModificationType.EnsureChildNode
,false)
｝;

public override void FeatureActivated(SPFeatureReceiverProperties properties)
｛
//Get a reference to the web application and then remove entries for the blorum.
SPSite site = properties.Feature.Parent as SPSite;
SPWebApplication webApplication = site.WebApplication;

site.RootWeb.Title = "Set from activating code at " + DateTime.Now.ToString();
site.RootWeb.Update();

foreach (ModificationEntry entry in entries)
｛
webApplication.WebConfigModifications.Add(CreateModification(entry));
｝

webApplication.WebService.WebConfigModifications.Clear();
webApplication.WebService.ApplyWebConfigModifications();


｝

public override void FeatureDeactivating(SPFeatureReceiverProperties properties)
｛
SPSite site = properties.Feature.Parent as SPSite;
SPWebApplication webApplication = site.WebApplication;

site.RootWeb.Title = "Set from deactivating code at " + DateTime.Now.ToString();
site.RootWeb.Update();

foreach (ModificationEntry entry in entries)
｛
if (!entry.CreateOnly)
webApplication.WebConfigModifications.Remove(CreateModification(entry));
｝

webApplication.WebService.ApplyWebConfigModifications();
｝


private SPWebConfigModification CreateModification(ModificationEntry entry)
｛
SPWebConfigModification modification = new SPWebConfigModification(entry.Name, entry.XPath);
modification.Owner = Assembly.GetExecutingAssembly().FullName;
modification.Sequence = 0;
modification.Type = entry.ModificationType;
modification.Value = entry.Value;

return modification;
｝

private struct ModificationEntry
｛
public string Name;
public string XPath;
public string Value;
public SPWebConfigModification.SPWebConfigModificationType ModificationType;
public bool CreateOnly;

public ModificationEntry(string name, string xPath, string value,
SPWebConfigModification.SPWebConfigModificationType modificationType, bool createOnly)
｛
Name = name;
XPath = xPath;
Value = value;
ModificationType = modificationType;
CreateOnly = createOnly;
｝
｝
4. Press F5 in VS2010 to open Sharepoint site , and insert add webpart from "custom" location.

Open large text file fast and consume little resource tool

Open large text file fast and consume little resource tool.
I try to open a .txt file with 300MB size by notepad/notepad++ , those txt editor use lots of memory and slow my windows system. Finally the pop-up message show me "file too large,cannot open.".
After try to use "LTFViewer" to open the large file, the performance is very good and only a little resource to by occupy.

http://www.swiftgear.com/fltfviewer/fLTFViewr.zip

Get windows eventlog and specified time record

'This vbscript is for retrieve windows eventlog "Application" , SourceName="YourService.exe" , EventCode=1 log record
' and then execute c:\maint\batch\execproc.bat to do something

Set dtmStartDate = CreateObject("WbemScripting.SWbemDateTime")
'1440 is one day , "Now - 3/1440" is represent for 3 minutes ago
DateToCheck = Now - 3/1440
dtmStartDate.SetVarDate DateToCheck, True
intNumberID=1
Set objWMI = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")

Set colLoggedEvents = objWMI.ExecQuery _
("Select * from Win32_NTLogEvent Where Logfile = 'Application' and TimeWritten >='" & dtmStartDate & "'")

For Each objEvent in colLoggedEvents
If objEvent.EventCode = intNumberID Then
if objEvent.SourceName="YourService.exe" then
Set shell = CreateObject("WScript.Shell")
Set exec = shell.run("c:\maint\batch\execproc.bat")
end if
End if

Next

WScript.Quit

Hide user from exchange address list by msExchHideFromAddressLists

' Setup ADO objects.
Set adoCommand = CreateObject("ADODB.Command")
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Provider = "ADsDSOObject"
adoConnection.Open "Active Directory Provider"
Set adoCommand.ActiveConnection = adoConnection
' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")

strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = ""
' Filter on user objects.
strFilter = "(&(objectCategory=person)(objectClass=user))"

' Comma delimited list of attribute values to retrieve.
strAttributes = "sAMAccountName,cn,distinguishedName"

' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

' Run the query.
Set adoRecordset = adoCommand.Execute

' Enumerate the resulting recordset.
Do Until adoRecordset.EOF
' Retrieve values and display.
strName = adoRecordset.Fields("sAMAccountName").Value
strDName = adoRecordset.Fields("distinguishedName").Value
strCN = adoRecordset.Fields("cn").value
'Change "test_admin" to your data
if strName="test_admin" then
'Wscript.Echo "NT Name: " & strName & ", distinguishedName: " & strDName
Set oUser = GetObject("LDAP://"&strDName)
oUser.put "msExchHideFromAddressLists", True
oUser.SetInfo
ExchangeAddressList="Disabled"
end if
' Move to the next record in the recordset.
adoRecordset.MoveNext
Loop

' Clean up.
adoRecordset.Close
adoConnection.Close

Reset oracle sequence procedure

Oracle sequence cannot reset by command , need workaround to achieve original start value.

CREATE OR REPLACE PROCEDURE reset_sequence (
seq_name IN VARCHAR2) AS
cval INTEGER;
inc_by VARCHAR2(30);
BEGIN
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';
EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval;
IF cval < 0 THEN
inc_by := ' INCREMENT BY ';
cval:= ABS(cval);
ELSE
inc_by := ' INCREMENT BY -';
END IF;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by || cval;
EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval;
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' INCREMENT BY 1';
END reset_sequence;
/

jxl 中文 亂碼 解決

使用 jxl 操作 excel , 當 java code 中的字串值是中文時,遭遇到亂碼狀況,從資料庫取出並寫到Excel 中的資料值是正常中文顯示, 從網路上的解決方式測試也無法解決.
----
WorkbookSettings wbsettings = new WorkbookSettings();
wbsettings.setEncoding("ISO-8859-1");

jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os,wbsettings); //建立Excel

----

Solution:
Compile java code use "javac -encoding BIG5 xxx.java" , 可解決.

Movie list

1. 為愛朗讀 - good
2. 暮光之城 - good
3. 吹動大麥的風(風吹稻浪) - not yet
4. 史前一萬年10000 B.C - not yet
5. 功夫之王 - not yet
6. 瓦力 - not Yet
7. 神鬼第六感 - not yet
8. 魔幻時刻 - not yet
9. 黑暗騎士 - not yet
10. 險路勿近 - not yet
11. 地獄魔咒 - good
12. 七級公務員(韓片) - not yet