Data to MVC in 10 minutes.

Database

Assuming you created your table in SSMS already, It should look something like this:

Now drag select the 3 columns like and control+C copy them to the clipboard:

…and the clipboard…

ID              uniqueidentifier     Unchecked

International   bit                  Unchecked

Name            nvarchar(20)         Unchecked

Description     nvarchar(200)        Checked

BarPercent      int                  Unchecked

IncludesSteps   varchar(MAX)         Unchecked

Model

Go to Visual Studio and in your Solution Explorer, right click Models and Add, then Class

Make it easy on yourself:  Name it the same as your table.

Make the code look like this…

namespace SA.DS._0._2.Models  //this should match your other models, or leave it how Visual Studio created it

{

        using System;

        using System.Collections.Generic;

        using System.ComponentModel.DataAnnotations;

        using System.ComponentModel.DataAnnotations.Schema;

        using System.Data.Entity.Spatial;

        [Table("Lookups.StatusBarTypes")]  //This is the name of your table.  If the schema is left out, it assumes [dbo]

        public partial class StatusBarTypes

        {

                [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]

                //a default constructor

                public StatusBarTypes()

                {

                }

        }

}

Now, we'll paste in the list from the clipboard.

                //a default constructor

                public StatusBarTypes()

                {

                }

                ID      uniqueidentifier        Unchecked

International   bit     Unchecked

Name    nvarchar(20)    Unchecked

Description     nvarchar(200)   Checked

BarPercent      int     Unchecked

IncludesSteps   varchar(MAX)    Unchecked

                Unchecked

        }

}

Then doctor it into object properties.  DO NOT rename any fields!  Make sure anything that says "Checked" is nullable.

Keep tabs on the string lengths and whether the columns are required or not. (not null means Requred)

                [Key]

                public Guid ID { get; set; }

                [Required]

                public bool International { get; set; }

                [Required]

                [StringLength(20)]

                public string Name { get; set; }

                [StringLength(200)]

                public string? Description { get; set; }

                [Required]

                public int BarPercent { get; set; }

                public int IncludesSteps { get; set; }

If you want, you can add display info, like prettier column names.

                [Key]

                public Guid ID { get; set; }

                [Required]

                [Display(Name="Intl")]

                public bool International { get; set; }

                [Required]             

                [StringLength(20)]

                public string Name { get; set; }

                [StringLength(200)]

                public string Description { get; set; }

                [Required]

                [Display(Name="Bar%")]

                public int BarPercent { get; set; }

                [Display(Name="Incl Steps")]

                public int IncludesSteps { get; set; }

OK save it and BUILD.

If all went well, we can…

Controller and View

In Controllers, right click Add, then Controller

Pick MVC5 Controller with Views, Using Entity Framework

Click Add.

Pick your new Model Class.

Make sure it's your correct Database, pick a layout page, and Add.

Note that it autogenerates your controller…

…and your CRUD views.

These Views are ALL WIRED UP and ready to use.  Open Index.cshtml in Visual Studio and run it.

Your empty data table awaits!  Add some records, edit them, delete them, modify them!

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.

Select
    COLUMNS.TABLE_SCHEMA, 
    COLUMNS.TABLE_NAME,
    COLUMNS.COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
inner join
    INFORMATION_SCHEMA.TABLES
    on Tables.TABLE_CATALOG = Columns.TABLE_CATALOG
        and Tables.TABLE_SCHEMA = Columns.TABLE_SCHEMA
        and Tables.TABLE_NAME = COLUMNS.TABLE_NAME
        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.

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.

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)
AS

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.

Refresh All Views on SQL Server

Whenever you make a change to a table - say, to modify a column - you need to update all the views that depend on that column.  This script will find and update all your views. 




-- Back Up All Databases
-- by Bryan Valencia

--create temp table
declare @temp table(commands varchar(500), completed bit)

--load it with backup commands
insert into @temp (commands, completed)
(
    SELECT DISTINCT 'EXEC sp_refreshview ''' + name + '''',0
    FROM sys.objects AS so
    INNER JOIN sys.sql_expression_dependencies AS sed
        ON so.object_id = sed.referencing_id
    WHERE so.type = 'V'
    and is_schema_bound_reference = 0
)

--variable for the current command
declare @thisCommand varchar(500);

--loop through the table
while (select count(1) from @temp where completed=0)>0
begin
    --find the first row that has not already been executed
    select top 1 @thisCommand = commands from @temp where completed=0

    --show the command in the "mesage" output window.
    print @thisCommand

    --execute the command
    EXEC (@thisCommand);

    --flag this row as completed.
    update @temp set completed=1 where commands=@thisCommand
end

--show the user the rows that have been found.
select * from @temp




Of course if a view is now no longer correct, you'll see on your messages tab in SQL Server Management Studio.




...

---

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.

Selecting scalar values from a stored procedure

The easy and obvious answer is to use a function, not a procedure.   Then you can use it like any built-in T-SQL function.




select 1, dbo.Function()

But alas, functions do not allow us to store (insert, update, delete) any data to the database and my stored procedure needs to do exactly that. 



In a moment of brilliant engineering, SQL Server does not allow the return value of a stored procedure to appear as a column in a query (select, insert, update, where clause),  so you can't just say...




select orderID, dbo.StoredProcedure() from...

In my task, I had to insert the results of a stored procedure into a column in a table.  The code example I show below creates a temporary lookup table, and uses it later for a list of inserts.  try to follow this...




--creates a temp table for later use in joining (not shown)
declare @PickListNo table(OrderID varchar(20), PickListNo int, done bit);

--loads the temp table, except the column from the stored procedure
insert into @PickListNo (OrderID, done)
(
    Select distinct AOO.[order number], 0 from uploads.open_orders AOO
)

--vars the stored procedure needs
declare @A int
declare @NextVal int   

--while there are unprocessed rows...
while exists (select 1 from @PickListNo where PickListNo is null)
BEGIN
    --execute the procedure and capture the return value
    exec @A=dbo.NextPickListNo @NextVal OUTPUT
   
    --update one row in the table
    --I used Max(ID) to find a single row, but I might have used MIN, or Select top 1 as well.
    update @PickListNo set PicklistNo = @A where OrderID=(select MAX(orderID) from @PickListNo where PickListNo is null)
END

This use of the while loop is my way of cheating and not using cursors.  Cursors are nicely powerful but demand a lot of babysitting and resources (so I am told).



...

---

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.

Passing a List as a SQL Parameter

Take a simple query something like this:

select * from Orders
where OrderID in (1724, 1722, 1710)
order by OrderID desc

In the course of using this query, we might try input it's values as parameters. 

select * from Orders
where OrderID in (@ord1, @ord2, @ord3)
order by OrderID desc

But we don't always know how  many of them there are.  For this case we need what is called a table valued parameter - or a list parameter.









...

---

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.