Programming Journal C#, Java, SQL and to a lesser extent HTML, CSS, XML, and regex. I made this so other programmers could benefit from my experience.

Thursday, September 27, 2007

Connecting to Kensoft.net SQL Server 2005 database

The side note for this is that the database name in Kensoft has an '_' in it, not a '.'.


<connectionStrings>
<!--<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Integrated Security=True; User Instance=True" providerName="System.Data.SqlClient"/>
-->
<add name="ConnectionString" connectionString="Data Source=mssql2005.kensoft.net;Database=myDatabase;uid=myUserId;pwd=myPassword" providerName="System.Data.SqlClient" />
</connectionStrings>

Copying a MDF SQLExpress Database file to SQL Server 2005 Server

Copying a MDF SQLExpress Database objects to SQL Server 2005:



  1. First make sure myDatabase.mdf is on your local machine unless you already
    have remote access enabled.


  2. Open Microsoft SQL Server Management Studio Express. Hint: Run as Administrator on Vista.


  3. Make sure myDatabase is listed as a Database in the Object Explorer,
    otherwise attach it.


  4. Right click myDatabase -> Tasks -> Generate Scripts -> [select]
    myDatabase -> Select All ... ->Script to new Query Window -> Finish


Next



  1. In the Object Explorer open the remote database where you want the copied
    tables and views.


  2. Right click myTargetDatabase ->Tasks -> New Query


  3. Paste in copied script from step 4 from above.


  4. Run



link to download Microsoft SQL Server Management Studio Express
Note: Be careful with constraint options. I also had to copy and paste the data from the source to target data tables in Microsoft SQL Server Management Studio Express like an Excel copy and paste.

Tuesday, September 25, 2007

Building an AJAX Slide Show in ASP.net

This was almost simple, but then the image paths made plug and chug necessary until the path problem was resolved. The key is to use image paths in the web service without ~/ and without /. Instead just type the directory name and the file name.



using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;


/// <summary>
/// Summary description for WebServiceSlideShow
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService()]
public class WebServiceSlideShow : System.Web.Services.WebService {

public WebServiceSlideShow () {

//Uncomment the following line if using designed components
//InitializeComponent();
}

[WebMethod]
public AjaxControlToolkit.Slide[] GetSlides() {
AjaxControlToolkit.Slide[] slides;
System.Collections.ArrayList alSlideUrls = new ArrayList();
alSlideUrls.Add("_imgLibrary/photo1.jpg");
alSlideUrls.Add("_imgLibrary/photo2.jpg");
alSlideUrls.Add("_imgLibrary/photo3.jpg");
slides = new AjaxControlToolkit.Slide[alSlideUrls.Count];
int i = 0;
foreach (string str in alSlideUrls)
{
slides[i] = new AjaxControlToolkit.Slide(str, str, str);
i++;
}
return slides;
}
}

Here is the ASPX code:


<div style="text-align: center">
<table style="vertical-align: text-top">
<tr>
<td style="width: 278px" nowrap="noWrap" align="center">
<asp:Image ID="ImagePlayer" runat="server" />
</td>
</tr>
<tr>
<td nowrap="nowrap" style="width: 278px" align="center">
<asp:Button ID="btnBack" runat="server" Text="Back" />
<asp:Button ID="btnPlay" runat="server" Text="Play" />
<asp:Button ID="btnNext" runat="server" Text="Next" />&nbsp;
</td>
</tr>
</table>
</div>
<cc1:SlideShowExtender ID="SlideShowExtender1" runat="server" TargetControlID="ImagePlayer" AutoPlay="True" Loop="True" NextButtonID="btnNext" PlayButtonID="btnPlay" PlayButtonText="Play" PreviousButtonID="btnBack" SlideShowServiceMethod="GetSlides" SlideShowServicePath="WebServiceSlideShow.asmx" StopButtonText="Pause">
</cc1:SlideShowExtender>

Saturday, September 22, 2007

Proper ViewState use and the coalescing operator

