silverlight 通过 WebService 连接数据库 实现对 DataGrid 的增删改查 示例

InformationWebService.asmx.cs代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;   //引入该命名空间为了操作Access数据库

namespace SilverlightTest
{
    /// <summary>
    /// InformationWebService 的摘要说明
    /// </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 InformationWebService : System.Web.Services.WebService
    {
        public static string ConnectionString = "Server=192.168.16.202;User id=sa;Pwd=sa0123456;database=test";
        [WebMethod]  //获取数据表信息
        public List<UserModel> GetInfo()
        {
            List<UserModel> returnedValue = new List<UserModel>();
            SqlCommand cmd = new SqlCommand();
            SQLExcute("SELECT * from userinfor order by userid asc", cmd);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                UserModel tmp = new UserModel();
                tmp.userid = Convert.ToInt32(dr[0]);
                tmp.username = Convert.ToString(dr[1]);
                tmp.sex = Convert.ToString(dr[2]);
                tmp.age = Convert.ToInt32(dr[3]);
                returnedValue.Add(tmp);
            }
            return returnedValue;
        }

        [WebMethod]  //添加信息
        public void insert(string UserName, string Sex,int Age)
        {
            string sql = "insert into userinfor(username,sex,age) values('" + UserName + "','" + Sex + "'," + Age + ")";
            SQLExcute(sql);
        }

        [WebMethod] //修改信息
        public bool update(int ID, string UserName, string Sex, int Age)
        {
            string sql = "Update userinfor set username='" + UserName + "',sex='" + Sex + "',age=" + Age + " where userid=" + ID;
            return SQLExcute(sql);
        }
        [WebMethod]
        public bool Del(int ID)
        {
            string sql = "delete from userinfor where userid=" + ID;
            return SQLExcute(sql);
        }
        //SQL的操作  
        private bool SQLExcute(string SQLCmd)
        {
            bool blResult = false;
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandTimeout = 15;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = SQLCmd;
            if (cmd.ExecuteNonQuery() > 0)
            {
                blResult = true;
            }
            conn.Close();
            return blResult;
        }

        //SQL的操作 是SQLExcute的重构
        private void SQLExcute(string SQLCmd, SqlCommand Cmd)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection Conn = new SqlConnection(ConnectionString);
            Conn.Open();
            Cmd.Connection = Conn;
            Cmd.CommandTimeout = 15;
            Cmd.CommandType = CommandType.Text;
            Cmd.CommandText = SQLCmd;
            Cmd.ExecuteNonQuery();
            Conn.Close();
        }
    }
}

MainPage.xaml 页面前台代码如下：

