Development

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

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

How to Work With a GridView Row in JavaScript

by JBrooks 14. July 2009 16:40

The question came up asking how to change something in a GridView row based on something else in that row without doing a postback.   So I created a simple example where if you check a CheckBox then another column in that row would have it's background color changed.  I did this in a generic way by sending the checkbox to the function by using the keywork "this" and then getting a reference to the row.  Here is what it looks like:

So when you check the CheckBox for a given row, it's "Name" column's background changes color to Aqua.

Here is the code behind to bind the table:

using System;
using System.Data;

namespace BlogTests
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
    
        if (!IsPostBack)
            {
               DataTable dt = new DataTable();

                dt.Columns.Add("Include", typeof(Boolean));
                dt.Columns.Add("Name", typeof(String));
                dt.Rows.Add(new Object[] { 0, "Jim" });
                dt.Rows.Add(new Object[] { 0, "Jen" });
                dt.Rows.Add(new Object[] { 0, "Kylie" });
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }

    }
}

 

and then the .aspx page:

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

<!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>Row Test</title>
    <style type="text/css">
        .selected
        {
            background-color: Aqua;
            font-weight: bold;
        }
    </style>
</head>
<body>

    <script type="text/javascript">
        function toggleSelected(sender) {
            var row = GetParentElementByTagName(sender, "TR");

            if (sender.checked)
                row.cells[1].className = "selected";
            else
                row.cells[1].className = '';

        }


        function GetParentElementByTagName(element, tagName) {
            var element = element;
            while (element.tagName != tagName)
                element = element.parentNode;
            return element;
        }
    </script>

    <form id="form1" runat="server">
    <div>
    </div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
        <Columns>
            <asp:TemplateField HeaderText="Include" SortExpression="Include">
                <ItemTemplate>
                    <asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("Include") %>' onclick="toggleSelected(this)" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name">
                <ItemStyle HorizontalAlign="Left" />
            </asp:BoundField>
        </Columns>
    </asp:GridView>
    </form>
</body>
</html>

 

Tags:

ASP.Net | Development | GridView

Attaching upload files to a record.

by JBrooks 2. July 2009 11:31

One of our applications has a page for our users to report bugs.  We wanted to allow them to “attach” one or more files to the bug record.  The files could be screen shots, spreadsheets, etc.    So the bug entry page would have the normal data entry part and have the following box at the bottom of the page:

 

If you click the FileName it will download the file if IIS is setup to send that extention.  All this should be made into a user control or a server control, but for now I have the code in the page.  Here are the key parts:

// the page has a property that returns the bug's record id.

private int id

{ get { return Convert.ToInt32(Request.QueryString["id"]); } }

 

 

private string uploadDir

{ get { return System.AppDomain.CurrentDomain.BaseDirectory+ "\\Uploads\\"; } }

 

protected void btnUpload_Click(object sender, EventArgs e)

   if (this.FileUpload1.HasFile)

   {  //we rename the file to start with the id and an underscore.

   string fileName = uploadDir + id.ToString() + "_" + FileUpload1.FileName;

 

   FileUpload1.SaveAs(fileName);

 

   bindFiles();

   } else { setErrorMessage("You have not specified a file."); }

 }

 

protected void bindFiles()

{

// make a table to hold the file's information.

    DataTable dt = new DataTable();

    dt.Columns.Add("FileName", typeof(String));

    dt.Columns.Add("Created", typeof(DateTime));

    dt.Columns.Add("Size", typeof(Int32));

    dt.Columns.Add("id", typeof(Int32));

 

         // get a list of files that start with our id.

    string[] files = Directory.GetFiles(uploadDir,id.ToString()+"_*.*");

 

//populate the table.

    foreach (string fileName in files)

    {

        DataRow dr = dt.NewRow();

        FileInfo fi = new FileInfo(fileName);

        // at this point we need to strip off the id.

        dr["FileName"] = fileName.Substring(fileName.LastIndexOf("\\") + 2 + id.ToString().Length);

        dr["Created"] = fi.CreationTime;

        dr["Size"] = fi.Length;

        dr["id"] = this.id;

 

        dt.Rows.Add(dr);

    }

 

// now bind the table to our gridview

    this.gvFiles.DataSource = dt;

    this.gvFiles.DataBind();

 

}

 

