Silverlight+WCF+Sql Server

      这是来公司后做的第三个实例项目，功能是Silverlight+WCF+Sql Server实现对数据库数据的增删改查。过程中也遇到一些问题。不过还好解决了！刚开始以为自己做不出来呢，没想到竟然成功了！值此十一国庆佳节之际特此记录！祝大家国庆快乐！

解决方案如下：

创建过程：

1.打开VS，创建silverlight程序，选择web站点承载silverlight程序；

2.在web项目中添加wcf服务，添加契约并实现相应功能；

WCF契约：

 public interface IUserService    {        [OperationContract]        List<User> RetrieveUser();        [OperationContract]        bool CreateUser(int userID, string userName);        [OperationContract]        bool UpdateUser(int userID, string userName);        [OperationContract]        bool DeleteUser(int userID);    }    [DataContract]    public class User    {        [DataMember]       public int UserId {get; set;}        [DataMember]        public string UserName { get; set; }         }

WCF契约实现：

 //查询用户        public List<User> RetrieveUser()        {            List<User> user = new List<User>();            try            {                                SqlConnection _sqlConnection =new SqlConnection("Database=test1;Server=服务器名;Integrated Security=false;password=123;user id=sa;");                _sqlConnection.Open();                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [User] ", _sqlConnection);                         DataSet ds = new DataSet();                da.Fill(ds);                foreach (DataRow row in ds.Tables[0].Rows)                {                    User a = new User();                    a.UserId =(int) row["UserId"];                    a.UserName =(string) row["UserName"];                    user.Add(a);                                   }                return user;            }            catch (Exception ex)            {               user.Clear();               return user;            }        }        //创建用户        public bool CreateUser(int userID, string userName)        {            try            {                SqlConnection _sqlConnection = new SqlConnection("Database=test1;Server=服务器名;Integrated Security=false;password=123;user id=sa;");                _sqlConnection.Open();                SqlCommand command = new SqlCommand();                command.Connection = _sqlConnection;                command.CommandType = CommandType.Text;                command.CommandText = "INSERT INTO [User]  ([UserId],[UserName]) VALUES ('" + userID+ "','" + userName+ "')";                command.ExecuteNonQuery();                _sqlConnection.Close();                return true;            }            catch (Exception ex)            {                return false;            }        }        //更新用户        public bool UpdateUser(int userID, string userName)        {            try            {                SqlConnection _sqlConnection = new SqlConnection("Database=test1;Server=服务器名;Integrated Security=false;password=123;user id=sa;");                _sqlConnection.Open();                SqlCommand command = new SqlCommand();                command.Connection = _sqlConnection;                command.CommandType = CommandType.Text;                command.CommandText = "UPDATE [User] SET [UserName] = " +userName + "WHERE [UserId] = " + userID;                               command.ExecuteNonQuery();                _sqlConnection.Close();                return true;            }            catch (Exception ex)            {                return false;            }                  }               //删除用户            public bool DeleteUser(int userID)        {            try            {                SqlConnection _sqlConnection = new SqlConnection("Database=test1;Server=服务器名;Integrated Security=false;password=123;user id=sa;");                _sqlConnection.Open();                SqlCommand command = new SqlCommand();                command.Connection = _sqlConnection;                command.CommandType = CommandType.Text;                command.CommandText = "DELETE [User] WHERE [UserId] =" + userID;                command.ExecuteNonQuery();                _sqlConnection.Close();                return true;            }            catch (Exception ex)            {                return false;            }        }

3.在silverlight中添加wcf的服务引用。

