Dec 16, 2011

How to Get Last Day of the week and Month

DECLARE @Date datetime
SET @Date = '2000/02/1'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the week'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the week'
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Last day of the month'

SELECT convert(varchar, DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date)), 101) AS 'Last Date of the month'

Insert into oracle table By XML In StoreProcedure

Well, after a bit of looking on Oracle's Metalink, I found some information on
passing XML to stored procedures via varchars or CLOBs. Here are examples of an
insert and of an update:

Create procedure to take in xml as a varchar or a clob:

create or replace procedure InsertXML(xmlDoc IN VARCHAR2, tableName IN
VARCHAR2) is

insCtx DBMS_XMLSave.ctxType;

rows number;

begin

insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle

rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document

dbms_output.put_line(to_char(rows) || ' rows inserted');

DBMS_XMLSave.closeContext(insCtx); -- this closes the handle

end;

The xml text that is passed must be in the form of:

<?xml version="1.0"?>

<ROWSET>

<ROW num="1">

<EMPID>10</EMPID>

<EMPNAME>Perry Smith</EMPNAME>

<EMPJOB>Manager</EMPJOB>

<EMPSAL>800</EMPSAL>

</ROW>

<ROW num="1">

<EMPID>20</EMPID>

<EMPNAME>John Calvach</EMPNAME>

<EMPJOB>Principal Support Consultant</EMPJOB>

<EMPSAL>900</EMPSAL>

</ROW>
</ROWSET>

Notice that the tags nested in the <ROW> tag are the same as the columns in
any table you would use. I don't know if it is case sensitive or not, but I
build all the xml as uppercase just in case.

This is using the internal Oracle XML parser for PL/SQL.

The update procedure is a bit different, because you have to tell it which
item is the primary key This procedure would update the empleados table which
has a primary key of empid:

create or replace procedure UpdateEmpleados ( xmlDoc IN VARCHAR2) is

updCtx DBMS_XMLSave.ctxType;

rows number;

begin

updCtx := DBMS_XMLSave.newContext('empleados'); -- get the context

DBMS_XMLSave.clearUpdateColumnList(updCtx); -- clear the update settings..

DBMS_XMLSave.setKeyColumn(updCtx,'EMPID'); -- set EMPNO as key column

rows := DBMS_XMLSave.updateXML(updCtx,xmlDoc); -- update the table.

dbms_output.put_line(to_char(rows) || ' rows updated');

DBMS_XMLSave.closeContext(updCtx); -- close the context..!

end;

And that's it! It seems to work quite well, and now I don't have to use the
<cftransaction> tag and let the database handle all rollbacks, etc.

Thanks for all the help on this!

Nov 28, 2011

Linking Sql Server 2005 to Oracle

Here is a quick guide to setting up linked servers so you can query Oracle through Sql Server.  For my testing of this I used Sql Server Express 2005, and Oracle 9.2.   This all uses simple commands that can be run via Query Analyzer or Query Tool.


First, if you've been doing this and having issues, you need to clear out any of your linked server's logins:

sp_droplinkedsrvlogin @rmtsrvname = 'LINKED_ORA', @locallogin = null

Next, drop the server

EXEC sp_dropserver LINKED_ORA

Ok, now let's add the linked server to Sql Server.  The linked server will be called LINKED_ORA.  Once this is done you should see this listed in SS Management Studio under  Server Objects->Linked Servers

-- add server
EXEC sp_addlinkedserver
  @server= 'LINKED_ORA'
, @provider='MSDASQL'
, @srvproduct='Oracle'
, @datasrc= 'myOracle'

@server - this is the name to use.  It can be anything
@provider - can be either MSDASQL and MSDAORA.  Both seem to both work fine.  There is talk of another provider from Oracle, but I have not gotten this one to work.
@srvproduct - must be "Oracle".  I do not know why this matters
@datasrc - this is a DSN that you have set up using Oracle's .NET manager

