Update to Narayana Vyas Kondreddi's generate insert script for SQL Server 2012

October 14, 2012 at 3:01 PMMe

If you are like me you like re-using tools that you find extremely simple and easy to use. That is the case with Narayana Vyas Kondreddi’s sp_generate_inserts script for SQL Server 2000, 2005, (works with 2008 yet not documented). So when I installed SQL Server 2012 developer my first item was to re-create all of the system stored procs I have in my toolbox.

Unfortunately running the script from the above link will generate an error because the code references a system stored procedure that no longer exists in SQL Server 2012 master.dbo.sp_MS_upd_sysobj_category.  This stored procedure toggles the system mode feature where any object created while the mode is enabled is flagged as a system object.  Since the undocumented sproc does not exist the script will be created but it will not work properly.

However there is a small update that can be done to the original script to fix the issue.

You need to remove the following lines

--Turn system object marking on
EXEC master.dbo.sp_MS_upd_sysobj_category 1
GO

And replace the following lines

--Turn system object marking off
EXEC master.dbo.sp_MS_upd_sysobj_category 2
GO

With

EXEC sys.sp_MS_marksystemobject sp_generate_inserts
GO

The script should ruin without any errors and work as it did in the previous versions.

I have emailed the owner of the script so hopefully he will update the script for 2012.

Posted in: Developer Tools

Tags: ,

Some fun at the Winnipeg Code Camp 2012

February 26, 2012 at 9:54 AMMe

Overall the Winnipeg Code Camp 2012 did not disappoint, lots of interesting sessions to be found and even more interesting people.

One cool item was: Sam - the Twitter Aware, Arduino Powered, Traffic Light.  Tyler & Jay talk about Sam: http://www.youtube.com/watch?v=Cj5Jdfo00xI

These were the following sessions I took in, each of them offered something different and were quite enjoyable.

  • ASP.NET MVC vs. Ruby on Rails SMACKDOWN - ROUND 2! (Part 1) (Marc Jeanson & James Chambers)
  • ASP.NET MVC vs. Ruby on Rails SMACKDOWN - ROUND 2! (Part 2) (Marc Jeanson & James Chambers)
  • Git 101: Source Control for the Future (David Alpert)
  • Software Craftsmanship Panel Discussion (David Alpert, Amir Barylko, James Chambers, and others!)
  • Illuminated Integration with Team City and Arduino (Tyler Dueck & Jay Smith)

P.S. the winner of the smack down was RoR by a significant margin.  Both sessions and presenters were very informative and dove into the how each framework handles certain features and "how do you do this" in each framework.

Just want to give a big kudos to the organizers and speakers for putting on another successful code camp as well the folks over at Skullspace for hooking up free Wi-Fi for everyone in attendance.

Posted in: Code Development | IT - Software

Tags: , ,

Type 'ContosoUniversity.DAL.Department' could not be found

September 11, 2011 at 10:00 PMMe

So I was going through the tutorial Handling Concurrency with the Entity Framework in an ASP.NET MVC Application when I encountered this error on step 7 of 10.

Type 'ContosoUniversity.DAL.Department' could not be found. Make sure that the required schemas are loaded and that the namespaces are imported correctly. Near type name, line 1, column 132.

The error is thrown on the following line:

var databaseValues = (Department)entry.GetDatabaseValues().ToObject();

This error is due to a bug in EF 4.1 where you separate the DAL and Model Entities out into different namespaces and is referenced here EF4.1 CodeFirst - entry.GetDatabaseValues() throw EntitySqlException.

To fix this issue I came up with this workaround:

Replace the lines:

var databaseValues = (Department)entry.GetDatabaseValues().ToObject(); 
var clientValues = (Department)entry.Entity;

with the following lines:

var clientValues = (Department)entry.CurrentValues.Clone().ToObject();
entry.Reload();
var databaseValues = (Department)entry.CurrentValues.ToObject();

 It seems to work ok for simple concurrency checks and it allows you to continue on with the tutorial.

 

Posted in: Knowledgebase

Tags: , , ,

Converting a DataTable to a Html table

November 20, 2010 at 4:58 PMMe

Introduction

This post is another common question I see on the ASP.NET forums "How to convert a DataTable to a Html table".  The code uses simple looping through the DataTable’s columns and rows/columns to pull the column names and the row data out.  I use the HtmlTable, HtmlTableRow and HtmlTableCell objects to actually build the table on the fly.  I find this much easier than using a StringBuilder to create the Html.

The code samples in this post can be downloaded using the link at the bottom and were created using Visual Studio 2010.  There are samples for both C# and Visual Basic.NET in the download file as well.

DataTableToHtml.zip (24 kb)

Posted in: Articles | Code Development

Tags: , , ,

Summary totals in a GridView

June 28, 2010 at 5:13 PMMe

Introduction

