Share This!

Monday, August 3, 2015

Select a List of Column Names in SQL Server

This gives a list of all the tables and columns in the tables in your database.  The TABLES table was included to exclude views.
inner join
        and Tables.TABLE_SCHEMA = Columns.TABLE_SCHEMA
        and Tables.TABLE_TYPE = 'BASE TABLE'


Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Sunday, June 1, 2014

Easy Linq Example

Linq is a great tool to introduce the power of SQL to the C# environment.  I have found lots of uses for Linq, but this is just a quick sample to get you started.

 //Create a list of random integers
            List<int> myList = new List<int>();
            Random random = new Random();
            for (int i = 0; i < 1000; i++)
                myList.Add(random.Next(0, 500));

            //list is loaded
            //find the values > 10, eliminate dups, and sort.
            //now watch Linq in action

            List<int> filteredList = myList
                .Where<int>(r => r > 10)
                .OrderBy(r => (int)r).ToList();

            int fullList = myList.Count();
            int distinctList = filteredList.Count();

            //pop up the counts
            MessageBox.Show(string.Format("{0} records, {1} unique values.", fullList, distinctList));

            //pop up the list
            MessageBox.Show(string.Join(", ",filteredList.ToList()));


Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Saturday, May 24, 2014


I have heard it a thousand times.  Web pages are stateless.  That means that - unlike desktop apps where you load a form with data and it stays loaded as long as the app is running - a web page loads. renders, and the webserver immediately forgets everything.

At least that's the way it was.

In the bad old days, web designers would put hidden fields on the form, and hide data there, sometimes raw data, and sometimes just a handle to session data being stored on the webserver, or in a database.

But these days, our frameworks have built-in storage for state information, and some components save their state data by default. Note the differences:

Create an HTML form (even in an ASP.NET site) and put this code in it.

<!DOCTYPE html>
<html xmlns="">
    <form id="thisform" action="HtmlPage.html" method="get">
        <input id="Text1" type="text" />
        <input id="Button1" type="submit" value="Post" />

This is the oldschool way of doing form posts.  Note that if you click the submit button, the text you put in the text box will disappear.  That's because it's state (value) is not being stored.  Now create an form.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="">
<head runat="server">
    <form id="form1" runat="server">
            <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
            <asp:Button ID="Button1" runat="server" Text="Button" />

Note that this example remembers the values typed in its boxes on a postback.  Now, in the browser, do a view page source.

Notice that there is a line in there that looks like this:

<input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE" value="E+wH9na5ksRkcpEtk56HJ50O8S2k8vu5dqruZeQtCdLxTdF9WHAcmxUsqAeaxzlLld9hLgv6LIwp+KFpLzzJEdyW2AOL2gz/b2NZ2SOhfx0=" />
That's the automatic way ASP.NET stores state information on the web page.  If you pick the textbox and look at its properties, you'll notice one called EnableViewstate and another called ViewstateMode.  Saving state is enabled by default.

So this gets us to the two ways to store state information on an ASP.NET page.
  • Viewstate
  • Session


As discussed, viewstate stores the status of all viewstate-enabled controls.  But you can also add stuff to Viewstate, and read it out again, even complex objects.
            //add a key
            ViewState.Add("myKey", "myValue");

            //store complex objects
            DataSet myData = new DataSet();
            ViewState.Add("data", myData);

            //change a key
            ViewState["myKey"] = "myNewValue";

            //read a key
            string data = (string)ViewState["myKey"];

            //delete a key

Note the following:
  1. ViewState lives in the page.  It does not flow from page to page. 
  2. Multiple users (like different computers and even 2 different browsers on the same computer do not share ViewState).
  3. ViewState is only read on a POSTBACK, not on an initial load of the page.
  4. I have found that storing things in ViewState can result in hard-to-debug errors that make your development life a living hell.
learn more


Session is like ViewState, in that it is created automatically whether you want it or not. Session is linked to the user, not the page.  When someone browses to your page and there is no session ID, a new session is created.  Some features of sessions are:
  • Sessions time out after a period of inactivity.
  • Session data persists for that user until the session times out
  • Sessions can store complex items like ViewState
  • Sessions are unique, not shared if more than one browser is opened
Using sessions is easy, and since regular objects do not do anything with sessions, it's not likely to get you trapped into the impossible-to-diagnose error loop you get with ViewState.

            //add a key
            Session.Add("myKey", "myValue");

            //store complex objects
            DataSet mySessionData = new DataSet();
            Session.Add("data", mySessionData);

            //change a key
            Session["myKey"] = "myNewValue";

            //read a key
            string data2 = (string)Session["myKey"];

            //delete a key

            //Special session methods
            if (Session.IsNewSession) { }
            string s = Session.SessionID;

Take care when using sessions. Note that the values are available to all pages on the site.  For instance, let's say you put a SortedBy value in multiple pages.  If the user travels from Customers sorted by LastName to the Orders page, the "SortedBy" key still says "LastName".
learn more


Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Thursday, January 9, 2014

Dramatically Speed Up Stored Procedures using Temp Tables

If you're wondering how to create a list, or temp table in a SQL Server Stored Procedure, look here.

But what if you're joining to an in-memory table and you experience performance issues?  I had a multiple join against such a table, and found that the query was taking over 12 seconds to complete - causing a timeout ocassionally.  Here was the temp table declaration:

    declare @tempIDs TABLE
        pick_list_id integer
Simple enough, right? Just a long list of integers.  But like I said the entire stored procedure was taking over 12 seconds to execute.  In a database table, my first approach would be to make an index on the column.  So that's what I did in my stored procedure.  It turned out to be incredibly simple and improved performance from 12 seconds to 30 milliseconds.  Looky!

    declare @tempIDs TABLE
        pick_list_id integer not null PRIMARY KEY
That's it! The rest of the stored procedure is exactly the same.  That's a 40000% speed boost!  Not bad for one modified line of code!


Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.

Tuesday, January 7, 2014

Column Modification Checklist

This is one of those things that I always forget part of, so because I just went through this, I thought I would document what needs to be done to sync your application whenever you modify any column in your database.

This tutorial is designed for:
  • MS SQL Server
  • Visual Studio (for web or desktop)
Quick Checklist:
  1. In SQL Server Management Studio (SSMS)
    1. Check for source and destination columns (for instance if widening "Address1" from 40 to 50, make sure all the columns in the order table, address book, Shipping and Billing etc are all the same)
    2. Update All Views that depend on this column. (SQL Server does not do this automatically)
    3. Update all stored procedures that operate on this column (for instance in and out parameters that access the changed column)
  2. In your Desktop App:
    1. Check all dataset xsd files to ensure the result column maxlengths are updated.
    2. Check all dataset xsd files to ensure the query parameter maxlengths are updated.
    3. Ensure all databound textboxes are set to the correct MaxLength.
    4. Ensure all DataGridView Columns are set to the correct MaxInputLength.
Updating Views:

There is actually a stored procedure for updating views.  Once you have found a dependent view, just run...

EXECUTE sp_refreshview 'dbo.v_myViewName';

That will take care of it.  Of course if your view is no longer valid because of the change, you'll get an appropriately misleading error message from Microsoft.

Updating Stored Procs:

Your stored procs have a header much like this:

ALTER PROCEDURE [dbo].[StoredProcName]
    -- Add the parameters for the stored procedure here
    @customer varchar(10),
    @PurchaseOrder varchar(15),
    @Address1 varchar(40)

These header parameters and any internally declared variables must be changed to match any column changes.


Bryan Valencia is a contributing editor and founder of Visual Studio Journey.  He owns and operates Software Services, a web design and hosting company in Manteca, California.