String Format for DateTime [C#]

This example shows how to format DateTime using String.Format method. All formatting can be done also using DateTime.ToString method.

Custom DateTime Formatting

There are following custom format specifiers y (year), M (month), d (day), h (hour 12), H (hour 24), m (minute), s (second), f (second fraction), F (second fraction, trailing zeroes are trimmed), t (P.M or A.M) and z (time zone).

Following examples demonstrate how are the format specifiers rewritten to the output.

[C#]

// create date time 2008-03-09 16:05:07.123
DateTime dt = new DateTime(2008, 3, 9, 16, 5, 7, 123);

String.Format("{0:y yy yyy yyyy}", dt);  // "8 08 008 2008"   year
String.Format("{0:M MM MMM MMMM}", dt);  // "3 03 Mar March"  month
String.Format("{0:d dd ddd dddd}", dt);  // "9 09 Sun Sunday" day
String.Format("{0:h hh H HH}",     dt);  // "4 04 16 16"      hour 12/24
String.Format("{0:m mm}",          dt);  // "5 05"            minute
String.Format("{0:s ss}",          dt);  // "7 07"            second
String.Format("{0:f ff fff ffff}", dt);  // "1 12 123 1230"   sec.fraction
String.Format("{0:F FF FFF FFFF}", dt);  // "1 12 123 123"    without zeroes
String.Format("{0:t tt}",          dt);  // "P PM"            A.M. or P.M.
String.Format("{0:z zz zzz}",      dt);  // "-6 -06 -06:00"   time zone

You can use also date separator / (slash) and time sepatator : (colon). These characters will be rewritten to characters defined in the current DateTimeForma­tInfo.DateSepa­rator and DateTimeForma­tInfo.TimeSepa­rator.

[C#]

// date separator in german culture is "." (so "/" changes to ".")
String.Format("{0:d/M/yyyy HH:mm:ss}", dt); // "9/3/2008 16:05:07" - english (en-US)
String.Format("{0:d/M/yyyy HH:mm:ss}", dt); // "9.3.2008 16:05:07" - german (de-DE)

Here are some examples of custom date and time formatting:

[C#]

// month/day numbers without/with leading zeroes
String.Format("{0:M/d/yyyy}", dt);            // "3/9/2008"
String.Format("{0:MM/dd/yyyy}", dt);          // "03/09/2008"

// day/month names
String.Format("{0:ddd, MMM d, yyyy}", dt);    // "Sun, Mar 9, 2008"
String.Format("{0:dddd, MMMM d, yyyy}", dt);  // "Sunday, March 9, 2008"

// two/four digit year
String.Format("{0:MM/dd/yy}", dt);            // "03/09/08"
String.Format("{0:MM/dd/yyyy}", dt);          // "03/09/2008"

Standard DateTime Formatting

In DateTimeForma­tInfo there are defined standard patterns for the current culture. For example property ShortTimePattern is string that contains value h:mm tt for en-US culture and value HH:mm for de-DE culture.

Following table shows patterns defined in DateTimeForma­tInfo and their values for en-US culture. First column contains format specifiers for the String.Format method.

Specifier	DateTimeFormatInfo property	Pattern value (for en-US culture)
t	ShortTimePattern	h:mm tt
d	ShortDatePattern	M/d/yyyy
T	LongTimePattern	h:mm:ss tt
D	LongDatePattern	dddd, MMMM dd, yyyy
f	(combination of D and t)	dddd, MMMM dd, yyyy h:mm tt
F	FullDateTimePattern	dddd, MMMM dd, yyyy h:mm:ss tt
g	(combination of d and t)	M/d/yyyy h:mm tt
G	(combination of d and T)	M/d/yyyy h:mm:ss tt
m, M	MonthDayPattern	MMMM dd
y, Y	YearMonthPattern	MMMM, yyyy
r, R	RFC1123Pattern	ddd, dd MMM yyyy HH':'mm':'ss 'GMT' (*)
s	SortableDateTi­mePattern	yyyy'-'MM'-'dd'T'HH':'mm':'ss (*)
u	UniversalSorta­bleDateTimePat­tern	yyyy'-'MM'-'dd HH':'mm':'ss'Z' (*)
		(*) = culture independent

Following examples show usage of standard format specifiers in String.Format method and the resulting output.

[C#]

String.Format("{0:t}", dt);  // "4:05 PM"                         ShortTime
String.Format("{0:d}", dt);  // "3/9/2008"                        ShortDate
String.Format("{0:T}", dt);  // "4:05:07 PM"                      LongTime
String.Format("{0:D}", dt);  // "Sunday, March 09, 2008"          LongDate
String.Format("{0:f}", dt);  // "Sunday, March 09, 2008 4:05 PM"  LongDate+ShortTime
String.Format("{0:F}", dt);  // "Sunday, March 09, 2008 4:05:07 PM" FullDateTime
String.Format("{0:g}", dt);  // "3/9/2008 4:05 PM"                ShortDate+ShortTime
String.Format("{0:G}", dt);  // "3/9/2008 4:05:07 PM"             ShortDate+LongTime
String.Format("{0:m}", dt);  // "March 09"                        MonthDay
String.Format("{0:y}", dt);  // "March, 2008"                     YearMonth
String.Format("{0:r}", dt);  // "Sun, 09 Mar 2008 16:05:07 GMT"   RFC1123
String.Format("{0:s}", dt);  // "2008-03-09T16:05:07"             SortableDateTime
String.Format("{0:u}", dt);  // "2008-03-09 16:05:07Z"            UniversalSortableDateTime

String Format for DateTime [C#]

EXECUTE IMMEDIATE option for Dynamic SQL and PL/SQL – Database Journal

EXECUTE IMMEDIATE option for Dynamic SQL and PL/SQL – Database Journal

Conversion of Oracle REF CURSOR used as OUTPUT Parameter | DB Best blog : oracle 11

create or replace
PROCEDURE testing
(
emp_refcur OUT SYS_REFCURSOR
)
IS
pn_dept VARCHAR(6);
sql_stmt VARCHAR(256);
BEGIN
pn_dept:='456';
sql_stmt :='SELECT ' || pn_dept || ' emp_code FROM dual' ;
OPEN emp_refcur FOR sql_stmt;
END testing;

and run this by

VARIABLE resultSet REFCURSOR
EXEC p_cursor_proc( :resultSet );
PRINT :resultSet

Conversion of Oracle REF CURSOR used as OUTPUT Parameter | DB Best blog

How to get resultset from Oracle 11 : C#

Oracle PL\SQL stored procedures by default DO NOT return record sets!

I have learned that people with SQL Server experience expect this functionality, but Oracle historically did not provide this via PL\SQL. That may be because the standard Oracle client tools (Oracle Forms and Oracle Reports) built their own record sets internally - they did not depend on PL\SQL for this. In recent versions of Oracle, as Oracle has tried to make their database and software get along better with non-Oracle software, support for "ref cursors", arrays, collections. etc has been added to PL\SQL. But getting a "record set" out of a stored procedure may still be an easier task in SQL Server than in Oracle. Now, getting a stored procedure to perform efficiently and scale well to large numbers of users may be a much easier task in Oracle than in SQL Server.

Both systems have things they do very well, but be aware that they are very different systems, and their strengths and weaknesses are different.

--step 1
--Cretate the required package first
create or replace
PACKAGE UserRights AS
type t_cursor is ref cursor;
procedure RetriveList(cur_Result out t_cursor,val_MasterList VARCHAR2 );
procedure RetriveMaster(cur_Result out t_cursor,val_UserGroup in nvarchar2,val_MasterFileName in nvarchar2,val_CurrentAccessType in nvarchar2);

END UserRights;


--step 2
--Cretate the package body

create or replace PACKAGE BODY UserRights AS

PROCEDURE RetriveList(cur_Result out t_cursor,val_MasterList in VARCHAR2 ) AS
cMasterData VARCHAR2(50);
BEGIN
cMasterData:= upper(val_MasterList);


IF cMasterData = 'PRO' THEN
open cur_Result for SELECT GUPro REFID ,ProCode Code, Description Name FROM Protable WHERE upper(Status) = 'ACTIVE';
END IF;
IF cMasterData = 'ACCOUNT' THEN
open cur_Result for SELECT AccountCategoryID REFID, AccountCat Code, Name FROM AccountCategory;
END IF;
IF cMasterData = 'REPORTS' THEN
open cur_Result for SELECT REFID, Description Code, ReportName Name FROM Report;
END IF;


END RetriveList;

PROCEDURE RetriveMaster(
cur_Result out t_cursor
,val_UserGroup in nvarchar2
,val_MasterFileName in nvarchar2
,val_CurrentAccessType in nvarchar2) AS

cUserGroup VARCHAR2(50);
cMasterFileName VARCHAR2(50);
cCurrentAccessType VARCHAR2(50);
BEGIN
cUserGroup:= upper(val_UserGroup);
cMasterFileName:= upper(val_MasterFileName);
cCurrentAccessType:= upper(val_CurrentAccessType);

IF(cCurrentAccessType = 'ALL') THEN

IF cMasterFileName = 'TEST1' THEN
OPEN cur_Result FOR
SELECT PlanID , PlanCode Code , Description Name
FROM Promo;
END IF;

IF cMasterFileName ='TEST2' THEN
OPEN cur_Result FOR
SELECT AccountCategoryID GUID, AccountCategory , Name
FROM AccountCategory ;
END IF;
END IF;

END RetriveMaster;

END UserRights;

--step 3
-- Now run the required procedure

VARIABLE resultSet REFCURSOR
EXEC UserRights.RetriveList( :resultSet,'reports');
PRINT :resultSet

--
--c# 2003 code blog
public DataTable GetTheMaster()
{
string MasterFileName="Reports";
DataTable dtRMD=new DataTable();

OracleParameter []ParaList=new OracleParameter[2];

ParaList[0] = new OracleParameter("cur_Result",OracleDbType.RefCursor,ParameterDirection.Output );
ParaList[1] = new OracleParameter("MasterList",MasterFileName );

dtRMD= getDatasetSP("UserRights.RetriveList",ParaList).Tables[0];

return dtRMD;


}
public DataSet getDatasetSP(string strCommandText, OracleParameter []spList )
{
DataSet Results=new DataSet();

try
{
dbConn = new OracleConnection();
//conn.ConnectionString ="User ID=dbo_TestUser;Password=dbo_123abc;Data Source=abcoracle:1521/TestDB;Persist Security Info= true;";
dbConn.ConnectionString=connString;
//conn.ConnectionString = build.ToString();
dbConn.Open();
OracleCommand objCmd = new OracleCommand();
objCmd.Connection=dbConn ;//here your connection
objCmd.CommandText = strCommandText;
objCmd.CommandType = CommandType.StoredProcedure;
//objCmd.Parameters.Add( "cur_Result", OracleDbType.RefCursor,ParameterDirection.Output) ;
foreach(OracleParameter op in spList)
{
objCmd.Parameters.Add(op) ;
}
OracleDataAdapter da=new OracleDataAdapter(objCmd);
da.Fill(Results);
dbConn.Close();
}
catch(Exception ex)
{
dbConn.Close();
throw New Exception ("Unable to run the command. Reason." +ex.Message);
}

return Results;
}

for More infor for C# 2008 follow the link

http://www.c-sharpcorner.com/UploadFile/john_charles/CallingOraclestoredproceduresfromMicrosoftdotNET06222007142805PM/CallingOraclestoredproceduresfromMicrosoftdotNET.aspx

Keith Combs' Blahg : RichCopy bulk file copy tool released – get it here

Keith Combs' Blahg : RichCopy bulk file copy tool released – get it here

Making Queries Case Insensitive

With several third-party databases, it is common for queries to be case insensitive. For example, in such cases the following queries return the same results:
SELECT * FROM orders WHERE sales_rep = 'Oracle';
SELECT * FROM orders WHERE sales_rep = 'oracle';
SELECT * FROM orders WHERE sales_rep = 'OrAcLe';



If you want queries to be case insensitive for a user in the Oracle database, you can create an AFTER LOGON ON DATABASE trigger, in which you set, for that database user, the NLS_SORT session parameter to an Oracle sort name with _CI (for "case insensitive") appended.

The following example causes queries for user SMITH to use the German sort order and to be case insensitive:
CREATE OR REPLACE TRIGGER set_sort_order AFTER LOGON ON DATABASE
DECLARE
username VARCHAR2(30);
BEGIN
username:=SYS_CONTEXT('USERENV','SESSION_USER');
IF username LIKE 'SMITH' then
execute immediate 'alter session set NLS_COMP=LINGUISTIC';
execute immediate 'alter session set NLS_SORT=GERMAN_CI';
END IF;
END;
Source : oracle help

Dynamic login info to Crystal report : C#

using System;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

namespace WindowsApplication1
{
  public partial class Form1 : Form
  {
      public Form1()
      {
          InitializeComponent();
      }

      private void button1_Click(object sender, EventArgs e)
      {
          ReportDocument cryRpt = new ReportDocument();
          TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
          TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
          ConnectionInfo crConnectionInfo = new ConnectionInfo();
          Tables CrTables ;

          cryRpt.Load("PUT CRYSTAL REPORT PATH HERE\CrystalReport1.rpt");

          crConnectionInfo.ServerName = "YOUR SERVER NAME";
          crConnectionInfo.DatabaseName = "YOUR DATABASE NAME";
          crConnectionInfo.UserID = "YOUR DATABASE USERNAME";
          crConnectionInfo.Password = "YOUR DATABASE PASSWORD";

          CrTables = cryRpt.Database.Tables ;
          foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
          {
              crtableLogoninfo = CrTable.LogOnInfo;
              crtableLogoninfo.ConnectionInfo = crConnectionInfo;
              CrTable.ApplyLogOnInfo(crtableLogoninfo);
          }

          crystalReportViewer1.ReportSource = cryRpt;
          crystalReportViewer1.Refresh();
      }
  }
}