C# Merge Splitted File

Hi all, here a very easy lwebcode free script to split file in C#

// calls example
...

int iSplittedFileSize = 1024 * 8;MergeFile(@"C:\script\", iSplittedFileSize, @"C:\LwebCodeMergeFile\");

...

public static void MergeFile(string inputfoldername1, string SaveFileFolder)
        {
            string[] tmpfiles = Directory.GetFiles(inputfoldername1, "*.tmp");

            FileStream outPutFile = null;
            string PrevFileName = "";

            foreach (string tempFile in tmpfiles)
            {
                string fileName = Path.GetFileNameWithoutExtension(tempFile);
                string baseFileName = fileName.Substring(0, fileName.IndexOf(Convert.ToChar(".")));
                string extension = Path.GetExtension(fileName);

                if (!PrevFileName.Equals(baseFileName))
                {
                    if (outPutFile != null)
                    {
                        outPutFile.Flush();
                        outPutFile.Close();
                    }
                    outPutFile = new FileStream(SaveFileFolder + "\\" + baseFileName + extension, FileMode.OpenOrCreate, FileAccess.Write);

                }

                int bytesRead = 0;
                byte[] buffer = new byte[1024];
                FileStream inputTempFile = new FileStream(tempFile, FileMode.OpenOrCreate, FileAccess.Read);

                while ((bytesRead = inputTempFile.Read(buffer, 0, 1024)) > 0)
                    outPutFile.Write(buffer, 0, bytesRead);

                inputTempFile.Close();
               
                PrevFileName = baseFileName;

            }
        }

Hope you like it ;)

C# Split File

Hi all, here a very easy lwebcode free script to split file in C#

// calls example
...

int iSplittedFileSize = 1024 * 8;
SplitFile(@"C:\script\lwebcode.bak", iSplittedFileSize, @"C:\script\");

...

public static void SplitFile(string inputFile, int chunkSize, string path)
{
    const int BUFFER_SIZE = 20 * 1024; //2 Kb for each file
    byte[] buffer = new byte[BUFFER_SIZE];

    string baseFileName = Path.GetFileNameWithoutExtension(inputFile);
    string Extension = Path.GetExtension(inputFile);

    using (Stream input = File.OpenRead(inputFile))
    {
        int index = 0;
        while (input.Position < input.Length)
        {
            string sFinalName = path + "\\" + baseFileName + "." + index.ToString().PadLeft(5, Convert.ToChar("0")) + Extension + ".tmp";
            using (Stream output = File.Create(sFinalName))
            {
                int remaining = chunkSize, bytesRead;
                while (remaining > 0 && (bytesRead = input.Read(buffer, 0,
                        Math.Min(remaining, BUFFER_SIZE))) > 0)
                {
                    output.Write(buffer, 0, bytesRead);
                    remaining -= bytesRead;
                }
            }
            index++;
            System.Threading.Thread.Sleep(500);
        }
    }
}

Hope you like it ;)

C# Windows Service Debug

Hi all, here an easy example to make a debug on C# Windows Service ,
Implementation of Code founded on:

https://www.codeproject.com/Articles/14353/Creating-a-Basic-Windows-Service-in-C

With this useful example you create a Single *.exe file , but if you start the debugger
you can't debug the code with Visual Studio, Here is how to do:

First, make all step writed on CodeProject(in future reference I call this project "CodeProjectWS"), after you need to do this:

Right Clik on Current Solution -> Add -> New Project
On Project Type select Windows Consolle Application
select a name for new project, I choose :"LwebCodeWinDbg" -> Ok
Now in New Project "LwebCodeWinDbg" right-click the project name and go to:
[Add]->[Class]. Name the class "LwebCodeWindowsService.cs" and then hit OK.
Open class "LwebCodeWindowsService" and put this code:

namespace LwebCodeWinDbg
{
    public class LwebCodeWindowsService
    {
        public static void WindowsServiceStart()
        {
            System.IO.File.WriteAllText("c:\\LwebCodeTetsService.txt", DateTime.Now.ToString());
        }
    }
}
   
Now in New Project "LwebCodeWinDbg" open Program.cs and paste this code over Main Function:

namespace LwebCodeWinDbg
{
    class Program
    {
        static void Main(string[] args)
        {
            //put debug breakpoint on this istruction:
            LwebCodeWindowsService.WindowsServiceStart();
        }
    }
}

Now Open "CodeProjectWS" , right Clik on "Reference"-> Add Reference
in left pane select: "Solution"    -> Browse... and browse to "LwebCodeWinDbg" Project and then hit OK.
Open WindowsService.cs
and paste this code on OnStart Event:

protected override void OnStart(string[] args)
        {
            base.OnStart(args);
            LwebCodeWinDbg.LwebCodeWindowsService.WindowsServiceStart();
        }

with this istruction you can call the same function from Windows Service and Windows Consolle Application.
To debug the Windows service right click on "LwebCodeWinDbg" -> Set as Startup Project
F5 to start debug.

Hope it helps

Javascript read JSON file and loop through records(optional with JQuery)

Hi all, here a very fast example on how to read json data from file in Javascript,
here the content of *.htm file:

<!DOCTYPE html>
<html>
<head>
    <title>LWebCode Javascript Read JSON from file</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.0/jquery.min.js"></script>
    <script type="text/javascript" src="mydata.json"></script>
    <script>
        function ReadData()
        {
            var dataLstAz = JSON.parse(jsLstData);
            alert(jsLstData.length);
          
            for (var key=0, size=jsLstData.length; key<size; key++)
            {
                var obj = jsLstData[key];
                liout = obj.Name + ", " + obj.Age + "<br/>";
                $("#Output").append(liout);
            }
        }
      
        $( document ).ready(function() {
            ReadData();
        });
    </script>
</head>
<body>
    <div id="Output">
    </div>
</body>
</html>

Here the content of "mydata.json" file,
put this file in same folder of *.htm file, or change path in *.htm file:

jsLstData = '[{"Name":"MyName1","Age":"20"}, {"Name":"MyName2","Age":"22"}]';
//End of file "mydata.json"

Now simply open *.htm file in browser and you'll get this output:

MyName1 20
MyName2 22

That's it, hope it helps

PowerShell Certificate Authority List, Loop, Filter Trought CA Certificate Authority

PowerShell Loop Trought CA Certificate Authority,
Hi all, here a quick and easy PowerShell script to list and filter all details of Installed Certificate Authority, if needed filter on line(s) given from certlst.txt file