Now, the Oracle system does not implicitly let Sql Server connect.  You need to add a login for this linked server to use.

-- add login - the DSN is protected by a password
EXEC sp_addlinkedsrvlogin
  @rmtsrvname='LINKED_ORA'
, @useself='false'
, @rmtuser='oracleUserId'
, @rmtpassword='oraclePassword'

Once this is set, then you can test using this command:

--verify tables OK
exec sp_tables_ex @table_server = 'LINKED_ORA', @table_schema='MySchema'

@table_schema is optional.  If not provided, you will get a list of all  tables in all schemas.

This will return information about the tables in the linked server.  I use it as a basic diagnostic to verify that the linked server is working.  if it's not, then I use the scripts above to delete the login and linked server, then re-create using other inputs.

Now, the big pain with Oracle is that you cannot just query into the database. I know, it is ridiculous, but I found that only OPENQUERY works.  Here is how it looks:

SELECT * FROM OPENQUERY( LINKED_ORA, 'SELECT * FROM Exp.Proj')

But from there you can now do handy things in Sql Server.  One of my favorites is to re-create the Oracle schema in a Sql Server database, then use INSERT to pull in all of the Oracle data.  Then you can just work with Sql Server!

Nov 24, 2011

Upload file on fileUpload onchange event in asp.net



==================== ASPX code======================
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
        <asp:FileUpload ID="FileUpload1" onchange="if (confirm('Upload ' + this.value + '?')) this.form.submit();" runat="server" />      
            <br />

<asp:RegularExpressionValidator ID="rexp" runat="server" ControlToValidate="FileUpload1"
     ErrorMessage="Only .DAT,.PNG,.BMP,.JPEG,.XLSX,.DOCX,.PPTX,
.DXF,.DRW,.SLD,.TXT,.PPT,.XLS,.PPS,.DOC,
.PDF,.GIF,.JPG,.RTF"
     ValidationExpression="(.*\.([Gg][Ii][Ff])|.*\.([Jj][Pp][Gg])|.*\.([Bb][Mm][Pp])|.*\.([pP][nN][gG])|.*\.([xX][lL][sS][xX])|.*\.([dD][oO][cC][xX])|.*\.([pP][pP][tT][xX])|.*\.([dD][xX][fF])|.*\.([dD][rR][wW])|.*\.([sS][lL][dD])|.*\.([tT][xX][tT])|.*\.([pP][pP][tT])|.*\.([xX][lL][sS])|.*\.([pP][pP][sS])|.*\.([dD][oO][cC])|.*\.([pP][dD][fF])|.*\.([dD][aA][tT])|.*\.([rR][tT][fF])$)"></asp:RegularExpressionValidator>


            <asp:Label ID="lblFileSize" runat="server" Text="Label"></asp:Label></div>
    </form>
</body>
</html>
==================================================

====================On page Load======================
protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack)
        {
            if (FileUpload1.HasFile)
            {
                string strFileName, strFileExtension;
                strFileName = FileUpload1.FileName;
                strFileExtension = strFileName.Substring(strFileName.LastIndexOf('.') + 1);
                lblFileSize.Text = FileUpload1.PostedFile.ContentLength.ToString();
                string strSavedFilePath = Server.MapPath("~//CTD_Files");
                strFileName = strFileName.Substring(0, strFileName.LastIndexOf('.')) + DateTime.Now.ToString("ddMMyyyyhhmmss") + "." + strFileExtension.Trim();
                strSavedFilePath += "\\" + strFileName;
                FileUpload1.PostedFile.SaveAs(strSavedFilePath);
            }
        }
    }

====================================================

Nov 22, 2011

How to give Link for Download or code for uploding and downloading file.