Here is a quick lesson the ViewState object for viewing an object's state. I found the use of the coalescing operator helpful for future use instead of wordy if then else statments.
  • public int CompanyID

    {

    get { return (int)(ViewState["CompanyID"] ?? 0); }

    set { ViewState["CompanyID"] = value; }

    }
The coalescing operator is a brother of if-then-else and
the more succinct: if (isX ? doA() : elseDoB).
I would describe coalescing as shorthand for: if (isX!=null) then getX() else getB().

Monday, September 17, 2007

GridView table headers and iterating over a GridView cells in the row and columns.

Here is a function to get the GridView's table header columns:


protected string getTableHeaders()
{
string retVal = "";
int TotalColumns = GridView1.Columns.Count;
int col = 0;
for (col = 0; col < TotalColumns; col++)
{
retVal += GridView1.Columns[col].HeaderText;
if ((col + 1) < TotalColumns) retVal += ", ";
}
return retVal;
}


Here is how to iterate over the GridView cells to extract row and column data into a string. The cells can be custom delimited. Notice how this does not include Column.HeaderText :


protected string getDataCells()
{
string retVal = "";
int TotalColumns = GridView1.Columns.Count;
int TotalRows = GridView1.Rows.Count;
int col = 0;
int row = 0;
if (TotalColumns > 0 && TotalRows > 0)
{
for (row =0; row<TotalRows; row++)
{
for (col = 0; col < TotalColumns; col++)
{ // uses arbitrary ',' between column cells
retVal += GridView1.Rows[row].Cells[col].Text;
if ((col + 1) < TotalColumns) retVal += ", ";
}
// uses arbitrary ';' for end of row
retVal += ";";
}
}
return retVal;
}

Sunday, September 16, 2007

DataGridView cell contents and header contents




// How do I get the contents of a GridView cell?
GridView1.Rows[0].Cells[0].Text;
// Note: This will only get the result data and not the table headers a.k.a. column names.

// How do I get the contents of a GridView table header (<th>) a.k.a. column name?
GridView1.Columns[0].HeaderText;

Friday, September 14, 2007

HTML Table header accessibility standards

This fascinating piece of law regarding federal accessibility standards on HTML table headers requires data tables that contain two more rows or columns to identify row and column headers. The header fields that you create render as , and the columns reference the headers. . The bottom line: Use TH for table headers.

ILLEGAL:


<TABLE>
<TR><TD>Stock</TD><TD>Price</TD></TR>
<TR><TD>AAPL</TD><TD>133.33</TD></TR>
</TABLE>


LEGAL


<TABLE>
<TR><TH SCOPE="COL">Stock</TH> <TH SCOPE="COL">Price</TH></TR>
<TR><TD>AAPL</TD> <TD>133.33</TD></TR>
</TABLE>


Microsoft Article: from excerpt
To better understand the DataGrid's rendering machinery, it's useful to review how the DataGrid's implementation has changed after the hotfix discussed in Knowledge Base article 823030 ("FIX: DataGrid Made Compliant with Section 508 of the Rehabilitation Act Amendments of 1998"). In ASP.NET 1.1, the DataGrid's markup is not compliant with Section 508 of the Rehabilitation Act Amendments of 1998, a U.S. law regarding accessibility standards. To resolve this problem, Microsoft made a hotfix rollup package available, as explained in the aforementioned article. In short, the act states that data tables that contain two or more rows or columns must clearly identify row and column headers. This means that, at the very minimum, the header row(s) of an HTML table must define its cells through the TH tag instead of the ordinary TD tag.

C# switch statement for DropDownList to update Image url.

Simple control-flow statement to reference C# switch statement, which is similar to other language's case statement.


switch (DropDownList1.SelectedValue)
{
case "Image 1":
imgFoo.ImageUrl = "images/photo1.jpg";
break;
case "Image 2":
imgFoo.ImageUrl = "images/photo2.jpg";
break;
case "Image 3":
imgFoo.ImageUrl = "images/photo3.jpg";
break;
default:
imgFoo.ImageUrl = "images/photo_blank.jpg";
break;
}