function LWebCode_CALoop
{
    $MyDir = Split-Path -Path $MyInvocation.ScriptName
    $foutput = $MyDir + "\certlst.txt"
    Get-ChildItem -Recurse Cert: > $foutput
    $reader = [System.IO.File]::OpenText($foutput)
    while($null -ne ($line = $reader.ReadLine()))
    {
        echo $line
        /* optional filter on line(s)
        if($line -Match "Thumbprint")
        {
            echo $line
        }
        */
    }
}

LWebCode_CALoop

Do inside if block your preferred stuff

Send Mass Mail C#, PowerShell, Visual Basic, ASP, PHP, Curl, Node, Ruby, Python, Java, Go

Hi all, here some example and links to send Mass mail,
All this site can works with most common language: C#, PowerShell, Visual Basic, ASP, PHP, Curl, Node, Ruby, Python, Java, Go,
have lot of report and statistics graph, offering high level of personalization, and allow you to create your custom Marketing campaign


http://www.mailjet.com
Free Account has:
Month Max Limit: 6000
Day Max Limit: 200
Developement Support: SMTP , API


http://www.mailgun.com
Free Account has:
Month Max Limit: 10000
Day Max Limit: 400
Developement Support: SMTP , API


http://mailchimp.com
Free Account has:
Month Max Limit: 12000
Day Max Limit: ?
Developement Support: API

PowerShell Encryption and Decryption, PS V 2.0 or Higher, Easy

Hi All, here's an easy example on how to encrypt or decrypt a string
this way IS NOT SURE because Key value is inside the *.ps1 file,
Another user can easily retrive your data.
This is only to understand basic of encryption in PowerShell



<#
LWEBCODE 
EASY POWERSHELL 2.0 or higher Encryption and Decryption
#>
$global:byteArr = New-Object Byte[] 32
$global:byteArr = [Byte[]](100, 101, 100, 101, 100, 101, 100, 101, 100, 101, 100, 101, 100, 101, 100, 101, 100, 101, 100, 101, 100, 101, 100, 101, 100, 101, 100, 101, 100, 101, 100, 101)

function encrypt($content)
{
    $Password = $content | ConvertTo-SecureString -AsPlainText -Force
    $Password | ConvertFrom-SecureString -key $global:byteArr
    Write-Output "$Password"
    Write-Output "ENC END"
}

function decrypt($content)
{
    $User = "MyUserName"
    $MyCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, ($content | ConvertTo-SecureString -Key $global:byteArr)
    $plainText = $MyCredential.GetNetworkCredential().Password

    Write-Output "Plain text: $plainText"
    Write-Output "DEC END"
}

encrypt("lwebcode is the best ;D ")