MainPage前台：

 <Grid x:Name="LayoutRoot" Background="White">        <StackPanel HorizontalAlignment="Center" VerticalAlignment="Center">            <StackPanel VerticalAlignment="Center" HorizontalAlignment="Center">            <StackPanel Orientation="Horizontal">                <TextBlock TextAlignment="Center" VerticalAlignment="Center">UserId:</TextBlock>                <TextBox x:Name="userid" Width="76" Margin="21,0,0,0"></TextBox>            </StackPanel>        </StackPanel>            <StackPanel Margin="0,10,0,0" HorizontalAlignment="Center" VerticalAlignment="Center">                <StackPanel Orientation="Horizontal">                    <TextBlock VerticalAlignment="Center" TextAlignment="Center" HorizontalAlignment="Center">UserName:</TextBlock>                    <TextBox x:Name="username" Width="76"></TextBox>                </StackPanel>            </StackPanel>            <StackPanel Orientation="Horizontal" HorizontalAlignment="Center" VerticalAlignment="Center">            <StackPanel Margin="0,10,0,0">        <Button x:Name="btnselect" Click="btnselect_Click" Margin="30,0,0,0">SelectButton</Button>        </StackPanel>                       <StackPanel Margin="0,10,0,0">                <Button x:Name="btnupdate" Click="btnupdate_Click" Margin="10,0,0,0">UpdateButton</Button>            </StackPanel>            <StackPanel Margin="0,10,0,0">                <Button Click="deleteButton_Click" Margin="10,0,0,0">DeleteButton</Button>            </StackPanel>                <StackPanel Margin="0,10,0,0">                    <Button x:Name="btncreate" Click="btncreate_Click" Margin="10,0,0,0">CreateButton</Button>                </StackPanel>            </StackPanel>            <StackPanel>                            </StackPanel>            <StackPanel Height="165" Margin="0,10,0,0">                <sdk:DataGrid  AutoGenerateColumns="False" Height="152" Name="dataGrid1" Width="144" DataContext="{Binding}" SelectionChanged="dataGrid1_SelectionChanged">                    <sdk:DataGrid.Columns>                        <sdk:DataGridTextColumn Binding="{Binding UserId,Mode=TwoWay}" CanUserReorder="True" CanUserResize="True" CanUserSort="True" Header="UserId" Width="Auto" />                        <sdk:DataGridTextColumn  Binding="{Binding UserName,Mode=TwoWay}" CanUserReorder="True" CanUserResize="True" CanUserSort="True" Header="UserName" Width="Auto" />                    </sdk:DataGrid.Columns>                </sdk:DataGrid>            </StackPanel>                   </StackPanel>    </Grid>

MainPage后台：

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 LYY.UserService;namespace LYY{    public partial class MainPage : UserControl    {        public MainPage()        {            InitializeComponent();        }        void deleteButton_Click(object sender, RoutedEventArgs e)        {                       UserServiceClient userSvcClient = new UserServiceClient();            int userID;            userID = Convert.ToInt32(this.userid.Text);            userSvcClient.DeleteUserCompleted += new EventHandler<DeleteUserCompletedEventArgs> (userSvcClient_DeleteUserCompleted);                  userSvcClient.DeleteUserAsync(userID);        }        void userSvcClient_DeleteUserCompleted(object sender,DeleteUserCompletedEventArgs e)        {            if (e.Error == null)            {                MessageBox.Show("删除用户成功！");                           }            else            {                MessageBox.Show("删除用户失败！");                           }        }               private void btncreate_Click(object sender, RoutedEventArgs e)        {            UserServiceClient client = new UserServiceClient();            int userID;            userID = Convert.ToInt32(this.userid.Text);            string username = this.username.Text;            client.CreateUserCompleted += new EventHandler<CreateUserCompletedEventArgs>(client_create);            client.CreateUserAsync(userID, username);        }        void client_create(object sender, CreateUserCompletedEventArgs e)        {            if (e.Result)            {                MessageBox.Show("用户创建成功！");            }            else            {                MessageBox.Show("用户创建成功！");            }        }        private void btnupdate_Click(object sender, RoutedEventArgs e)        {            UserServiceClient client = new UserServiceClient();            int userID;            userID = Convert.ToInt32(this.userid.Text);            string username = this.username.Text;            client.UpdateUserCompleted += new EventHandler<UpdateUserCompletedEventArgs>(client_update);            client.UpdateUserAsync(userID, username);        }        void client_update(object sender, UpdateUserCompletedEventArgs e)        {            if (e.Result)            {                MessageBox.Show("用户更新成功！");            }            else            {                MessageBox.Show("用户更新成功！");            }        }        private void btnselect_Click(object sender, RoutedEventArgs e)        {            UserServiceClient client = new UserServiceClient();            client.RetrieveUserCompleted += new EventHandler<RetrieveUserCompletedEventArgs>(client_select);            client.RetrieveUserAsync();        }        void client_select(object sender,RetrieveUserCompletedEventArgs e)        {            if (e.Error==null)            {                this.dataGrid1.ItemsSource = e.Result;            }         }        private void dataGrid1_SelectionChanged(object sender, SelectionChangedEventArgs e)        {            User current = new User();            current = (User)this.dataGrid1.SelectedItem;            this.userid.Text = Convert.ToString(current.UserId);            this.username.Text = current.UserName;        }    }}