Using XPath for reading attribute and content of XML

I used this type of XPath functionality for creating a rotating image based on an XML file.


<-- First the XML -->
<images>
<image href="img1">1</image>
<image href="img2">2</image>
</images>

<-- Next the XPath for C# -->
<-- get all image hrefs (and other attributes) -->
images//image

<-- get image value at image position 1 -->
images//image[position()=1]

<-- get href attribute at images/image position 1 -->
images//image[position()=1][@href]

Nested GridView with subtotals, totals, and counts

This complicated example shows a nested GridView with subtotals, totals, and counts. The actual process of modifying the GridView ItemTemplates is not explained here. Note: The SQL should be replaced with a stored procedure for production.
NestedGridView example


<script runat="server">
Decimal masterTotal = 0;
Decimal masterCount = 0;
Decimal priceTotal = 0;
int priceCount = 0;
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{

if (e.Row.RowType == DataControlRowType.DataRow)
{
SqlDataSource s = (SqlDataSource)e.Row.FindControl("SqlDataSource2");
String str = e.Row.Cells[0].Text;
s.SelectParameters[0].DefaultValue = e.Row.Cells[0].Text;

}
}

protected void GridView2_RowDataBound(object sender, GridViewRowEventArgs e)
{

if (e.Row.RowType == DataControlRowType.DataRow)
{
// add the UnitPrice and QuantityTotal to the running total variables
priceTotal += Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "contribution_amount"));
priceCount++;
masterCount++;
}
else if ((e.Row.RowType == DataControlRowType.Footer) )
{
masterTotal += priceTotal;
if (priceCount > 1)
{
e.Row.Cells[0].Text = "Subtotal:";
// for the Footer, display the running totals
e.Row.Cells[2].Text = priceCount.ToString();
e.Row.Cells[3].Text = priceTotal.ToString("c");
e.Row.Cells[3].HorizontalAlign = e.Row.Cells[2].HorizontalAlign = HorizontalAlign.Right;
e.Row.Font.Bold = true;
{
GridView1.FooterRow.Cells[0].Text = "Total:";
// for the Footer, display the running totals
GridView1.FooterRow.Cells[1].Text = masterCount.ToString();
GridView1.FooterRow.Cells[1].Text += "&nbsp&nbsp&nbsp&nbsp" + masterTotal.ToString("c");
GridView1.FooterRow.Cells[1].HorizontalAlign = GridView1.FooterRow.Cells[1].HorizontalAlign = HorizontalAlign.Right;
GridView1.FooterRow.Font.Bold = true;
}
}
else if (priceCount == 1)
{// blank out borders and background color for 1 row footers

e.Row.Visible = false;
}
else
{
e.Row.Visible = false;
}
priceTotal = 0;
priceCount = 0;
}
}


</script>

Here is the html part of the aspx:


<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Campaign Finance Contributions By Date</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table align="center">
<tr>
<td align="center" nowrap="nowrap" style="width: 253px; height: 92px">
<asp:Image ID="TelWebImage" runat="server" ImageAlign="Middle" ImageUrl="~/tel-web.gif"
Style="z-index: 101; left: 7px; position: relative; top: 9px" /></td>
</tr>
<tr>
<td align="center" nowrap="nowrap" style="width: 253px; height: 42px">
<asp:Label ID="TitleLabel" runat="server" Font-Size="Larger" Style="z-index: 100;
left: 4px; position: relative; top: 0px"
Text="Campaign Finance Contributions"
Width="206px"></asp:Label></td>
</tr>
<tr>
<td align="center" nowrap="nowrap" style="width: 253px">
<asp:Label ID="ByDateLabel" runat="server" Style="z-index: 102; left: 16px; position: relative;
top: 0px"
Text="BY CONTRIBUTION DATE"></asp:Label></td>
</tr>
<tr>
<td align="center" nowrap="nowrap" style="width: 253px; height: 19px">
<asp:Label ID="DateTimeLab" runat="server" Style="left: 0px; position: relative;
top: 2px"
Text="Date and Time"></asp:Label></td>
</tr>
</table>
</div>
&nbsp;&nbsp;
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT contribution_date FROM ContributionCandidateview WHERE (contribution_number LIKE @contribution_number)">
<SelectParameters>
<asp:Parameter DefaultValue="%" Name="contribution_number" />
</SelectParameters>
</asp:SqlDataSource>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"></asp:ObjectDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound" ShowFooter="True">
<Columns>
<asp:BoundField DataField="contribution_date" HeaderText="contribution_date" SortExpression="contribution_date" />
<asp:TemplateField HeaderText="Contributions">
<ItemTemplate>
&nbsp;
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [candidate_name], [contribution_date], [contribution_name1], [contribution_amount] FROM [ContributionCandidateview] WHERE ([contribution_date] = @contribution_date)">
<SelectParameters>
<asp:Parameter Name="contribution_date" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView2" runat="server" DataSourceID="SqlDataSource2" OnRowDataBound="GridView2_RowDataBound" ShowFooter="True" Width="500px">
<AlternatingRowStyle BorderColor="White" BackColor="#E0E0E0" />
<FooterStyle BackColor="#DFFFDF" />
<HeaderStyle BackColor="#DFDFFF" />
</asp:GridView>
</ItemTemplate>
<ItemStyle VerticalAlign="Bottom" />
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#DFDFFF" />
</asp:GridView>
</form>
</body>
</html>

Modifying the contents of a GridView after DataBinding

This is more than bare functionality, but it can be simplified by eliminating the contents in foreach brackets.


protected void GridView1_DataBound(object sender, EventArgs e)
{
foreach (GridViewRow dgrow in GridView1.Rows)
{
String myString, myPattern;
Boolean result;
myPattern = txtSearch.Text;
myString = dgrow.Cells[0].Text;
result = true;

if (isEndsWith == true)
{
if (myPattern.Length > 0)
{
result = myString.EndsWith(myPattern);
dgrow.Cells[0].Text = myString.Substring(0, myString.LastIndexOf(myPattern))
+ HighlightText(myPattern, myString.Substring(myString.LastIndexOf(myPattern)));
}
else result = false;
if (result == false)
dgrow.Cells[0].Visible = false;
}
else
{
if (isStartsWith)
{
if (myPattern.Length > 0)
{
result = myString.StartsWith(myPattern);
dgrow.Cells[0].Text = HighlightText(myPattern, myString.Substring(0,myPattern.Length)) + myString.Substring(myPattern.Length, myString.Length-myPattern.Length);
dgrow.Cells[0].Visible = true;
}
}
else
{
dgrow.Cells[0].Text = HighlightText(myPattern, myString);
dgrow.Cells[0].Visible = true;
}
}
}
}

Highlight search text functions using css tags and regex



protected string HighlightText(string searchWord, string inputText)
{

Regex expression = new Regex(searchWord.Replace(" ", "|"));
// Regex.ignorecase

return expression.Replace(inputText, new MatchEvaluator(ReplaceKeywords));
}
public string ReplaceKeywords(Match m)
{
return "<span class='highlight'>" + m.Value + "</span>";
}

Thursday, September 13, 2007

C# to run stored procedure after parsing text



char[] sSeparators = { ',', ' ' };
string[] sStocks = txtStocks.Text.Split(sSeparators);
SqlConnection conn = new SqlConnection("AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;User Instance=True;Data Source=.\\SQLEXPRESS;Integrated Security=True;");
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("sproc_aspnet_CreateStockInList", conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
conn.Open();
foreach (string _stock in sStocks)
{
command.Parameters.AddWithValue("@ListId", txtListId.Text.Trim());
command.Parameters.AddWithValue("@Symbol", _stock.Trim());
command.ExecuteNonQuery();
command.Parameters.Clear();
}
conn.Close();