decrypt("76492d1116743f0423413b16050a5345MgB8AEoATwB4ADYAegBQAHcAaABhAGMATQBxAEwAWAA4AEI
ARgA2AE4ARwBlAFEAPQA9AHwAOQBjAGUAOABkAGQAYgAxADgAOABlAGMAMwA3AGEAYwA3AGQAMgA5AD
YAMgBmADQAZAA3ADIAYgAyADYANABiAGMAYQBjADIAMgA3AGEAYwA4AGQAYQBkAGUAOAAwADgANwAyA
DMANAAwADkAZQBhADcANgA5ADUANABhAGEAZgAwAGEAYwA3ADIANAA2ADcANQA3AGIAYQA4ADIAYgAx
ADUAOAAyADIANwBjADgAYgAxADgANAAyAGMAOAA5ADUAMQA0AGYAMQAxADgAOQAwADQAOQBiADQAZQA
yADMANwAyADgAZgBjAGMAOABkADkAYwAxAGMAMQBhADgANAA4AA==")

PowerShell Send Email with Gmail, PS V. 2.0 or Higher

Hi All, here a free script to sending email with gmail and powershell v 2.0 or higher:
If you get error on sending like:

Exception calling "Send" with "1" argument(s): "The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required. Learn more at"...

You MUST enable less secure App access on your gmail's account
https://myaccount.google.com/security

<#
LWEBCODE Mail Sender
On your gmail account enable less secure App access
https://myaccount.google.com/security

In code above replace GMAILUSER with your gmail's user:
lwebcode123@gmail.com=>lwebcode123
#>
function sendmail
{
    $smtpClient = new-object system.net.mail.smtpClient
    $smtpClient.Host = "smtp.gmail.com"
    $smtpClient.Port = 587
    $smtpClient.EnableSsl = $true
    $SMTPClient.Credentials = New-Object System.Net.NetworkCredential("GMAILUSER", "password");
      
    $emailfrom = "lwebcode123@gmail.com"
    $emailto = "lwebcode123@gmail.com"
    $subject = "test"
    $body = "this is body"

    $emailMessage = New-Object System.Net.Mail.MailMessage
    $emailMessage.From = $EmailFrom
    $emailMessage.To.Add($EmailTo)
    $emailMessage.Subject = $Subject
    $emailMessage.Body = $Body
    $emailMessage.Attachments.Add("C:\lwebcode\attachement.txt")
    $SMTPClient.Send($emailMessage)
}
sendmail

Overlapped I/O operation is in progress, fix Windows Installer error

Hi all, here how you can try to fix Windows Installer error:
Overlapped I/O operation is in progress
On Microsoft SQL Server Setup Installing have Overlapped error

Try to follow these steps:

1    Click Start, click Run, type regedit in the Open box, and then click OK.
2    Locate and then click the following subkey in the registry:
     HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Installer
3    On the Edit menu, point to New, and then click DWORD Value.
     Type SecureRepairPolicy for the name of the DWORD, and then press Enter.
4    Right-click SecureRepairPolicy, and then click Modify.
5    In the Value data box, type 2, and then click OK.
6    Locate and then click the following subkey in the registry:
     HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Installer
7    On the Edit menu, point to New, and then click Key.
8    Type SecureRepairWhitelist for the name of the key, and then press Enter.
9    Double-click the SecureRepairWhitelist key to open it.
10   On the Edit menu, point to New, and then click String Value. Create String   Values that contain the product codes (including braces {})
     of the products that have to be added to the Safe Recipients list.

     The NAME of the String Value is the "product code" and the VALUE can be left blank. To obtain the product code for other MSIs, open the MSI by using the ORCA tool that is available in Windows SDK.
   
     LWEBCODE NOTES:
     If you can't access to "product code" with ORCA, you can try this on another machine wich have already the program installed
     in this example we have trouble installing MSSQL Server, so find in another machine
     HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup
     key:Product Code => you can copy the value
   
11     Retry to install
   
     If install doesn't work in step N° 5 try to set 1 for SecureRepairPolicy value
   
Reference:
https://support.microsoft.com/en-us/help/2918614/ms14-049-description-of-the-security-update-for-windows-installer-service-august-12,-2014

https://blogs.msdn.microsoft.com/vsnetsetup/2014/10/27/error-997-overlapped-io-operation-is-in-progress-kb2918614-breaks-windows-installer-service/

http://www.sqlservergeeks.com/sql-server-finding-out-sql-server-product-code-from-windows-registry/

C# Export Dataset To XML, ISNULL Exporting fix

Hi All, Now see what happen, when export Datate filled with a standard SQL query/stetement:
With this SQL Table:


CREATE TABLE [dbo].[TabExample](
    [FieldNvar] [nvarchar](10) NULL,
    [FieldInt] [int] NULL,
    [FieldDate] [datetime] NULL
   
) ON [PRIMARY]

GO

With this record #1:

 FieldNvar | FieldInt | FieldDate
 lwebcode  |   NULL   | 01/01/2017


with standard SQL to fill Dataset we can have some problem:

Dataset ds = ... "SELECT * FROM TabExample"

ds.WriteXml(sTargetXML);

//will return this XML:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldNvar>lwebcode</FieldNvar>
        <FieldDate>2017-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

FieldInt Was Not Write!

With this record #2:

 FieldNvar | FieldInt | FieldDate
    NULL   |    2     | 01/01/2017

with standard SQL to fill Dataset we can have some problem:

Dataset ds = ... "SELECT * FROM TabExample"

ds.WriteXml(sTargetXML);

//will return this XML:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldInt>2</FieldInt>
        <FieldDate>2017-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

FieldNvar Was Not Write!

With this record #3:

 FieldNvar | FieldInt | FieldDate
    NULL   |    2     | NULL


with standard SQL to fill Dataset we can have some problem:

Dataset ds = ... "SELECT * FROM TabExample"

ds.WriteXml(sTargetXML);

//will return this XML

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldInt>2</FieldInt>
        <FieldDate>1900-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

If datetime has NULL value it will put firs day of datetime in xml
As in #2 FieldNvar Was Not Write!

To fix this issue, you have to edit SQL Statement using ISNULL Function:

Dataset ds = ... "SELECT ISNULL(FieldNvar, '') FieldNvar, ISNULL(FieldInt, '') FieldInt, ISNULL(FieldDate , '') FieldDate FROM TabExample"

with record #1, we have a error on converting Numeric Value to char, see later what to do.

with record #2
XML file will like:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldNvar/>
        <FieldInt>2</FieldInt>
        <FieldDate>2017-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

with record #3
XML file will like:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldNvar/>
        <FieldInt>2</FieldInt>
        <FieldDate/>
    </Table>
</NewDataSet>

with record #1 there is a last thing to do on all numeric value (int, decimal etc...), in SQL you neet to put ISNULL function with CAST our SQL query will Like:

Dataset ds = ... "SELECT ISNULL(FieldNvar, '') FieldNvar, CASE WHEN FieldInt IS NULL THEN '' ELSE CAST(FieldInt AS NVARCHAR(50)) END FieldInt, ISNULL(FieldDate , '') FieldDate FROM TabExample"

Now XML file will like:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldNvar>lwebcode</FieldNvar>
        <FieldInt/>
        <FieldDate>2017-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

With this fix in sql statement, we can export every kind of fields and be sure that each field will be write on xml file.

VBA Closing Excel Spreadsheet In Internet Explorer Window without Saving Prompts

Hi all ,
SCENARIO:
We’ve an Excel Spredsheet opened with Internet Explorer ,as you should know funcions like

ThisWorkbook.Close False

donesn’t work because Excel’s file is opened in cache memory of Internet Explorer and you can’t refer to Excel Library as this file is open in Excel.
If Excel’s files is on server (for internet access it shoud be) you CAN’T save it.
For save you need to write a custom funcion wich saves sheets status and after retrive it , to do
this thing we’ve used SQL database here there is LWEBCODE function to SAVE value:

With ThisWorkbook
    For i = 1 To .Sheets.Count
        tbl = .Worksheets(i).Name
        tName = "EXCEL_" & tbl
        sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = 'BASE TABLE')" & _
        " AND TABLE_NAME='" & tName & "'"
        Set rs = cn.Execute(sql)
        If rs.EOF Then
            sql = "CREATE TABLE [dbo].[" & tName & "] (" & _
            " [Row] [int] NOT NULL ," & _
            " [Col] [int] NOT NULL ," & _
            " [Val] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ," & _
            " [SName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL" & _
            ") ON [PRIMARY]"
            cn.Execute sql
        End If
        sql = "DELETE FROM " & tName
        cn.Execute sql

        For r = 1 To 1000' your max row used
            For c=1 to 100 ' your max column used
            If Worksheets(i).Cells(r, Colonna).Value "" Then
            sql = "INSERT INTO " & tName & " ( Row, Col, Val, SName ) VALUES (" & r & "," & c & ",'" & Worksheets(i).Cells(r, c).Value & "','" & Worksheets(i).Name & "')"
            Debug.Print sql
            cn.Execute sql
          
            Next c
        Next r
        End If
    Next i
End With
You have to implement LOAD function it isn’t hard.
For closing Excel’s file when is opened in IE (Internet Explorer)
We’ve used a VBA Timer getting from:
http://www.cpearson.com/excel/OnTime.aspx

Every thick of Timer Call CloseExcel() see this later
as
Public RunWhen As Double
Public Const cRunIntervalSeconds = 1 ' 1 seconds
Public Const cRunWhat = "TheSub" ' the name of the procedure to run

Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
    Schedule:=True
End Sub

Sub TheSub()
    ''''''''''''''''''''''''
    ' LWEBCODE
    '
-Website Building & free script at http://lwebcode.blogspot.com/     ''''''''''''''''''''''''
    Debug.Print Format(Now, "HH:mm:ss")
    Call CloseExcel ''Every seconds we check if value was change and even close excel
    StartTimer ' Reschedule the procedure
End Sub


Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
    Schedule:=False
End Sub


In WorkBook Open Events I’ve put :

Private Sub Workbook_Open()
StartTimer
End Sub

