Example #1
0
 private void Bind()
 {
     using (SqlConnection sqlcn = new SqlConnection(Config.SqlCredentials))
     {
         try
         {
             #region 通过传入的ISBN查询出Books表中的书籍信息,并绑定到TextBox
             using (SqlCommand cmd = new SqlCommand("SELECT ISBN,AID,PubID,BName,Sales FROM BOOKS Where ISBN=@ISBN", sqlcn))
             {
                 DataSet ds = new DataSet();
                 cmd.Parameters.Add(new SqlParameter("@ISBN", long.Parse(_ISBN)));
                 SqlDataAdapter adapter = new SqlDataAdapter();
                 sqlcn.Open();
                 adapter.SelectCommand = cmd;
                 adapter.Fill(ds);
                 ISBN.Text  = ds.Tables[0].Rows[0][0].ToString();
                 AID.Text   = ds.Tables[0].Rows[0][1].ToString();
                 PubID.Text = ds.Tables[0].Rows[0][2].ToString();
                 oldAID     = int.Parse(AID.Text);
                 oldPubID   = int.Parse(PubID.Text);
                 BName.Text = ds.Tables[0].Rows[0][3].ToString();
                 Sales.Text = ds.Tables[0].Rows[0][4].ToString();
             }
             #endregion
             #region 通过书籍信息中的AID获取作者信息,并绑定到TextBox
             using (SqlCommand cmd = new SqlCommand("SELECT AName,ANationality FROM Authors Where AID=@AID", sqlcn))
             {
                 DataSet ds = new DataSet();
                 cmd.Parameters.Add(new SqlParameter("@AID", int.Parse(AID.Text)));
                 SqlDataAdapter adapter = new SqlDataAdapter();
                 adapter.SelectCommand = cmd;
                 adapter.Fill(ds);
                 AName.Text        = ds.Tables[0].Rows[0][0].ToString();
                 ANationality.Text = ds.Tables[0].Rows[0][1].ToString();
             }
             #endregion
             #region 通过书籍信息获取出版社信息,并绑定到TextBox
             using (SqlCommand cmd = new SqlCommand("SELECT PubName,PubTele,PubAddr FROM Publishers Where PubID=@PubID", sqlcn))
             {
                 DataSet ds = new DataSet();
                 cmd.Parameters.Add(new SqlParameter("@PubID", int.Parse(PubID.Text)));
                 SqlDataAdapter adapter = new SqlDataAdapter();
                 adapter.SelectCommand = cmd;
                 adapter.Fill(ds);
                 PubName.Text = ds.Tables[0].Rows[0][0].ToString();
                 PubTele.Text = ds.Tables[0].Rows[0][1].ToString();
                 PubAddr.Text = ds.Tables[0].Rows[0][2].ToString();
             }
             #endregion
         }
         #region 错误处理
         catch (Exception ex)
         {
             var message = new myMessageBox("出现错误!" + Environment.NewLine + "详细信息:" + ex.Message, "警告");
             message.ShowDialog();
             ParentWindow.frmMain.Navigate(Res);
         }
         #endregion
     }
 }
Example #2
0
 private void DeleteButton_Click(object sender, RoutedEventArgs e)
 {
     #region  除当前图书记录
     using (SqlConnection sqlcn = new SqlConnection(Config.SqlCredentials))
     {
         try
         {
             using (SqlCommand cmd = new SqlCommand("DELETE FROM BOOKS Where ISBN=@ISBN", sqlcn))
             {
                 cmd.Parameters.Add(new SqlParameter("@ISBN", long.Parse(ISBN.Text)));
                 sqlcn.Open();
                 cmd.ExecuteNonQuery();
             }
             myMessageBox myMessageBox = new myMessageBox("删除记录成功", "提示");
             myMessageBox.ShowDialog();
             ParentWindow.frmMain.Navigate(new Index(ParentWindow));
         }
         #endregion
         #region 出错时的处理
         catch (Exception excp)
         {
             myMessageBox messageBox = new myMessageBox("出现错误!" + Environment.NewLine + "详细信息:" + excp.Message, "警告");
             messageBox.ShowDialog();
         }
         #endregion
     }
 }