protected void lbDelete_Click(object sender, EventArgs e)

{

         //Don't like how fragile this is, but good enough for now.

    string fileName = uploadDir + id.ToString() + "_" + ((HyperLink)((GridViewRow)((LinkButton)sender).Parent.Parent).Cells[1].Controls[0]).Text;

 

    File.Delete(fileName);

 

    bindFiles();

}

 

 

 Here is the related mark-up from the aspx page:

<div  id="divFiles" style="border: solid 1px black">

<span id="Span1" style="font-size:X-Large;font-weight:bold;">FILES</span>

<br />

    <asp:FileUpload ID="FileUpload1" runat="server" />&nbsp;&nbsp;&nbsp;

    <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" /><br />

    <br />

    <asp:GridView ID="gvFiles" runat="server" AutoGenerateColumns="False" Caption="<strong>FILES</strong>"

        CaptionAlign="Top" CellPadding="4" ForeColor="#333333" GridLines="None">

        <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />

        <Columns>

            <asp:TemplateField InsertVisible="False" ShowHeader="False">

                <ItemTemplate>

                    <asp:LinkButton ID="lbDelete" runat="server" CausesValidation="False" CommandName="Delete"

                        Text="Delete" OnClientClick="confirm('Are you sure you want to delete this file?');" OnClick="lbDelete_Click"></asp:LinkButton>

                </ItemTemplate>

            </asp:TemplateField>

            <asp:HyperLinkField DataTextField="FileName" SortExpression="FileName" Text="FileName"

                HeaderText="FileName" DataNavigateUrlFormatString="~/Uploads/{0}_{1}"

                DataNavigateUrlFields="id,FileName" >

                <ItemStyle HorizontalAlign="Left" />

            </asp:HyperLinkField>

            <asp:BoundField DataField="Created" DataFormatString="{0:M/d/yy H:mm}" HeaderText="Created"

                SortExpression="Created"  />

            <asp:BoundField DataField="Size" HeaderText="Size" SortExpression="Size" DataFormatString="{0:#,###,###}" >

                <ItemStyle HorizontalAlign="Right" />

            </asp:BoundField>

        </Columns>

        <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />

        <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />

        <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

        <AlternatingRowStyle BackColor="White" />

    </asp:GridView>

</div>

<br />

 

 

Tags:

ASP.Net | GridView | Development

Formatting Dates

by JBrooks 2. July 2009 09:37

I was having a lot of code that looked like the following:

         if(dr["StartTime"] != DBNull.Value)   
                tbStartTime.Text = dr["StartTime"].ToString("M/d/yy H:mm");
         else

              tbStartTime.Text = "";

and:

         tbStartTime.Text = DateTime.Now.ToString("M/d/yy H:mm");

 

You usually use the same formatting throughout an application so I simplified this by using the following function:

        public string formatDate(object date)
        {
            if (date != DBNull.Value)
                return Convert.ToDateTime(date.ToString()).ToString("M/d/yy H:mm");
            else
                return "";
        }

 So now I can have my formatting in one place and use this like so:

       tbStartTime.Text = formatDate(dr["StartTime"]);

 and using the same function:

     tbStartTime.Text = formatDate(DateTime.Now);

 

 

 

 

Tags:

ASP.Net | Development

Release Manager for Subversion

by JBrooks 27. June 2009 06:36

Getting close to being done my release manager.

I got the video done, it can be seen here: http://www.ReleaseManager.com

Any comments would be appreciated!

Tags:

Development | General

