Jim's blog | .Net and SQL Server blog

Passing IDs to Stored Procs Using XML

by jbrooks 15. October 2010 10:58

In the past we would use a Varchar to pass a comma delimited list of IDs to a stored proc. We would then build a big varchar string of the full SQL and run the dynamic SQL.  Starting with SQL Server 2005 you can pass the stored proc XML and do your select without using dynamic SQL.

The stored proc would look something like:

 
create proc [dbo].[getLocationTypes](@locationIds XML,
@typeIds XML=null)
as  
begin  
set nocount on  
 
   
SELECT locationId, typeId
FROM xrefLocationTypes 
WHERE locationId 
IN (SELECT Item.value('.', 'int' )
FROM @locationIDs.nodes('IDList/ID') AS x(Item))
AND (typeId IN
 (SELECT Item.value('.', 'int' )
FROM @typeIds.nodes('IDList/ID') AS x(Item)))
ORDER BY 1, 2
  
end  
 
 
 
And then you would call this like:
 
 
EXECUTE dbo.getLocationTypes '<IDList><ID>1</ID><ID>3</ID></IDList>', 
'<IDList><ID>200</ID><ID>300</ID><ID>400</ID></IDList>'    
 

Tags:

SQL | Development

Changing HTML Attributes on ASP.NET Pages in the Code Behind

by JBrooks 24. September 2010 11:40

Not many developers realize that you can change a plain HTML object’s attributes in the code behind without having to convert it into an ASP.Net control.

So if I have a checkbox like this:

<input type="checkbox" id="cbShowDef" name="cbShowDef"  onclick="javascript: show()" />

I can hide this in the code behind with:

((System.Web.UI.HtmlControls.HtmlInputCheckBox) Form.FindControl("cbShowDef")).Style.Add("display","none");

Tags:

ASP.Net | Development

Delete Spam Users from YetAnotherForum

by JBrooks 5. September 2010 14:21

 

I use YetAnotherForum (YAF) for my Forums here.   Like others, I get hundreds of spam users created each month.  I wrote the following SQL to delete any user that hasn’t visited in the last day, never posted or emailed a message, isn’t the last user on a forum and isn’t in the Active list.
 
-- first delete the UserGroup rows.
DELETE FROM dbo.yaf_UserGroup
WHERE UserID in
(
SELECT u.UserID
FROM dbo.yaf_User u
WHERE u.LastVisit < getdate() -1
AND u.NumPosts = 0)
AND UserID NOT IN
(
SELECT a.UserID
FROM dbo.yaf_Active a)
AND UserID NOT IN
(
SELECT f.LastUserID
FROM dbo.yaf_Forum f
WHERE f.LastUserID IS NOT NULL)
AND UserID NOT IN
(
SELECT pm.FromUserID
FROM dbo.yaf_PMessage pm)
AND UserID NOT IN
(
SELECT m.UserId
FROM dbo.yaf_Message m)
 
--
-- now delete the User rows.
--

DELETE FROM dbo.yaf_User
WHERE LastVisit < getdate() -1
AND NumPosts = 0
AND UserID NOT IN
(
SELECT a.UserID
FROM dbo.yaf_Active a)
AND UserID NOT IN
(
SELECT f.LastUserID
FROM dbo.yaf_Forum f
WHERE f.LastUserID IS NOT NULL)
AND UserID NOT IN
(
SELECT pm.FromUserID
FROM dbo.yaf_PMessage pm)
AND UserID NOT IN
(SELECT m.UserId
FROM dbo.yaf_Message m)

Tags:

SQL

Displaying a Long Description Column in a GridView

by JBrooks 6. July 2010 20:05

A lot of times you have something like a description column and you want to display it in a GridView.  But since for some of the rows the description column can be very long, it doesn’t always work very well. One solution is where you set the TD’s attribute NOWRAP, then the column runs off to the right for as long as is needed.  The solution I sometimes employ is the following: 

First, here is my sample table:

CREATE TABLE Categories
(CategoryName varchar(20),
Description varchar(1000))
 
And here is the SQL that is going to bind to my GridView:
 
SELECT c.CategoryName,
CASE WHEN LEN(c.Description) > 30
THEN SUBSTRING(c.Description,1,27)+'...'
ELSE c.Description END AS LineDescription,
c.Description AS FullDescription
FROM dbo.Categories c
ORDER BY c.CategoryName

Notice that the Description column is in there twice. Once where it maxes out at 30 characters (the LineDescription column), and the second one that contains the full text (the FullDescription column.) This brings back a table that looks like the following:

CategoryName LineDescription FullDescription
Beverages Soft drinks, coffees, teas,... Soft drinks, coffees, teas, beers, and ales
Condiments Sweet and savory sauces, re... Sweet and savory sauces, relishes, spreads, and seasonings
Confections Desserts, candies, and swee... Desserts, candies, and sweet breads
Dairy Products Cheeses Cheeses
Grains/Cereals Breads, crackers, pasta, an... Breads, crackers, pasta, and cereal
Meat/Poultry Prepared meats Prepared meats
Produce Dried fruit and bean curd Dried fruit and bean curd
Seafood Seaweed and fish Seaweed and fish

 

So to bind this to the GridView we bind the first two columns only.  The first GridView column is bound to the CategoryName data column and the second one is bound to the LineDescription data column with the heading title of “Description”.  On the second GridView column we also click the “Convert this field into a TemplateField” as shown below.

GridViewColumns

 

We save this and then edit the templates for the GridView columns.  For the Description column we edit the ItemTemplate and we bind the ToolTip to the FullDescription data column.

ToolTipBinding

 

Now we have the GridView column that is titled “Description” bound to the LineDescription data column and the tooltip is bound to the FullDescription data column.  We can run this and  see how it works:

GridViewWithToolTip

So now we have a short description in our grid, but the user can easily see the full description in the ToolTip.

Tags:

ASP.Net | GridView | Development

Quickly Finding Missing Objects in Two SQL Server 2005 Databases

by JBrooks 5. July 2010 09:22

Real quick I wanted to know what tables and stored procs were in my ReleaseManagerTest database and not in my ReleaseManager database.  So here is the SQL for that.

select min(DB) as DB, name, type
from
(
select 'ReleaseManagerTest' as DB, name, type 
from ReleaseManagerTest.sys.objects 
 
union all
 
select 'ReleaseManager' as DB, name, type
from ReleaseManager.sys.objects
) A
group by name, type
having count(*) <> 2
 

Tags:

Development | SQL

Finding Objects in SQL Server During Development

by JBrooks 13. March 2010 06:57

I kept writing the same SQL over and over while developing to find the names of stored procs or to see what objects I had recently added.  So I created a quick stored proc that I now have in my tool box.

create proc obj(@name varchar(50)=null, @type char(1)=null)
as
begin
set nocount on
 
select o.name, 
o.type, 
convert(varchar(10),o.create_date,1) created, 
convert(varchar(10),o.modify_date,1) modified
from sys.objects o
where (@name is null or o.name like @name)
and (@type is null or o.type = @type)
and o.is_ms_shipped = 0
order by o.modify_date desc
 
end
go
 
 

So if I just do:
obj ‘get%’, ‘P’

I’ll get

name type created modified
GetProjectsTree P 7/28/2008 2/27/2010
GetProjectOutputVariables P 10/10/2009 2/26/2010
GetXrefRequestIssue P 9/9/2008 2/11/2010
GetProjectOutputVariable P 2/11/2010 2/11/2010
GetRequestsItemsOverlap P 1/6/2010 2/9/2010
GetControl P 2/6/2010 2/6/2010
getSourceIdFromPath P 2/5/2010 2/5/2010
GetRequestedFiles P 10/14/2008 2/2/2010
GetRequestsForBranch P 12/30/2008 1/15/2010
GetRequestWithChildren P 7/28/2008 1/1/2010
GetRequestsItems P 4/28/2009 1/1/2010

and if I call obj with no parameters I will get a list of all my objects with the most recently modified objects first. This works with SQL 2005 and above.

Tags:

SQL

Unhide Exceptions Hidden By AJAX

by JBrooks 3. February 2010 09:35

If you work with AJAX for any length of time you will find that no matter what the exception thrown in the code behind the message displayed to the user is “Exception has been thrown by the target of an invocation.”  Not very useful.

image

My approach to debugging the exception had been to comment out the UpdatePanel, recreate the exception and see the real error.  But this doesn’t work at all when the code is deployed to the end-user.

So the solution is to capture the AsyncPostBackError event of the ScriptManager as shown here:

image

Then in the  code for this event (code behind) we can change the AsyncPostBackErrorMessage to the actual error message in the exception.

protected void ScriptManager1_AsyncPostBackError(object sender,AsyncPostBackErrorEventArgs e)
{
    if (e.Exception != null && e.Exception.InnerException != null)
    {
        ScriptManager1.AsyncPostBackErrorMessage = e.Exception.InnerException.Message;
        cApp.LogExceptionNoThrow(e.Exception.InnerException);
    }
}

Note that I also log the exception which includes the stack trace so I can later research the problem. 