Example #3
0
 private void Button_Click(object sender, RoutedEventArgs e)
 {
     try
     {
         if (PassedAuthentication())
         {
             Window1 window1 = new Window1();
             window1.Show();
             Close();
         }
         else
         {
             myMessageBox myMessage = new myMessageBox("用户名或密码无效", "提示");
             myMessage.ShowDialog();
         }
     }
     catch (Exception excp)
     {
         myMessageBox messageBox = new myMessageBox("出现错误!" + Environment.NewLine + "详细信息:" + excp.Message, "警告");
         messageBox.ShowDialog();
     }
 }
Example #4
0
        private void Bind()
        {
            DataSet   ds = new DataSet();
            DataTable dt = new DataTable();

            try
            {
                using (SqlConnection sqlcn = new SqlConnection(Config.SqlCredentials))
                {
                    #region 通过ISBN精准查询
                    try
                    {
                        if (Key.Length == 13 || Key.Length == 10)
                        {
                            using (SqlCommand cmd = new SqlCommand("SELECT ISBN,AName,ANationality,BName,PubName FROM BOOKS,Authors,Publishers Where Books.AID=Authors.AID AND Books.PubID=Publishers.PubID AND ISBN=@ISBN", sqlcn))
                            {
                                cmd.Parameters.Add(new SqlParameter("@ISBN", long.Parse(Key)));
                                SqlDataAdapter adapter = new SqlDataAdapter();
                                sqlcn.Open();
                                adapter.SelectCommand = cmd;
                                adapter.Fill(ds);
                                dt = ds.Tables[0];
                            }
                        }
                        else
                        {
                            throw new FormatException();
                        }
                    }
                    #endregion
                    #region 通过书名、作者名、出版社名模糊查询,并合并重复结果
                    catch (FormatException)
                    {
                        using (SqlCommand cmd = new SqlCommand("SELECT ISBN,AName,ANationality,BName,PubName FROM BOOKS,Authors,Publishers Where Books.AID=Authors.AID AND Books.PubID=Publishers.PubID AND BName like @BName;" +
                                                               "SELECT ISBN,AName,ANationality,BName,PubName FROM BOOKS,Authors,Publishers Where Books.AID=Authors.AID AND Books.PubID=Publishers.PubID AND AName like @AName;" +
                                                               "SELECT ISBN,AName,ANationality,BName,PubName FROM BOOKS,Authors,Publishers Where Books.AID=Authors.AID AND Books.PubID=Publishers.PubID AND PubName like @PubName",
                                                               sqlcn))
                        {
                            cmd.Parameters.Add(new SqlParameter("@BName", "%" + Key + "%"));
                            cmd.Parameters.Add(new SqlParameter("@AName", "%" + Key + "%"));
                            cmd.Parameters.Add(new SqlParameter("@PubName", "%" + Key + "%"));
                            SqlDataAdapter adapter = new SqlDataAdapter {
                                SelectCommand = cmd
                            };
                            adapter.Fill(ds);
                            ds.Tables[0].Merge(ds.Tables[1]);
                            ds.Tables[0].Merge(ds.Tables[2]);
                            var      dv      = new DataView(ds.Tables[0]);
                            string[] strCols = { "ISBN", "AName", "ANationality", "BName", "PubName" };
                            dt = dv.ToTable(true, strCols);
                        }
                    }
                    #endregion
                }
            }
            #region 错误处理
            catch (Exception ex)
            {
                var message = new myMessageBox("出现错误!" + Environment.NewLine + "详细信息" + ex.Message, "警告");
                message.ShowDialog();
                ParentWindow.frmMain.Navigate(new Index(ParentWindow));
            }
            #endregion

            #region 获取查询到的结果条数,绑定到xaml
            listBox.DataContext = dt;
            Countobj countobj = new Countobj {
                Count = dt.Rows.Count
            };
            countS.DataContext = countobj;
            #endregion
        }