Another Place to Put the Connection String

by JBrooks 18. June 2009 09:44

Another way to store your connection string in a way that it doesn't get in the way when you promote your web site from Test to QA to Prod is the following:

Have your web site under a given directory, say d:\WebSites\MyWebsite.
And you also have another directory to hold your connection strings that isn't under that directory.   So I could have my connection string as an appSetting in the following file:
d:\WebSites\Support\config\WebEnvironment.config

The section looks like:
<appSettings>
    <add key="Env" value="Dev"/>
    <add key="DefaultConnection" value="server=MyServer;uid=MyUser;pwd=P@ssw0rd;database=MyDB;" />
</appSettings>

Then you have the following in your d:\WebSites\MyWebsite\web.config file

<appSettings file="../Support/config/WebEnvironment.config">
</appSettings>


You would retreive the string by:
System.Configuration.ConfigurationManager.AppSettings["DefaultConnection"]


-Things to note are that this now allows you to delete everything under d:\WebSites\MyWebsite before you redeploy your site.  
-If you change the connection string you need to do an IIS reset for the site to pick up the change.  
-You can't do the file="..." part with a connection

Tags:

ASP.Net | Development

Generate Save Stored Proc from Table

by JBrooks 15. June 2009 11:18

Part of my pattern during development is to:
    1. create a new table
    2. create the stored procs to get data and save data

 This second step is tedious and lends itself to automation.  So I created the stored proc createSPFromTable that takes the name of a table and creates a simple save store proc.  So the example:

createSPFromTable SourceTypes

 

create proc saveSourceTypes(@id int, @Name varchar(50), @DisplayOrder float, @Status char(1))

as

begin

set nocount on

 

 

if @id > 0

    update SourceTypes

    set Name = @Name,

    DisplayOrder = @DisplayOrder,

    Status = @Status

    where id = @id

else

begin

    insert into SourceTypes

    (Name, DisplayOrder, Status)

    values (@Name, @DisplayOrder, @Status)

 

 set @id = scope_identity()

end

 

select @id

 

return @id

 

end

 

 

 

Below is the stored proc that generates the basic save proc.   Not that you have to pass in the primary key column if it is not named "id".

create proc [dbo].[createSPFromTable](@TableName varchar(250), @idColumn varchar(25)=null)
as
set nocount on

declare @crlf char(2)
set @crlf = char(13)+char(10)

declare @parameterList varchar(8000)
set @parameterList='create proc save'+@TableName+'('

-- This converts sql type to c# type.
SELECT @parameterList=  @parameterList + case when lower(c.COLUMN_NAME) = isnull(@idColumn,'id') then '' else '@'+c.COLUMN_NAME+' ' + c.DATA_TYPE+
CASE c.DATA_TYPE
WHEN 'binary' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'char' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'decimal' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'nchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'numeric' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'nvarchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'varbinary' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
WHEN 'varchar' THEN '('+ltrim(cast(c.CHARACTER_MAXIMUM_LENGTH as varchar(4)))+')'
ELSE '' END+', ' end,
@idColumn = case when @idColumn is null and lower(c.COLUMN_NAME) = 'id' then c.COLUMN_NAME else @idColumn end
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
order by c.ORDINAL_POSITION

set @parameterList=substring(@parameterList,1,len(@parameterList)-1)

set @parameterList=@parameterList+')'+@crlf+'as'+@crlf+'begin'+@crlf+'set nocount on'+@crlf+@crlf+@crlf

set @parameterList=@parameterList+'if @'+@idColumn +' > 0 ' + @crlf

set @parameterList=@parameterList+' update '+@TableName+ @crlf +' set'

SELECT @parameterList=@parameterList+' '+ c.COLUMN_NAME+' = @'+c.COLUMN_NAME+', '+ @crlf
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn
order by c.ORDINAL_POSITION