=========================== Upload Code ============================

 String strFileName = "", strFileExtension, strSavedFilePath;

            if (FileUploadCtd.HasFile)
            {
                strFileName = FileUploadCtd.FileName;
                strFileExtension = strFileName.Substring(strFileName.LastIndexOf('.') + 1);
                //if (strFileExtension.ToUpper() != "XLS")
                //{
                //    ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('Select Excel File Only.')</script>");
                //    return;
                //}
                strSavedFilePath = Server.MapPath("../CTD_Files");
                if (!Directory.Exists(strSavedFilePath))
                {
                    Directory.CreateDirectory(strSavedFilePath);
                }
                strFileName = strFileName.Substring(0, strFileName.LastIndexOf('.')) + DateTime.Now.ToString("ddMMyyyyhhmmss") + "." + strFileExtension.Trim();
                //strSavedFilePath += "\\" + strFileName.Substring(0, strFileName.LastIndexOf('.')) + DateTime.Now.ToString("ddMMyyyyhhmmss") + "." + strFileExtension.Trim();
                strSavedFilePath += "\\" + strFileName;
                FileUploadCtd.PostedFile.SaveAs(strSavedFilePath);

                objDesign._strvCTDPath = strFileName; // save in DB table.
                lblCTDLink.Text = strFileName;
            }
            else
            {
                objDesign._strvCTDPath = "";
                lblCTDLink.Text = "";
            }
            // CTD file uploading end Here

===============================================================

=========================On Page Load (Data Load) ====================

 // get file name from Db
lblCTDLink.Text = dtItemData.Rows[0]["vCTDPath"].ToString();

================================================================

======================= On download  link button click ====================

 protected void linkCTD_Click(object sender, EventArgs e)
    {
        // code for show donload link
        if (lblCTDLink.Text.Trim() != "")
        {
            string strSavedFilePath;
            strSavedFilePath = Server.MapPath("../CTD_Files" + "\\" + lblCTDLink.Text);
            FileInfo fileInfo = new FileInfo(strSavedFilePath);

            if (fileInfo.Exists)
            {
                Response.Clear();
                Response.AddHeader("Content-Disposition", "attachment; filename=" + fileInfo.Name);
                Response.AddHeader("Content-Length", fileInfo.Length.ToString());
                Response.ContentType = "application/octet-stream";
                Response.Flush();
                Response.WriteFile(fileInfo.FullName);
            }
        }
        else
        {
            objFnLib.WebMessageBox("No CTD Path Present", this);
        }
    }

================================================================

Nov 16, 2011

How to Send value return from child form/page to parent form/page via window.open() in java script

------------------------------------------In Default4.aspx------------------------------------------------

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TextBox ID="YourTextBox" runat="server"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" Text="open" OnClientClick="openPopup();" /></div>
    </form>
</body>


<script type="text/javascript">
<!--
function openPopup()
{
 var elementId = '<%= YourTextBox.ClientID %>';
 var windowUrl = 'PopupPage.aspx?ElementId=' + elementId;
 var windowId = 'NewWindow_' + new Date().getTime();
 var windowFeatures =
  'channelmode=no,directories=no,fullscreen=no,' +
  'location=no,dependent=yes,menubar=no,resizable=no,scrollbars=yes,' +
  'status=no,toolbar=no,titlebar=no,' +
  'left=0,top=0,width=400px,height=200px';
 var windowReference = window.open(windowUrl, windowId, windowFeatures);
 windowReference.focus();

}
// -->
</script>
</html>
------------------------------------------------------------------------------------------------


------------------------------------------PopupPage.aspx----------------------------------
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" OnClientClick="closeWindow();" Text="Close" /></div>
    </form>
</body>



<script type="text/javascript">
<!--
function closeWindow()
{
 // C#
  window.opener.document.getElementById('<%= Request["ElementId"] %>').value = 'Some new value';


 window.opener.focus();
 window.close();
}
// -->
</script>
</html>
-------------------------------------------------------------------------------------------------

What is the use of n-tier architecture and 3-tier architecture?

how to implement 3-tier architecture in asp.net using c#. 3-Tier architecture is also called layered architecture. Some people called it ...