dataengine.cs

小型图书管理系统,C#和SQlServer构建,Winform桌面应用程序

using System;
using System.Data;
using System.Data.OleDb;

using library.Forms;

namespace library.VisiteDB
{
	public class DataEngine
	{
		private static string m_strConnectionString = @"Provider=Microsoft.Jet.OleDb.4.0; Data Source="+ AppDomain.CurrentDomain.BaseDirectory + "\\Database\\library.mdb;Persist Security Info=False;Jet OLEDB:Database Password=";
		private OleDbConnection conn = new OleDbConnection(m_strConnectionString);
			
		public DataEngine()
		{
		}

		public void getUserList(DataSet ds)
		{			
			try
			{
				conn.Open();

				OleDbCommand cmd = conn.CreateCommand();
				cmd.CommandText = "select userid, username, password, emailid, lend from user_info order by userid";
				cmd.CommandType = CommandType.Text; 

				OleDbDataAdapter da = new OleDbDataAdapter(cmd);
				da.Fill(ds, "user");
			}
			catch(Exception ex)
			{
				String strError = ex.ToString();
			}
			finally
			{
				conn.Close();
			}
		}

		public void getBookList(DataSet ds)
		{	
			try
			{
				conn.Open();

				OleDbCommand cmd = conn.CreateCommand();
				if(frmData.bookCanLend == true)
				{
					cmd.CommandText = "select bookid, bookname, authorname, publishername from book_info where available = 1 order by bookid";
				}
				else
				{
					cmd.CommandText = "select bookid, bookname, authorname, publishername from book_info where available = 0 order by bookid";
				}
				cmd.CommandType = CommandType.Text; 

				OleDbDataAdapter da = new OleDbDataAdapter(cmd);
				da.Fill(ds, "book");
			}
			catch(Exception ex)
			{
				String strError = ex.ToString();
			}
			finally
			{
				conn.Close();
			}
		}

		public bool userRegister(string username, string password, string email)
		{
			try
			{
				conn.Open();

				OleDbCommand cmd = conn.CreateCommand();	
//				cmd.CommandText = "INSERT INTO user_info(username, password, emailid) VALUES (username, password, email)";				
				cmd.CommandText = "INSERT INTO user_info(username, [password], [emailid]) VALUES (?, ?, ?)";
				cmd.CommandType = CommandType.Text; 
				cmd.Parameters.Add("?username", OleDbType.VarChar).Value = username;
				cmd.Parameters.Add("?password", OleDbType.VarChar).Value = password;
				cmd.Parameters.Add("?emailid", OleDbType.VarChar).Value = email;
				cmd.ExecuteNonQuery();
				return true;
			}
			catch(Exception ex)
			{
				String strError = ex.ToString();
				return false;
			}
			finally
			{
				conn.Close();
			}
		}

		public void getNotLendUser(DataSet ds)
		{
			try
			{
				conn.Open();

				OleDbCommand cmd = conn.CreateCommand();
				cmd.CommandText = "SELECT userid, username FROM user_info where lend = 0";	
				cmd.CommandType = CommandType.Text; 

				OleDbDataAdapter da = new OleDbDataAdapter(cmd);
				da.Fill(ds, "NotLendUser");
			}
			catch(Exception ex)
			{
				String strError = ex.ToString();
			}
			finally
			{
				conn.Close();
			}
		}

		public void getNotLendBook(DataSet ds)
		{
			try
			{
				conn.Open();

				OleDbCommand cmd = conn.CreateCommand();
				cmd.CommandText = "SELECT bookid, bookname FROM book_info where available = 1";	
				cmd.CommandType = CommandType.Text; 

				OleDbDataAdapter da = new OleDbDataAdapter(cmd);
				da.Fill(ds, "NotLendBook");
			}
			catch(Exception ex)
			{
				String strError = ex.ToString();
			}
			finally
			{
				conn.Close();
			}
		}

		public void getHasLendUser(DataSet ds)
		{
			try
			{
				conn.Open();

				OleDbCommand cmd = conn.CreateCommand();
				cmd.CommandText = "SELECT userid, username FROM user_info where lend = 1";	
				cmd.CommandType = CommandType.Text; 

				OleDbDataAdapter da = new OleDbDataAdapter(cmd);
				da.Fill(ds, "HasLendUser");
			}
			catch(Exception ex)
			{
				String strError = ex.ToString();
			}
			finally
			{
				conn.Close();
			}
		}