Now chek if Excel files is open in IE or Excel  

Public Function ExcelOrIE() As String
Dim CName As String
On Error GoTo MustBeExcel
CName = ThisWorkbook.Container.Name
If InStr(1, UCase(CName), UCase("Internet Explorer")) > 0 Then
CName = CName
Else
CName = "Something Else"
End If
ExcelOrIE = CName
Exit Function
MustBeExcel:
ExcelOrIE = "EXCEL"
End Function
 
After this we’ve added a button in excel sheet which write a value in a hidden sheet and call Window Api to close
current tab of IE  

If ExcelOrIE "EXCEL" Tehn
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = "'---"
CloseApp ExcelOrIE, "IEFrame"
End If

Close App(), We suggest to put this in a separate module:
http://www.tek-tips.com/faqs.cfm?fid=6073
 
Private Declare Function PostMessage Lib "user32" _
Alias "PostMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
Private Declare Function GetDesktopWindow Lib "user32" () As Long
Private Declare Function GetWindow Lib "user32" _
(ByVal hwnd As Long, _
ByVal wCmd As Long) As Long
Private Declare Function GetWindowText Lib "user32" _
Alias "GetWindowTextA" _
(ByVal hwnd As Long, _
ByVal lpString As String, _
ByVal cch As Long) As Long
Private Declare Function GetClassName Lib "user32" _
Alias "GetClassNameA" _
(ByVal hwnd As Long, _
ByVal lpClassName As String, _
ByVal nMaxCount As Long) _
As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Const GW_HWNDFIRST = 0
Private Const GW_HWNDLAST = 1
Private Const GW_HWNDNEXT = 2
Private Const GW_HWNDPREV = 3
Private Const GW_OWNER = 4
Private Const GW_CHILD = 5
Private Const WM_CLOSE = &H10
Public Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Const KEYEVENTF_EXTENDEDKEY = &H1 'pressione del tasto (keyDown)
Const KEYEVENTF_KEYUP = &H2 'rilascio del tasto premuto (keyUp)
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Function FindWindowHwndLike(ByVal hWndStart As Long, _
ClassName As String, _
WindowTitle As String, _
level As Long, _
lHolder As Long) As Long
'finds the first window where the class name start with ClassName
'and where the Window title starts with WindowTitle, returns Hwnd
'----------------------------------------------------------------
Dim hwnd, WndChild, WndChild2 As Long
Dim sWindowTitle As String
Dim sClassName As String
Dim r As Long
'Initialize if necessary. This is only executed
'when level = 0 and hWndStart = 0, normally
'only on the first call to the routine.
If level = 0 Then
If hWndStart = 0 Then
hWndStart = GetDesktopWindow()
End If
End If
'Increase recursion counter
level = level + 1
'Get first child window
hwnd = GetWindow(hWndStart, GW_CHILD)
Do Until hwnd = 0
'Search children by recursion
lHolder = FindWindowHwndLike(hwnd, _
ClassName, _
WindowTitle, _
level, _
lHolder)
'Get the window text
sWindowTitle = Space$(255)
r = GetWindowText(hwnd, sWindowTitle, 255)
sWindowTitle = Left$(sWindowTitle, r)
'get the class name
sClassName = Space$(255)
r = GetClassName(hwnd, sClassName, 255)
sClassName = Left$(sClassName, r)
Debug.Print sClassName & " " & sWindowTitle
If InStr(1, sWindowTitle, WindowTitle, vbBinaryCompare) > 0 And _
sClassName Like ClassName & "*" Then
FindWindowHwndLike = hwnd
'#If Internet Explorer is opened close current tab
WndChild = FindWindowEx(hwnd, 0, "Frame Tab", vbNullString)
WndChild2 = FindWindowEx(WndChild, 0, "TabWindowClass", vbNullString)
PostMessage WndChild2, WM_CLOSE, 0, 0
lHolder = hwnd
Exit Function
End If
'Get next child window
hwnd = GetWindow(hwnd, GW_HWNDNEXT)
Loop
FindWindowHwndLike = lHolder
End Function
Function CloseApp(ByVal strApp As String, _
ByVal strClass As String) As Long
'will find a window based on:
'the partial start of the Window title and/or
'the partial start of the Window class
'and then close that window
'for example, this will close Excel:
'CloseApp "", "XLM" and this will:
'CloseApp "Microsoft Excel", ""
'but this won't: CloseApp "", "LM"
'it will only close the first window that
'fulfills the criteria
'will return Hwnd if successfull, and 0 if not
'---------------------------------------------
Dim hwnd As Long
On Error GoTo ERROROUT
hwnd = FindWindowHwndLike(0, _
strClass, _
strApp, _
0, _
0)
If hwnd = 0 Then
CloseApp = 0
Exit Function
End If
'Post a message to the window to close itself
'--------------------------------------------
''PostMessage hwnd, WM_CLOSE, 0&, 0&
CloseApp = hwnd
Exit Function
ERROROUT:
On Error GoTo 0
CloseApp = 0
End Function
  
Remember CloseExcel() ? is the last function to do the trick : 

Public Function CloseExcel()
If ThisWorkbook.Sheets("Sheet1").Cells(99, 1) = "---" Then
SendKeys "{TAB}"
SendKeys "{ENTER}"
End If
End Function

Sending {TAB} you can automate selection of Excel prompt for Save File as usually options are
Yes , No, Cancel
sending 1 tab, you select No option and sending {ENTER} emulate button click and that’s All
 

Sql Grab the SQL Command/Statement that fired a Trigger

Hi All, here a easy script to Grab the SQL Command/Statement that fired a Trigger:

--LWEBCODE
--Website Building & free script at http://lwebcode.blogspot.com/
CREATE TRIGGER trgUpdate
ON User_Master
FOR UPDATE

AS
--Print ('AFTER Trigger [trgUpdate] – Trigger executed !!')
--DBCC INPUTBUFFER(@@spid)

--Before Check if not exists
CREATE TABLE TMP_CMD ( [eventtype] NVARCHAR(30), [parameters] INT, [eventinfo] NVARCHAR(4000))
DECLARE @sSql nvarchar(4000)
SET @sSql = 'DBCC INPUTBUFFER(' + STR(@@SPID) + ')'
INSERT INTO TMP_CMD EXEC(@sSql)

This works but has a big problem, it can’t grab more than 4000 characters , to solve this  there are SQL “Magic Table” which stores last row edited by query
To clone this row you can use:

