dataaccess.cs

PDA計算汽車平均油耗程式,使用C#程式及SQL CE 數據庫應用

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;

namespace GasNote
{
    
    class DataAccess
    {
        string strConn = "Data Source =" + System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\GasNoteDB.sdf;Password =\'vs2OO5\'";
        internal DataSet GetCarList()
        {
            try
            {
                SqlCeConnection conn = new SqlCeConnection(strConn);
                string strcmd = "select * from CarList";
                SqlCeDataAdapter da = new SqlCeDataAdapter(strcmd, conn);
                DataSet dsCar = new DataSet();
                da.Fill(dsCar, "CarList");
                return dsCar;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal DataSet GetGasList(int CarNo)
        {
            try
            {
                SqlCeConnection conn = new SqlCeConnection(strConn);
                string strcmd = "select * from GasList where CarNo=@CarNo order by FillDate,ODO asc";
                SqlCeCommand cmd = new SqlCeCommand(strcmd, conn);
                cmd.Parameters.AddWithValue("@CarNo", CarNo);
                SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
                DataSet dsGas = new DataSet();
                da.Fill(dsGas, "GasList");
                return dsGas;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        internal int AddCarList(string CarName)
        {
            SqlCeConnection conn = new SqlCeConnection(strConn);
            try
            {
                string strcmd = "insert into CarList (CarName) values(@CarName)";
                SqlCeCommand cmd = new SqlCeCommand(strcmd, conn);
                cmd.Parameters.AddWithValue("@CarName", CarName);
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        internal int AddGasList(DateTime FillDate, string ODO, string Gas, string Payment, int CarNo)
        {
            SqlCeConnection conn = new SqlCeConnection(strConn);
            try
            {
                string strcmd = "insert into GasList (FillDate,ODO,Gas,Payment,CarNo) values(@FillDate,@ODO,@Gas,@Payment,@CarNo)";
                SqlCeCommand cmd = new SqlCeCommand(strcmd, conn);
                cmd.Parameters.AddWithValue("@FillDate", FillDate);
                cmd.Parameters.AddWithValue("@ODO", ODO);
                cmd.Parameters.AddWithValue("@Gas", Gas);
                cmd.Parameters.AddWithValue("@Payment", Payment);
                cmd.Parameters.AddWithValue("@CarNo", CarNo);
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        internal int EditGasList(DateTime FillDate, string ODO, string Gas, string Payment, int CarNo,int ID)
        {
            SqlCeConnection conn = new SqlCeConnection(strConn);
            try
            {
                string strcmd = "update GasList set FillDate=@FillDate,ODO=@ODO,Gas=@Gas,Payment=@Payment,CarNo=@CarNo where ID=@ID";
                SqlCeCommand cmd = new SqlCeCommand(strcmd, conn);
                cmd.Parameters.AddWithValue("@FillDate", FillDate);
                cmd.Parameters.AddWithValue("@ODO", ODO);
                cmd.Parameters.AddWithValue("@Gas", Gas);
                cmd.Parameters.AddWithValue("@Payment", Payment);
                cmd.Parameters.AddWithValue("@CarNo", CarNo);
                cmd.Parameters.AddWithValue("@ID", ID);
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        internal int DeleteGasList(int ID)
        {
            SqlCeConnection conn = new SqlCeConnection(strConn);
            try
            {
                string strcmd = "delete GasList where ID=@ID";
                SqlCeCommand cmd = new SqlCeCommand(strcmd, conn);
                cmd.Parameters.AddWithValue("@ID", ID);
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        internal int DeleteCarList(int CarNo)
        {
            SqlCeConnection conn = new SqlCeConnection(strConn);
            try
            {
                string strcmd = "delete GasList where CarNo=@CarNo";
                SqlCeCommand cmd = new SqlCeCommand(strcmd, conn);
                cmd.Parameters.AddWithValue("@CarNo", CarNo);
                conn.Open();
                cmd.ExecuteNonQuery();
                cmd.CommandText = "delete CarList where CarNo=@CarNo";
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}