flyinweb's blog - DotNet专栏

Creating your SQL Server Compact Edition database and schema in code

shanyiwan@live.com() — Wed, 04 Jan 2012 03:01:27 GMT

In most cases you'll want to create your database and it's schema as part of your applications first deployment. Depending on the scenario, you may create it on the client, or create it on the server, do the initial sync, then stream the file down to the client pre-populated.

Why would you create the database on the fly, rather than ship the initial database with your app?

While you could create the empty shell, and deploy it, you'll almost never want to start with a database that has data? Why? While in development, you'll have some set of data. Months later, when another user first installs your app that data may no longer be valid. Since most sync systems don't track deletes forever, you could wind up with data that is very stale, and now way to effectively update it.

Data Files and Click Once

If you're using ClickOnce, the other problem is how ClickOnce manages data files. If you initially ship your app with a datafile that your customers edit you could loose those changes. If the file timestamp on your data file changes, ClickOnce will try to "update it" and replace the data file in your ClickOnce data directory. It does move the previous version to a .pre folder, but only once. If you realized you made this mistake, and send another update to ‘fix it", you could again move the data file to the .pre folder, and the original one that had the data you were trying to save will be permanently lost.

For this post, I'm going to focus on how to create the database and schema. You'll see that with a combination of some SQLServerCe APIs, and the use of Resources, it's not very difficult to create, test within SQL Server Management Studio and deploy these updates to your client. This same procedure could be used for the initial deployment, as well as app updates where you may need to add new tables/columns, or add new relationships, constraints or indexes.

When an app starts up, I like to validate a few things like whether this is the first time the app was installed, if it's the appropriate version, if all the necessary files still exist, as well as online/offline status.

Does the database exist?

I'll first create a class called DatabaseHealthCheck() I'll first check if the database even exists. Since with SQL Server Compact a database is a file, and a file is a database, it's pretty straight forward. The file either exists, or it doesn't. The connection string for a SQLce database contains a few name/value pairs depending on the options.

Data Source ="|DataDirectory|\Another.sdf"; Password ="SomePassword";

Rather than parse the string, or build it up manually, the SqlCeConnection object there's a Database property that many don't even think to look for. Since the database is a file..., all you need to do is create a connection object, and ask for the Database property. No need to open the connection, which wouldn't work if the database/file didn't exist.

Dim localConnection AsNew SqlCeConnection(My.Settings.LocalConnectionString)

IfNot System.IO.File.Exists(localConnection.Database)

Nope, doesn't exist, let's create it

Next, if the database didn't exist, we'll need to create it. This is where it gets interesting. Since SQLce doesn't run as a service, you can't open a connection to the Master database and issue a Create Database command. No problem, SQLce has just the API for you. How about these two lines of code:

Dim sqlCeEngine AsNew SqlCeEngine(My.Settings.LocalConnectionString)

sqlCeEngine.CreateDatabase()

Pretty simple, huh. For those of you that were wondering why you would use SQL Server Compact Edition as your local database, now you're probably starting to see how SQLce can be used to reduce the complexity really needed in a local/embedded database.

Creating the initial schema

It would be nice if we had a simple CreateTable API on the SqlCeEngine object, but we're not there yet. But we can leverage T-SQL that we'd write in SQL Server Management Studio, at least the appropriate subset. We're all familiar, although it's likely not our favorite, with the ability to create scripts, separated by the GO command. Using this model we can test scripts, save them and re-execute them.

CREATE TABLE Suppliers(

SupplierID nVarChar(10) NOT NULL CONSTRAINT Suppliers_PK PRIMARY KEY,

CompanyName nvarchar(40) NOT NULL,

Description nvarchar(100) NULL,

SortOrder int NOT NULL DEFAULT(50),

Active bit NOT NULL DEFAULT (1)

)

CREATE TABLE OrderStatus(

OrderStatus nChar(10) NOT NULL CONSTRAINT OrderStatus_PK PRIMARY KEY,

Status nvarchar(40) NOT NULL,

Description nvarchar(100) NULL,

SortOrder int NOT NULL DEFAULT(50),

Active bit NOT NULL DEFAULT (1)

)

The problem is how do we get this in our code? We could embed the whole thing in quotes in our code:

Dim commandTest AsString

commandTest = "CREATE TABLE OrderStatus(" + _

" OrderStatus nChar(10) NOT NULL CONSTRAINT OrderStatus_PK PRIMARY KEY," + _

"Status nvarchar(40) NOT NULL,"

But we don't have to get past line 2 before you realize, that sucks. And, if you ever want to make a change, you'll have to decrypt this to put it back inside a query window. No problem, this is where we can leverage the handy dandy resource features.

Adding a SQL Script Resource

We'll start by creating a script in SQL Server Management Studio. You can use the above CREATE TABLE scripts above to get things started. Within Management Studio, choose to save the script. It doesn't really matter where, because VS will pull a copy into your project in the next step.

Name the script something with a .sql extension. If you do this, when you open the file within Visual Studio, you'll get T-SQL syntax coloring.

Within Visual Studio, open the Resource designer. For VB, double click My Project from within your Solution Explorer. For C#, double click Properties. Click on the Resources tab on the left. From the toolbar select Add Resource à Add Existing Resource. Select the file you just created, and voila.

Now you might ask why I didn't just create the file here using the Add Resource à Add New Text File. If I went that path, and I named the file Script.sql, Visual Studio would have added .txt to it so it would wind up as Script.sql.txt. If I then renamed the file in Solution Explorer, the link in the resources would be broken, and I would have to delete the resource reference, and then drag the renamed file back to the resource designer. Either works, this second way just highlights a less optimized scenarios. But, I guess I just did that. ...either way...

Double click the script file you added, and voila, T-SQL syntax coloring. You may get a warning dialog, but just ignore it. The project template doesn't understand this extension, yet Visual Studio does. You could just uncheck the checkbox so you won't get nagged anymore.

Now, it would be nice if you could just execute the script, but no, this isn't enabled in non-database projects.

Executing the script

We now have a script that we can copy/paste back and forth between Visual Studio and SQL Server Management Studio. But how do I execute this with my app? This is where we start to see the sum of the parts come together. We can use the My.Resources api, or in C#, the Resources.Default api to get a strongly typed experience to the resources in your project. We'll then simply use the String.Split api to parse the commands on the word GO.

Dim commands() AsString

commands = My.Resources.DatabaseCreation.Split( _

NewString() {"GO"}, _

StringSplitOptions.RemoveEmptyEntries)

Putting it all together

That's about it, we just need to put this all together now.

PublicClass DatabasHealthCheck

PublicSub VerifyDatabaseExists(ByVal connectionString AsString)

' we'll use the SqlServerCe connection object to get the database file path

Using localConnection AsNew SqlCeConnection(My.Settings.LocalConnectionString)

' The SqlCeConnection.Database contains the file parth portion

' of the database from the full connectionstring

IfNot System.IO.File.Exists(localConnection.Database) Then

' No file, no database

Using sqlCeEngine AsNew SqlCeEngine(connectionString)

sqlCeEngine.CreateDatabase()

CreateInitialDatabaseObjects(connectionString)

EndUsing

EndIf

EndUsing

EndSub

''' <summary>

''' When the database is created, we need to initialize it with new tables, relations and possibly data