SELECT * INTO TMP_INSERTED FROM INSERTED
SELECT * INTO TMP_DELETED FROM DELETED

and you can inspect affected row.

LWEBCODE on Facebook, Get Like and Share buttons on your website or custom URL

Hi all, LWEBCODE is now on facebook

To get Facebook like or share button to a custom page, refer to this address: https://developers.facebook.com/docs/plugins/like-button
Click to Open

c# fast traslation of websites, Extract *.resx To *.xls

Hi All, Here a very quick Class which get all Entries to translate from resource *.resx file and make a Excel *.xls file with a list of all Entries.
Program writes entries in “A” column, 
In “B” (“C”,”D”…if you have more languages) Final user have to puts his language translation,
when done, after xls is saved and came back:
make a copy of *.resx file
With advanced text editor such as notepad++ or ultraedit after have make a copy of *.resx file, with “Find and Replace” tool I can easly traslate all the entries.
here the code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using Excel; // Add refer to Excel (in COM object Microsoft Excel Library )
using System.IO;

//#LWEBCODE
//Website Building & free script at http://lwebcode.blogspot.com/

namespace LWEBCODE
{
    public partial class ResxToXls
    {
        public static void MyStart()
        {
        string sPath =@"\\server\folder\" // where resx files are
        string DestFile = "", Title = "", ResxFile = "";
        string[] SourceFiles = Directory.GetFiles(sPath);
        if (SourceFiles.Length > 0)
        {
            for (int i = 0; i < SourceFiles.Length; i++)
            {
                if (SourceFiles[i].EndsWith(".resx"))
                {
                ResxFile = SourceFiles[i];
                Title = PageNameASPX(ResxFile);
                DestFile = ResxFile.Substring(0, ResxFile.LastIndexOf(@"\") + 1) + Title + ".xls";

                ReadResx(ResxFile, DestFile);
                }
            }
        }
        }

        public static void ReadResx(string ResxPath, string XlsPath)
        {
        Excel.ApplicationClass excel = new ApplicationClass();
        XmlTextReader xTr = new XmlTextReader(ResxPath);
        string ris = "";
        bool bwrite = true;
            while (xTr.Read())
            {
                // Do some work here on the data.
                //Console.WriteLine(xTr.Name);
                switch (xTr.NodeType)
                {
                case XmlNodeType.Element: // The node is an element.
                Console.Write("");

                break;
                case XmlNodeType.Text: //Display the text in each element.
                Console.WriteLine(xTr.Value);

                if (bwrite)
                ris += xTr.Value + ";";
                break;
                case XmlNodeType.EndElement: //Display the end of the element.
                Console.Write("");

                if ((xTr.Name.ToLower() == "comment") || (xTr.Name.ToLower() == "resheader"))
                bwrite = true;
                break;
                }
            }
        Console.Write(ris);
        Excel.Workbook workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        Excel.Sheets sheets = workbook.Worksheets;

        excel.Visible = true;
        Excel.Worksheet mySheet = (Excel.Worksheet)sheets.get_Item(1);

        string[] splRis = ris.Split(Convert.ToChar(";"));
        int ExRow = 0;
        for (int j = 1; j < splRis.Length; j++)
        {
            ExRow = j + 4;
            Excel.Range myCell = (Excel.Range)mySheet.get_Range("A" + ExRow.ToString(), "A" + ExRow.ToString());
            myCell.Value = splRis[j - 1];
        }
        workbook.SaveAs(XlsPath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null);
        excel.Quit();
        }

        public static string PageNameASPX(string ResxPath)
        {
        return ResxPath.Substring(ResxPath.LastIndexOf(@"\") + 1).Replace(".resx","");
        }
    }
}

C# GridView Runtime Change Header Text, AutoGenerateColumns=”true” AND AllowSorting =”True”

Hi All, here’s function to runtime Change Header Text with AutoGenerateColumns=”true” AND AllowSorting =”True” in ASPX GridView:

//#LWEBCODE
//Website Building & free script at http://lwebcode.blogspot.com/
 
protected void GVTestate_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.Header)
    {

        LinkButton lnk = new LinkButton();
        // I've hide My first column in my GridView
        // You should have to Start from index 0
        // lnk = (LinkButton)e.Row.Cells[0].Controls[0];

        lnk = (LinkButton)e.Row.Cells[1].Controls[0];
        lnk.Text = "HeaderText1"
        e.Row.Controls[1].Controls.Clear();
        e.Row.Controls[1].Controls.Add(lnk);

        lnk = (LinkButton)e.Row.Cells[2].Controls[0];
        lnk.Text = "HeaderText2"
        e.Row.Controls[2].Controls.Clear();
        e.Row.Controls[2].Controls.Add(lnk);

        lnk = (LinkButton)e.Row.Cells[3].Controls[0];
        lnk.Text = "HeaderText3"
        e.Row.Controls[3].Controls.Clear();
        e.Row.Controls[3].Controls.Add(lnk);

        lnk = (LinkButton)e.Row.Cells[4].Controls[0];
        lnk.Text = "HeaderText4"
        e.Row.Controls[4].Controls.Clear();
        e.Row.Controls[4].Controls.Add(lnk);

        lnk = (LinkButton)e.Row.Cells[5].Controls[0];
        lnk.Text = "HeaderText5"
        e.Row.Controls[5].Controls.Clear();
        e.Row.Controls[5].Controls.Add(lnk);

        lnk = (LinkButton)e.Row.Cells[6].Controls[0];
        lnk.Text = "HeaderText6"
        e.Row.Controls[6].Controls.Clear();
        e.Row.Controls[6].Controls.Add(lnk);
    }
}

C# RSS 2.0 Atom FEED Writer

Hi All, here a lwebode's C# class to generate a Feed rss 2.0 ,
it can generate by code or by SQL with dataset create a new class naming it “RSS_Writer” and just copy and paste code below.

//LWEBCODE C# RSS 2.0 Atom FEED Writer Usage example:

//To generate a feed by code (in this case rss will placed into root directory of websites):
string RssPath = Server.MapPath("../rss.xml");
RSS_Writer.NewRss(RssPath, "FEED Title", "www.exmple.com", "Header Description of feed", "en-us");
//to add a items programmatically:
RSS_Writer.AddItems(RssPath, "your title", "link to this article", "Description", "");

//to generate rss from SQL after have filled a Dataset (ds) :
RSS_Writer.CreateRssFromDataSet(RssPath, ds, "titleFiled", "LnkField", "DescrField", "GuidField");


//Here C# LWEBCODE RSS 2.0 Atom FEED Writer Class

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
using System.IO;


/// RSS_Writer
/// generated by LWEBCODE
/// http://lwebcode.blogspot.com/
///
///

public class RSS_Writer
{
public RSS_Writer()
{

//string path = HttpContext.Current.Server.MapPath("rss.xml");
public static int NewRss(string path, string sTitle, string sLink, string sDescription, string sLanguage)
{
    //XmlTextWriter writer = OpenXMLWriter(path);
    /*writer.WriteStartDocument();
    writer.WriteProcessingInstruction("xml-stylesheet", "type='text/xsl' href='lead.xsl'");
    writer.WriteStartElement("rss");
    writer.WriteAttributeString("version", "2.0");
    writer.WriteStartElement("channel");
    writer.WriteElementString("title", sTitle);
    writer.WriteElementString("link", sLink);
    writer.WriteElementString("description", sDescription);
    writer.WriteElementString("language", sLanguage);
    string GMTDate = System.TimeZone.CurrentTimeZone.ToUniversalTime(DateTime.Now).ToString("r");
    writer.WriteElementString("lastBuildDate", GMTDate);
    */
    XmlDocument xd = new XmlDocument();
    //xd.Load(path);
    XmlNode xn;
    xn = xd.CreateNode(XmlNodeType.Element, "rss", null);
    XmlAttribute xa;
    xa = xd.CreateAttribute("version");
    xa.Value = "2.0";
    xn.Attributes.Append(xa);
    xd.AppendChild(xn);
    xn = xd.CreateNode(XmlNodeType.Element, "channel", null);
    xd.SelectSingleNode("//rss").AppendChild(xn);
    xn = xd.CreateNode(XmlNodeType.Element, "title", null);
    xn.InnerText = sTitle;
    xd.SelectSingleNode("//channel").AppendChild(xn);
    xn = xd.CreateNode(XmlNodeType.Element, "link", null);
    xn.InnerText = sLink;
    xd.SelectSingleNode("//channel").InsertAfter(xn, xd.SelectSingleNode("//title"));
    xn = xd.CreateNode(XmlNodeType.Element, "description", null);
    xn.InnerText = sDescription;
    xd.SelectSingleNode("//channel").InsertAfter(xn, xd.SelectSingleNode("//link"));
    xn = xd.CreateNode(XmlNodeType.Element, "language", null);
    xn.InnerText = sLanguage;
    xd.SelectSingleNode("//channel").InsertAfter(xn, xd.SelectSingleNode("//description"));
    string GMTDate = System.TimeZone.CurrentTimeZone.ToUniversalTime(DateTime.Now).ToString("r");
    xn = xd.CreateNode(XmlNodeType.Element, "lastBuildDate", null);
    xn.InnerText = GMTDate;
    xd.SelectSingleNode("//channel").InsertAfter(xn, xd.SelectSingleNode("//language"));
    XmlTextWriter writer = OpenXMLWriter(path, false);
    xd.Save(writer);
    CloseXMLWriter(writer);
    return 0;
}
public static int AddItems(string path, string sTitle, string sLink, string sDescription, string sGuid)
{
    UpdateRssHEAD(path);
    XmlDocument xd = new XmlDocument();
    xd.Load(path);
    XmlNode xn = xd.CreateNode(XmlNodeType.Element, "item", null);
    XmlNode xn2;
    xn2 = xd.CreateNode(XmlNodeType.Element, "title", null);
    xn2.InnerText = sTitle;
    xn.AppendChild(xn2);
    xn2 = xd.CreateNode(XmlNodeType.Element, "link", null);
    xn2.InnerText = sLink;
    xn.AppendChild(xn2);
    xn2 = xd.CreateNode(XmlNodeType.Element, "description", null);
    xn2.InnerText = sDescription;
    xn.AppendChild(xn2);
    string GMTDate = System.TimeZone.CurrentTimeZone.ToUniversalTime(DateTime.Now).ToString("r");
    xn2 = xd.CreateNode(XmlNodeType.Element, "pubDate", null);
    xn2.InnerText = GMTDate;
    xn.AppendChild(xn2);
    xn2 = xd.CreateNode(XmlNodeType.Element, "guid", null);
    xn2.InnerText = sGuid;
    xn.AppendChild(xn2);
    xd.SelectSingleNode("//channel").InsertAfter(xn, xd.SelectSingleNode("//lastBuildDate"));
    XmlTextWriter writer = OpenXMLWriter(path, false);
    xd.Save(writer);
    CloseXMLWriter(writer);
    return 0;
}

public static int CreateRssFromDataSet(string path, DataSet ds, string sTitleField, string sLinkField, string sDescriptionField, string sGuidField)
{
    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
    {
    AddItems(path, ds.Tables[0].Rows[i][sTitleField].ToString(), ds.Tables[0].Rows[i][sLinkField].ToString(), ds.Tables[0].Rows[i][sDescriptionField].ToString(), ds.Tables[0].Rows[i][sGuidField].ToString());
    }
    return 0;
}

private static int UpdateRssHEAD(string path)
{
    XmlDocument xd = new XmlDocument();
    xd.Load(path);
    XmlNode nodeCh = xd.DocumentElement.SelectSingleNode("//channel");
    XmlNode xnd = xd.DocumentElement.SelectSingleNode("//lastBuildDate");
    string GMTDate = System.TimeZone.CurrentTimeZone.ToUniversalTime(DateTime.Now).ToString("r");
    xnd.InnerText = GMTDate;
    nodeCh.ReplaceChild(xnd, xnd);
    XmlTextWriter writer = OpenXMLWriter(path, false);
    xd.Save(writer);
    CloseXMLWriter(writer);
    return 0;
}
private static XmlTextWriter OpenXMLWriter(string path)
{
    XmlTextWriter writer;
    if (!File.Exists(path))
    {
        writer = new XmlTextWriter(path, System.Text.Encoding.UTF8);
    }
    else
    {
        Stream xmlFile = new System.IO.FileStream(path, FileMode.Append);
        writer = new XmlTextWriter(xmlFile, System.Text.Encoding.UTF8);
    }
    return writer;
}
    private static XmlTextWriter OpenXMLWriter(string path, bool Append)
    {
        XmlTextWriter writer;
        if ((!File.Exists(path)) || (!Append))
        {
        writer = new XmlTextWriter(path, System.Text.Encoding.UTF8);
        }
        else
        {
        Stream xmlFile = new System.IO.FileStream(path, FileMode.Append);
        writer = new XmlTextWriter(xmlFile, System.Text.Encoding.UTF8);
        }
    return writer;
    }
   
    private static void CloseXMLWriter(XmlTextWriter writer)
    {
    writer.WriteEndDocument();
    writer.Flush();
    writer.Close();
    }
}

/*
IMPORTANT NOTES:
Then to configure IIS, follow these steps:
Open IIS and navigate to the appropriate application/website
Right click and choose ‘Properties’ from the menu
Select the ‘HTTP Headers’ tab
There’s a section at the bottom entitled ‘MIME Map’, and from that click on ‘File Types’
Click ‘New Type’
For the ‘Associated extension’ enter .rss
And for ‘Content type (MIME)’ enter application/rss+xml
Click ‘OK’ and then ‘Apply’
Feel free to restart the IIS server, although you shouldn’t have to.
Ensure that the HTML page link to the RSS file includes the RSS extension, e.g.
*/
}

C# CSS Stylesheet Compression, fast website loading

Hi All, Here's a static class developed in c# to compress Css stylesheets,
It removes all “\r\n” (newline carriage return) , all “\t” (tab) all spaces before and after “:” “;” , it also remove all comment from your css.
Thanks to this function Stylesheet should be more light and fast for web and you can remove additionals spaces manually for complete optimization .
You can copy your css and pass it to class with:
textBox2.Text = LWEBCODE.Get_CSS_RTM(textBox1.Text);
I suggest to make a form o webform with 2 textboxes and paste in textbox1 your css stylesheet ,result can be show in textbox2 , after paste into css_runtime_sheet.css, 
REMEMBER on pagae_load event or in <head> tag to correctly set the right stylesheet: version for development and debug, or runtime version
Please for any optimization or suggestion contact us.
Here the code:


using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;

/*
Css Runtime Generator, compressor
developed by: lwebcode;
for other script and resource :
http://lwebcode.blogspot.com/
*/
class LWEBCODE
{
    public static string Get_CSS_RTM(string sInpunt)
    {
    string sOut = "";
    char[] c = System.Environment.NewLine.ToCharArray();
    string[] sRows = new string[] { };
    string[] sRow = new string[] { };
    bool Esc1 = false;
    bool Esc2 = false;
    bool Esc3 = false;

    sRows = Regex.Split(sInpunt, "\t");
    for (int i = 0; i 0)
    {
        if (sRows[i].IndexOf(Convert.ToChar("-")) < 0)
        {
        sRows[i] = sRows[i].Trim();
        }
    }
    sRow = SplitChar(sRows[i]);
    for (int j = 0; j < sRow.Length; j++)
    {
        if (sRow[j] == "/")
        {
            if (j < sRow.Length - 1)
            {
            if (sRow[j + 1] == "*")
            {
            Esc1 = true;
            }
        }
    }
    if (sRow[j] == "*")
    {
        if (j < sRow.Length - 1)
        {
            if (sRow[j + 1] == "/")
            {
            Esc1 = false;
            j = j + 2;
            }
        }
    }

    if (j < sRow.Length)
    {
    char[] c2 = sRow[j].ToCharArray();
        if ((c[0] == c2[0]) || (c[1] == c2[0]))
        Esc2 = true;
        else
        Esc2 = false;
    }
    if (j < sRow.Length)
    {
    char[] c2 = sRow[j].ToCharArray();
    if (c2[0] == Convert.ToChar(" "))
    {
        if ((sRow[j - 1] == ":") || (sRow[j - 1] == ";"))
        Esc3 = true;
        else
        Esc3 = false;
        if ((j + 1 < sRow.Length) && (!Esc3))
        {
            if ((sRow[j + 1] == ":") || (sRow[j + 1] == ";"))
            Esc3 = true;
            else
            Esc3 = false;
        }
    }
    else
    {
    Esc3 = false;
    }
    }

    if ((!Esc1) && (!Esc2) && (!Esc3))
    {
        if (j <= sRow.Length - 1)
        {
        sOut += sRow[j];
        }
    }
    }
    return sOut;
    }