One further enhancement would be to replace the alert box with a label on the page.  To do this I first wire up an event in JavaScript to capture the return from an Ajax call.  So here I am registering the JavaScript function “endRequest” to be called at the end of an AJAX request.

function pageLoad(sender, args) {
    Sys.WebForms.PageRequestManager.getInstance().add_endRequest(endRequest);
}

And in my endRequest function I set the label and turns off the alert box.

function endRequest(sender, args) {

    if (args.get_error() != undefined) {

        var lbl = $get('<%= this.lblMessage.ClientID %>');
        lbl.innerHTML = args.get_error().message;
        lbl.style.color = 'Red'

        // This will stop the alert box from displaying.
        args.set_errorHandled(true);

        // go to the top so they can see the error message.
        window.scroll(0, 0);

    }
}

Note that the lblMessage label control needs to be inside the UpdatePanel so that it is cleared on the next PostBack. 

So now when I get an exception it displays the real error to the user in a label with red text, something like below. 

image 

And the details are logged in my exceptions table.

image

Much better than the default.

Tags:

ASP.Net

Forgotten Windows Password Recovery and the "no tables found" error.

by JBrooks 4. January 2010 17:59

A friend of mine brought over his laptop because he couldn't remember his new Windows Vista login password. I said I would work on it and worst case I would have to clear everything and reinstall Vista.

Googling around I found that there were tools for clearing the password. I downloaded and burned to a CD the tool here: here at pogostick.net and later Ophcrack found here. Both are free if you are just clearing a password.

Obviously, I first cleared all of the BIOS based passwords (he did remember these). Then I tried to run the 2 tools listed above. The first one couldn't mount the hard drive and the second gave the error "no tables found".

So I Googled for this error. Found 1 comment that said something about not having a drive for the controller card... I see that this is Linux, so maybe the drive isn't there for my friends laptop's controller card, after all, it is a cheap eMachine. (I'm not a Linux guy so I'm not even sure how this works.) So I pop the hard drive out of his laptop and put it in my laptop.

I then booted and tried the Ophcrack CD and it worked. Case closed.

Tags:

General

Knowing What to Test When Changing a SQL Server Object

by jbrooks 23. October 2009 12:50

We were changing our linked server and needed to test every page in our website that used that link server.  None of the pages used it directly, but they did call stored procs that would use it or stored procs that would in turn call other stored procs that us it.    I need to find all of the pages that eventually would use it so that we could test them all.  The fact that it could be nested within stored proc calls made it a little more difficult.   Here is how I did it:

Step 1:  A simple stored proc used for searching for text within other stored procs. 

This work for SQL Server 2005

create proc dbo.Find(@search VARCHAR(100) = '') AS  
begin  
set nocount on  
  
SET @search = '%' + @search + '%'  
  
select object_name(o.object_id) Object, o.type
from sys.sql_modules m  
join sys.objects o on m.object_id = o.object_id  
where m.definition like @search  
order by 1  
  
end  

Step 2: Make a working table to store you work as you process the stored procs.

create table spDoc
(parentObj varchar(500),
obj varchar(500),
[type] varchar(3),
level int)

You will be storing the object name (stored proc, function, or trigger) in the column “obj”.  You will be storing the calling stored proc in the “parentObj” column. 

Step 3: Create a stored proc to process your search.

create proc doc(@obj varchar(256))
as
begin
set nocount on
declare @level int


if exists (select 1 from spDoc where parentObj = @obj)
    return  -- already done.

select @level = isnull(level,0)+1
from spDoc
where obj = @obj

if @level is null
    set @level = 1


--Find all of the objects that have the text of the current object.
insert into spDoc
(obj, [type])
exec Find @obj

-- The @obj is the parent of the rows just created.
update spDoc
set parentObj = @obj,
level = @level
where parentObj is null

--Delete where they are both the same.
delete from spDoc
where parentObj = @obj
and obj = @obj


--Loop thru each of the rows just created
--and recursively call this function.
DECLARE @currentObj varchar(256)

DECLARE @getObjs CURSOR

SET @getObjs = CURSOR FOR
select obj
from spDoc
where parentObj = @obj


OPEN @getObjs

FETCH NEXT
FROM @getObjs INTO @currentObj

WHILE @@FETCH_STATUS = 0
BEGIN
    exec doc @currentObj  -- recursive call

    FETCH NEXT
    FROM @getObjs INTO @currentObj
END

CLOSE @getObjs
DEALLOCATE @getObjs

end

If you were trying to document the object called “MyLink” you would call it like:

   doc MyLink

                          

Step 4: Get your results.

Once the processing is done you only need a listing of the parent objects and this is the list that you would use to check your front end code.  

