silverlight\SQL server\webservice

Silverlight+SQL Server (WebService)

1、新建数据库（数据库名称：SqlTest）

开发环境：SQL Server 2005

创建User 表：

CREATE TABLE [dbo].[User](

[UserID] [int] NOT NULL,

[UserName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

[HomeAddress] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED

(

[UserID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

创建插入数据的存储过程：（后期要用）

CREATE procedure [dbo].[InsertUser]

(

@userid int,

@username nvarchar(50),

@homeaddress nvarchar(50),

@outvalue nchar(1)='0' output

)as

if exists(select * from [user] where userid=''+rtrim(ltrim(@userid))+'')

begin

set @outvalue ='1'----数据重复

print @outvalue +'数据重复'

end

else

begin

insert into [user]

values(''+ @userid +'',''+ @username +'',''+ @homeaddress +'')

end

2、创建SILVERLIGHT应用程序项目：

开发环境VS2008（Visual_Studio_2008_SP1补丁+Silverlight3_Tools）

“确定”后选定WEB项目类型

确定后创建项目：

在WEB项中添加WEB服务，命名为UserManage

打开Web.config文件创建数据库连接

在Web.congfig文件中的 <connectionString/> 替换成如下代码，创建数据连接

</connectionStrings>

connectionString 中 LONG"SQLEXPRESS 为本机的SQL Server的服务器名称（因机器而异），SqlTest 为数据库名称，User ID=sa;Password=long343为登陆SQL服务器的用户名和密码

编辑WebService文件，打开UserManage.asmx

在文件中建入如下代码：在修改文件前需加入如下命名空间（using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Text;）

详细代码如下：

///////////////////////细节问题自己研究

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Services;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Text;

namespace longSilverlightSQL.Web

{

/// <summary>

/// UserManage 的摘要说明

/// </summary>

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[System.ComponentModel.ToolboxItem(false)]

// 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消对下行的注释。

// [System.Web.Script.Services.ScriptService]

public class UserManage : System.Web.Services.WebService

{

[WebMethod]

public string HelloWorld()

{

return "Hello World";

}

[WebMethod]

public bool CreateUser(int userid, string userName, string homeaddress)

{

try

{

SqlConnection _sqlConnection = new SqlConnection();

_sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();

_sqlConnection.Open();

SqlCommand command = new SqlCommand();

command.Connection = _sqlConnection;

command.CommandType = CommandType.Text;

command.CommandText =

"INSERT INTO [User] ([UserID],[UserName],[HomeAddress]) VALUES ('" + userid.ToString().Replace("'", "''") + "','" +

userName.ToString().Replace("'", "''") + "','" +

homeaddress.ToString().Replace("'", "''") + "')";

command.ExecuteNonQuery();

_sqlConnection.Close();

return true;

}

catch (Exception ex)

{

return false;

}

//adduser 调用存储过程进行数据加载

[WebMethod]

public bool AddProcedureUser(int userid, string userName, string homeaddress)

{

try

{

SqlConnection _sqlConnection = new SqlConnection();

_sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();

_sqlConnection.Open();

SqlCommand command = new SqlCommand();

command.Connection = _sqlConnection;

command.CommandType = CommandType.StoredProcedure;

command.CommandText = "InsertUser";

//in参数

command.Parameters.Add(new SqlParameter("@userid", SqlDbType.Int)).Value = userid;

command.Parameters.Add(new SqlParameter("@username", SqlDbType.NVarChar, 50)).Value = userName;

command.Parameters.Add(new SqlParameter("@homeaddress", SqlDbType.NVarChar, 50)).Value = homeaddress;

//out参数

command.Parameters.Add(new SqlParameter("@outvalue", SqlDbType.NChar, 1));

command.Parameters["@outvalue"].Direction = ParameterDirection.Output;

//执行存储过程

command.ExecuteNonQuery();

//赋值

string valuetype = command.Parameters["@outvalue"].Value.ToString();

if (valuetype == "1")//数据重复

return false;

else

return true;

}

catch (Exception ex)

{

return false;

}

//RetrieveUser方法

[WebMethod]

public string RetrieveUsers()

{

try

{

SqlConnection _sqlConnection = new SqlConnection();

_sqlConnection.ConnectionString = ConfigurationManager.

ConnectionStrings["sqlConnectionString"].ToString();

_sqlConnection.Open();

SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = new SqlCommand(

"SELECT * FROM [User]", _sqlConnection);

DataSet ds = new DataSet();

da.Fill(ds);

StringBuilder sb = new StringBuilder();

sb.Append("<?xml version=""1.0"" encoding=""utf-8"" ?>");

sb.Append("<Users>");

foreach (DataRow dr in ds.Tables[0].Rows)

{

sb.Append("<User>");

sb.Append("<UserID>");

sb.Append(dr[0].ToString());

sb.Append("</UserID>");

sb.Append("<UserName>");

sb.Append(dr[1].ToString());

sb.Append("</UserName>");

sb.Append("<HomeAddress>");

sb.Append(dr[2].ToString());

sb.Append("</HomeAddress>");

sb.Append("</User>");

}

sb.Append("</Users>");

_sqlConnection.Close();

return sb.ToString();

}

catch (Exception ex)

{

return string.Empty;

}

//UpdateUser方法

[WebMethod]

public bool UpdateUser(int userID, string userName, string homeaddress)

{

try

{

SqlConnection _sqlConnection = new SqlConnection();

_sqlConnection.ConnectionString = ConfigurationManager.

ConnectionStrings["sqlConnectionString"].ToString();

_sqlConnection.Open();

SqlCommand command = new SqlCommand();

command.Connection = _sqlConnection;

command.CommandType = CommandType.Text;

command.CommandText = "UPDATE [User] " +

"SET [UserName] = '" + userName.ToString().Replace("'", "''") + "'" +

", [HomeAddress] = '" + homeaddress.ToString().Replace("'", "''") + "'" +

"WHERE [UserID] = " + "'" + userID.ToString() + "'";

command.ExecuteNonQuery();

_sqlConnection.Close();

return true;

}

catch (Exception ex)

{

return false;

}

//DeleteUser方法

[WebMethod]

public bool DeleteUser(int userID)

{

try

{

SqlConnection _sqlConnection = new SqlConnection();

_sqlConnection.ConnectionString = ConfigurationManager.

ConnectionStrings["sqlConnectionString"].ToString();

_sqlConnection.Open();

SqlCommand command = new SqlCommand();

command.Connection = _sqlConnection;

command.CommandType = CommandType.Text;

command.CommandText =

"DELETE [User] WHERE [UserID] = " + userID.ToString();

command.ExecuteNonQuery();

_sqlConnection.Close();

return true;

}

catch (Exception ex)

{

return false;

}

//////////////////////

进行完以上步骤，进行执行WEB生成，为下文调用webservice服务做准备

开始进行silverlight项的编写

首先添加上面创建的webservice服务的引用

点击“发现”可以进行自动搜索

确定后完成引用。

开始创建silverlight操作页面，打开MainPage.xaml页面

注意在创建过程中，需要加入新的命名空间的引用（System.Windows.Control + System.Windows.Control.Data + System.Xml.Linq ），如上图。

在MainPage.xaml中引入空间： xmlns:myControl="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls"

界面创建代码如下：

//////////////////////////////

<myControl:TabControl Canvas.Top="20" Canvas.Left="20" Height="450" Width="450">

<myControl:TabItem Header="添加">

<Grid.ColumnDefinitions>

</Grid.ColumnDefinitions>

<Grid.RowDefinitions>

</Grid.RowDefinitions>

</Grid>

</StackPanel>

</myControl:TabItem>

<myControl:TabItem Header="修改">

<Grid.ColumnDefinitions>

</Grid.ColumnDefinitions>

<Grid.RowDefinitions>

</Grid.RowDefinitions>

</Grid>

</StackPanel>

</myControl:TabItem>

<myControl:TabItem Header="删除">

<Grid.ColumnDefinitions>

</Grid.ColumnDefinitions>

<Grid.RowDefinitions>

</Grid.RowDefinitions>

</Grid>

</StackPanel>

</myControl:TabItem>

</myControl:TabControl>

</Grid>

</Canvas>

////////////////////////////

应设计到数据绑定的问题，本人将数据绑定到子窗体中。

创建子窗体：UserDataGrid.xaml

界面代码如下：

<controls:ChildWindow x:Class="longSilverlightSQL.UserDataGrid"

xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

xmlns:controls="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls"

xmlns:Data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data"

Width="400" Height="300" Title="UserDataGrid">

<Grid.RowDefinitions>

</Grid.RowDefinitions>

<Data:DataGrid x:Name="userDataGrid" Height="200" Width="700" Margin="0,5,0,10" AutoGenerateColumns="True" VerticalAlignment="Top" Grid.Row="1">

</Data:DataGrid>

</Grid>

</controls:ChildWindow>

子窗体创建完成。

在MainPage.xaml.cs中导入如下代码：

/////////////////////////////////

using System;

using System.Collections.Generic;

using System.Linq;

using System.Net;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Documents;

using System.Windows.Input;

using System.Windows.Media;

using System.Windows.Media.Animation;

using System.Windows.Shapes;

using System.Xml.Linq;

namespace longSilverlightSQL

{

public partial class MainPage : UserControl

{

UserDataGrid udGrid = new UserDataGrid();

public MainPage()

{

InitializeComponent();

btAdd.Click += new RoutedEventHandler(btAdd_Click);

btUpdate.Click += new RoutedEventHandler(btUpdate_Click);

btDelete.Click += new RoutedEventHandler(btDelete_Click);

btProcAdd.Click += new RoutedEventHandler(btProcAdd_Click);//调用存储过程

}

void btProcAdd_Click(object sender, RoutedEventArgs e)

{

if (AddTextID.Text.Trim() == string.Empty)

{

errMessage.Foreground = new SolidColorBrush(Colors.Red);

errMessage.Text = "请输入用户ID！";

errMessage.Visibility = Visibility.Visible;

return;

}

//调用WebService

ServiceReference1.UserManageSoapClient userMgrSoapClient = new longSilverlightSQL.ServiceReference1.UserManageSoapClient();

userMgrSoapClient.AddProcedureUserAsync(Int16.Parse(AddTextID.Text), AddTextName.Text, AddAddress.Text);

userMgrSoapClient.AddProcedureUserCompleted += new EventHandler<longSilverlightSQL.ServiceReference1.AddProcedureUserCompletedEventArgs>(userMgrSoapClient_AddProcedureUserCompleted);

}

void userMgrSoapClient_AddProcedureUserCompleted(object sender, longSilverlightSQL.ServiceReference1.AddProcedureUserCompletedEventArgs e)

{

if (e.Result == true)

{

errMessage.Text = "添加用户成功！";

errMessage.Foreground = new SolidColorBrush(Colors.Blue);

errMessage.Visibility = Visibility.Visible;

udGrid.Show();

ListingControlDisplay(null, null);

}

else

{

errMessage.Foreground = new SolidColorBrush(Colors.Red);

errMessage.Text = "添加用户失败！";

errMessage.Visibility = Visibility.Visible;

}

void btDelete_Click(object sender, RoutedEventArgs e)

{

if (DeleteTextID.Text.Trim() == string.Empty)

{

errMessage.Foreground = new SolidColorBrush(Colors.Red);

errMessage.Text = "请输入用户ID！";

errMessage.Visibility = Visibility.Visible;

return;

}

ServiceReference1.UserManageSoapClient userMgrSoapClient = new longSilverlightSQL.ServiceReference1.UserManageSoapClient();

//调用删除方法

userMgrSoapClient.DeleteUserAsync

(Int16.Parse(DeleteTextID.Text.Trim()));

userMgrSoapClient.DeleteUserCompleted += new EventHandler<longSilverlightSQL.

ServiceReference1.DeleteUserCompletedEventArgs>(userMgrSoapClient_DeleteUserCompleted);

}

void userMgrSoapClient_DeleteUserCompleted(object sender, longSilverlightSQL.ServiceReference1.DeleteUserCompletedEventArgs e)

{

if (e.Result == true)

{

errMessage.Text = "删除用户成功！";

errMessage.Foreground = new SolidColorBrush(Colors.Blue);

errMessage.Visibility = Visibility.Visible;

udGrid.Show();

ListingControlDisplay(null, null);

}

else

{

errMessage.Foreground = new SolidColorBrush(Colors.Red);

errMessage.Text = "删除用户失败！";

errMessage.Visibility = Visibility.Visible;

}

void btUpdate_Click(object sender, RoutedEventArgs e)

{

if (UpdateTextID.Text.Trim() == string.Empty)

{

errMessage.Foreground = new SolidColorBrush(Colors.Red);

errMessage.Text = "请输入用户ID！";

errMessage.Visibility = Visibility.Visible;

return;

}

if (UpdateTextName.Text.Trim() == string.Empty)

{

errMessage.Foreground = new SolidColorBrush(Colors.Red);

errMessage.Text = "请输入用户名称！";

errMessage.Visibility = Visibility.Visible;

return;

}

ServiceReference1.UserManageSoapClient userMgrSoapClient = new longSilverlightSQL.ServiceReference1.UserManageSoapClient();

//调用更新用户方法

userMgrSoapClient.UpdateUserAsync

(Int16.Parse(UpdateTextID.Text.Trim()), UpdateTextName.Text.Trim(), UpdateAddress.Text.Trim());

userMgrSoapClient.UpdateUserCompleted += new EventHandler<longSilverlightSQL.

ServiceReference1.UpdateUserCompletedEventArgs>

(userMgrSoapClient_UpdateUserCompleted);

}

void userMgrSoapClient_UpdateUserCompleted(object sender, longSilverlightSQL.ServiceReference1.UpdateUserCompletedEventArgs e)

{

//if (e.Error == null)

if (e.Result == true)

{

errMessage.Text = "修改用户成功！";

errMessage.Foreground = new SolidColorBrush(Colors.Blue);

errMessage.Visibility = Visibility.Visible;

udGrid.Show();

ListingControlDisplay(null, null);

}

else

{

errMessage.Foreground = new SolidColorBrush(Colors.Red);

//errMessage.Text = e.Error.ToString();

errMessage.Text = "修改用户失败！";

errMessage.Visibility = Visibility.Visible;

}

void btAdd_Click(object sender, RoutedEventArgs e)

{

if (AddTextID.Text.Trim() == string.Empty)

{

errMessage.Foreground = new SolidColorBrush(Colors.Red);

errMessage.Text = "请输入用户ID！";

errMessage.Visibility = Visibility.Visible;

return;

}

//调用WebService

ServiceReference1.UserManageSoapClient userMgrSoapClient = new longSilverlightSQL.ServiceReference1.UserManageSoapClient();

//创建用户操作

userMgrSoapClient.CreateUserAsync(Int16.Parse(AddTextID.Text), AddTextName.Text, AddAddress.Text);

userMgrSoapClient.CreateUserCompleted += new EventHandler<longSilverlightSQL.ServiceReference1.

CreateUserCompletedEventArgs>(userMgrSoapClient_CreateUserCompleted);

}

void userMgrSoapClient_CreateUserCompleted(object sender, longSilverlightSQL.ServiceReference1.CreateUserCompletedEventArgs e)

{

//if (e.Error == null)

if (e.Result == true)

{

errMessage.Text = "添加用户成功！";

errMessage.Foreground = new SolidColorBrush(Colors.Blue);

errMessage.Visibility = Visibility.Visible;

udGrid.Show();

ListingControlDisplay(null, null);

}

else

{

errMessage.Foreground = new SolidColorBrush(Colors.Red);

errMessage.Text = "添加用户失败！";

errMessage.Visibility = Visibility.Visible;

}

//显示数据

void ListingControlDisplay(object sender, RoutedEventArgs e)

{

ServiceReference1.UserManageSoapClient userMgrSoapClient = new longSilverlightSQL.ServiceReference1.UserManageSoapClient();

userMgrSoapClient.RetrieveUsersAsync();

userMgrSoapClient.RetrieveUsersCompleted += new EventHandler<longSilverlightSQL.ServiceReference1.

RetrieveUsersCompletedEventArgs>(userMgrSoapClient_RetrieveUsersCompleted);

}

void userMgrSoapClient_RetrieveUsersCompleted(object sender, longSilverlightSQL.ServiceReference1.RetrieveUsersCompletedEventArgs e)

{

if (e.Error == null)

displayData(e.Result);

}

private void displayData(string xmlContent)

{

try

{

if (xmlContent != string.Empty)

{

XDocument xmlUsers = XDocument.Parse(xmlContent);

var users = from user in xmlUsers.Descendants("User")

select new

{

UserID = Convert.ToInt32

(user.Element("UserID").Value),

UserName = (string)

user.Element("UserName").Value,

HomeAddress = (string)

user.Element("HomeAddress").Value

};

List<User> usersList = new List<User>();

foreach (var u in users)

{

User use = new User { UserID = u.UserID, UserName = u.UserName, HomeAddress = u.HomeAddress };

usersList.Add(use);

}

udGrid.userDataGrid.ItemsSource = usersList;

}

else

{

udGrid.userDataGrid.ItemsSource = null;

}

catch (Exception ex)

{

Console.Write(ex.Message);

}

public class User

{

public int UserID { get; set; }

public string UserName { get; set; }

public string HomeAddress { get; set; }

}

////////////////////////////////

运行项目

posted on 2009-09-29 08:59 李承隆阅读(1841) 评论(0) 编辑收藏举报