Example #5
0
 private void Update_Click(object sender, RoutedEventArgs e)
 {
     using (SqlConnection sqlcn = new SqlConnection(Config.SqlCredentials))
         try
         {
             sqlcn.Open();
             foreach (TextBox textBox in ChangedTextBox)
             {
                 #region 被修改内容为Authors表中内容,若PubID被修改,则级联修改到Books表
                 if (textBox.Name.StartsWith("A"))
                 {
                     using (SqlCommand cmd = new SqlCommand("UPDATE Authors SET " + textBox.Name + "=@value WHERE AID=@oldAID", sqlcn))
                     {
                         cmd.Parameters.Add(new SqlParameter("@oldAID", oldAID));
                         if (textBox.Name == "AID")
                         {
                             cmd.Parameters.Add(new SqlParameter("@value", int.Parse(textBox.Text)));
                         }
                         else
                         {
                             cmd.Parameters.Add(new SqlParameter("@value", textBox.Text));
                         }
                         cmd.ExecuteNonQuery();
                     }
                 }
                 #endregion
                 #region 被修改内容为Publishers表中内容,若PubID被修改,则级联修改到Books表
                 else if (textBox.Name.StartsWith("Pub"))
                 {
                     using (SqlCommand cmd = new SqlCommand("UPDATE Publishers SET " + textBox.Name + "=@value WHERE PubID=@oldPubID", sqlcn))
                     {
                         cmd.Parameters.Add(new SqlParameter("@oldPubID", oldPubID));
                         if (textBox.Name == "PubID")
                         {
                             cmd.Parameters.Add(new SqlParameter("@value", int.Parse(textBox.Text)));
                         }
                         else
                         {
                             cmd.Parameters.Add(new SqlParameter("@value", textBox.Text));
                         }
                         cmd.ExecuteNonQuery();
                     }
                 }
                 #endregion
                 #region 被修改内容为Books表中非外键内容
                 else
                 {
                     using (SqlCommand cmd = new SqlCommand("UPDATE Books SET " + textBox.Name + "=@value WHERE ISBN=@oldISBN", sqlcn))
                     {
                         cmd.Parameters.Add(new SqlParameter("@oldISBN", long.Parse(_ISBN)));
                         if (textBox.Name == "ISBN")
                         {
                             if (textBox.Text.Length != 13 && textBox.Text.Length != 10)
                             {
                                 throw new FormatException("ISBN长度有误");
                             }
                             else
                             {
                                 cmd.Parameters.Add(new SqlParameter("@value", long.Parse(textBox.Text)));
                             }
                         }
                         else if (textBox.Name == "Sales")
                         {
                             cmd.Parameters.Add(new SqlParameter("@value", int.Parse(textBox.Text)));
                         }
                         else
                         {
                             cmd.Parameters.Add(new SqlParameter("@value", textBox.Text));
                         }
                         cmd.ExecuteNonQuery();
                     }
                 }
                 #endregion
             }
             var message = new myMessageBox("修改成功!", "提示");
             message.ShowDialog();
             ParentWindow.frmMain.Navigate(new Index(ParentWindow));
         }
     #region 错误处理
     catch (Exception excp)
     {
         myMessageBox messageBox = new myMessageBox("出现错误!" + Environment.NewLine + "详细信息:" + excp.Message, "警告");
         messageBox.ShowDialog();
     }
     #endregion
 }