set @parameterList=substring(@parameterList,1,len(@parameterList)-4)+ @crlf+ ' where '+@idColumn +' = @'+@idColumn+ @crlf

set @parameterList=@parameterList+'else '+ @crlf +'begin'+@crlf+ ' insert into '+@TableName+@crlf +' ('

SELECT @parameterList=@parameterList+ c.COLUMN_NAME+', '
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn
order by c.ORDINAL_POSITION

set @parameterList=substring(@parameterList,1,len(@parameterList)-1)

set @parameterList=@parameterList+')'+@crlf

set @parameterList=@parameterList+' values ('

SELECT @parameterList=@parameterList+'@'+ c.COLUMN_NAME+', '
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @TableName
and c.COLUMN_NAME <> @idColumn
order by c.ORDINAL_POSITION

set @parameterList=substring(@parameterList,1,len(@parameterList)-1)

set @parameterList=@parameterList+')'+@crlf+@crlf

set @parameterList=@parameterList+' set @'+@idColumn+' = scope_identity()'+@crlf

set @parameterList=@parameterList+'end'+@crlf

set @parameterList=@parameterList+'select @'+@idColumn+@crlf

set @parameterList=@parameterList+'return @'+@idColumn+@crlf

set @parameterList=@parameterList+'end'+@crlf
print @parameterList

 

 

Tags:

Development | SQL

Generate Data Access Layer Methods From Stored Procs

by JBrooks 3. June 2009 10:42

Part of my pattern during development is to:
    1. create a new table
    2. create the stored procs to get data and save data
    3. create the data access layer methods that call the stored procs.

This is a little tedious so I created SQL stored proc called createDALFromSP to generate a data access method from a stored proc.  

To create a DAL method for a stored proc called SaveRequest I would run this from SQL Analyzer:

      createDALFromSP 'SaveRequest'

An what it generates looks like the following:

public void SaveRequest(Int32 ID, String Name, Guid UserID, String Comments, Boolean IsSpecial, Double BuildOrder, String status, Boolean clear)
{
    SqlCommand cmd = GetNewCmd("dbo.SaveRequest");

    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
    cmd.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = Name;
    cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier).Value = UserID;
    cmd.Parameters.Add("@Comments", SqlDbType.VarChar, 8000).Value = Comments;
    cmd.Parameters.Add("@IsSpecial", SqlDbType.Bit).Value = IsSpecial;
    cmd.Parameters.Add("@BuildOrder", SqlDbType.Float).Value = BuildOrder;
    cmd.Parameters.Add("@status", SqlDbType.Char, 1).Value = status;
    cmd.Parameters.Add("@clear", SqlDbType.Bit).Value = clear;

    cmd.Parameters["@ID"].Direction = ParameterDirection.InputOutput;
    ExecuteNonQuery(cmd);
}
 

 

Below is the stored proc createDALFromSP, it looks like a mess but it is basic stuff.  This works on SQL Server 2000 and 2005.

create  proc dbo.createDALFromSP(@procName varchar(250))
as
begin

declare @parameterList varchar(8000)
declare @isGet bit

select @isGet = case when lower(@procName) like 'get%' then 1 else 0 end

if @isGet = 1
    set @parameterList='public DataTable '+@procName+'('   
else
    set @parameterList='public void '+@procName+'('

