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;
    }

   
}
}

VBA (Excel) – Add Single Quote, Excel text cells, Zeros 0s Fix

Hill All, happens to copy and paste from SQL tables or web Page to Excel files, if some fields begin with Zero 
example:“0000545LWEBCODE” , 
when paste to Excel Zeros 0s before other numbers were removed 
example:“545LWEBCODE”
To fix Zeros 0 before number and other things , Here is VBA funcion/Script which add a single quote ‘ before each cells: you can copy from any external platform like Internet, SQL Tables, SQL Views etc.., and paste it in your Microsoft Excel sheet,
Just Copy and Paste this code in a Module a run it.


‘** VBA Script Excel Zeros 0s Fix Script by lwebcode.blogspot.com
Public Sub AddQuote()
Dim Start_, End_, Col, i As Integer
Dim SName As String

Start_ = InputBox(“Row Start:”, “Start”, “1”)
End_ = InputBox(“Row End:”, “End”, “10000”)

Col = CInt(InputBox(“Column (in Number) to Add Quote”, “Column”, “1”))

SName = InputBox(“Sheets’s Name:”, “Sheets”, Sheets(1).Name)

For i = Start_ To End_
    If Sheets(SName).Cells(i, Col).Value “” Then
        Sheets(SName).Cells(i, Col).Value = “‘” & Sheets(SName).Cells(i, Col).Value
    End If
Next

MsgBox “Executed”, vbInformation, “Executed”

End Sub

SQL - Double Rows

Hi All, here a fast script to find double rows!
Happens to have some table/s with 2 or more rows which have the same value on field, to find this rows here there's a script which select all double rows in table, 
After you can choose to delete or analize why there are double.

--lwebcode Select all double rows in a table
SELECT     City, Address, Zip, Year,  COUNT(*) AS Expr1
FROM        MyTable
GROUP BY  City, Address, Zip, Year
HAVING      (COUNT(*) > 1)

--lwebcode For a single  column double rows:
SELECT     City  COUNT(*) AS Expr1
FROM        MyTable
GROUP BY  City
HAVING      (COUNT(*) > 1)

SQL – Servers, Databases, Tables & Fields List

Hi All, here a very easy SQL script to get  Servers, Databases, Tables & Fields of  Table
 
To see other property of fields put ” ,*”  after COLUMN_NAME

1) Servers List:

SELECT * FROM master.dbo.sysservers

2)Databases List:

SELECT * FROM master.dbo.sysdatabases

3)Tables/Views List:

SELECT     TABLE_NAME
FROM         INFORMATION_SCHEMA.TABLES
WHERE     (TABLE_TYPE = ‘BASE TABLE’)

Change ‘BASE TABLE’ to ‘VIEWS’ to get Views list

4)Table's Field/Columns List:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =’MyTalbe_Name’

C# HTML to PDF Converter, iTextSharp

Hi All, we 've made lot of research to make this super useful function in c# to convert HTML to PDF,
with help of fantastic free library iTextSharp:
https://sourceforge.net/projects/itextsharp/
With only 6 rows it's done!
Warning!: HTML code must be very clear, simple and clean(CSS was not fully supported, is better use attributes to element if exists)
Example:
<!--lwebcode:inside yout html code-->
<div style='font-size:15px;text-align:left;'>Title Of Page<br/></div> ==> Works
<td align='center' style='width:15%' >cell content</td> ==> DOESN'T Works
<td align='center' width='15%' >cell content</td> ==> Works

//lwebcode:inside *.cs file
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using iTextSharp.text.html.simpleparser;
using iTextSharp.tool.xml;

//lwebcode:inside your class lwebcode
private void ConvertHtmlToPdf(string sHTML)
    {
        Document document = new Document();
        PdfWriter.GetInstance(document, new FileStream(Server.MapPath("~/OutPath/TargetName.pdf"), FileMode.Create));
        document.Open();
        iTextSharp.text.html.simpleparser.HTMLWorker hw = new iTextSharp.text.html.simpleparser.HTMLWorker(document);
        hw.Parse(new StringReader(sHTML));
        document.Close();
    }

SQL - Fast Table Copy

Hi All, this script is very easy,a simple and fast solution to copy (structure and data) a table :

SELECT * INTO Table_Destination_Name FROM Table_Source_Name

Warning:Using this script Index aren't copied in new table (Table_Destination_Name) for adds it you have to add it manually.

C# AJAX TextBox Cursor,Caret Focus with AutoPostBack="True" OnTextChanged="TextBox1_TextChanged"

Hi All, here explain how to mantain get and set cursor/caret position after a c# with AJAX Textbox TextChanged with AutoPostBack="True", this solution is cross browser compatible.
Put this Javascript code in your *.aspx Page:

<script language="javascript" type="text/javascript" >
//lwebcode.bloggger.com
//http://blog.vishalon.net/javascript-getting-and-setting-caret-position-in-textarea
var selstart=0;
var selend=0;

 var pos = 0;
 function GetPos(el)
 {
    if("selectionStart" in el) {
       pos = el.selectionStart;
    } else if("selection" in document) {
       el.focus();
       var Sel = document.selection.createRange();
       var SelLength = document.selection.createRange().text.length;
       Sel.moveStart("character", -el.value.length);
       pos = Sel.text.length - SelLength;
    }
    return pos;
 }

 function SetPos(ctrl, mypos)
 {
        if(ctrl.setSelectionRange)
        {
            ctrl.focus();
            ctrl.setSelectionRange(mypos, mypos);
        }
        else if (ctrl.createTextRange)
        {
            var range = ctrl.createTextRange();
            range.collapse(true);
            range.moveEnd('character', mypos);
            range.moveStart('character', mypos);
            range.select();
        }
  }
</script>

Always in your *.aspx page:

<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True" OnTextChanged="TextBox1_TextChanged" > 
<asp:TextBox ID="TextBox2" runat="server" AutoPostBack="True" OnTextChanged="TextBox2_TextChanged" >

On Page Code behind *.apsx.cs

private const string SCRIPT_DOFOCUS =
        @"window.setTimeout('DoFocus()', 1);
    function DoFocus()
    {
        try {
            document.getElementById('REQUEST_LASTFOCUS').focus();
            SetPos(document.getElementById('REQUEST_LASTFOCUS'), pos);
        } catch (ex) {}
    }";

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            HookOnFocus(this.Page as Control);
        }
        ScriptManager.RegisterStartupScript(this, typeof(Page), "ScriptDoFocus", SCRIPT_DOFOCUS.Replace("REQUEST_LASTFOCUS", Request["__LASTFOCUS"]), true);
    }
   
    private void HookOnFocus(Control CurrentControl)
    {
        //checks if control is one of TextBox, DropDownList, ListBox or Button
        if ((CurrentControl is TextBox)
            /*|| 
            (CurrentControl is DropDownList) ||
            (CurrentControl is ListBox) ||
            (CurrentControl is Button)*/
           
            )
            //adds a script which saves active control on receiving focus
            //in the hidden field __LASTFOCUS.
            (CurrentControl as WebControl).Attributes.Add(
               "onfocus",
               "try{document.getElementById('__LASTFOCUS').value=this.id; GetPos(this);} catch(e) {}");
        //checks if the control has children
        if (CurrentControl.HasControls())
            //if yes do them all recursively
            foreach (Control CurrentChildControl in CurrentControl.Controls)
                HookOnFocus(CurrentChildControl);
    }

protected void TextBox1_TextChanged(object sender, EventArgs e) 
{ //your code... }
protected void TextBox2_TextChanged(object sender, EventArgs e) 
{ //your code... }