Example #6
0
        private void Button_Click(object sender, RoutedEventArgs e)
        {
            DataSet ds = new DataSet();

            try
            {
                #region 强制要求ISBN为13位或10位
                if (ISBN.Text.Length != 13 && ISBN.Text.Length != 7)
                {
                    throw new FormatException("ISBN长度有误!");
                }
                #endregion
                #region 检查所填记录是否有存在的部分,不存在则ds对应的table里的值为0
                using (SqlConnection sqlcn = new SqlConnection(Config.SqlCredentials))
                {
                    using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM Books Where ISBN=@ISBN;Select COUNT(*) from Authors Where AID=@AID;Select COUNT(*) from Publishers Where PubID=@PubID", sqlcn))
                    {
                        #region 添加参数并将参数格式化为所需类型
                        cmd.Parameters.Add("ISBN", SqlDbType.BigInt);
                        cmd.Parameters.Add("AID", SqlDbType.Int);
                        cmd.Parameters.Add("PubID", SqlDbType.Int);
                        cmd.Parameters[0].Value = long.Parse(ISBN.Text);
                        cmd.Parameters[1].Value = int.Parse(AID.Text);
                        cmd.Parameters[2].Value = int.Parse(PubID.Text);
                        #endregion
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        sqlcn.Open();
                        adapter.SelectCommand = cmd;
                        adapter.Fill(ds);
                        var d = ds;
                    }
                    #endregion
                    #region 根据检查结果决定是否对每个表插入新数据
                    if (ds.Tables[2].Rows[0][0].ToString() == "0")
                    {
                        using (SqlCommand cmd = new SqlCommand("Insert into Publishers(PubID,PubName,PubTele,PubAddr) Values(@PubID,@PubName,@PubTele,@PubAddr) ", sqlcn))
                        {
                            cmd.Parameters.Add(new SqlParameter("@PubID", int.Parse(PubID.Text)));
                            cmd.Parameters.Add(new SqlParameter("@PubName", PubName.Text));
                            cmd.Parameters.Add(new SqlParameter("@PubTele", PubTele.Text));
                            cmd.Parameters.Add(new SqlParameter("@PubAddr", PubAddr.Text));
                            cmd.ExecuteNonQuery();
                        }
                    }
                    if (ds.Tables[1].Rows[0][0].ToString() == "0")
                    {
                        using (SqlCommand cmd = new SqlCommand("Insert into Authors(AID,AName,ANationality) Values(@AID,@AName,@ANationality)", sqlcn))
                        {
                            cmd.Parameters.Add(new SqlParameter("@AID", int.Parse(AID.Text)));
                            cmd.Parameters.Add(new SqlParameter("@AName", AName.Text));
                            cmd.Parameters.Add(new SqlParameter("@ANationality", ANationality.Text));
                            cmd.ExecuteNonQuery();
                        }
                    }
                    //由于外键约束要把对另两个表的操作放在Books表前
                    if (ds.Tables[0].Rows[0][0].ToString() == "0")
                    {
                        using (SqlCommand cmd = new SqlCommand("Insert into Books(ISBN,PubID,AID,BName,Sales) Values(@ISBN,@PubID,@AID,@BName,@Sales ) ", sqlcn))
                        {
                            cmd.Parameters.Add(new SqlParameter("@ISBN", long.Parse(ISBN.Text)));
                            cmd.Parameters.Add(new SqlParameter("@PubID", int.Parse(PubID.Text)));
                            cmd.Parameters.Add(new SqlParameter("@AID", int.Parse(AID.Text)));
                            cmd.Parameters.Add(new SqlParameter("BName", BName.Text));
                            cmd.Parameters.Add(new SqlParameter("@Sales", int.Parse(Sales.Text)));
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                #endregion
                #region 成功提示并关闭窗口
                myMessageBox myMessageBox = new myMessageBox("添加记录成功", "提示");
                myMessageBox.ShowDialog();
                Close();
                #endregion
            }
            #region 错误处理
            catch (Exception excp)
            {
                myMessageBox messageBox = new myMessageBox("出现错误!" + Environment.NewLine + "详细信息:" + excp.Message, "警告");
                messageBox.ShowDialog();
            }
            #endregion
        }