<UserControl xmlns:controls="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls"  xmlns:my="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data"  x:Class="SilverlightApplication1.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d">
    <UserControl.Background>
        <LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0">
            <GradientStop Color="#FF1847EE"/>
            <GradientStop Color="#FFFFFFFF" Offset="1"/>
            <GradientStop Color="#FF7390F4" Offset="0.125"/>
        </LinearGradientBrush>
    </UserControl.Background>
    <Canvas Background="Silver" Height="647" Width="800">
        <TextBlock x:Name="info" OpacityMask="#FF181111" Foreground="#FF5A5959"/>
        <my:DataGrid x:Name="userDataGrid" Height="276" Width="776" Grid.Row="1" Canvas.Top="20" Canvas.Left="8" AutoGenerateColumns="False" CellEditEnding="userDataGrid_CellEditEnding" CellEditEnded="userDataGrid_CellEditEnded">
            <my:DataGrid.Background>
                <LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0">
                    <GradientStop Color="gray"/>
                    <GradientStop Color="#FFFFFFFF" Offset="1"/>
                </LinearGradientBrush>
            </my:DataGrid.Background>
            <my:DataGrid.BorderBrush>
                <LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0">
                    <GradientStop Color="#FFA3AEB9"/>
                    <GradientStop Color="#FF8399A9" Offset="0.375"/>
                    <GradientStop Color="#FF718597" Offset="0.571"/>
                    <GradientStop Color="#FF617584" Offset="1"/>
                </LinearGradientBrush>
            </my:DataGrid.BorderBrush>
            <my:DataGrid.Columns>
                <my:DataGridTemplateColumn Header="选择">
                  
                    <my:DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <StackPanel Orientation="Horizontal">
                                <CheckBox x:Name="ck" Tag="{Binding userid,Mode=TwoWay}"></CheckBox>
                            </StackPanel>
                        </DataTemplate>
                    </my:DataGridTemplateColumn.CellTemplate>

                </my:DataGridTemplateColumn>
                <my:DataGridTextColumn Header="编号" Binding="{Binding userid,Mode=TwoWay}"/>
                <my:DataGridTextColumn Header="姓名" Binding="{Binding username,Mode=TwoWay}"/>
                <my:DataGridTextColumn Header="性别" Binding="{Binding sex,Mode=TwoWay}"/>
                <my:DataGridTextColumn Header="年龄" Binding="{Binding age,Mode=TwoWay}"/>
                <my:DataGridTemplateColumn Header="操作">
                    <my:DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <StackPanel Orientation="Horizontal">
                                <Button x:Name="bt_del" Tag="{ Binding userid}" Content="删除" BorderBrush="#FFC1B5B5" Foreground="Black" Click="bt_del_Click" />
                            </StackPanel>
                        </DataTemplate>
                    </my:DataGridTemplateColumn.CellTemplate>
                </my:DataGridTemplateColumn>               
            </my:DataGrid.Columns>
        </my:DataGrid>
        <my:DataPager x:Name="pager" Canvas.Left="154" Canvas.Top="265" DisplayMode="FirstLastPreviousNextNumeric" PageSize="5" Source="{Binding ItemsSource,ElementName=userDataGrid}"/>
            <Rectangle Height="267" Width="776" Canvas.Left="8" Canvas.Top="305" Stroke="{x:Null}" RadiusX="8" RadiusY="8">
            <Rectangle.Fill>
                <LinearGradientBrush EndPoint="0.5,1" StartPoint="0.5,0">
                    <GradientStop Color="#FF2D2D2F" Offset="0.027"/>
                    <GradientStop Color="#FFC1BCBC" Offset="1"/>
                    <GradientStop Color="#FF999292" Offset="1"/>
                </LinearGradientBrush>
            </Rectangle.Fill>
        </Rectangle>
        <TextBlock x:Name="TextBlock" Canvas.Top="397" Canvas.Left="260" Text="姓   名：" Foreground="#FFF7F6F6"/>
        <TextBox x:Name="txtname" Width="122" Canvas.Left="316" Canvas.Top="393" Background="#FFFBF6F6" />
        <TextBlock x:Name="TextBlock_Copy" Canvas.Left="260" Canvas.Top="432" Text="性   别：" Foreground="#FFFBF9F9"/>
        <TextBox x:Name="txtage" Width="122" Canvas.Left="316" Canvas.Top="461" VerticalScrollBarVisibility="Hidden" Height="26"/>
        <Button x:Name="addButton" Height="24" Width="56" Canvas.Left="316" Canvas.Top="513" Content="确认"/>
        <Button x:Name="ReWriteButton" Height="24" Width="56" Content="重写" Canvas.Left="382" Canvas.Top="513"/>
        <Rectangle Height="40" Width="768" Canvas.Left="12" Canvas.Top="596" RadiusX="8" RadiusY="8" Stroke="{x:Null}">
            <Rectangle.Fill>
                <LinearGradientBrush EndPoint="0.507000029087067,-0.291999995708466" StartPoint="0.507000029087067,1.83299994468689">
                    <GradientStop Color="#FF2D2D2F" Offset="0.504"/>
                    <GradientStop Color="#FFC1BCBC" Offset="1"/>
                    <GradientStop Color="#FF999292" Offset="1"/>
                </LinearGradientBrush>
            </Rectangle.Fill>
        </Rectangle>
                <TextBlock Canvas.Left="260" Canvas.Top="466" Height="15" Name="textBlock1" Text="年   龄：" Foreground="White" />
        <ComboBox Canvas.Left="317" Canvas.Top="429" Height="23" Name="cbsex" Width="120">
            <ComboBoxItem Content="男" Tag="M" />
            <ComboBoxItem Content="女" Tag="F" />
        </ComboBox>
        <CheckBox Canvas.Left="35" Canvas.Top="268" Content="全选" Height="16" Name="CBAll" Click="CBAll_Click" />
        <Button Canvas.Left="85" Canvas.Top="265" Content="删除" Height="23" Name="BtnDel" Width="50" Click="BtnDel_Click" />
    </Canvas>
</UserControl>

MainPage.xaml.cs后台代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Data;
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 SilverlightApplication1.ServiceReference1;
namespace SilverlightApplication1
{
    public partial class MainPage : UserControl
    {
        int originalNum;//记录初始时表中的数据总数
        InformationWebServiceSoapClient client = new InformationWebServiceSoapClient();
        public MainPage()
        {
            InitializeComponent();
            addButton.Click += new RoutedEventHandler(addButton_Click);  //添加按钮事件的声明
            ReWriteButton.Click += new RoutedEventHandler(ReWriteButton_Click);  //重写按钮事件的声明
            //调用Web service
            InformationWebServiceSoapClient client = new InformationWebServiceSoapClient();
            client.GetInfoCompleted += new EventHandler<GetInfoCompletedEventArgs>(client_GetInfoCompleted);
            client.GetInfoAsync();
        }

