一个关于windows mobile的小程序源代码１，数据访问层

using System;

using System.Collections.Generic;

using System.Text;

using TSMCISMobile.DataFactory;

using System.Data;

using System.IO;

using System.Data.SqlServerCe;

using System.Collections;

namespace TSMCISMobile.DataAccess

{

///<summary>

/// 模块编号：

/// 作用：

/// 创建者：豹子

/// 创建日期：2008-4-14

/// 最后修改者:

/// 最后修改日

/// 最后修改描述：

///</summary>

class DA_LocalTable

{

#region 构造函数

public DA_LocalTable()

{

}

#endregion

#region 私有变量

private string strSQL = string.Empty;

private string Sql = string.Empty;

//private StringBuilder sbSQL = new StringBuilder();

#endregion

#region 公有变量

#endregion

#region 公开方法

/// <summary>

/// 创建收支明细表

/// </summary>

public void CreatDictTable()

{

try

{

//创建P$DEPT_DICT表

strSQL = @"CREATE TABLE P_MoneyDetailed(s_ID NVARCHAR(10),S_DataTime NVARCHAR(12),S_GETOUTFLAG NVARCHAR(8),S_GETOUTTYPE NVARCHAR(20),I_MONEYSUM NUMERIC,S_ACCOUNTS NVARCHAR(40),S_NOTE NVARCHAR(100))";

StaticAccess.CreatDictTable("P_MoneyDetailed", strSQL);

}

catch (Exception ex)

{

throw ex;

}

}

/// <summary>

/// 创建礼尚往来表

/// </summary>

public void CreatInteTable()

{

try

{

//创建P$DEPT_DICT表

string SQL = @"CREATE TABLE P_Intercourse(s_ID NVARCHAR(10),S_DataTime NVARCHAR(14),S_GETOUTFLAG NVARCHAR(8),S_USERNAME NVARCHAR(12),I_REASON NVARCHAR(20),GOODSNAME NVARCHAR(20), I_MONEY NUMERIC,S_NOTE NVARCHAR(100))";

StaticAccess.CreatDictTable("P_Intercourse", SQL);

}

catch (Exception ex)

{

throw ex;

}

}

/// <summary>

/// 创建用户表

/// </summary>

public void CreatUserTable()

{

try

{

//创建P$DEPT_DICT表

string SQL = @"CREATE TABLE P_UserTable(s_ID NVARCHAR(10),S_UserName NVARCHAR(14),S_PassWord NVARCHAR(8))";

StaticAccess.CreatDictTable("P_UserTable", SQL);

}

catch (Exception ex)

{

throw (ex);

}

}

/// <summary>

/// 添加用户记录

/// </summary>

/// <param name="P_id">编号</param>

/// <param name="p_UserName">用户名</param>

/// <param name="p_PassWord">密码</param>

public void AddUser(string P_id, string p_UserName, string p_PassWord)

{

try

{

string SQL = "INSERT INTO P_UserTable(s_ID,S_UserName,S_PassWord) VALUES ('" + P_id + "','" + p_UserName + "','" + p_PassWord + "')";

StaticAccess.ExecuteNonQuery(SQL);

}

catch (Exception ex)

{

throw (ex);

}

}

/// <summary>

/// 获得用户记录

/// </summary>

/// <returns></returns>

public DataTable SelectUser()

{

try

{

string sql = "Select * from P_UserTable";

DataTable dt = StaticAccess.ReadTable(sql);

return dt;

}

catch (Exception ex)

{

throw (ex);

}

}

/// <summary>

/// 添加收支明细记录

/// </summary>

public bool AddMoneyDetil(string p_Id,

string p_DataTime,

string p_GetOutFlag,

string p_GetOutType,

string p_MoneySum,

string p_Accounte,

string p_Note)

{

try

{

Sql = @"INSERT INTO P_MoneyDetailed(s_ID,S_DataTime,s_GetOutFlag,s_GetOutType,i_MoneySum,s_Accounts,s_Note) VALUES ('" + p_Id + "','"

+ p_DataTime + "','"

+ p_GetOutFlag + "','"

+ p_GetOutType + "','"

+ p_MoneySum + "','"

+ p_Accounte + "','"

+ p_Note + "' )";

StaticAccess.ReadTable(Sql);

return true;

}

catch (Exception ex)

{

throw(ex);

//return false;

}

}

/// <summary>

/// 修改收支明细表

/// </summary>

/// <param name="p_id"></param>

/// <param name="p_DataTime"></param>

/// <param name="p_GetOutFlag"></param>

/// <param name="p_GetType"></param>

/// <param name="p_MoneySum"></param>

/// <param name="p_Accounts"></param>

/// <param name="p_Noet"></param>

/// <returns></returns>

public bool UpDataList(string p_id,

string p_DataTime,

string p_GetOutFlag,

string p_GetType,

string p_MoneySum,

string p_Accounts,

string p_Noet)

{

try

{

string sql = @"UPDATE P_MoneyDetailed Set s_ID = '" + p_id +

"',S_DataTime = '" + p_DataTime +

"',s_GetOutFlag = '" + p_GetOutFlag +

"',s_GetOutType = '" + p_GetType +

"',i_MoneySum = '" + p_MoneySum +

"',s_Accounts = '" + p_Accounts +

"',s_Note = '" + p_Noet +

"' where s_ID = '" + p_id + "'";

StaticAccess.ReadTable(sql);

return true;

}

catch (Exception ex)

{

throw(ex);

//return false;

}

}

/// <summary>

/// 删除收支明细

/// </summary>

/// <param name="p_Id"></param>

/// <returns></returns>

public bool DeleteMoneyLost(string p_Id)

{

try

{

string sql = @"DELETE FROM P_MoneyDetailed WHERE s_ID = '" + p_Id + "'";

StaticAccess.ReadTable(sql);

return true;

}

catch (Exception ex)

{

throw(ex);

}

}

/// <summary>

/// 得到收支明细表数据

/// </summary>

/// <returns></returns>

public DataTable GetMoneyList()

{

try

{

strSQL = @"SELECT * FROM P_MoneyDetailed ORDER BY S_ID";

DataTable dtbl = new DataTable();

dtbl = StaticAccess.ReadTable(strSQL);

return dtbl;

}

catch (Exception ex)

{

throw (ex);

}

}

/// <summary>

/// 得到礼尚往来表数据

/// </summary>

/// <returns></returns>

public DataTable GetMoneyInteList()

{

try

{

strSQL = @"SELECT * FROM P_Intercourse ORDER BY S_ID";

DataTable dtbl = new DataTable();

dtbl = StaticAccess.ReadTable(strSQL);

return dtbl;

}

catch (Exception ex)

{

throw (ex);

}

}

/// <summary>

/// 添加礼尚往来记录

/// </summary>

public bool AddMoneyInte(string p_Id,

string p_DataTime,

string p_GetOutFlag,

string p_UserName,

string p_Reason,

string p_GoodsName,

string p_MONEY,

string p_Note)

{

try

{

Sql = @"INSERT INTO P_Intercourse(s_ID,S_DataTime,s_GetOutFlag,S_USERNAME,I_REASON,GOODSNAME,I_MONEY，s_Note) VALUES ('" + p_Id + "','"

+ p_DataTime + "','"

+ p_GetOutFlag + "','"

+ p_UserName + "','"

+ p_Reason + "','"

+ p_GoodsName + "','"

+ p_MONEY + "','"

+ p_Note + "' )";

StaticAccess.ReadTable(Sql);

return true;

}

catch (Exception ex)

{

throw (ex);

//return false;

}

}

/// <summary>

/// 统计本月收支情况

/// </summary>

/// <returns></returns>

public ArrayList GetStatData()

{

try

{

//获取服务器时间

CommonFunc Fun = new CommonFunc();

ArrayList Ar = new ArrayList();

//DateTime DateTimeNow = Fun.GetSysDateTime();

DateTime DateTimeNow = DateTime.Now;

string DataTimeStr = DateTimeNow.ToString("yyyy-MM-dd");

string Year = DateTimeNow.Year.ToString();

string Month = DateTimeNow.Month.ToString();

int month = Convert.ToInt32(Month);

if (month < 10)

{

Month = "0" + Month;

}

string TimeStar = Year + "-" + Month + "-" + "01";

string TimeEnd = Year + "-" + Month + "-" + "31";

//查询当天的收入或支出之和

string sql1 = @"SELECT SUM(i_MoneySum) SumMoney FROM P_MoneyDetailed WHERE S_DataTime = '" + DataTimeStr + "' AND s_GetOutFlag = '收入'";

//查询当天的收入或支出之和

string sql2 = @"SELECT SUM(i_MoneySum) SumMoney FROM P_MoneyDetailed WHERE S_DataTime = '" + DataTimeStr + "' AND s_GetOutFlag = '支出'";

//查询当月的收入或支出之和

string sql3 = @"SELECT SUM(i_MoneySum) SumMoney FROM P_MoneyDetailed WHERE S_DataTime >= '" + TimeStar + "' AND S_DataTime <='" + TimeEnd + "' AND s_GetOutFlag = '收入'";

//查询当月的收入或支出之和

string sql4 = @"SELECT SUM(i_MoneySum) SumMoney FROM P_MoneyDetailed WHERE S_DataTime >= '" + TimeStar + "' AND S_DataTime <='" + TimeEnd + "' AND s_GetOutFlag = '支出'";

DataTable dt1 = new DataTable();

DataTable dt2 = new DataTable();

DataTable dt3 = new DataTable();

DataTable dt4 = new DataTable();

dt1 = StaticAccess.ReadTable(sql1);

dt2 = StaticAccess.ReadTable(sql2);

dt3 = StaticAccess.ReadTable(sql3);

dt4 = StaticAccess.ReadTable(sql4);

string Sum1 = dt1.Rows[0][0].ToString();

string Sum2 = dt2.Rows[0][0].ToString();

string Sum3 = dt3.Rows[0][0].ToString();

string Sum4 = dt4.Rows[0][0].ToString();

Ar.Add(Sum1);

Ar.Add(Sum2);

Ar.Add(Sum3);

Ar.Add(Sum4);

return Ar;

}

catch (Exception ex)

{

throw(ex);

}

}

/// <summary>

/// 得到按条件收支明细表数据

/// </summary>

/// <returns></returns>

public DataTable GetSelectMoney(string p_DataStar,

string p_DataEnd,

string p_GetOut,

string p_Typet,

string p_Accounte)

{

try

{

strSQL = @"SELECT * FROM P_MoneyDetailed WHERE 1 = 1 ";

if (p_DataStar != string.Empty)

{

strSQL += "AND S_DataTime >= '" + p_DataStar + "' AND S_DataTime <='" + p_DataEnd + "'";

}

if (p_GetOut != string.Empty)

{

strSQL += " AND s_GetOutFlag = '" + p_GetOut + "'";

}

if (p_Typet != string.Empty)

{

strSQL += " AND s_GetOutType = '" + p_Typet + "'";

}

if (p_Accounte != string.Empty)

{

strSQL += " AND s_Accounts = '" + p_Accounte + "'";

}

DataTable dtbl = new DataTable();

dtbl = StaticAccess.ReadTable(strSQL);

return dtbl;

}

catch (Exception ex)

{

throw (ex);

}

}

/// <summary>

/// 与Oracle数据库同步，更新本地字典表

/// </summary>

public void SyncWithOracle(DataSet p_ds)

{

//插入P$DEPT_DICT表

int i;

ClearTable("P$DEPT_DICT");

try

{

//反序列化二进制流

//DataSet ds = new DataSet();

//byte[] bDictTable = ClientGlobal.GetWebService().AddDictTable();

//MemoryStream ms = new MemoryStream(bDictTable);

//IFormatter bf = new BinaryFormatter();

//object obj = bf.Deserialize(ms);

//ds = (DataSet)obj;

//ms.Close();

DataRow myRow = null;

for (i = 0; i < p_ds.Tables[0].Rows.Count; i++)

{

strSQL = @"INSERT INTO P$DEPT_DICT

(

S_DEPTCODE,

C_DEPTTYPECODE,

S_DEPTNAME,

S_BRANCHNUM,

S_DEPTABBR,

S_FATHERDEPTCODE,

C_ABLEFLAG,

I_SORTID

)

VALUES (

?,

?,

?,

?,

?,

?,

?,

?

)";

myRow = p_ds.Tables[0].Rows[i];

SqlCeCommand mySqlCeCommand = new SqlCeCommand();

mySqlCeCommand.CommandText = strSQL;

//mySqlCeCommand.Parameters.Add("S_DEPTCODE",SqlDbType.VarChar);

if (myRow["S_DEPTCODE"] == null || myRow["S_DEPTCODE"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("S_DEPTCODE", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("S_DEPTCODE", myRow["S_DEPTCODE"].ToString());

}

if (myRow["C_DEPTTYPECODE"] == null || myRow["C_DEPTTYPECODE"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("C_DEPTTYPECODE", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("C_DEPTTYPECODE", myRow["C_DEPTTYPECODE"].ToString());

}

if (myRow["S_DEPTNAME"] == null || myRow["S_DEPTNAME"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("S_DEPTNAME", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("S_DEPTNAME", myRow["S_DEPTNAME"].ToString());

}

if (myRow["S_BRANCHNUM"] == null || myRow["S_BRANCHNUM"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("S_BRANCHNUM", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("S_BRANCHNUM", int.Parse(myRow["S_BRANCHNUM"].ToString()));

}

if (myRow["S_DEPTABBR"] == null || myRow["S_DEPTABBR"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("S_DEPTABBR", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("S_DEPTABBR", myRow["S_DEPTABBR"].ToString());

}

if (myRow["S_FATHERDEPTCODE"] == null || myRow["S_FATHERDEPTCODE"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("S_FATHERDEPTCODE", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("S_FATHERDEPTCODE", myRow["S_FATHERDEPTCODE"].ToString());

}

if (myRow["C_ABLEFLAG"] == null || myRow["C_ABLEFLAG"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("C_ABLEFLAG", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("C_ABLEFLAG", myRow["C_ABLEFLAG"].ToString());

}

if (myRow["I_SORTID"] == null || myRow["I_SORTID"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("I_SORTID", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("I_SORTID", int.Parse(myRow["I_SORTID"].ToString()));

}

StaticAccess.ExecuteNonQuery(mySqlCeCommand);

}

//插入P$DEPT_BRANCH_DICT表

ClearTable("P$DEPT_BRANCH_DICT");

for (i = 0; i < p_ds.Tables[1].Rows.Count; i++)

{

string strSQL = @"INSERT INTO P$DEPT_BRANCH_DICT

(

S_DEPTCODE,

S_BRANCHCODE,

S_BRANCHNAME,

S_BRANCHABBR,

S_BRANCHDESC,

C_ABLEFLAG,

I_SORTID

)

VALUES (

?,

?,

?,

?,

?,

?,

?

)";

myRow = p_ds.Tables[1].Rows[i];

SqlCeCommand mySqlCeCommand = new SqlCeCommand();

mySqlCeCommand.CommandText = strSQL;

if (myRow["S_DEPTCODE"] == null || myRow["S_DEPTCODE"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("S_DEPTCODE", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("S_DEPTCODE", myRow["S_DEPTCODE"].ToString());

}

if (myRow["S_BRANCHCODE"] == null || myRow["S_BRANCHCODE"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("S_BRANCHCODE", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("S_BRANCHCODE", myRow["S_BRANCHCODE"].ToString());

}

if (myRow["S_BRANCHNAME"] == null || myRow["S_BRANCHNAME"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("S_BRANCHNAME", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("S_BRANCHNAME", myRow["S_BRANCHNAME"].ToString());

}

if (myRow["S_BRANCHABBR"] == null || myRow["S_BRANCHABBR"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("S_BRANCHABBR", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("S_BRANCHABBR", myRow["S_BRANCHABBR"].ToString());

}

if (myRow["S_BRANCHDESC"] == null || myRow["S_BRANCHDESC"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("S_BRANCHDESC", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("S_BRANCHDESC", myRow["S_BRANCHDESC"].ToString());

}

if (myRow["C_ABLEFLAG"] == null || myRow["C_ABLEFLAG"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("C_ABLEFLAG", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("C_ABLEFLAG", myRow["C_ABLEFLAG"].ToString());

}

if (myRow["I_SORTID"] == null || myRow["I_SORTID"].ToString() == "")

{

mySqlCeCommand.Parameters.Add("I_SORTID", DBNull.Value);

}

else

{

mySqlCeCommand.Parameters.Add("I_SORTID", int.Parse(myRow["I_SORTID"].ToString()));

}

StaticAccess.ExecuteNonQuery(mySqlCeCommand);

}

}

catch (System.Exception E)

{

throw (E);

}

}

/// <summary>

/// 删除表

/// </summary>

private void ClearTable(string p_TableName)

{

strSQL = "DELETE FROM " + p_TableName;

StaticAccess.ExecuteNonQuery(strSQL);

}

/// <summary>

/// 添加班

/// </summary>

/// <returns></returns>

public DataTable AddShift()

{

try

{

strSQL = @"SELECT * FROM P$DEPT_DICT ORDER BY S_DEPTCODE";

DataTable dtbl = new DataTable();

dtbl = StaticAccess.ReadTable(strSQL);

return dtbl;

}

catch (Exception ex)

{

throw (ex);

}

}

/// <summary>

/// 添加组

/// </summary>

/// <returns></returns>

public DataTable AddGroup(string p_ShiftNo)

{

try

{

if (p_ShiftNo == string.Empty)

{

strSQL = @"SELECT * FROM P$DEPT_BRANCH_DICT ORDER BY S_DEPTCODE";

}

else

{

strSQL = @"SELECT * FROM P$DEPT_BRANCH_DICT WHERE S_DEPTCODE='" + p_ShiftNo + "' ORDER BY S_DEPTCODE";

}

DataTable dtbl = new DataTable();

dtbl = StaticAccess.ReadTable(strSQL);

return dtbl;

}

catch (Exception ex)

{

throw (ex);

}

}

#endregion

}

}