Archive for asp.net

Insert Unicode Chinese Characters into SQL Server

Problem: Insert Chinese characters (or other Unicode) into SQL Server (or SQL Server Express).  The characters are inserted as question marks.  What gives?

Solution:

Instead of this: insert into table(field) values (’惨’)

Do this:  insert into table(field) values (N’惨’)

Just use the N prefix on any unicode field and it should work.

Source:http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21699281.html (just scroll down to the bottom)

Leave a Comment

August 2, 2009 · Filed under asp.net, chinese

Create a Simple XML Document

Here’s how you can create a simple XML document in .net without saving it first to a file.  This lets you create an xml document in memory and do what you want with it.

Imports System.Xml
 
...
 
'Create StringWriter to write the xml return string
Dim sw As System.IO.StringWriter = New System.IO.StringWriter
Dim writer As XmlTextWriter = New XmlTextWriter(sw)
 
writer.WriteStartDocument()
writer.WriteStartElement("Movies")
 
writer.WriteStartElement("Movie")
writer.WriteAttributeString("name", "Forrest Gump")
writer.WriteStartElement("Actors")
writer.WriteStartElement("Actor")
writer.WriteAttributeString("name", "Tom Hanks")
writer.WriteEndElement() 'End Actor
writer.WriteStartElement("Actor")
writer.WriteAttributeString("name", "Gary Sinise")
writer.WriteEndElement() 'End Actor
writer.WriteEndElement() 'End Actors
writer.WriteEndElement() 'End Movie
 
writer.WriteEndElement() 'End Movies
writer.WriteEndDocument()
writer.Close()
 
Return sw.ToString()

This code returns the following XML string:

Leave a Comment

October 31, 2008 · Filed under asp.net

Using ASP.NET DataSources Without TableAdapters

You don’t have to use TableAdapters to use some of the advantages of strongly-typed DataSets and declarative DataSources.  In fact, leaving out table adapters can make your code more flexible, and frees you from some of the TableAdapter’s awkward behavior.

If you’re not familiar with typed datasets and table adapters, take a look at these tutorials.  Here’s what I did to avoid using TableAdapters while still taking advantage of BLLs and declarative DataSources.

1. Created a blank dataset called CustomDataSet. Added a blank table to the dataset, and then added columns to that table.

Add DataTable to the DataSet

Add DataTable to the DataSet

2. Created a Business Logic Layer (BLL), and put the results of my executed command into the table I created in step 1.

'I'm using the Oracle Data Provider, not the Microsoft one, but you can use whatever gets you connected to the db
Imports System.Data
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types
 
Imports CustomDataSet  'Reference the custom dataset we just created
 
 _
Public Class aBLL
     _
    Public Function getStuff() As CustomDataTable  'Reference the CustomDataTable we created
 
        'Create a connection to the db
 
        'Create a command (called cmd is my case)
 
        'Create a data adapter, which we use to move data from my database to the table
        Dim MyDA As New OracleDataAdapter
 
        'Create an instance of the CustomDataSet
        Dim ds As CustomDataSet = New CustomDataSet
 
        'Put your command results into the table we created
        MyDA.SelectCommand = cmd
        MyDA.Fill(CustomDataTable)
        Return ds.CustomDataTable
    End Function
End Class

3. Then all you’ve got to do is create an Object DataSource in the .aspx page, and link that datasource to your datagrid.

I’ve also found this to be a lot more flexible than having to work with table adapters–which I think are too complicated and restrictive. This way, I get to create the code like I want it and still take advantage of some of the cool stuff .net offers.

Leave a Comment

October 12, 2008 · Filed under asp.net

Create and Send CSV Without Saving File to a Drive

You want to dynamically create a CSV file and make it available for download for your user.  But you don’t want to save the file to the drive.  You just want to create the file on the fly, send it to the user, and let it evaporate into thin air after it’s downloaded.  You can do that using a memorystream and streamwriter.

In this example, I take a datagrid, write its contents to a bytearray, and then send the array to the user as an http response.

Dim mstream As MemoryStream = New MemoryStream()
Dim writer As StreamWriter = New StreamWriter(mstream)
 
Dim Str As String
Dim i As Integer
Dim j As Integer
 
Dim headertext As String = "Col1,Col2,Col3"
writer.WriteLine(headertext)
For i = 0 To (Me.GridView1.Rows.Count - 1)
    For j = 0 To (Me.GridView1.Columns.Count - 1)
 
        'this IF statement stops it from adding a comma afterthe last field
        If j = (Me.GridView1.Columns.Count - 1) Then
            Str = (Me.GridView1.Rows(i).Cells(j).Text.ToString)
        Else
            Str = (Me.GridView1.Rows(i).Cells(j).Text.ToString & ",")
        End If
 
        writer.Write(Str)
    Next
    writer.WriteLine()
Next
 
writer.Flush()
writer.Close()
 
Dim byteArray As Byte() = mstream.ToArray()
 
mstream.Flush()
mstream.Close()
 
Response.Clear()
Response.AddHeader("Content-Disposition", "attachment; filename=aFile.csv")
Response.AddHeader("Content-Length", byteArray.Length.ToString())
Response.ContentType = "application/octet-stream"
Response.BinaryWrite(byteArray)
Response.Flush()
Response.Close()

Leave a Comment

October 12, 2008 · Filed under asp.net