select distinct parentObj
from spDoc
order by 1

 

And that is all there is to it.

Tags:

SQL

Select One Row From a GridView

by JBrooks 5. August 2009 16:53

 

I have a page where the user can select which users are active (using CheckBoxes), and also select one (only one) to be the administrator.  I put together this sample to show how I did it.  The sample uses technologies instead of user.  The picture below gives you an idea.  I didn't want to use the GridView "selected row" functionality because I didn't want to do a PostBack and I have another project where the user has to select 1 row per day where a day can take up 3 to 10 rows and there were many days in the grid.

 
PickOne

 

 

using System;

using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 
public partial class SelectOne : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            ViewState["selectedBest"] = "ASP.Net";
 
            bind();
        }
    }
 
    protected void bind()
    {
        DataTable dt = new DataTable();
 
        dt.Columns.Add("Include", typeof(Boolean));
        dt.Columns.Add("Name", typeof(String));
        dt.Rows.Add(new Object[] { false, "C#" });
        dt.Rows.Add(new Object[] { false, "ASP.Net" });
        dt.Rows.Add(new Object[] { false, "SQL Server" });
        dt.Rows.Add(new Object[] { false, "VB.Net" });
 
        GridView1.DataSource = dt;
        GridView1.DataBind();
 
    }
 
 
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType != DataControlRowType.DataRow)
            return;
 
        // See if this matches our saved selection.
        if (e.Row.Cells[1].Text.Equals(ViewState["selectedBest"].ToString()))
            e.Row.Cells[2].Text = e.Row.Cells[2].Text.Replace("value", "checked value");
 
        // Set the value to the RowIndex so it is unique.
        e.Row.Cells[2].Text = e.Row.Cells[2].Text.Replace("{0}", e.Row.RowIndex.ToString());
 
    }
 
    protected void btnSave_Click(object sender, EventArgs e)
    {
        int total = 0;
 
        //Count of the number that our checked.
        foreach (GridViewRow gvr in this.GridView1.Rows)
        {
            if (gvr.RowType != DataControlRowType.DataRow)
                continue;
 
            if (((CheckBox)gvr.Cells[0].FindControl("cbSelected")).Checked)
                total++;
 
        }
 
        //Save the selected Best.
        ViewState["selectedBest"] = 
            this.GridView1.Rows[Convert.ToInt32(Request.Form["RBBest"])].Cells[1].Text;
 
        //Normally you would be saving to the database here.
        this.lblMessage.Text = "You selected " + total.ToString() 
            + " with " + ViewState["selectedBest"].ToString() + " as the best.";
 
        bind();
 
    }
 
}
 
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SelectOne.aspx.cs" 
Inherits="SelectOne"%>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Test check all CheckBoxes</title>
 
    <script type="text/javascript" src="jquery-1[1].3.2.min.js"></script>
 
    <script type="text/javascript" language="javascript">
 
        function changeAllCheckBoxes(sender) {
            var gridViewRows = GetParentElementByTagName(sender, "TABLE").rows;
            for (var i = 1; i < gridViewRows.length; ++i) {
                gridViewRows[i].cells[0].childNodes[0].checked = sender.checked;
                //gridViewRows[i].cells[0].childNodes[1].style.display = 'none';
            }
            return false;
        }
 
 
        function GetParentElementByTagName(element, tagName) {
            var element = element;
            while (element.tagName != tagName)
                element = element.parentNode;
            return element;
        }
    </script>
 
</head>
<body>
    <form id="form1" runat="server">
    <div id="divp" style="text-align: left">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        Caption="Pick Some Technologies"
            BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px"
            CellPadding="4" onrowdatabound="GridView1_RowDataBound">
            <Columns>
                <asp:TemplateField HeaderText="Include" SortExpression="Include">
                    <HeaderTemplate>
                        <asp:CheckBox ID="CheckBox1" runat="server" 
                        onclick="changeAllCheckBoxes(this)" />
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:CheckBox ID="cbSelected" runat="server" />
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                </asp:TemplateField>
                <asp:BoundField DataField="Name" HeaderText="Technology" />
                <asp:TemplateField HeaderText="Best">
                    <ItemTemplate>
                        <input id="RBBest" type="radio" name="RBBest" value="{0}" />
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                </asp:TemplateField>
            </Columns>
            <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
        </asp:GridView>
    </div>
    <br />
    <div>
        <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" />
    </div>
    <br />
    <asp:Label ID="lblMessage" runat="server" Text="Make selections and then click Save">
    </asp:Label>
 
    </form>
</body>
</html>

Tags:

ASP.Net | GridView | Development