		public void getHasLendBook(DataSet ds)
		{
			try
			{
				conn.Open();

				OleDbCommand cmd = conn.CreateCommand();
				cmd.CommandText = "SELECT bookid, bookname FROM book_info where available = 0";	
				cmd.CommandType = CommandType.Text; 

				OleDbDataAdapter da = new OleDbDataAdapter(cmd);
				da.Fill(ds, "HasLendBook");
			}
			catch(Exception ex)
			{
				String strError = ex.ToString();
			}
			finally
			{
				conn.Close();
			}
		}

		public bool checkUser(string strUser, string strPassword)
		{
			try
			{
				conn.Open();

				OleDbCommand cmd = conn.CreateCommand();
				cmd.CommandText = "SELECT username, password FROM user_info where username = ? and password = ?";
				cmd.CommandType = CommandType.Text; 
				cmd.Parameters.Add("?username", OleDbType.VarChar).Value = strUser;
				cmd.Parameters.Add("?password", OleDbType.VarChar).Value = strPassword;

				return cmd.ExecuteReader().Read();
			}
			catch(Exception ex)
			{
				String strError = ex.ToString();
				return false;
			}
			finally
			{
				conn.Close();
			}
		}

		public void LendBook(int userId, int bookId)
		{
			try
			{
				conn.Open();

				OleDbCommand cmd = conn.CreateCommand();	
				cmd.CommandText = "INSERT INTO book_lend(bookid, [userid],[dateoflend], [dateofreturn]) VALUES (?, ?, ?, ?)";
				cmd.CommandType = CommandType.Text; 
				cmd.Parameters.Add("?bookid", OleDbType.VarChar).Value = userId;
				cmd.Parameters.Add("?userid", OleDbType.VarChar).Value = bookId;
				cmd.Parameters.Add("?dateoflend", OleDbType.VarChar).Value = DateTime.Today;
				cmd.Parameters.Add("?dateofreturn", OleDbType.VarChar).Value = DateTime.Today.AddDays(30);
				cmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				String strError = ex.ToString();
			}
			finally
			{
				conn.Close();
			}

			try
			{
				conn.Open();

				OleDbCommand cmd = conn.CreateCommand();	
				cmd.CommandText = "UPDATE user_info set lend = 1 where userid = ?";
				cmd.CommandType = CommandType.Text; 
				cmd.Parameters.Add("?userid", OleDbType.VarChar).Value = userId;
				cmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				String strError = ex.ToString();
			}
			finally
			{
				conn.Close();
			}

			try
			{
				conn.Open();

				OleDbCommand cmd = conn.CreateCommand();	
				cmd.CommandText = "UPDATE book_info set available = 0 where bookid = ?";
				cmd.CommandType = CommandType.Text; 
				cmd.Parameters.Add("?bookid", OleDbType.VarChar).Value = bookId;
				cmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				String strError = ex.ToString();
			}
			finally
			{
				conn.Close();
			}
		}

		public void ReturnBook(int userId, int bookId)
		{
			try
			{
				conn.Open();

				OleDbCommand cmd = conn.CreateCommand();	
				cmd.CommandText = "DELETE FROM book_lend where bookid = ? and userid = ?";
				cmd.CommandType = CommandType.Text; 
				cmd.Parameters.Add("?bookid", OleDbType.VarChar).Value = userId;
				cmd.Parameters.Add("?userid", OleDbType.VarChar).Value = bookId;
				cmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				String strError = ex.ToString();
			}
			finally
			{
				conn.Close();
			}

			try
			{
				conn.Open();

				OleDbCommand cmd = conn.CreateCommand();	
				cmd.CommandText = "UPDATE user_info set lend = 0 where userid = ?";
				cmd.CommandType = CommandType.Text; 
				cmd.Parameters.Add("?userid", OleDbType.VarChar).Value = userId;
				cmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				String strError = ex.ToString();
			}
			finally
			{
				conn.Close();
			}

			try
			{
				conn.Open();

				OleDbCommand cmd = conn.CreateCommand();	
				cmd.CommandText = "UPDATE book_info set available = 1 where bookid = ?";
				cmd.CommandType = CommandType.Text; 
				cmd.Parameters.Add("?bookid", OleDbType.VarChar).Value = bookId;
				cmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				String strError = ex.ToString();
			}
			finally
			{
				conn.Close();
			}
		}
	}
}