''' </summary>

PublicSub CreateInitialDatabaseObjects(ByVal connectionString AsString)

' Grab a reference to the connection on the client provider

Using connection AsNew SqlCeConnection(connectionString)

' Using the SQL Management Studio convention of using GO to identify individual commands

' Create a list of commands to execute

Dim commands() AsString

' To simplify editing and testing TSQL statements,

' the commands are placed in a managed resource of the dll.

commands = My.Resources.DatabaseCreation.Split( _

NewString() {"GO"}, _

StringSplitOptions.RemoveEmptyEntries)

Dim cmd AsNew SqlCeCommand()

' make sure we put the connection back to its previous state

cmd.Connection = connection

connection.Open()

ForEach command AsStringIn commands

cmd.CommandText = command

cmd.ExecuteNonQuery()

EndUsing

EndSub

EndClass

In your application startup, simply execute the following code:

Dim databaseHealthCheck AsNew DatabasHealthCheck()

databaseHealthCheck.VerifyDatabaseExists(My.Settings.LocalConnectionString)

So, now you've got an easy way to create your initial database, or alter an existing database as part of your app. As always, I love to get your feedback and thoughts,

Membership&RoleManager(成员资格和角色管理)

shanyiwan@live.com() — Sun, 17 Jul 2011 03:40:48 GMT

介绍
现在 ASP.NET 2.0 提供了对成员资格（用户名/密码凭据存储）和角色管理服务的内置支持。由于所有这些服务都是提供程序驱动的（Provider），因此可以方便地用您自己的自定义实现替换。

关键
1、基于Windows的身份验证将<system.web>元素下的<authentication> 设置为 Windows；基于Forms的身份验证将<system.web>元素下的<authentication> 设置为 Forms。

2、基于Forms的身份验证时，设置<system.web>元素下的<authentication> 元素的 <forms> 子元素，示例如下，仅为说明

<forms>元素的属性说明如下
1) cookieless - 身份验证可以将 Forms 身份验证票存储在 Cookie 中也可以以无 Cookie 的表示形式存储在 URL 上。有效值如下：
·UseDeviceProfile - 默认值表示 ASP.NET 根据预先计算得到的浏览器配置文件来确定存储票证的位置。
·AutoDetect - 选项使 ASP.NET 动态确定浏览器是否支持 Cookie。
·UseUri - 强制实施无 Cookie 票证
·UseCookies - 强制实施有 Cookie 票证。
2) defaultUrl - 指定在成功登录后，请求将重定向到的默认 URL。
3) domain - 指定包含 Forms 身份验证票的 HttpCookie 的 Domain 属性的值。显式设置此属性可使应用程序共享同一个 Cookie，前提是这些应用程序共享某个 DNS 命名空间的一个公共部分（例如，如果 domain 属性设置为“cnblogs.com”，则 webabcd.cnblogs.com 和 dudu.cnblogs.com可以共享一个 Cookie）。
4) enableCrossAppRedirects - Forms 身份验证允许以查询字符串变量或窗体 POST 变量的形式在应用程序之间传递 Forms身份验证票。将此属性设置为 true 可使 FormsAuthenticationModule 能够从查询字符串或窗体 POST 变量提取票证。
5) loginUrl - 指定未经身份验证的用户的请求将被重定向到的 URL。该 URL 可以在同一台计算机上或在远程计算机上。如果是在远程计算机上，则两台计算机上 machineKey 配置元素中的 decryptionkey 和 validationKey 属性都需要使用相同的值。
6) name - 用于身份验证的 HTTP Cookie 的名称。注意，如果多个应用程序需要在一台计算机上使用基于窗体的身份验证服务，并且每个应用程序都希望由应用程序隔离 Forms 身份验证 Cookie，则每个应用程序都应配置一个唯一的 Cookie 值。为避免在 URL 中产生依赖项，在设置身份验证 Cookie 时，ASP.NET 还使用“/”作为 Path 值，以便将这些 Cookie 发送回站点上的每个应用程序。
7) path - 用于发出的 Cookie 的路径。默认值为“/”，以避免路径中大小写不匹配的造成的困难，因为在返回 Cookie 时，浏览器是严格区分大小写的。共享服务器环境中的应用程序应使用此指令来维护专用 Cookie。（它们还可以使用 API 在运行时指定路径来发出 Cookie。）
8) protection - 用于保护 Cookie 数据的方法。有效值如下：
·All - 同时使用数据验证和加密来保护 Cookie。所配置的数据验证算法是基于 <machinekey> 元素的。如果密钥足够长（48 个字符），默认情况下将使用 AES 进行加密。All 是默认（和建议）值。
·None - 用于仅将 Cookie 用于个性化设置并且安全性要求不高的站点。加密和验证都可以被禁用。尽管以此方式使用 Cookie 需谨慎，但对于使用 .NET Framework 实现个性化设置的任何方法，此设置提供了最佳性能。
·Encryption - 使用 AES、TripleDES 或 DES 加密 Cookie，但不对 Cookie 进行数据验证。这类 Cookie 容易受到精心选择的纯文本的攻击。
·Validation - 不加密 Cookie 的内容，但验证 Cookie 数据在传输过程中是否未被更改。若要创建 Cookie，验证密钥在缓冲区中与 Cookie 数据连接，并且计算出 MAC 并将其追加到输出的 Cookie。
9) requireSSL - 如果设置为 true，则 Forms 身份验证会设置 Forms 身份验证 Cookie 的安全位。兼容的浏览器只将 Cookie 通过 SSL 连接发送回 ASP.NET。注意，如果使用无 Cookie Forms 身份验证，则此设置无效。
10) slidingExpiration - 如果设置为 true，则 Forms 身份验证将定期更新 Forms 身份验证票的生存期。无论票证是包含在 Cookie 中，还是以无 Cookie 的格式包含在 URL 中，都会进行此操作。
11) timeout - 时间量（以整数分钟为单位），经过该时间量之后，Cookie 则会过期。默认值是 30。超时属性是一个可调值，从收到上次请求的时间开始计算，它将在 n 分钟后过期。为了避免对性能产生负面影响，也为了避免那些打开了 Cookie 警告的应用程序产生多个浏览器警告，Cookie 在超时时间过半时更新。（这意味着在某些情况下可能会出现精度损失。）（注意：createPersistentCookie为true的话，则该属性失效，过期时间将为50年）

3、授权用户和角色设置<system.web>元素下的<authorization>元素，示例如下，仅为说明

注：可以把授权用户和角色设置的配置写在某个文件夹内，则所做的配置只作用于该文件夹内，自动继承外面的配置。
allow - 允许
deny - 拒绝
users - 用户（多用户用逗号隔开）
roles - 角色（多角色用逗号隔开）
verb - 指定http方法，post或get
* - 所有用户
? - 匿名（未经过身份验证的）用户

4、分路径设置授权用户和角色设置，示例如下，仅为说明

<location>元素的path属性可以是文件夹也可以是某一文件

5、<membership>元素设置，示例如下，仅为说明

enablePasswordRetrieval - 是否可以检索用户密码（总是false）
enablePasswordReset - 是否允许用户重置其密码
requiresQuestionAndAnswer - 是否要求在创建用户时提供密码提示问题和答案
applicationName - 自定义成员资格提供程序的应用程序的名称
requiresUniqueEmail - 电子邮件地址是否必须是唯一的
passwordFormat - 存储的密码的格式
maxInvalidPasswordAttempts - 指定允许的无效密码或无效密码提示问题答案尝试的次数。当无效尝试的次数达到配置的值时，将锁定该成员资格用户
minRequiredPasswordLength - 密码所要求的最小长度
minRequiredNonalphanumericCharacters - 有效密码中必须包含的最少特殊字符数
passwordAttemptWindow - 跟踪失败的尝试所用的时间（以分钟为单位）。每当发生另一次失败时都将重置窗口。如果达到了允许的无效密码或密码提示问题答案的最大尝试次数，将锁定成员资格用户
passwordStrengthRegularExpression - 用于验证密码的正则表达式

6、<roleManager>元素设置，示例如下，仅为说明

各属性详细说明参看MSDN，索引处查找“roleManager 元素”

7、使用加密密码

使用FormsAuthentication.HashPasswordForStoringInConfigFile(String password, String passwordFormat)生成密码
Clear - 密码以明文形式存储
SHA1 - 密码存储为 SHA1 摘要
MD5 - 密码存储为 MD5 摘要

8、使用用户帐户模拟，在<system.web>元素下加如下元素

9、常用的就是如下这些东东
System.Web.Security.Membership，System.Web.Security.MembershipUser，System.Web.Security.Roles，Page.User
FormsAuthentication.RedirectFromLoginPage，FormsAuthentication.SetAuthCookie，FormsAuthentication.SignOut
重写那些Provider的话，参看源码中微软提供的示例

示例
读取用户名和验证类型
Security/Test.aspx

<%@ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="Test.aspx.cs"
Inherits="Security_Test" Title="读取用户名和验证类型"%>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">

用户名称：
<%=User.Identity.Name %>
 
验证类型：
<%=User.Identity.AuthenticationType %>

</asp:Content>

Membership测试
App_Code/User.cs

using System;  
using System.Data;  
using System.Configuration;  
using System.Web;  
using System.Web.Security;  
using System.Web.UI;  
using System.Web.UI.WebControls;  
using System.Web.UI.WebControls.WebParts;  
using System.Web.UI.HtmlControls;  
 
using System.ComponentModel;  
 
/// <summary>  
/// User 的摘要说明  
/// </summary>  
[DataObject(true)]  
public class User  
{  
    public User()  
    {  
        //  
        // TODO: 在此处添加构造函数逻辑  
        //  
    }  
 
    /// <summary>  
    /// 获得所有用户  
    /// </summary>  
    /// <returns></returns>  
    [DataObjectMethod(DataObjectMethodType.Select, true)]  
    public MembershipUserCollection GetMembers()  
    {  
        MembershipUserCollection muc = Membership.GetAllUsers();  
        return muc;  
    }  
 
    /// <summary>  
    /// 删除用户  
    /// </summary>  
    /// <returns></returns>  
    [DataObjectMethod(DataObjectMethodType.Delete, true)]  
    public void DeleteMember(string username)  
    {  
        Membership.DeleteUser(username, true);  
    }  
}

Security/User.aspx

<%@ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="User.aspx.cs"
Inherits="Security_User" Title="Membership测试"%>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">

<asp:Label ID="lblMsg" runat="Server" ForeColor="red"/>


用户名：
<asp:TextBox ID="txtUsername" runat="server"></asp:TextBox>


密    码：
<asp:TextBox ID="txtPassword" runat="server"></asp:TextBox>


<asp:Button ID="btnSubmit" runat="server" Text="添加" OnClick="btnSubmit_Click"/>


<asp:GridView ID="GridView1" runat="server" DataKeyNames="UserName" AutoGenerateColumns="False"
DataSourceID="ObjectDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True"/>
<asp:BoundField DataField="ProviderName" HeaderText="ProviderName" ReadOnly="True"
SortExpression="ProviderName"/>
<asp:CheckBoxField DataField="IsOnline" HeaderText="IsOnline" ReadOnly="True" SortExpression="IsOnline"/>
<asp:BoundField DataField="LastPasswordChangedDate" HeaderText="LastPasswordChangedDate"
ReadOnly="True" SortExpression="LastPasswordChangedDate"/>
<asp:BoundField DataField="PasswordQuestion" HeaderText="PasswordQuestion" ReadOnly="True"
SortExpression="PasswordQuestion"/>
<asp:CheckBoxField DataField="IsLockedOut" HeaderText="IsLockedOut" ReadOnly="True"
SortExpression="IsLockedOut"/>
<asp:BoundField DataField="Comment" HeaderText="Comment" SortExpression="Comment"/>
<asp:BoundField DataField="UserName" HeaderText="UserName" ReadOnly="True" SortExpression="UserName"/>
<asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email"/>
<asp:BoundField DataField="CreationDate" HeaderText="CreationDate" ReadOnly="True"
SortExpression="CreationDate"/>
<asp:CheckBoxField DataField="IsApproved" HeaderText="IsApproved" SortExpression="IsApproved"/>
<asp:BoundField DataField="LastLockoutDate" HeaderText="LastLockoutDate" ReadOnly="True"
SortExpression="LastLockoutDate"/>
<asp:BoundField DataField="LastLoginDate" HeaderText="LastLoginDate" SortExpression="LastLoginDate"/>
<asp:BoundField DataField="LastActivityDate" HeaderText="LastActivityDate" SortExpression="LastActivityDate"/>
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetMembers"
TypeName="User" DeleteMethod="DeleteMember">
<DeleteParameters>
<asp:Parameter Name="username" Type="String"/>
</DeleteParameters>
</asp:ObjectDataSource>


备注： 
用户和角色之间的操作如下 
Roles.AddUserToRole - 向角色添加用户 
Roles.RemoveUserFromRole - 从角色删除用户 
Roles.GetRolesForUser - 用户所属的角色列表 

</asp:Content>

Security/User.aspx.cs

using System;  
using System.Data;  
using System.Configuration;  
using System.Collections;  
using System.Web;  
using System.Web.Security;  
using System.Web.UI;  
using System.Web.UI.WebControls;  
using System.Web.UI.WebControls.WebParts;  
using System.Web.UI.HtmlControls;  
 
public partial class Security_User : System.Web.UI.Page  
{  
    protected void Page_Load(object sender, EventArgs e)  
    {  
 
    }  
 
    protected void btnSubmit_Click(object sender, EventArgs e)  
    {  
        MembershipCreateStatus status;  
        Membership.CreateUser(txtUsername.Text.Trim(), txtPassword.Text.Trim(), null, null, null, true, out status);  
 
        string strInsertMessage = "";  
 
        switch (status)  
        {  
            case MembershipCreateStatus.Success:  
 
                break;  
            case MembershipCreateStatus.DuplicateUserName:  
                strInsertMessage = "用户名重复";  
                break;  
            case MembershipCreateStatus.InvalidUserName:  
                strInsertMessage = "用户名输入错误";  
                break;  
            case MembershipCreateStatus.InvalidPassword:  
                strInsertMessage = "密码输入不符合要求";  
                break;  
            default:  
                strInsertMessage = "出现未知错误";  
                break;  
        }  
 
        if (strInsertMessage != "")  
        {  
            lblMsg.Text = strInsertMessage;  
        }  
        else 
        {  
            lblMsg.Text = "注册成功";  
            GridView1.DataBind();  
        }  
    }  
}

RoleManager测试
App_Code/Role.cs

using System;  
using System.Data;  
using System.Configuration;  
using System.Web;  
using System.Web.Security;  
using System.Web.UI;  
//using System.Web.UI.WebControls;  
//using System.Web.UI.WebControls.WebParts;  
//using System.Web.UI.HtmlControls;  
 
using System.Collections.Generic;  
using System.ComponentModel;  
 
/// <summary>  
/// Role 的摘要说明  
/// </summary>  
[DataObject(true)]  
public class Role  
{  
    public Role()  
    {  
        //  
        // TODO: 在此处添加构造函数逻辑  
        //  
    }  
 
    /// <summary>  
    ///  得到所有角色  
    /// </summary>  
    /// <param name="userName">用户名称</param>  
    /// <returns></returns>  
    [DataObjectMethod(DataObjectMethodType.Select, true)]  
    static public List<RoleData> GetRoles()  
    {  
        RoleData r = null;  
        List<RoleData> roleList = new List<RoleData>();  
        string[] ary = Roles.GetAllRoles();  
 
        foreach (string s in ary)  
        {  
            r = new RoleData();  
            r.RoleName = s;  
 
            roleList.Add(r);  
        }  
 
        return roleList;  
    }  
 
    /// <summary>  
    /// 删除角色  
    /// </summary>  
    /// <param name="roleName">角色名称</param>  
    [DataObjectMethod(DataObjectMethodType.Delete, true)]  
    static public void DeleteRole(string roleName)  
    {  
        MembershipUserCollection muc = Membership.GetAllUsers();  
        string[] allUserNames = new string[1];  
 
        foreach (MembershipUser mu in muc)  
        {  
            if (Roles.IsUserInRole(mu.UserName, roleName))  
            {  
                allUserNames[0] = mu.UserName;  
                Roles.RemoveUsersFromRole(allUserNames, roleName);  
            }  
        }  
        Roles.DeleteRole(roleName);  
    }  
}  
 
/// <summary>  
/// 角色的实体类  
/// </summary>  
public class RoleData  
{  
    protected string _roleName;  
 
    /// <summary>  
    /// 角色名称 关键字  
    /// </summary>  
    [DataObjectField(true)]  
    public string RoleName  
    {  
        get { return this._roleName; }  
        set { this._roleName = value; }  
    }  
}

Security/Role.aspx

<%@ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="Role.aspx.cs"
Inherits="Security_Role" Title="RoleManager测试"%>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">

<asp:Label ID="lblMsg" runat="Server" ForeColor="red"/>


角色名：
<asp:TextBox ID="txtRolename" runat="server"></asp:TextBox>


<asp:Button ID="btnSubmit" runat="server" Text="添加" OnClick="btnSubmit_Click"/>


<asp:GridView ID="GridView1" runat="server" DataKeyNames="RoleName" DataSourceID="ObjectDataSource1" AutoGenerateColumns="False">
<Columns>
<asp:CommandField ShowDeleteButton="True"/>
<asp:BoundField DataField="RoleName" HeaderText="RoleName" ReadOnly="True"
SortExpression="RoleName"/>
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="DeleteRole"
SelectMethod="GetRoles" TypeName="Role">
<DeleteParameters>
<asp:Parameter Name="roleName" Type="String"/>
</DeleteParameters>
</asp:ObjectDataSource>


备注： 
用户和角色之间的操作如下 
Roles.AddUserToRole - 向角色添加用户 
Roles.RemoveUserFromRole - 从角色删除用户 
Roles.GetRolesForUser - 用户所属的角色列表 

</asp:Content>

Security/Role.aspx.cs

using System;  
using System.Data;  
using System.Configuration;  
using System.Collections;  
using System.Web;  
using System.Web.Security;  
using System.Web.UI;  
using System.Web.UI.WebControls;  
using System.Web.UI.WebControls.WebParts;  
using System.Web.UI.HtmlControls;  
 
public partial class Security_Role : System.Web.UI.Page  
{  
    protected void Page_Load(object sender, EventArgs e)  
    {  
 
    }  
 
    protected void btnSubmit_Click(object sender, EventArgs e)  
    {  
        if (Roles.RoleExists(txtRolename.Text.Trim()))  
        {  
            lblMsg.Text = "该角色已存在";  
        }  
        else 
        {  
            Roles.CreateRole(txtRolename.Text.Trim());  
            GridView1.DataBind();  
        }  
    }  
}

注：需要用aspnet_regsql配置数据库
源码下载

Access LoginView Templated Controls with ASP 4.0 and C#

shanyiwan@live.com() — Fri, 15 Jul 2011 01:07:37 GMT

This tutorial we will demonstrate how to access ASP.NET controls located in the LoggedInTemplate of a LoginView Control from the C# code behind class.

Adding the LoginView Control
At this point in the tutorial I have created a new ASP.NET Empty Web Site and have added in a new blank Web Form named Default.aspx. What we need to do now is add a LoginView Control to the Web Form. To do this open up Default.aspx to Design mode and:

Expand the Login tab in your toolbox.
Drag and drop a LoginView Control on to the Web Form.

Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.

Now that we have added the LoginView Control, we have the option of adding ASP.NET controls in either the AnonymousTemplate or the LoggedInTemplate appropriately. Either way, when you add a control to a template it goes out of the scope of our Default.aspx.cs code behind class. Let’s add some controls to the LoggedInTemplate and see what happens when we try to access them in C# from our code behind class. To do this:

Expand the LoginView tasks menu.
In the Views DropDownList, select LoggedInTemplate.
Drag and drop a Label into the editable area of the LoginView Control.
Drag and drop a Button into the editable area of the LoginView Control under the Label.

We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.

Accessing Our Control
Right now we will leave the names of these controls default, Label1 and Button1. Normally, when you drop something onto a Web Form you can access the controls directly from the code behind class. However, with the controls in a template, this is not the case. To test this out open up the Default.aspx.cs code behind file and add in the following code to the Page_Load event method:

Label1.Text = "Hello World";

Notice, that intellisense immediately underlines Label1 and gives us an error that says ‘The name ‘Label1’ does not exist in the current context’.

This is a problem if we need to access this Label with C#. The way around this is to access it from the LoginView Control that we added earlier. To do this, get rid of the code we added earlier and open the Default.aspx back up to Design mode. Then double click the button we added earlier to generate a Click event method on that button. Then add the following code to that method:

((Label)LoginView1.FindControl("Label1")).Text = "Hello World";

If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!

What this will do, is call the FindControl method of the LoginView and find the Label we added named Label1. Then, we will cast that as a Label type so that we can access the Text property of it and change that to 'Hello World’. Go ahead and load up the website and click the button to make sure that it works.

The Default.aspx source looks like this:

<body>
<formid="form1"runat="server">
<div>
<asp:LoginViewID="LoginView1"runat="server">
<LoggedInTemplate>

<asp:LabelID="Label1"runat="server"Text="Label"></asp:Label>
<br/>
<asp:ButtonID="Button1"runat="server"Text="Button"onclick="Button1_Click"/>

</LoggedInTemplate>
</asp:LoginView>
</div>
</form>
</body>

The Default.aspx.cs codebehind looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

publicpartialclass_Default : System.Web.UI.Page
{
protectedvoid Page_Load(object sender, EventArgs e)
    {
//How we would access the control outside of a template
//Label1.Text = "Hello World";
    }
protectedvoid Button1_Click(object sender, EventArgs e)
    {
//How we have to access it inside of the template
        ((Label)LoginView1.FindControl("Label1")).Text = "Hello World";
    }
}

convert a network in CIDR notation

shanyiwan@live.com() — Fri, 28 Jan 2011 03:15:24 GMT

C# code to convert a network in CIDR notation (72.20.10.0/24) to an IP address range

References:

http://stackoverflow.com/questions/218604/whats-the-best-way-to-convert-from-network-bitcount-to-netmask

"Whatmask" C code from http://www.laffeycomputer.com/whatmask.html

Usage:

uint startIP, endIP;  
Network2IpRange("72.20.10.0/24",out startIP,out endIP);

The code assumes 32 bits for everything.

        staticvoidNetwork2IpRange(string sNetwork,outuint startIP,outuint endIP)
        {                       
                uint ip,                /* ip address */
                        mask,           /* subnet mask */                               
                        broadcast,      /* Broadcast address */
                        network;        /* Network address */

                int bits;                               

                string[] elements = sNetwork.Split(newChar[]{'/'});                 

                ip = IP2Int(elements[0]);
                bits =Convert.ToInt32(elements[1]);

                mask =~(0xffffffff>> bits);

                network = ip & mask;
                broadcast = network +~mask;

                usableIps =(bits >30)?0:(broadcast - network -1);

                if(usableIps <=0)
                {
                        startIP = endIP =0;
                }
                else
                {
                        startIP = network +1;                          
                        endIP = broadcast -1;
                }
        }

        publicstaticuint IP2Int(stringIPNumber)
        {
                uint ip =0;
                string[] elements =IPNumber.Split(newChar[]{'.'});
                if(elements.Length==4)
                {
                        ip  =Convert.ToUInt32(elements[0])<<24;
                        ip +=Convert.ToUInt32(elements[1])<<16;
                        ip +=Convert.ToUInt32(elements[2])<<8;
                        ip +=Convert.ToUInt32(elements[3]);
                }
                return ip;
        }

WinForm应用程序自动升级实现

shanyiwan@live.com() — Mon, 15 Nov 2010 06:59:27 GMT

最近单位开发一个项目，其中需要用到自动升级功能。因为自动升级是一个比较常用的功能，可能会在很多程序中用到，于是，我就想写一个自动升级的组件，在应用程序中，只需要引用这个自动升级组件，并添加少量代码，即可实现自动升级功能。因为我们的程序中可能包含多个exe或者dll文件，所以要支持多文件的更新。

首先，要确定程序应该去哪里下载需要升级的文件。我选择了到指定的网站上去下载，这样比较简单，也通用一些。在这个网站上，需要放置一个当前描述最新文件列表的文件，我们估且叫它服务器配置文件。这个文件保存了当前最新文件的版本号(lastver)，大小(size)，下载地址(url)，本地文件的保存路径(path)，还有当更新了这个文件后，程序是否需要重新启动(needRestart)。这个文件大致如下：
updateservice.xml

<?xml version="1.0" encoding="utf-8"?>
<updateFiles>
 <file path="AutoUpdater.dll" url="http://update.iyond.com/CompanyClientApplication/AutoUpdater.zip" lastver="1.0.0.0" size="28672" needRestart="true" />
 <file path="CompanyClient.exe" url="http://update.iyond.com/CompanyClientApplication/CompanyClient.zip" lastver="1.1.0.0" size="888832 " needRestart="true" />
 <file path="HappyFenClient.dll" url="http://update.iyond.com/CompanyClientApplication/HappyFenClient.zip" lastver="1.0.0.0" size="24576" needRestart="true" />
 <file path="NetworkProvider.dll" url="http://update.iyond.com/CompanyClientApplication/NetworkProvider.zip" lastver="1.0.0.0" size="32768" needRestart="true" />
 <file path="Utility.dll" url="http://update.iyond.com/CompanyClientApplication/Utility.zip" lastver="1.0.0.0" size="20480" needRestart="true" />
 <file path="Wizard.dll" url="http://update.iyond.com/CompanyClientApplication/Wizard.zip" lastver="1.0.0.0" size="24576" needRestart="true" />
</updateFiles>

同时，客户端也保存了一个需要升级的本地文件的列表，形式和服务器配置文件差不多，我们叫它本地配置文件。其中，<Enable>节点表示是否启用自动升级功能，<ServerUrl>表示服务器配置文件的地址。
update.config

<?xml version="1.0" encoding="utf-8"?>
<Config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <Enabled>true</Enabled>
 <ServerUrl>http://update.iyond.com/updateservice.xml</ServerUrl>
 <UpdateFileList>
 <LocalFile path="AutoUpdater.dll" lastver="1.0.0.0" size="28672" />
 <LocalFile path="CompanyClient.exe" lastver="1.1.0.0" size="888832 " />
 <LocalFile path="HappyFenClient.dll" lastver="1.0.0.0" size="24576" />
 <LocalFile path="NetworkProvider.dll" lastver="1.0.0.0" size="32768" />
 <LocalFile path="Utility.dll" lastver="1.0.0.0" size="20480" />
 <LocalFile path="Wizard.dll" lastver="1.0.0.0" size="24576" />
 </UpdateFileList>
</Config>

使用自动各级组件的程序在启动时，会去检查这个配置文件。如果发现有配置文件中的文件版本和本地配置文件中描述的文件版本不一致，则提示用户下载。同时，如果本地配置文件中某些文件在服务器配置文件的文件列表中不存在，则说明这个文件已经不需要了，需要删除。最后，当升级完成后，会更新本地配置文件。

我们先来看一下如何使用这个组件。
在程序的Program.cs的Main函数中：

[STAThread]
static void Main()
{
    Application.EnableVisualStyles();
    Application.SetCompatibleTextRenderingDefault(false);

    AutoUpdater au = new AutoUpdater();
    try
    {
        au.Update();
    }
    catch (WebException exp)
    {
        MessageBox.Show(String.Format("无法找到指定资源\n\n{0}", exp.Message), "自动升级", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    catch (XmlException exp)
    {
        MessageBox.Show(String.Format("下载的升级文件有错误\n\n{0}", exp.Message), "自动升级", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    catch (NotSupportedException exp)
    {
        MessageBox.Show(String.Format("升级地址配置错误\n\n{0}", exp.Message), "自动升级", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    catch (ArgumentException exp)
    {
        MessageBox.Show(String.Format("下载的升级文件有错误\n\n{0}", exp.Message), "自动升级", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    catch (Exception exp)
    {
        MessageBox.Show(String.Format("升级过程中发生错误\n\n{0}", exp.Message), "自动升级", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }

    Application.Run(new MainUI());
}

如上所示，只需要简单的几行代码，就可以实现自动升级功能了。

软件运行截图：

下面，我们来详细说一下这个自动升级组件的实现。
先看一下类图：

AutoUpdater：自动升级的管理类，负责整体的自动升级功能的实现。
Config：配置类，负责管理本地配置文件。
DownloadConfirm：一个对话框，向用户显示需要升级的文件的列表，并允许用户选择是否马上升级。
DownloadFileInfo：要下载的文件的信息
DownloadProgress：一个对话框，显示下载进度。
DownloadProgress.ExitCallBack，
DownloadProgress.SetProcessBarCallBack，
DownloadProgress.ShowCurrentDownloadFileNameCallBack：由于.NET2.0不允许在一个线程中访问另一个线程的对象，所以需要通过委托来实现。
LocalFile：表示本地配置文件中的一个文件
RemoteFile：表示服务器配置文件中的一个文件。
UpdateFileList：一个集合，从List<LocalFile>继承

我们先整体看一下AutoUpdater.cs：

AutoUpdater.cs
public class AutoUpdater
{
 const string FILENAME = "update.config";
 private Config config = null;
 private bool bNeedRestart = false;

 public AutoUpdater()
 {
 config = Config.LoadConfig(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FILENAME));
 }
 /// <summary>
 /// 检查新版本
 /// </summary>
 /// <exception cref="System.Net.WebException">无法找到指定资源</exception>
 /// <exception cref="System.NotSupportException">升级地址配置错误</exception>
 /// <exception cref="System.Xml.XmlException">下载的升级文件有错误</exception>
 /// <exception cref="System.ArgumentException">下载的升级文件有错误</exception>
 /// <exception cref="System.Excpetion">未知错误</exception>
 /// <returns></returns>
 public void Update()
 {
 if (!config.Enabled)
 return;
 /*
 * 请求Web服务器，得到当前最新版本的文件列表，格式同本地的FileList.xml。
 * 与本地的FileList.xml比较，找到不同版本的文件
 * 生成一个更新文件列表，开始DownloadProgress
 * <UpdateFile>
 * <File path="" url="" lastver="" size=""></File>
 * </UpdateFile>
 * path为相对于应用程序根目录的相对目录位置，包括文件名
 */
 WebClient client = new WebClient();
 string strXml = client.DownloadString(config.ServerUrl);

 Dictionary<string, RemoteFile> listRemotFile = ParseRemoteXml(strXml);

 List<DownloadFileInfo> downloadList = new List<DownloadFileInfo>();

 //某些文件不再需要了，删除
 List<LocalFile> preDeleteFile = new List<LocalFile>();

 foreach (LocalFile file in config.UpdateFileList)
 {
 if (listRemotFile.ContainsKey(file.Path))
 {
 RemoteFile rf = listRemotFile[file.Path];
 if (rf.LastVer != file.LastVer)
 {
 downloadList.Add(new DownloadFileInfo(rf.Url, file.Path, rf.LastVer, rf.Size));
 file.LastVer = rf.LastVer;
 file.Size = rf.Size;

 if (rf.NeedRestart)
 bNeedRestart = true;
 }

 listRemotFile.Remove(file.Path);
 }
 else
 {
 preDeleteFile.Add(file);
 }
 }

 foreach (RemoteFile file in listRemotFile.Values)
 {
 downloadList.Add(new DownloadFileInfo(file.Url, file.Path, file.LastVer, file.Size));
 config.UpdateFileList.Add(new LocalFile(file.Path, file.LastVer, file.Size));

 if (file.NeedRestart)
 bNeedRestart = true;
 }

 if (downloadList.Count > 0)
 {
 DownloadConfirm dc = new DownloadConfirm(downloadList);

 if (this.OnShow != null)
 this.OnShow();

 if (DialogResult.OK == dc.ShowDialog())
 {
 foreach (LocalFile file in preDeleteFile)
 {
 string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, file.Path);
 if (File.Exists(filePath))
 File.Delete(filePath);

 config.UpdateFileList.Remove(file);
 }

 StartDownload(downloadList);
 }
 }
 }

 private void StartDownload(List<DownloadFileInfo> downloadList)
 {
 DownloadProgress dp = new DownloadProgress(downloadList);
 if (dp.ShowDialog() == DialogResult.OK)
 {
 //更新成功
 config.SaveConfig(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FILENAME));

 if (bNeedRestart)
 {
 MessageBox.Show("程序需要重新启动才能应用更新，请点击确定重新启动程序。", "自动更新", MessageBoxButtons.OK, MessageBoxIcon.Information);
 Process.Start(Application.ExecutablePath);
 Environment.Exit(0);
 }
 }
 }

 private Dictionary<string, RemoteFile> ParseRemoteXml(string xml)
 {
 XmlDocument document = new XmlDocument();
 document.LoadXml(xml);

 Dictionary<string, RemoteFile> list = new Dictionary<string, RemoteFile>();
 foreach (XmlNode node in document.DocumentElement.ChildNodes)
 {
 list.Add(node.Attributes["path"].Value, new RemoteFile(node));
 }

 return list;
 }
 public event ShowHandler OnShow;
}

在构造函数中，我们先要加载配置文件：

public AutoUpdater()
{
config = Config.LoadConfig(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FILENAME));
}

最主要的就是Update()这个函数了。当程序调用au.Update时，首先检查当前是否开户了自动更新：

if (!config.Enabled)
return;

如果启用了自动更新，就需要去下载服务器配置文件了：

WebClient client = new WebClient();
string strXml = client.DownloadString(config.ServerUrl);

然后，解析服务器配置文件到一个Dictionary中：

Dictionary<string, RemoteFile> listRemotFile = ParseRemoteXml(strXml);

接下来比较服务器配置文件和本地配置文件，找出需要下载的文件和本地需要删除的文件：

List<DownloadFileInfo> downloadList = new List<DownloadFileInfo>();
 //某些文件不再需要了，删除
 List<LocalFile> preDeleteFile = new List<LocalFile>();

 foreach (LocalFile file in config.UpdateFileList)
 {
 if (listRemotFile.ContainsKey(file.Path))
 {
 RemoteFile rf = listRemotFile[file.Path];
 if (rf.LastVer != file.LastVer)
 {
 downloadList.Add(new DownloadFileInfo(rf.Url, file.Path, rf.LastVer, rf.Size));
 file.LastVer = rf.LastVer;
 file.Size = rf.Size;

 if (rf.NeedRestart)
 bNeedRestart = true;
 }

 listRemotFile.Remove(file.Path);
 }
 else
 {
 preDeleteFile.Add(file);
 }
 }

 foreach (RemoteFile file in listRemotFile.Values)
 {
 downloadList.Add(new DownloadFileInfo(file.Url, file.Path, file.LastVer, file.Size));
 config.UpdateFileList.Add(new LocalFile(file.Path, file.LastVer, file.Size));

 if (file.NeedRestart)
 bNeedRestart = true;
 }

如果发现有需要下载的文件，则向用户显示这些文件，并提示其是否马上更新。如果用户选择了马上更新，则先删除本地不再需要的文件，然后开始下载更新文件。

        if (downloadList.Count > 0)
        {
            DownloadConfirm dc = new DownloadConfirm(downloadList);

            if (this.OnShow != null)
                this.OnShow();

            if (DialogResult.OK == dc.ShowDialog())
            {
                foreach (LocalFile file in preDeleteFile)
                {
                    string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, file.Path);
                    if (File.Exists(filePath))
                        File.Delete(filePath);

                    config.UpdateFileList.Remove(file);
                }

                StartDownload(downloadList);
            }
        }

我们再来看一下StartDownload函数

private void StartDownload(List<DownloadFileInfo> downloadList)
 {
 DownloadProgress dp = new DownloadProgress(downloadList);
 if (dp.ShowDialog() == DialogResult.OK)
 {
 //更新成功
 config.SaveConfig(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FILENAME));

 if (bNeedRestart)
 {
 MessageBox.Show("程序需要重新启动才能应用更新，请点击确定重新启动程序。", "自动更新", MessageBoxButtons.OK, MessageBoxIcon.Information);
 Process.Start(Application.ExecutablePath);
 Environment.Exit(0);
 }
 }
 }

在这个函数中，先调用DownloadProgress下载所有需要下载的文件，然后更新本地配置文件，最后，如果发现某些更新文件需要重新启动应用程序的话，会提示用户重新启动程序。

至此，AutoUpdater这个类的使命就完成了，其实，整个的升级过程也就完成了。（废话）。

最后，我们来看一下这个组件是如何下载更新文件的

DownloadProgress.cs

在构造函数中，将要下载的文件列表传进来

public DownloadProgress(List<DownloadFileInfo> downloadFileList)
 {
 InitializeComponent();

 this.downloadFileList = downloadFileList;
 }

在Form的Load事件中，启动下载线程，开始下载。

        private void OnFormLoad(object sender, EventArgs e)
        {
            evtDownload = new ManualResetEvent(true);
            evtDownload.Reset();
            Thread t = new Thread(new ThreadStart(ProcDownload));
            t.Name = "download";
            t.Start();
        }

下载线程没什么特殊的，使用了WebClient的异步下载文件函数DownloadFileAsync，并且注册了两个事件，分别负责下载进度显示和下载完成后的处理：

clientDownload.DownloadProgressChanged += new DownloadProgressChangedEventHandler(OnDownloadProgressChanged);
clientDownload.DownloadFileCompleted += new AsyncCompletedEventHandler(OnDownloadFileCompleted);

大家看一下就明白了。

        private void ProcDownload()
        {
            evtPerDonwload = new ManualResetEvent(false);

            foreach (DownloadFileInfo file in this.downloadFileList)
            {
                total += file.Size;
            }

            while (!evtDownload.WaitOne(0, false))
            {
                if (this.downloadFileList.Count == 0)
                    break;

                DownloadFileInfo file = this.downloadFileList[0];

                //Debug.WriteLine(String.Format("Start Download:{0}", file.FileName));

                this.ShowCurrentDownloadFileName(file.FileName);

                //下载
                clientDownload = new WebClient();

                clientDownload.DownloadProgressChanged += new DownloadProgressChangedEventHandler(OnDownloadProgressChanged);
                clientDownload.DownloadFileCompleted += new AsyncCompletedEventHandler(OnDownloadFileCompleted);

                evtPerDonwload.Reset();

                clientDownload.DownloadFileAsync(new Uri(file.DownloadUrl), Path.Combine(AppDomain.CurrentDomain.BaseDirectory, file.FileFullName + ".tmp"), file);

                //等待下载完成
                evtPerDonwload.WaitOne();

                clientDownload.Dispose();
                clientDownload = null;

                //移除已下载的文件
                this.downloadFileList.Remove(file);
            }

            //Debug.WriteLine("All Downloaded");

            if (this.downloadFileList.Count == 0)
                Exit(true);
            else
                Exit(false);

            evtDownload.Set();
        }

最后，在OnDownloadFileCompleted函数中进行最后的处理。包括备份原文件，替换现有文件等。

        void OnDownloadFileCompleted(object sender, AsyncCompletedEventArgs e)
        {
            DownloadFileInfo file = e.UserState as DownloadFileInfo;
            nDownloadedTotal += file.Size;
            this.SetProcessBar(0, (int)(nDownloadedTotal * 100 / total));
            //Debug.WriteLine(String.Format("Finish Download:{0}", file.FileName));
            //替换现有文件
            string filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, file.FileFullName);
            if (File.Exists(filePath))
            {
                if (File.Exists(filePath + ".old"))
                    File.Delete(filePath + ".old");

                File.Move(filePath, filePath + ".old");
            }

            File.Move(filePath + ".tmp", filePath);
            //继续下载其它文件
            evtPerDonwload.Set();
        }

其它的函数只是一些显示进度条和下载信息的，这里就不再详细介绍了。大家可以下载源码看一下。

Validate Image extensions

shanyiwan@live.com() — Fri, 05 Nov 2010 07:18:08 GMT

we have checked the posted file of the FileUpload and pass it to the IsImageFile() method that returns a Boolean type for checking the actual type of the posted file. If the file contains image extension such as “jpg”, “gif”, “bmp” and “png” then it will return TRUE else it will return false.

private void StartUpLoad() 
{ 
    if (FileUpload1.HasFile) 
    { 
        HttpPostedFile postedFile = FileUpload1.PostedFile; 
        if (IsImageFile(postedFile)) 
        { 
            //Save image here 
        } 
        else 
        { 
            Response.Write("Invalid File, Cannot Upload!"); 
        } 
    } 
    else 
    { 
        Response.Write("Please select a File"); 
    } 
} 
 
protected bool IsImageFile(HttpPostedFile file) 
{ 
    bool isImage = false; 
    System.IO.FileStream fs = new System.IO.FileStream(file.FileName, System.IO.FileMode.Open, System.IO.FileAccess.Read); 
    System.IO.BinaryReader br = new System.IO.BinaryReader(fs); 
 
    string fileclass = ""; 
    byte buffer; 
    try 
    { 
        buffer = br.ReadByte(); 
        fileclass = buffer.ToString(); 
        buffer = br.ReadByte(); 
        fileclass += buffer.ToString(); 
    } 
    catch 
    { 
        return false; 
    } 
    finally 
    { 
        br.Close(); 
        fs.Close(); 
    } 
 
    /*extension lists with codes 
     *7173        gif 
     *255216      jpg 
     *13780       png 
     *6677        bmp 
     *239187      txt,aspx,asp,sql 
     *208207      xls.doc.ppt 
     *6063        xml 
     *6033        htm,html 
     *4742        js 
     *8075        xlsx,zip,pptx,mmap,zip 
     *8297        rar   
     *01          accdb,mdb 
     *7790        exe,dll           
     *64101       bat 
     */ 
 
    //only allow images    jpg       gif     bmp     png      
    String[] fileType = { "255216", "7173", "6677", "13780" }; 
    for (int i = 0; i < fileType.Length; i++) 
    { 
        if (fileclass == fileType[i]) 
        { 
            isImage = true; 
            break; 
        } 
    } 
    return isImage; 
}

Microsoft Chart Controls

shanyiwan@live.com() — Tue, 12 Oct 2010 08:35:45 GMT

.NET 3.5 发布功能强大的新控件(Winform&WebForm) —— Chart

前言

11月25号，Scott在博客上发布了基于.NET Framework 3.5 SP1的图表控件——Chart，可在WinForm和WebForm下使用！并同时提供了大量的示例...

特点：

官方公布的特点如下：

All supported chart types. （支持所有类型图表，截图如下）

Data series, chart areas, axes, legends, labels, titles, and more. （元素便于管理，我截了张图，如下）

Data

Binding (确实，支持几乎所有类型的数据绑定)

Data manipulation, such as copying, splitting, merging, alignment, grouping, sorting, searching, filtering, and more. （数据实时操作，图表中的数据还可以实时编辑）

Statistical formulas and financial formulas. （有适合统计学和金融学的方案，示例中有日K线等实例，如下）

Advanced chart appearance, such as 3D, anti-aliasing, lighting, perspective, and more. （各种各样的显示效果，比如是否显示3D只需设置一个布尔属性）

Chart rendering.

Events and Customizations. （支持各种事件和定制，如鼠标点击、图表ImageMap等等）

Interactivity and AJAX. （与AJAX交互，可用于实时显示，如下）

asp.net连接远程informix

shanyiwan@live.com() — Tue, 23 Jun 2009 09:21:22 GMT

环境：

本地：Windows 2003+VS2008,IBM CSDK 3.50

远程：Red Hat Enterprise Linux AS release 4 (Nahant Update 5)+Informix Dynamic Server 2000 Version 9.21.UC2

现象：

创建ODBC数据源时，提示

“---------------------------IBM Informix ODBC Error Message:---------------------------Test connection was NOT successful.[Informix][Informix ODBC Driver][Informix]Client host or user (informix@zzy-flyinweb) is not trusted by the server.-----------------------”

解决办法：

在远程服务器上创建/etc/hosts.equiv 文件，内容如下：

[root@datacenter ~]# cat /etc/hosts.equiv
zzy-flyinweb
59.57.251.62

现象：

报错：System.Data.Odbc.OdbcException: ERROR [42000] [Informix][Informix ODBC Driver][Informix]A syntax error has occurred.

解决办法：

请检查SQL语句是还正确

特别注意：VS2008在自动生成的SQL代码中，会将字段名及表名加[]号括起，而这可能会引起语法错误（不知是不是Informix的原因，去掉相关的中括号即可）

另外，OLEDB方式一直没有通过，原因未知

Connect to Informix with ADO.NET

shanyiwan@live.com() — Tue, 23 Jun 2009 07:07:21 GMT

06 Oct 2005

Delve into the details of using ADO.Net and Informix® Dynamic Server beyond the basic connection string. This article covers stored procedures and parameterized SQL. Then it presents a model (with code) for generating your own strongly typed DataSets.

Introduction

With its massive .NET framework, Microsoft® introduced a new data access technology called ADO.NET. In this article, we will examine how to use the ADO.NET driver for Informix that is included with the IBM Client SDK version 2.90. The sample code included is written in C#.

Laying the groundwork

Before using the ADO.NET driver, you should make sure it is installed and working properly. The current version of the driver is installed with the Informix Client Software Developer's Kit (SDK) 2.90. Unlike the previous 2.81 version, this SDK installation includes the ADO.NET driver by default. As you might expect, the client machine must have the .NET framework installed in order to use the driver. The SDK install program will warn you about this, too. It does not seem to actually look for the .NET framework, it just warns you that it needs to already be installed. If you have the 2.81 SDK already installed, it is best to uninstall it first. The two versions do not co-exist well. Be aware that the 2.90 ADO.NET driver incorrectly reports itself as version 2.81 when you add it into your Visual Studio Projects.

The 2.90 version is a significant upgrade over the 2.81 version. It includes a new IfxDataAdapter wizard, IPv6 support, and new classes for Informix data types (IfxDateTime, IfxDecimal, IfxBlob, and IfxClob). The documentation is more complete with twice the amount of material.

Important: The IBM Informix ADO.Net driver is not self-contained in the IBM.Data.Informix.dll file that gets installed in the /bin directory of your installation. Apparently, it uses some of the other client code installed by the SDK. This means that you must install the Informix Client SDK on any machines that will use the ADO.Net driver. You cannot just include the IBM.Data.Informix.dll in your distribution. This could be a serious limitation for some applications. You also need to go through the SDK setup (SetNet32) to define your Informix data sources.

Before using the ADO.NET driver to connect, you must also run a stored procedure called cdotnet.sql. It is located in the /etc directory of your SDK installation. This is similar to the process of setting up the OLEDB driver, though the procedure is much shorter. This process is documented in the User's Guide. (See the Resources section below.)

After installation, check your driver and make sure you get a connection. To use the ADO.NET driver in your Visual Studio project, make sure you add a reference to the IBM.Data.Informix.dll found in the /bin directory of your client SDK installation. The proper using statement is: using IBM.Data.Informix. Here is a simple method that demonstrates how to get a connection to the database:

Listing 1. Connecting to an Informix database

public void MakeConnection() {  
      string ConnectionString = "Host=" + HOST + "; " +  
       "Service=" + SERVICENUM + "; " +  
       "Server=" + SERVER + "; " +  
       "Database=" + DATABASE + "; " +  
       "User Id=" + USER + "; " +  
       "Password=" + PASSWORD + "; ";  
      //Can add other DB parameters here like DELIMIDENT, DB_LOCALE etc  
      //Full list in Client SDK's .Net Provider Reference Guide p 3:13  
      IfxConnection conn = new IfxConnection();
      conn.ConnectionString = ConnectionString;  
      try {  
          conn.Open();  
          Console.WriteLine("Made connection!");
          Console.ReadLine();  
      } catch (IfxException ex) {
          Console.WriteLine("Problem with connection attempt: "                           + ex.Message);        
      }  
}

The sample code includes a BasicConnection class for this functionality. As you can see, the ConnectionString is just a semicolon-separated list of parameters for the connection. The Open() method opens the connection to the database and throws an IfxException if the connection fails. The IfxException.Message property usually gives a reasonable amount of detail on the reason for the failure.

Basic commands

Once you have a connection, you can begin to execute commands against the database. To do this, use an IfxCommand object. The constructor for an IfxCommand takes a string (the SQL command text) and an IfxConnection. The IfxCommand object has a series of Execute methods to execute the command against the database. To clean up, use the IfxConnection.Close() method. Here is an example of executing a simple command that doesn't return a set of results. It could be an insert, update, or delete.

Listing 2. Executing an insert, update or delete

IfxCommand cmd;  
cmd = new IfxCommand("insert into test values (1, 2, 'ABC')",conn);  
cmd.CommandTimeout = 200; //seconds to wait for command to finish    
try {  
      int rows = cmd.ExecuteNonQuery();  
  }  catch (IfxException ex) {  
      Console.WriteLine("Error "+ex.Message);  
}

ExecuteNonQuery returns, as an integer, the number of rows affected by the command. You can also build parameterized statements and queries, which we will also examine below. Notice the CommandTimeout property of the IfxCommand. The default timeout is 30 seconds, although it is undocumented. Unless you change this property, a command that runs for over 30 seconds will time out and throw an exception.

The next example is executing a select statement and working with the set of results returned by the database server. For a fast, forward-only cursor through the results, use an IfxDataReader returned by the ExecuteReader method. However, you can only have one open IfxDataReader per IfxConnection. (This is an ADO.NET limitation, not a specific limitation of the Informix ADO.NET driver.)

Listing 3. Iterating through an IfxDataReader

IfxCommand cmd = new IfxCommand("select * from test",bconn.conn);  try {  
      IfxDataReader dr = cmd.ExecuteReader();  
      while (dr.Read()) {  
          int a = dr.GetInt32(0);  
          int b = Convert.ToInt32(dr["b"]);  
          string c = (String)dr[2];  
      }  
      dr.Close();  
  }  catch (IfxException ex) {  
      Console.WriteLine("Error "+ex.Message);  
  }

Each column is retrieved as a generic Object type. As the code demonstrates, there are several ways to convert the column Objects into the correct datatypes. You can use the GetXxx methods of the IfxDataReader. There are methods for almost every datatype. The GetXxx methods take a column number as a parameter. You can use the indexers of the IfxDataReader to access the columns by their names. The .NET framework Convert functions will convert these Objects into the proper types, if possible. Finally, you can index into the columns by column number and cast the results directly (for some types).

This next example shows how to call a stored procedure that needs a parameter value.

Listing 4. Executing a stored procedure with a parameter

IfxCommand cmd = new IfxCommand("test_proc",conn);  cmd.CommandType = CommandType.StoredProcedure; //from System.Data  
  cmd.Parameters.Add("in_parameter",2); //many ways to create these  
  try {  
      cmd.ExecuteScalar();  
  }  catch (IfxException ifxe) {  
      Console.WriteLine("Error "+ifxe.Message);  
  }

For this IfxCommand, you must set the CommandType to the StoredProcedure value from the CommandType enum in System.Data. To create the parameter, you can use the IfxCommand's Parameters.Add method. IfxCommands.Parameters is a collection, so you can add as many parameters as you need. You can create the parameters with any of the IfxParameter() constructors, or you can shortcut their creation as above. Note, however, that each IfxParameter is associated with a specific IfxCommand. You cannot create IfxParameters and then use them in multiple IfxCommand objects. The ExecuteScalar() method returns only 1 row. This example does not return anything back from the stored procedure.

To build a parameterized SQL statement that doesn't execute a stored procedure, insert question marks as place-holders in the CommandText. For example:

Listing 5. Parameterized query

IfxCommand insCmd = new IfxCommand("insert into clientstest " 
       + "(clientcode, clientacctname, primarycontact, primaddrcode, " 
     + "initialamt,createdate) values (0,?,?,?,?,TODAY)",conn);

Add IfxParameter objects to the IfxCommand's Parameters collection in the exact order as they occur in the command text. This technique is further demonstrated in the final strongly typed DataSets in the extended example below.

Strongly typed DataSets

ADO.NET includes a specialized database object called a DataSet. It is an in-memory database. The DataSet consists of one or more DataTable objects (made up of DataRow objects). The DataTables can be related by primary and foreign keys. And constraints can be placed on the data. The DataSet is also disconnected from the actual data store. It gets filled through one or more DataAdapters (one per DataTable), and then keeps that data and any changes in memory. At a later point, the DataAdapters can submit changes back to the data store.

The basic DataSet is not strongly typed. It does not know what the real columns and rows of the database are. Columns can be indexed by name: row["itemcode"]. But compliler does not check these column names. Any mistake in the column name, for example, is not apparent until runtime. Also, the developer has no help remembering if the column is "itemcode" or "itemid."

A strongly typed DataSet addresses these problems. Instead of a generic DataRow, it would have, for example, an OrderDetailDataRow as part of an OrderDetailDataTable. And you could refer to columns as actual properties of the OrderDetailDataRow (row.ItemCode). This way you also get the productivity benefits of Intellisense. The table and column names also become available in the property editors to enhance designer-level tools like data binding.

So how can you build this productivity-enhancing, strongly typed DataSet? Will it take so much time or effort to build that you don't experience any net productivity gain? The Informix ADO.NET driver may not be as sophisticated as some other drivers. The Microsoft SQLDataAdapter (for SQL Server) includes a Generate DataSet wizard. The IfxDataAdapter doesn't have this wizard yet. However, you can build some tools to help you. You can also use some tools already built into the .NET framework. In the end, you will have a descendent of a strongly typed DataSet that encapsulates all of the database interaction.

The .NET framework includes an XSD compiler (xsd.exe) that can generate a strongly typed DataSet from a specially formatted .xsd file. But who wants to type in a bunch of XML? Fortunately, the DataSet object includes a method called WriteXmlSchema(). This method allows you to use a non-typed DataSet to create the XSD file for a strongly-typed DataSet. Let's look at how this works. Here is a simple table:

Listing 6. Clientstest table

CREATE TABLE clientstest (  
    clientcode SERIAL not null,  
    clientacctname CHAR(60) not null,  
    primarycontact CHAR(30) not null,  
    primaddrcode CHAR(10),  
    createdate DATE,  
    initialamt DECIMAL (18,0)  
  );

Here's the single-table DataSet for that table:

Listing 7. Defining the DataSet

DS = new DataSet("dsClients");  //main table definition  
    DataTable mainTable = new DataTable("clients");  
    DataColumnCollection cols = mainTable.Columns;  
    DataColumn column = cols.Add("clientcode",typeof(Int32));  
        column.AllowDBNull = false;  
        cols.Add("clientacctname",typeof(String)).MaxLength = 60;  
        cols.Add("primarycontact",typeof(String)).MaxLength = 30;  
        cols.Add("primaddrcode",typeof(String)).MaxLength = 10;  
        cols.Add("initialamt",typeof(Decimal));  
        cols.Add("createdate",typeof(System.DateTime));  //primary key   
     mainTable.PrimaryKey = new DataColumn[] {cols["clientcode"]};  //add table to DataSet      
    DS.Tables.Add(mainTable);  //Write schema to file  
DS.WriteXmlSchema("dsClients.xsd");

In this definition, you set the types and the constraints on the data. You also set the names for the columns. They do not have to match the database's column names. Look in the code files in the Download section of this article to see the resulting dsClients.xsd file.

To make it easier to generate the XSD file (and re-generate it, after changes), build a framework for these DataSet Builders. (All the code required for this is included below.) Since you want the framework to determine which Builders to build, use reflection to dynamically determine if something is a DataSetBuilder. Start by writing the IBuildable interface. It defines the properties and methods that our DataSetBuilders must implement.

Listing 8. IBuildable interface

public interface IBuildable {  
      string FileName {get; set;}  
      string FilePath {get; set;}  
      Logger Log {get; set;}  
      DataSet DS {get; set;}  
      void BuildXSD();  
      void CompileXSD(string outputDirectory);  
  }

The code from listing 7 (the definition of the DataSet) is basically the BuildXSD() method. Create an abstract parent class called DataSetBuilder. BuildXSD is the abstract method that must be overriden in each concrete descendent. The CompileXSD method is the same for each DataSetBuilder, so it resides in the DataSetBuilder. Here is the CompileXSD() method from that abstract class:

Listing 9. CompileXSD() method

log.LogStatus("Compiling "+filename);  
    ProcessStartInfo processinfo = new ProcessStartInfo();  
  processinfo.FileName =       @"C:\Program Files\Microsoft Visual Studio .NET 2003\" 
   +@"SDK\v1.1\Bin\xsd.exe";  
    processinfo.Arguments = FilePath+FileName+" /dataset /namespace:"
   +ds.Namespace+" /out:"+outputDirectory;
  processinfo.UseShellExecute = false;
  processinfo.RedirectStandardInput = true;
  processinfo.RedirectStandardOutput = true;
  processinfo.RedirectStandardError = true;
  processinfo.CreateNoWindow = true; //doesn't work
  processinfo.WindowStyle = ProcessWindowStyle.Hidden; //doesn't work  
  Process compiler = Process.Start(processinfo);
  log.LogStatus("Output:\n"+compiler.StandardOutput.ReadToEnd());  
  log.LogStatus("Error:\n"+compiler.StandardError.ReadToEnd());  
    compiler.WaitForExit();

This method uses the Process and ProcessStartInfo classes from System.Diagnostics in the .NET framework to execute the XSD compiler. This example code uses the free and relatively simple .NET Logging Framework from the ObjectGuy (see Resources).

Because the DataSetBuilder classes all implement the IBuildable interface, we can use reflection to look through the assembly and build all the DataSet classes from the DataSetBuilders. This is what the DataLibraryBuilder class does. For example, the ClientsBuilder gets compiled to a dsClients class.

The generated dsClients class is the strongly-typed DataSet. From the 42-line ClientsBuilder, we now have almost 500 lines of strongly typed code. Look at this generated code. It contains a clientsDataTable. The clientsDataTable has properties for each column. There are also methods like: NewclientsRow(), IsinitialamtNull(), and FindByclientcode(int clientcode). These will be quite useful when using this class.

To encapsulate the Informix database access into the strongly typed DataSet, inherit from dsClients. This is the Clients class that you can use in your application. Inheritance provides some protection from changes in the schema of the DataSet. If the schema changes, you can just regenerate the dsClients class. The Clients class remains unchanged (though you may need to make changes there, too). In the Clients class, add an IfxDataAdapter for each DataTable (just one in this case). For each IfxDataAdapter, define the SQL text and parameters for the select, insert, update, and delete commands. You can then override the Fill and Update methods to initialize, fill, and update all the IfxDataAdpaters. Look at the Insert Command as an example:

Listing 10. InsertCommand for the IfxDataAdapter

IfxCommand insCmd = new IfxCommand("insert into clientstest " 
   +"(clientcode, clientacctname, primarycontact, primaddrcode, " 
   +"initialamt,createdate) values (0,?,?,?,?,TODAY)",conn);  
  insCmd.Parameters.Add("clientacctname", IfxType.Char,60,  
    "clientacctname");  
  insCmd.Parameters.Add("primarycontact", IfxType.Char, 30,  
    "primarycontact");  
  insCmd.Parameters.Add("primaddrcode", IfxType.Char, 10,  
    "primaddrcode");  
  insCmd.Parameters.Add("initialamt", IfxType.Decimal, 16,  
    "initialamt");  
  daclients.InsertCommand = insCmd;

The IfxDataAdapter has the following command properties: SelectCommand, InsertCommand, DeleteCommand, and UpdateCommand. When the IfxDataAdapter executes the Fill() method, it uses the SelectCommand to query the database. When the Update() method is called, the IfxDataAdapter uses a combination of the Insert, Update, and Delete commands to conform the database to the in-memory version of the table. The IfxDataAdapter decides which rows and columns need to be changed; the developer does not have to code to track the changes to the data.

Notice that zero is inserted into the serial value, as is usual for the Informix serial type. But how can you get the database-generated value back into your disconnected DataSet? You have to hook into the RowUpdated event of the dsclientsIfxDataAdapter. In that event handler, the code looks for any inserts. For an insert, it executes a dbinfo command to retrieve the just-created serial value. It puts that value into the clientcode column for that DataRow. Here's the event handler code for this Informix-specific trick:

Listing 11. Retrieving the generated serial value

private void daclients_RowUpdated(object sender,
                                     IfxRowUpdatedEventArgs e) {  
      //For INSERTs only, gets the serial id and  
       //inserts into the clientcode  
      if (e.StatementType == StatementType.Insert) {  
          IfxCommand getSerial = new IfxCommand(  
            "select dbinfo('sqlca.sqlerrd1') from systables " 
           +"where tabid = 1",
            daclients.InsertCommand.Connection);  
          e.Row["clientcode"] = (int)getSerial.ExecuteScalar();  
      }  
  }

Results

You now have a fully encapsulated business object that handles its own database interaction. What can you do now? Since DataSets derive from System.Component, you can add your strongly typed DataSet objects (for example, Clients) onto your Visual Studio Toolbox. Then you can drag it out onto any WinForm or WebForm design view. Set the property for the Connection. In your code, execute the object's Fill() method (perhaps in a FormLoad event). That fills the object with all the data for each DataTable. In the Designer view, you can also databind by setting the DataSource (and perhaps the DataMember property) for the visual control or grid.

The LibraryConsoleTest program in the sample solution demonstrates how the strongly typed DataSet works. You can now write something like this: Console.WriteLine(client.clientcode + " " + client.clientacctname+" " + client.createdate); instead of this: Console.WriteLine(ds.Tables["clients"].Rows["clientcode"] + " " + ds.Tables["clients"].Rows["clientacctname"] + " " + ds.Tables["clients"].Rows ["createdate"] ); The LibraryConsoleTest adds a new client and retrieves the generated serial number. It deletes a client after using the Findbyclientcode() method to select the proper row. It also updates one column in a particular row. Finally it loops through the clients and prints the data to the Console. The sample solution also includes a quick Windows Forms application (WinFormsApp) that demonstrates databinding use a DataGrid.

Data access is constant need for most business applications. Yet, the models and methods for doing data access are continually changing. The examples in this article should help you get

.NET平台下Web树形结构程序设计

shanyiwan@live.com() — Sun, 21 Jun 2009 12:51:26 GMT

概述

TreeView是一个重要的控件，无论是在VB.NET，C# 还是VB、Delphi等各种语言中，都充当了导航器的作用。在实际工作中，很多情况下需要将TreeView与数据库进行连接，以填充其节点。在Windows Form和Web Form中，我们可以用TreeView来显示树形结构，如显示目录树、显示地区、分类显示商品等。可以说，在大部分软件的开发中，TreeView都是一个不可缺少的展示控件。因此，树形结构的设计就成了软件开发人员一个永恒的话题。
树形结构的展示方式
树形结构的展示一般来讲有三种方式：
1.界面设计时在TreeView设计器或者代码中直接填充TreeView控件。
2.从XML文件中建立树形结构。
3.从数据库中得到数据，建立树形结构。
第一种方式是最简单的，这种方式主要用于树形结构一般没有变化的应用程序，在设计时就固定一颗树。当然，在设计时固定了树的结构，以后要想修改、增加、删除树的节点，就必须修改源程序。所有不利于扩展。
第二种方式从XML文件中提取，由于XML本身就是树形结构的，微软提供的文档对象模型DOM 可以方便的读取、操作和修改 XML 文档。在.NET中，应用System.Xml类可以方便地将XML文件加载到TreeView控件中，微软的MSDN也提供了实例，此处就不再多说。
第三种方式，树形结构的数据，从数据库中获得。一般来讲，我们的应用程序多数是基于数据库的。采用这种方式，增加、修改、删除一颗树的节点很方便，只要操作数据库中的数据就可以了。而且，这种方式可以和数据库中的其它表做关联、查询和汇总，通过设计视图或存储过程，很容易查询出你想要的相关数据。下面，我们主要讨论这种方式的设计和实现。

数据库设计

首先，我们在SQL SERVER 2000里建立一个表tbTree，表的结构设计如下：
列名    数据类型    描述        长度    主键
ID    Int    节点编号        4    是
ParentID    Int    父节点编号        4
ConText    Nvarchar    我们要显示的节点内容    50

在SQL SERVER 2000中建表的脚本：

CREATE TABLE [dbo].[tbTree] (  
    [ID] [int] IDENTITY (1, 1) NOT NULL ,  
    [Context] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,  
    [ParentID] [int] NULL   
) ON [PRIMARY]

在表中添加如下记录：

SET IDENTITY_INSERT tbtree ON 
insert tbtree (ID,Context,ParentID)  values ( 1,'中国',0)  
insert tbtree (ID,Context,ParentID)  values ( 2,'北京',11)  
insert tbtree (ID,Context,ParentID)  values ( 3,'天津',11)  
insert tbtree (ID,Context,ParentID)  values ( 4,'河北省',1)  
insert tbtree (ID,Context,ParentID)  values ( 5,'广东省',1)  
insert tbtree (ID,Context,ParentID)  values ( 6,'广州',5)  
insert tbtree (ID,Context,ParentID)  values ( 7,'四川省',1)  
insert tbtree (ID,Context,ParentID)  values ( 8,'成都',7)  
insert tbtree (ID,Context,ParentID)  values ( 9,'深圳',5)  
insert tbtree (ID,Context,ParentID)  values ( 10,'石家庄',4)  
insert tbtree (ID,Context,ParentID)  values ( 11,'辽宁省',1)  
insert tbtree (ID,Context,ParentID)  values ( 12,'大连',11)  
insert tbtree (ID,Context,ParentID)  values ( 13,'上海',1)  
insert tbtree (ID,Context,ParentID)  values ( 14,'天河软件园',6)  
insert tbtree (ID,Context,ParentID)  values ( 15,'汕头',5)  
SET IDENTITY_INSERT tbtree off

下载地址
http://msdn.microsoft.com/downloads/samples/internet/ASP_DOT_NET_ServerControls/WebControls/default.asp
安装后，通过“自定义工具箱”->“.net框架组件”把TreeView添加到工具箱里。
新建一个项目，选择Visual Basic.Net 工程Asp.net Web应用程序，在页面上拖画一个TreeView控件。

Html页:

<%@ Register TagPrefix="iewc" Namespace="Microsoft.Web.UI.WebControls" Assembly="Microsoft.Web.UI.WebControls, Version=1.0.2.226, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %> 
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="Tree.WebForm1"%> 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> 
<HTML> 
<HEAD> 
<title>WebForm1</title> 
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.0"> 
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0"> 
<meta name="vs_defaultClientScript" content="JavaScript"> 
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5"> 
</HEAD> 
<body MS_POSITIONING="GridLayout"> 
<form id="Form1" method="post" runat="server"> 
<FONT face="宋体"> 
<iewc:TreeView id="TreeView1" style="Z-INDEX: 101; LEFT: 39px; TOP: 68px" runat="server"></iewc:TreeView></FONT> 
</form> 
</body> 
</HTML>

后台代码：

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load  
        Dim ds As New DataSet()  
        Dim CN As New SqlConnection()  
        Try 
            '初始化连接字符串  
            CN.ConnectionString =   
            "data source=pmserver;initial catalog=Benchmark;persist security info=False;user id=sa;Password=sa;" 
            CN.Open()  
            Dim adp As SqlDataAdapter = New SqlDataAdapter("select * from tbTree", CN)  
            adp.Fill(ds)  
            Me.ViewState("ds") = ds  
        Catch ex As Exception  
#If DEBUG Then  
            Session("Error") = ex.ToString()  
            Response.Redirect("error.aspx")        '̀跳转程序的公共错误处理页面  
#End If  
        Finally 
            '关闭连接  
            CN.Close()  
        End Try 
        '调用递归函数，完成树形结构的生成  
        AddTree(0, Nothing)  
    End Sub 
 
    '递归添加树的节点  
    Private Sub AddTree(ByVal ParentID As Integer, ByVal pNode As TreeNode)  
        Dim ds As DataSet  
        ds = Me.ViewState("ds")  
        Dim dvTree As New DataView()  
        dvTree = New DataView(ds.Tables(0))  
        '过滤ParentID,得到当前的所有子节点  
        dvTree.RowFilter = "PARENTID = " + ParentID.ToString  
 
        Dim Row As DataRowView  
        For Each Row In dvTree  
            Dim Node As New TreeNode()  
            If pNode Is Nothing Then  '判断是否根节点  
                '添加根节点  
                Node.Text = Row("ConText").ToString()  
                TreeView1.Nodes.Add(Node)  
                Node.Expanded = True 
                '再次递归  
                AddTree(Int32.Parse(Row("ID").ToString()), Node)  
            Else 
                '̀添加当前节点的子节点  
                Node.Text = Row("ConText").ToString()  
                pNode.Nodes.Add(Node)  
                Node.Expanded = True 
                '再次递归  
                AddTree(Int32.Parse(Row("ID").ToString()), Node)  
            End If 
        Next 
    End Sub

C#版本：

using System;  
using System.Collections;  
using System.ComponentModel;  
using System.Data;  
using System.Drawing;  
using System.Web;  
using System.Web.SessionState;  
using System.Web.UI;  
using System.Web.UI.WebControls;  
using System.Web.UI.HtmlControls;  
using Microsoft.Web.UI.WebControls;  
using System.Data.SqlClient;  
namespace TreeCS  
{  
    ///   
    /// WebForm1 的摘要说明  
    ///   
    public class WebForm1 : System.Web.UI.Page  
    {  
        protected Microsoft.Web.UI.WebControls.TreeView TreeView1;  
      
        private void Page_Load(object sender, System.EventArgs e)  
        {  
            // 定义数据库连接  
            SqlConnection CN = new SqlConnection();  
            try   
            {  
                //初始化连接字符串  
                CN.ConnectionString=  
                "data source=pmserver;initial catalog=Benchmark;persist security info=False;user id=sa;Password=sa;";  
                CN.Open();  
 
                SqlDataAdapter adp = new SqlDataAdapter("select * from tbTree",CN);  
                DataSet ds=new DataSet();  
                adp.Fill(ds);  
                this.ViewState["ds"]=ds;   
            }  
            catch (Exception ex)  
            {  
                Session["Error"] = ex.ToString();  
                Response.Redirect("error.aspx");       //̀跳转程序的公共错误处理页面  
            }  
            finally   
            {  
                CN.Close();  
            }  
            //调用递归函数，完成树形结构的生成  
            AddTree(0, (TreeNode)null);  
        }  
 
        //递归添加树的节点  
        public void AddTree(int ParentID,TreeNode pNode)   
        {  
            DataSet ds=(DataSet) this.ViewState["ds"];   
            DataView dvTree = new DataView(ds.Tables[0]);  
            //过滤ParentID,得到当前的所有子节点  
            dvTree.RowFilter =  "[PARENTID] = " + ParentID;  
 
            foreach(DataRowView Row in dvTree)   
            {  
                TreeNode Node=new TreeNode() ;  
                if(pNode == null)   
                {    //添加根节点  
                    Node.Text = Row["ConText"].ToString();  
                    TreeView1.Nodes.Add(Node);  
                    Node.Expanded=true;  
                    AddTree(Int32.Parse(Row["ID"].ToString()), Node);    //再次递归  
                }   
                else   
                {   //̀添加当前节点的子节点  
                    Node.Text = Row["ConText"].ToString();  
                    pNode.Nodes.Add(Node);  
                    Node.Expanded = true;  
                    AddTree(Int32.Parse(Row["ID"].ToString()),Node);     //再次递归  
                }  
            }                     
        }              
 
        #region Web Form Designer generated code  
        override protected void OnInit(EventArgs e)  
        {  
            //  
            // CODEGEN该调用是 ASP.NET Web 窗体设计器所必需的。  
            //  
            InitializeComponent();  
            base.OnInit(e);  
        }  
          
        /// <summary>  
        ///设计器支持所需的方法 - 不要使用代码编辑器修改  
        /// 此方法的内容  
        /// </summary>  
        private void InitializeComponent()  
        {      
            this.Load += new System.EventHandler(this.Page_Load);  
 
        }  
        #endregion  
    }  
}

后记：请读者自行修改程序中的连接字符串设置。
附：相关微软MSDN文档，包括在VB6和.NET中从XML建立树形结构
http://support.microsoft.com/default.aspx?kbid=311318
http://support.microsoft.com/default.aspx?kbid=308063
http://support.microsoft.com/default.aspx?kbid=317597
http://support.microsoft.com/default.aspx?kbid=244954

flyinweb's blog - DotNet专栏

Creating your SQL Server Compact Edition database and schema in code

Membership&RoleManager(成员资格和角色管理)

Access LoginView Templated Controls with ASP 4.0 and C#

convert a network in CIDR notation

WinForm应用程序自动升级实现

Validate Image extensions

Microsoft Chart Controls

前言

相关下载：

特点：

asp.net连接远程informix

Connect to Informix with ADO.NET

.NET平台下Web树形结构程序设计