Jerry's Tech Stuff

//
// GlossaryController.cs

public ActionResult Index()
{
    return View(Models.Term.SelectAll());
}

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Index(FormCollection collection)
{
    // used by non-AJAX form 
    string _SearchTerm;
    _SearchTerm = collection["SearchTerm"];
    // don't indicate the partial here, the view will call it
    return View(Models.Term.Where(x => x.Term.Contians(_SearchTerm)));
}

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Index2(FormCollection collection)
{
    // used by AJAX form 
    string _SearchTerm;
    _SearchTerm = collection["SearchTerm"];
    // you must indicate the partial here, it's all you want to render
    return View("Search", Models.Term.Where(x => x.Term.Contians(_SearchTerm)));
}

// The view: Index.aspx

<!-- the non-AJAX form to filter the list -->
<% using (Html.BeginForm()) { %>
    <fieldset>
    <legend>Search</legend>
    <input name="SearchTerm" />
    <input type="submit" value="Go" />
    </fieldset>
<% } %>

<!-- the AJAX form to filter the list -->
<script src="/Scripts/MicrosoftAjax.js" type="text/javascript"></script>
<script src="/Scripts/MicrosoftMvcAjax.js" type="text/javascript"></script>
<% using (Ajax.BeginForm("Index2", new AjaxOptions { UpdateTargetId = "results" })) { %>
    <fieldset>
    <legend>Search</legend>
    <input name="SearchTerm" />
    <input type="submit" value="Go" />
    </fieldset>
<% } %>

<!-- render the partial inside the view -->
<div id="results">
    <% Html.RenderPartial("Search", Model); %>
</div>

// The partial: Search.ascx (a default LIST T4 template, nothing special/custom)

-- old-school variables ;)

declare @x int
declare @y int
declare @z int

set @x = 1
set @y = 2
set @z = 3

-- combined initialization

declare 
     @a int
    ,@b int
    ,@c int

-- compound assigned

declare 
     @d int = 1
    ,@e int = 2
    ,@f int = 3

-- compound operator

set @i += 8
set @i -= 8
set @i /= 8
set @i *= 8
set @i %= 3

-- bitwise operators

set @i &= 1
set @i |= 1
set @i ^= 1

insert into Sales values
     (1999, 'Q1', 'Jerry', 123)
    ,(1999, 'Q2', 'Jerry', 234)
    ,(1999, 'Q3', 'Jerry', 345)
    ,(1999, 'Q4', 'Jerry', 456)

select * from
(
    values (1, 2), (2, 3), (3, 4)
) as DerivedTable(Col1, Col2)

-- ROLLUP = Generates the simple GROUP BY aggregate rows, plus subtotal or super-aggregate rows, and also a grand total row.

select
     Year
    ,AVG(Amount) as AverageSales
from 
    Sales
group by
    Year WITH ROLLUP

-- GROUPING = Indicates whether a specified column expression in a GROUP BY list is aggregated or not.

select
     Year
    ,AVG(Amount) as Average
    ,GROUPING(Year) as [YearRollUp?]
from 
    Sales
group by
    Year WITH ROLLUP

-- GROUPING SET = Specifies multiple groupings of data in one query. 

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
    ,GROUPING(Year) as [YearRollUp?]
from 
    Sales
group by
    GROUPING SETS ((Year, Quarter))

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
    ,GROUPING(Year) as [YearRollUp?]
from 
    Sales
group by
    GROUPING SETS ((Year, Quarter), (Year), ())

-- GROUPING_ID = Is a function that computes the level of grouping.

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
    ,GROUPING_ID(Year, Quarter) as [x]
from 
    Sales
group by
    GROUPING SETS ((Year, Quarter), (Year), ())

-- use it in the having

declare @level int = 1

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
    ,GROUPING_ID(Year, Quarter) as [x]
from 
    Sales
group by
    GROUPING SETS ((Year, Quarter), (Year), ())
having
    GROUPING_ID(Year, Quarter) = @level
    

-- CUBE = Generates simple GROUP BY aggregate rows, the ROLLUP super-aggregate rows, and cross-tabulation rows.

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
from 
    Sales
group by
    cube(Year, Quarter, SalesPerson)    
order by
    Year, Quarter, Average

// tSQL setup scripts
// create the SQL table

create table Bugs
(
     BugId int primary key
 identity(1,1)
    ,Title varchar(50)
    ,Details varchar(50)
)

// create the SQL table TYPE

create type BugsType as table
(
     Title varchar(50)
    ,Details varchar(50)
)

// create the SQL insert stored proc

create proc BugsInsert
(@x BugsType readonly) as
    set nocount on
    insert into Bugs 
        select * from @x

declare @p BugsType
insert into @p values
     ('Login Fails', 'Password rejected')
    ,('Login Fails', 'Username rejected')
exec BugsInsert @p

// C# // how to insert a table-type parameter using ADO.Net

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace TableValuedParameters
{
    public partial class Form1 : Form
    {
        DataTable m_DataTable = new DataTable();
        public Form1()
        {
            InitializeComponent();
            m_DataTable.Columns.Add(new DataColumn("Title", typeof(string)));
            m_DataTable.Columns.Add(new DataColumn("Details", typeof(string)));
            // you will need to add your own dataGridView to your form
            dataGridView1.DataSource = m_DataTable;
        }

        // this is the save button event handler (this form only inserts)
        private void button1_Click(object sender, EventArgs e)
        {
            string _ConnStr = @"server=.\sql2008express;database=jerry;integrated security=sspi;";
            using (SqlConnection _SqlConnection = new SqlConnection(_ConnStr))
            {
                _SqlConnection.Open();
                SqlCommand _SqlCommand = new SqlCommand("BugsInsert", _SqlConnection);
                _SqlCommand.CommandType = CommandType.StoredProcedure;
                // here's the magic, pass a datatable
                _SqlCommand.Parameters.AddWithValue("x", m_DataTable);
                _SqlCommand.ExecuteNonQuery();
            }
            this.Close();
        }
    }
}