        private void addButton_Click(object sender, RoutedEventArgs e)
        {
            if (txtname.Text == "")
            {
                MessageBox.Show("请输入姓名！");
            }
            else if (txtage.Text.Trim() == string.Empty)
            {
                MessageBox.Show("请输入留言内容！");
            }
            else
            {
                //调用Web service事件  
                client.insertCompleted += new EventHandler<System.ComponentModel.AsyncCompletedEventArgs>(client_insertCompleted);
                client.insertAsync(txtname.Text.ToString(), (cbsex.SelectedItem as ComboBoxItem).Tag.ToString() , int.Parse(txtage.Text));
            }
        }
        void client_insertCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e)
        {
            if (e.Error == null)
            {
                MessageBox.Show("添加成功！");
                //即时更新datagrid
                client.GetInfoCompleted += new EventHandler<GetInfoCompletedEventArgs>(client_GetInfoCompleted);
                client.GetInfoAsync();
                txtname.Text = "";
                cbsex.SelectedItem = 0;
                txtage.Text = "";
            }
            else
            {
                MessageBox.Show(e.Error.ToString());//
            }
        }
        void ReWriteButton_Click(object sender, RoutedEventArgs e)
        {
            txtname.Text = "";
            txtage.Text = "";
            cbsex.SelectedItem = 0;
        }

        void client_GetInfoCompleted(object sender, GetInfoCompletedEventArgs e)
        {
            PagedCollectionView pcv = new PagedCollectionView(e.Result);

            userDataGrid.ItemsSource = pcv;
            originalNum = e.Result.Count;
            info.Text = "共有留言" + originalNum.ToString() + "条，具体如下：";
        }

        private void bt_del_Click(object sender, RoutedEventArgs e)         //(sender as Button).Tag.ToString() 获取 Button Tag 值
        {
            client.DelCompleted += new EventHandler<DelCompletedEventArgs>(client_delCompleted);
            client.DelAsync(Convert.ToInt32((sender as Button).Tag.ToString()));
        }
        void client_delCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e)
        {
            if (e.Error == null)
            {
                MessageBox.Show("删除成功！");
                client.GetInfoCompleted += new EventHandler<GetInfoCompletedEventArgs>(client_GetInfoCompleted);
                client.GetInfoAsync();
            }
            else
            {
                MessageBox.Show(e.Error.ToString());//
            }
        }

        private void userDataGrid_CellEditEnding(object sender, DataGridCellEditEndingEventArgs e)
        {
            userDataGrid.BeginEdit();
        }

        private void userDataGrid_CellEditEnded(object sender, DataGridCellEditEndedEventArgs e)
        {
            var model = userDataGrid.SelectedItem as UserModel;
            client.updateCompleted += new EventHandler<updateCompletedEventArgs>(client_updateCompleted);
            client.updateAsync(model.userid,model.username,model.sex,model.age);
        }
        void client_updateCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e)
        {
            if (e.Error == null)
            {
                client.GetInfoCompleted += new EventHandler<GetInfoCompletedEventArgs>(client_GetInfoCompleted);
                client.GetInfoAsync();
            }
            else
            {
                MessageBox.Show(e.Error.ToString());//
            }
        }

        private void CBAll_Click(object sender, RoutedEventArgs e)
        {
            if (userDataGrid.ItemsSource != null)
            {
                if (this.CBAll.IsChecked.Value)//全选
                {
                    foreach (object ovj in userDataGrid.ItemsSource)
                    {
                        CheckBox cb1 = userDataGrid.Columns[0].GetCellContent(ovj).FindName("ck") as CheckBox; //cb为

                        cb1.IsChecked = true;
                    }
                }
                else//取消
                {
                    foreach (object obj in userDataGrid.ItemsSource)
                    {
                        CheckBox cb2 = userDataGrid.Columns[0].GetCellContent(obj).FindName("ck") as CheckBox;

                        cb2.IsChecked = false;
                    }
                }
            }
        }

        private void BtnDel_Click(object sender, RoutedEventArgs e)
        {
            foreach (object obj in userDataGrid.ItemsSource)
            {
                CheckBox cb2 = userDataGrid.Columns[0].GetCellContent(obj).FindName("ck") as CheckBox;

                if (Convert.ToBoolean(cb2.IsChecked))
                {
                    client.DelCompleted += new EventHandler<DelCompletedEventArgs>(client_delCompleteds);
                    client.DelAsync(Convert.ToInt32(cb2.Tag.ToString()));
                }
            }
        }
        void client_delCompleteds(object sender, System.ComponentModel.AsyncCompletedEventArgs e)
        {
            if (e.Error == null)
            {
                client.GetInfoCompleted += new EventHandler<GetInfoCompletedEventArgs>(client_GetInfoCompleted);
                client.GetInfoAsync();
            }
            else
            {
                MessageBox.Show(e.Error.ToString());//
            }
        }
    }
}