-- get the signiture of the method itelf - so convert SQL Server data types to C# datatypes
SELECT @parameterList=@parameterList+(SELECT CASE DATA_TYPE
        WHEN 'bigint' THEN 'Int64'
        WHEN 'binary' THEN 'Binary'
        WHEN 'bit' THEN 'Boolean'
        WHEN 'char' THEN 'String'
        WHEN 'datetime' THEN 'DateTime'
        WHEN 'decimal' THEN 'Decimal'
        WHEN 'float' THEN 'Double'
        WHEN 'image' THEN 'Binary'
        WHEN 'int' THEN 'Int32'
        WHEN 'money' THEN 'Decimal'
        WHEN 'nchar' THEN 'String'
        WHEN 'ntext' THEN 'String'
        WHEN 'numeric' THEN 'Decimal'
        WHEN 'nvarchar' THEN 'String'
        WHEN 'real' THEN 'Single'
        WHEN 'smalldatetime' THEN 'DateTime'
        WHEN 'smallint' THEN 'Int16'
        WHEN 'smallmoney' THEN 'Currency'
        WHEN 'sql_variant' THEN 'String'
        WHEN 'sysname' THEN 'String'
        WHEN 'text' THEN 'String'
        WHEN 'timestamp' THEN 'Binary'
        WHEN 'tinyint' THEN 'Byte'
        WHEN 'uniqueidentifier' THEN 'Guid'
        WHEN 'varbinary' THEN 'Binary'
        WHEN 'varchar' THEN 'String'
                ELSE '!MISSING' END)+' '+substring(PARAMETER_NAME,2,300)+', ' 
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @procName
order by ORDINAL_POSITION

if @@rowcount > 0
       set @parameterList=substring(@parameterList,1,len(@parameterList)-1)
set @parameterList=@parameterList+')'

-- This is the body of the method
-- Most of the work is to convert sql data types to SqlDbType while listing out the parameters.
select Line
from (
select -99 SortOrder, @parameterList Line
union all
select -98, '{'
union all
select -97, '    SqlCommand cmd = GetNewCmd("dbo.'+@procName+'");'
union all
select -96,''
union all
select ORDINAL_POSITION, '    cmd.Parameters.Add("'+PARAMETER_NAME+'", SqlDbType.'+
    (SELECT CASE DATA_TYPE
    WHEN 'bigint' THEN 'BigInt'
    WHEN 'binary' THEN 'Binary, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'bit' THEN 'Bit'
    WHEN 'char' THEN 'Char, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'datetime' THEN 'DateTime'
    WHEN 'decimal' THEN 'Decimal, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'float' THEN 'Float'
    WHEN 'image' THEN 'Image'
    WHEN 'int' THEN 'Int'
    WHEN 'money' THEN 'Money'
    WHEN 'nchar' THEN 'NChar, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'ntext' THEN 'NText'
    WHEN 'numeric' THEN 'Decimal, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'nvarchar' THEN 'NVarChar, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'real' THEN 'Real'
    WHEN 'smalldatetime' THEN 'SmallDateTime'
    WHEN 'smallint' THEN 'SmallInt'
    WHEN 'smallmoney' THEN 'SmallMoney'
    WHEN 'sql_variant' THEN 'Variant'
    WHEN 'text' THEN 'Text'
    WHEN 'timestamp' THEN 'Timestamp'
    WHEN 'tinyint' THEN 'TinyInt'
    WHEN 'uniqueidentifier' THEN 'UniqueIdentifier'
    WHEN 'varbinary' THEN 'VarBinary, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    WHEN 'varchar' THEN 'VarChar, '+ltrim(cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)))
    ELSE '!MISSING'
                         END)+').Value = '+substring(PARAMETER_NAME,2,300)+';'
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @procName
union all
select 901,''
union all
SELECT 902,'    cmd.Parameters["'+PARAMETER_NAME+'"].Direction = ParameterDirection.InputOutput;'
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @procName
and PARAMETER_MODE = 'INOUT'
union all
SELECT 903,'    cmd.Parameters["'+PARAMETER_NAME+'"].Direction = ParameterDirection.Output;'
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @procName
and PARAMETER_MODE = 'OUT'
union all
select 904,case when @isGet = 1 then '    return GetTable(cmd);' else '    ExecuteNonQuery(cmd);' end
union all
select 905, '}'
 ) x
order by SortOrder


end
 

 

 

 

 

 

 

Tags:

Development | SQL

One Script

OneScript Continuous Integration for you database Scripts in version control are automatically combined into a release script.

RecentPosts