    private static string[] SplitChar(string sIn)
    {
        string[] sInSpl;
        sInSpl = new string[sIn.Length];
        for (int i = 0; i < sIn.Length; i++)
        {
        sInSpl[i] = sIn.Substring(i, 1);
        }
        return sInSpl;
    }

}

SQL Dynamic table’s name in SELECT COUNT(*),TOP 1, MAX etc..

Hi All, here a easy lwebcode's sql script, to make a SELECT COUNT(*), or other sql statements wich return only 1 result (SELECT TOP 1, SELECT MAX, etc..) with dynamic tables name and put in a parameter o variable, 
It works well in All Sql Server versions, from MS SQL Server 2000 to last SQL Server 2016 

--LWEBCODE SQL SCRIPT Dynamic table's name
Declare @tableName varchar(100),
@tableCount int,
@sql nvarchar(100)
Set @tableName = 'Products'
Set @sql = N'Select @tableCount = Count(*) from ' + @tableName
exec sp_executesql @sql, N'@tableCount int output', @tableCount output
Select @tableCount

LWEBCODE Twitter Account

Follow LWEBCODE ON Twitter:

Follow @LWebCode

VBA get Word Excel Outlook Path without reference and declaration, late binding

Hi All, here an example to know Word, Excel or Outlook .exe path, without adding reference to object
Here the function, copy and paste it to vba module

Function GetProgFilesPath(ByVal sExecutable As String) As String
    On Error GoTo Err_Exit
    Dim obj As Object
    Select Case LCase(sExecutable)
        Case "winword.exe", "word.exe"
            Set obj = CreateObject("Word.Application")
        Case "excel.exe"
            Set obj = CreateObject("Excel.Application")
        Case "outlook.exe"
            Set obj = CreateObject("Outlook.Application")
        Case Else
            MsgBox "Not Managed" & sExecutable, vbInformation
            Exit Function
        End Select
    GetProgFilesPath = obj.path
    Set obj = Nothing

