SilverLight中使用WebService对数据库做CRUD

SilverLight中使用WebService对数据库做CRUD

 

1.       新建一个asmx文件

 

    public class MainService : System.Web.Services.WebService

    {

        #region User CRUD

        [WebMethod]

        public bool CreateUser(string userName)

        {

            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] ([UserName]) VALUES ('" +

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

                command.ExecuteNonQuery();

                _sqlConnection.Close();

                return true;

            }

            catch (Exception ex)

            {

                return false;

            }

        }

 

 

        [WebMethod]

        public string RetrieveUser(int userId)

        {

            try

            {

                SqlConnection _sqlConnection = new SqlConnection();

                _sqlConnection.ConnectionString = ConfigurationManager.

                    ConnectionStrings["sqlConnectionString"].ToString();

                _sqlConnection.Open();

                SqlDataAdapter da = new SqlDataAdapter();

                da.SelectCommand = new SqlCommand("SELECT * FROM [User] WHERE [UserID] = " +

                    userId.ToString().Replace("'", "''"), _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("<ID>");

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

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

                    sb.Append("<Name>");

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

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

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

                }

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

 

                _sqlConnection.Close();

                return sb.ToString();

            }

            catch (Exception ex)

            {

                return string.Empty;

            }

        }

 

        [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("</User>");

                }

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

 

                _sqlConnection.Close();

                return sb.ToString();

            }

            catch (Exception ex)

            {

                return string.Empty;

            }

        }

 

        [WebMethod]

        public bool UpdateUser(int userID, string userName)

        {

            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("'", "''") + "'" +

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

                command.ExecuteNonQuery();

                _sqlConnection.Close();

                return true;

            }

            catch (Exception ex)

            {

                return false;

            }

        }

 

        [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;

            }

        }

        #endregion

 

}

 

2.      设置xaml页面文件

  <data:DataGrid x:Name="dgWork" Background="Aquamarine" Height="200"

               Width="700" Margin="0,5,0,10"

              AutoGenerateColumns="False"  >

                    <data:DataGrid.Columns>

                        <data:DataGridTextColumn Header="用户ID" IsReadOnly="True"  Width="80"  Binding="{Binding UserID}" />

                        <data:DataGridTextColumn Header="用户名称" IsReadOnly="True"  Width="200"   Binding="{Binding UserName}" />

                    </data:DataGrid.Columns>

                    

                </data:DataGrid>

 

3.      在SilverLight项目中新建实体类

    public class User

    {

        public int UserID { get; set; }

        public string UserName { get; set; }

}

 

4.      调用WebService,并显示数据

 

  public partial class MainPage : UserControl

    {

        public MainPage()

        {

            InitializeComponent();

            Loaded += new RoutedEventHandler(MainPage_Loaded);

 

        }

        private void MainPage_Loaded(object sender, RoutedEventArgs e)

        {

            ListingControlDisplay();

        }

 

        /// <summary>

        /// 显示数据

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        void ListingControlDisplay()

        {

            MainServiceSoapClient userMgrSoapClient = new MainServiceSoapClient();

            userMgrSoapClient.RetrieveUsersAsync();

            userMgrSoapClient.RetrieveUsersCompleted += new EventHandler<RetrieveUsersCompletedEventArgs>(userMgrSoapClient_RetrieveUsersCompleted);

        }

        /// <summary>

        /// 取数完成后，显示数据

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        void userMgrSoapClient_RetrieveUsersCompleted(object sender, MainService.RetrieveUsersCompletedEventArgs e)

        {

            if (e.Error == null)

            {

                displayData(e.Result);

            }

        }

 

        /// <summary>

        /// 显示数据到界面上

        /// </summary>

        /// <param name="xmlContent"></param>

        private void displayData(string xmlContent)

        {

            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

                            };

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

                foreach (var u in users)

                {

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

                    usersList.Add(use);

                }

                dgWork.ItemsSource = usersList;

            }

            else

            {

                dgWork.ItemsSource = null;

            }

 

        }

 

    }