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
}
}
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
}
}
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();
}
}
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
}
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
}
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
}