    Exit_Exit:
    Exit Function
    Err_Exit:
        MsgBox "Err:GetProgFilesPath=" & Err.Description
    Resume Exit_Exit
End Function

SQL CURSOR, looping on talbe's or view's rows with Cursor like Recordset or DataReader

Hi All, here an example on how loop on some rows in SQL Stored Procedure, like a visual basic RecordSet or .NET Framework DataReader, simple put your query at line 7 of script:

SET @SQL ='SELECT Field1,Field2 FROM Table_Name '

That's all, We have spent lot of time to find an example which works, finally it comes:








--LWebCode SQL Script
DECLARE @SQL NVARCHAR (4000)
DECLARE @DynamicSQL NVARCHAR(250)
DECLARE @Field1 NVARCHAR(100)
DECLARE @Field2 NVARCHAR(100)
DECLARE @outputCursor CURSOR
SET @SQL ='SELECT Field1,Field2 FROM Table_Name '
SET @DynamicSQL = 'SET @outputCursor = CURSOR FORWARD_ONLY STATIC FOR ' +
@SQL + ' ; OPEN @outputCursor'
exec sp_executesql -- sp_executesql will essentially create a sproc
@DynamicSQL, -- The SQL statement to execute (body of sproc)
N'@outputCursor CURSOR OUTPUT', -- The parameter list for the sproc: OUTPUT CURSOR
@outputCursor OUTPUT
FETCH NEXT FROM @outputCursor INTO @Field1,@IDField2
WHILE @@FETCH_STATUS = 0
BEGIN
/*here is loop put your code here*/
SET @SQL='SELECT * FROM t_name WHERE Field1=''' + @Field1 + ''''
EXEC SP_EXECUTESQL @SQL
FETCH NEXT FROM @outputCursor INTO @Field1,@Field2
END

C# - SQL Class Implementation

Hi All, Here LWebCode's class to directly run SQL statements whitout declare and initialize Connection and Command objects.
The class works for aspx page and also for Windows Forms, 
in windows forms remember to change initial parameters like connection string, CodeID ect..

Very useful to write less code
Optionally in 2 ArrayList we can pass parameters and relative values

Class usage Example:

//Put this at top of your *.cs file in aspx page, or Windows Form Class
using LWebCode;

//simple calling function 
public void Calls_iSQL_Class()
{
string cnString = ConfigurationManager.ConnectionStrings[0].ConnectionString;
string sCodeID = Request.QueryString["qryPar1"].ToString();
string sDate = Request.QueryString["qryPar2"].ToString();
ArrayList aFields = new ArrayList();
ArrayList aValues = new ArrayList();

                    aFields.Add("@CODEID");
                    aFields.Add("@DATE"); 


                    aValues.Add(sCodeID);
                    aValues.Add(sDate);

//Performing SQL INSERT and UPDATE Statement:
//Call Example with Parameters more secure          
int res = iSQL.ExecuteNonQuery(cnString , "INSERT INTO MY_TABLE (CODE,DATE_R) VALUES (@CODEID,@DATE)",aFields ,aValues);   

//Call Example without Parameters less secure don't use it, or use it carrefully  recomended use for debugging SQL statement

int res2 = iSQL.ExecuteNonQuery(cnString , "UPDATE MY_TABLE SET DATE_R=NULL WHERE CODE=1235LWEBCODE", null , null);   

//Performing Fast Reading Value valid only for 1 record with SQL SELECT Statemet
string sres = iSQL.ExecuteReader(cnString , "SELECT TOP 1 DATE_R FROM MY_TABLE WHERE CODE=1235LWEBCODE", null , null);
                   
}

HERE THE LWebCode's CLASS:



using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;

///
/// Class to execute sql statement like SELECT,UPDATE,INSERT other
/// whitout initialize connection and command
///
namespace LWebCode
{
public class iSQL
{
    public iSQL()
    {
       
    }

    ///
    /// Esegue un ExecuteNonQuery con un Singolo Parametro
    ///
    /// The Connection Strings
    /// Sql Statements to execute
    /// Field Add '@'
    /// Value
    ///
    public static int ExecuteNonQuery(string CnString,string SQL,string Campo,string Valore)
    {
         cn = new SqlConnection(CnString);
        cn.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cn;

        cmd.Parameters.AddWithValue(Campo, Valore);
       
        cmd.CommandText = SQL;

        int ris =  cmd.ExecuteNonQuery();
        cn.Close();
        cn.Dispose();
        cmd.Dispose();
        return ris;
    }

    ///
    /// Execute ExecuteScalar with multiple Parameters
    ///
    /// The Connection Strings
    /// Sql Statements to execute
    /// Fields Array with @
    /// Values Array
    ///
    public static int ExecuteNonQuery(string CnString, string SQL, ArrayList Fields ,ArrayList Values)
    {
       
            SqlConnection cn = new SqlConnection(CnString);
            cn.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;

            for (int i = 0; i < Fields.Count; i++)
            {
                cmd.Parameters.AddWithValue(Fields[i].ToString(), Values[i]);
            }

            cmd.CommandText = SQL;

            int ris=cmd.ExecuteNonQuery();
           
            cn.Close();
            cn.Dispose();
            cmd.Dispose();

            return ris;
    }

    ///
    /// Execute ExecuteScalar with multiple Parameters
    ///
    /// The Connection Strings
    /// Sql Statements to execute
    /// Fields Array
    /// Values Array
    ///
    public static int ExecuteScalar(string CnString, string SQL, ArrayList Fields, ArrayList Values)
    {

        SqlConnection cn = new SqlConnection(CnString);
        cn.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cn;

        for (int i = 0; i < Fields.Count; i++)
        {
            cmd.Parameters.AddWithValue(Fields[i].ToString(), Values[i]);
        }

        cmd.CommandText = SQL;

        int ris = Convert.ToInt32(cmd.ExecuteScalar());

        cn.Close();
        cn.Dispose();
        cmd.Dispose();

        return ris;
    }

    ///
    /// Execute Reader which return only 1 value
    ///
    /// The Connection Strings
    /// Sql Statements to execute
    /// Fields Array
    /// Values Array
    ///
    public static string ExecuteReader(string CnString, string SQL, ArrayList Fields, ArrayList Values)
    {
        SqlConnection cn = new SqlConnection(CnString);
        cn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandText = SQL;
        for (int i = 0; i < Fields.Count; i++)
        {
            cmd.Parameters.AddWithValue(Fields[i].ToString(), Values[i]);
        }

        SqlDataReader rd;
        rd = cmd.ExecuteReader();
        string a = "";
        while (rd.Read())
        {
            a = rd[0].ToString();
        }
        cmd.Dispose();
        cn.Close();
        rd.Close();
        rd.Dispose();

        return a;
    }

   
}
}