// This class can be used to generate SQL.
Usage:
Query q = Query.Instance().Select("Associates.AID", "Associates.City", "Associates.Country", "Associates.Created_By", "Entities.Name")
                            .From("Associates", "Entities")
                            .Where("Associates.AID", Operator.Equals, "Entities.AID")
                            .And("Associates.AID", Operator.Equals, "12345")
                            .GroupBy("Associates.AID", "Entities.EID");
<code>
public class Query
    {
        private StringBuilder _sql = new StringBuilder(1024);
        private string[] _selectParams;
        private string[] _tableParams;
        private string[] _groupByParams;
        private List<string> _filterParams = new List<string>();

        public Query Select(params string[] selectParams)
        {
            _selectParams = selectParams;
            return this;
        }

        public static Query Instance()
        {
            return new Query();
        }

        public Query Where(string key, Operator op, string value)
        {
            _filterParams.Add(string.Format("{0} {1} {2}", key, GetOperator(op), value));
            return this;
        }

        public Query From(params string[] tableParams)
        {
            _tableParams = tableParams;
            return this;
        }

        private string GetOperator(Operator op)
        {
            switch (op)
            {
                case Operator.Equals: return "=";
                case Operator.GreaterThan: return ">";
                case Operator.GreaterThanEqualTo: return ">=";
                case Operator.LessThan: return "<";
                case Operator.LessThanEqualTo: return "<=";
                default: return "";
            }
        }

        public Query And(string key, Operator op, string value)
        {
            _filterParams.Add(string.Format("{0} {1} {2}", key, GetOperator(op), value));
            return this;
        }

        public Query GroupBy(params string[] groupByParams)
        {
            _groupByParams = groupByParams;
            return this;
        }

        public string ToSql()
        {
            _sql.Append(string.Format(" SELECT {0}", string.Join(", ", _selectParams)));
            _sql.Append("\r\n");
            _sql.Append(" FROM " + string.Join(", ", _tableParams));
            _sql.Append("\r\n");
            _sql.Append(" WHERE ");
            _sql.Append(string.Join(" AND ", _filterParams.ToArray()));
            _sql.Append("\r\n");
            _sql.Append(" GROUP BY " + string.Join(", ", _groupByParams));
            return _sql.ToString();
        }
    }

public enum Operator
    {
        Equals,
        LessThan,
        LessThanEqualTo,
        GreaterThan,
        GreaterThanEqualTo
    }
</code>                

                    Absurdly simple but utilitarian function returns a numeric array of associative arrays containing an entire result set.

<code>function mysql_fetch_all($result) {
    $all = array();
    while ($all[] = mysql_fetch_assoc($result)) {}
    return $all;
}</code>                

                    Absurdly simple but utilitarian function returns a numeric array of associative arrays containing an entire result set.

<code>function mysql_fetch_all($result) {
    $all = array();
    while ($all[] = mysql_fetch_assoc($result)) {}
    return $all;
}</code>                

                    // delete data from table using inner join

<code>
DELETE table1 t1 FROM table1 INNER JOIN table2 t2 ON t1.key = t2.key WHERE t1.column1 = <condition>
</code>                

                    // update table using inner join sql server

<code>
UPDATE table1 t1 SET column1=value
  FROM table1 INNER JOIN table2 t2 ON t1.key = t2.key
</code>                

                    // Query to get all the datails for transaction locks in sql server

<code>
SELECT  L.request_session_id AS SPID, 
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName, 
        P.object_id AS LockedObjectId, 
        L.resource_type AS LockedResource, 
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,        
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id
</code>                

                    // case  and if else statement in sql server

<code>

DECLARE @MyVal INT

DECLARE @OUTPUTValues VARCHAR(200)
SET @MyVal = 1

SELECT @OUTPUTValues =
(
CASE  @MyVal
        WHEN 1 THEN 'test1'
        WHEN 2 THEN 'test2'
        WHEN 3 THEN 'test3'
        ELSE 'New'
END
)

PRINT @OUTPUTValues

SET @TestVal = 5

IF @MyVal= 1
SET @OUTPUTValues= '1'
ELSE IF @MyVal= 2
SET @OUTPUTValues= '2'
ELSE IF @MyVal= 3
SET @OUTPUTValues= '3'
ELSE
SET @OUTPUTValues= @MyVal

PRINT @OUTPUTValues

</code>                

                    // Execute script file of sql server from vb.net code

<code>

Public Shared Function ExceuteScriptFile(ByVal ScriptFilePath As String, Optional ByVal MyConnectionKey As String ) As Integer
        Dim db As Database
        Dim MyServer As String = AppSettings("Server")
        Dim MyUserName As String = AppSettings("userid")
        Dim MyPassword As String = AppSettings("password")
        Dim MyDbName As String = AppSettings("DBName")

        Dim ScriptExeceute As New Process
        Dim osqlParams As String = ""
        db = DatabaseFactory.CreateDatabase(MyConnectionKey )
        ScriptFilePath = """" + ScriptFilePath + """"
        osqlParams = String.Format("-S {0} -U {1} -P {2} -d {3} -i ", MyServer, MyUserName, MyPassword, MyDbName)
        ScriptExeceute.StartInfo.FileName = "sqlcmd.exe"
       
        Try
            ScriptExeceute.StartInfo.Arguments = osqlParams & ScriptFilePath
            ScriptExeceute.StartInfo.WindowStyle = ProcessWindowStyle.Hidden
            ScriptExeceute.Start()
            ScriptExeceute.WaitForExit()
        Catch ex As Exception

        End Try
        Return 0
End Function

</code>                

                    // enable disable all the constraints of data base sql server

<code>

---For ENABLING

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

---To DISABLE all the constraints
EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"
GO

</code>                

                    // function for getting comma seperated values in sql server 

<code>


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

-- This function splits a variable-length parameter array (actually a string
-- with comma as a delimiter) and stored the values into the table.
-- CHARINDEX() function is used to identify the position of the first delimiter
-- in the text and SUBSTRING() function is used to set the 'element' column.

ALTER FUNCTION [dbo].[GetCSVValues](
    @string    varchar(550) -- '1,2,3,5,6,7'
)
RETURNS @table TABLE(element int)
AS
BEGIN
DECLARE @tempvarchar(550),
    @delimPos AS tinyint
     
SET @delimPos = 0         
SET @temp= LTRIM(RTRIM(@string))
        
WHILE CHARINDEX(',',@temp) > 0 
 BEGIN 
 SET @delimPos = CHARINDEX(',',@temp)
 INSERT INTO @table(element) VALUES (CAST((LEFT(@temp,@delimPos-1)) AS smallint))

 SET @temp= RTRIM(LTRIM(SUBSTRING(@temp,@delimPos+1,LEN(@temp)-@delimPos))) 
 END 

INSERT INTO @table(element) VALUES (CAST((@temp) AS smallint))

RETURN
END

</code>