This post is in response to the common request on the ASP.NET forums for adding summary totals in a GridView control.  The idea is to use the FooterTemplate of each column in the GridView that has some kind of value to place the summary of the column.  The sample also goes on to show how to use the RowDataBound event to set the actual totals as well to add a Total Price column that takes a quantity field and a price field to display the total for each line.

The code samples in this post can be downloaded using the link at the bottom and were created using Visual Studio 2010.  There are samples for both C# and Visual Basic.NET in the download file as well.

Implementation

Default.aspx Page

What we want on the grid is simple enough, display a line by line total as well a summary of the Qty and Total Price fields.  Below are the columns that make up the grid view.  For the columns that come out of the database we would simply use BoundField columns.  For our calculated columns we want to use a TemplateField so we can add asp.net controls to the columns so we can inject our own data.  I tend to use the Literal control as it renders just the contents of the Text property vs the Label control that renders a SPAN html tag.  For the columns that will contain a summary total we use the FooterTemplate to place Literal controls so we can write the summary data out.

            <Columns>
                <asp:BoundField DataField="Description" HeaderText="Description">
                    <HeaderStyle HorizontalAlign="Left" />
                    <ItemStyle HorizontalAlign="Left" />
                </asp:BoundField>
                <asp:BoundField DataField="Price" HeaderText="Price" DataFormatString="{0:c}">
                    <HeaderStyle HorizontalAlign="Right" Width="100" />
                    <ItemStyle HorizontalAlign="Right" Width="100" />
                </asp:BoundField>
                <asp:TemplateField HeaderText="Qty">
                    <ItemTemplate>
                        <asp:Literal ID="litQty" runat="server" Text="0" />
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:Literal ID="litSumQty" runat="server" Text="0" />
                    </FooterTemplate>
                    <HeaderStyle HorizontalAlign="Right" Width="100" />
                    <ItemStyle HorizontalAlign="Right" Width="100" />
                    <FooterStyle HorizontalAlign="Right" Width="100" />
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Total Price">
                    <ItemTemplate>
                        <asp:Literal ID="litTotalPrice" runat="server" Text="0" />
                    </ItemTemplate>
                    <FooterTemplate>
                        <asp:Literal ID="litSumTotalPrice" runat="server" Text="0" />
                    </FooterTemplate>
                    <HeaderStyle HorizontalAlign="Right" Width="100" />
                    <ItemStyle HorizontalAlign="Right" Width="100" />
                    <FooterStyle HorizontalAlign="Right" Width="100" />
                </asp:TemplateField>
            </Columns>

Default.aspx.cs Code Behind

To inject our computed data we use the RowDataBound event on the grid.  This event is fired for every row when the data is bound to the row and allows us access to the controls and the data at the same time.  We first check if the current row type is a DataRow (items in the grid) and then we grab an instance of the actual data that makes up the row.  This will be different if you are using a generic list of your objects, you would cast the DataItem as that object.  Then we get a reference to the data from the DataRow and perform our calculations, then we get an instance of the Literal controls in the row, check if they are null or not (null would mean we did not find them) and set the Text value.

        protected void GridView1_RowDataBound(object sender, System.Web.UI.WebControls.GridViewRowEventArgs e)
        {

            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                DataRowView dr = (DataRowView)e.Row.DataItem;
                int qty = (int)dr["Qty"];
                decimal price = (decimal)dr["Price"];
                decimal totalPrice = qty * price;

                Literal litTotalPrice = (Literal)e.Row.FindControl("litTotalPrice");
                Literal litQty = (Literal)e.Row.FindControl("litQty");

                if ((litQty != null))
                    litQty.Text = qty.ToString();

                if ((litTotalPrice != null))
                    litTotalPrice.Text = totalPrice.ToString("c");
            }

            if (e.Row.RowType == DataControlRowType.Footer)
            {
                int qty = 0;
                decimal price = default(decimal);

                GetTableTotals(ref qty, ref price);

                Literal litSumTotalPrice = (Literal)e.Row.FindControl("litSumTotalPrice");
                Literal litSumQty = (Literal)e.Row.FindControl("litSumQty");

                if ((litSumQty != null))
                    litSumQty.Text = qty.ToString();

                if ((litSumTotalPrice != null))
                    litSumTotalPrice.Text = price.ToString("c");
            }
        }

If the RowType is Footer we need to get the summary data from the entire table of data.  To do this we use some Linq in the GetTableTotals() method to get a sum of the quantity and calculate the sum of the quantity * price for each data row.

        public void GetTableTotals(ref int qty, ref decimal price)
        {
            DataTable dt = (DataTable)GridView1.DataSource;
            var rows = dt.AsEnumerable();

            qty = rows.Sum(p => (int)p["Qty"]);
            price = (from p in rows select (int)p["Qty"] * (decimal)p["Price"]).Sum();
        }

Code: GridTotalsSample.zip (22.01 kb)

Posted in: Articles | Code Development

Tags: ,