📄 accessdataprovider.cs
字号:
cmd.Parameters.Add("@S_Name", OleDbType.VarWChar).Value = fam.S_Name + ""; //系统名称
cmd.Parameters.Add("@S_Version", OleDbType.VarWChar).Value = fam.S_Version + ""; //版本号
cmd.Parameters.Add("@S_SystemConfigData", OleDbType.VarBinary).Value = FrameSystemInfo.Serializable_sys_ConfigDataTable(fam.S_SystemConfigData); //系统配置信息
cmd.Parameters.Add("@S_Licensed", OleDbType.VarWChar).Value = fam.S_Licensed + ""; //序列号
cmd.Parameters.Add("@SystemID", OleDbType.Integer).Value = fam.SystemID;
}
else if (fam.DB_Option_Action_ == "Delete")
{
CommTxt = "Delete from sys_SystemInfo WHERE (SystemID = @SystemID)";
cmd.CommandText = CommTxt;
cmd.Parameters.Add("@SystemID", OleDbType.Integer).Value = fam.SystemID;
}
else
throw new ApplicationException("无法识别的操作命令!");
Conn.Open();
rInt = cmd.ExecuteNonQuery();
if (fam.DB_Option_Action_ == "Insert")
{
cmd.CommandText = "SELECT @@identity";
rInt = Convert.ToInt32(cmd.ExecuteScalar());
}
cmd.Dispose();
Conn.Dispose();
Conn.Close();
}
return rInt;
}
/// <summary>
/// 返回sys_SystemInfoTable实体类的ArrayList对象
/// </summary>
/// <param name="qp">查询类</param>
/// <param name="RecordCount">返回记录总数</param>
/// <returns>sys_SystemInfoTable实体类的ArrayList对象</returns>
public override ArrayList sys_SystemInfoList(QueryParam qp, out int RecordCount)
{
PopulateDelegate mypd = new PopulateDelegate(base.Populatesys_SystemInfo);
return this.GetObjectList(mypd, qp, out RecordCount);
}
#endregion
#region "sys_Online - AccessDataProvider"
/// <summary>
/// 新增/删除/修改 sys_Online
/// </summary>
/// <param name="fam">sys_OnlineTable实体类</param>
/// <returns>返回0操正常</returns>
public override int sys_OnlineInsertUpdate(sys_OnlineTable fam)
{
int rInt = 0;
using (OleDbConnection Conn = GetSqlConnection())
{
string CommTxt;
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = Conn;
if (fam.DB_Option_Action_ == "Insert")
{
CommTxt = "Insert into sys_Online(O_SessionID,O_UserName,O_Ip,O_LoginTime,O_LastTime,O_LastUrl)VALUES(@O_SessionID,@O_UserName,@O_Ip,@O_LoginTime,@O_LastTime,@O_LastUrl)";
cmd.CommandText = CommTxt;
cmd.Parameters.Add("@O_SessionID", OleDbType.VarWChar).Value = fam.O_SessionID + ""; //用户SessionID
cmd.Parameters.Add("@O_UserName", OleDbType.VarWChar).Value = fam.O_UserName + ""; //用户名
cmd.Parameters.Add("@O_Ip", OleDbType.VarWChar).Value = fam.O_Ip + ""; //用户IP地址
cmd.Parameters.Add("@O_LoginTime", OleDbType.Date).Value = fam.O_LoginTime; //登陆时间
cmd.Parameters.Add("@O_LastTime", OleDbType.Date).Value = fam.O_LastTime; //最后访问时间
cmd.Parameters.Add("@O_LastUrl", OleDbType.VarWChar).Value = fam.O_LastUrl + ""; //最后请求网站
}
else if (fam.DB_Option_Action_ == "Update")
{
CommTxt = "UPDATE sys_Online SET O_SessionID = @O_SessionID,O_UserName = @O_UserName,O_Ip = @O_Ip,O_LoginTime = @O_LoginTime,O_LastTime = @O_LastTime,O_LastUrl = @O_LastUrl WHERE (OnlineID = @OnlineID)";
cmd.CommandText = CommTxt;
cmd.Parameters.Add("@O_SessionID", OleDbType.VarWChar).Value = fam.O_SessionID + ""; //用户SessionID
cmd.Parameters.Add("@O_UserName", OleDbType.VarWChar).Value = fam.O_UserName + ""; //用户名
cmd.Parameters.Add("@O_Ip", OleDbType.VarWChar).Value = fam.O_Ip + ""; //用户IP地址
cmd.Parameters.Add("@O_LoginTime", OleDbType.Date).Value = fam.O_LoginTime; //登陆时间
cmd.Parameters.Add("@O_LastTime", OleDbType.Date).Value = fam.O_LastTime; //最后访问时间
cmd.Parameters.Add("@O_LastUrl", OleDbType.VarWChar).Value = fam.O_LastUrl + ""; //最后请求网站
cmd.Parameters.Add("@OnlineID", OleDbType.Integer).Value = fam.OnlineID;
}
else if (fam.DB_Option_Action_ == "Delete")
{
CommTxt = "Delete from sys_Online WHERE (OnlineID = @OnlineID)";
cmd.CommandText = CommTxt;
cmd.Parameters.Add("@OnlineID", OleDbType.Integer).Value = fam.OnlineID;
}
else
throw new ApplicationException("无法识别的操作命令!");
Conn.Open();
rInt = cmd.ExecuteNonQuery();
if (fam.DB_Option_Action_ == "Insert")
{
cmd.CommandText = "SELECT @@identity";
rInt = Convert.ToInt32(cmd.ExecuteScalar());
}
cmd.Dispose();
Conn.Dispose();
Conn.Close();
}
return rInt;
}
/// <summary>
/// 返回sys_OnlineTable实体类的ArrayList对象
/// </summary>
/// <param name="qp">查询类</param>
/// <param name="RecordCount">返回记录总数</param>
/// <returns>sys_OnlineTable实体类的ArrayList对象</returns>
public override ArrayList sys_OnlineList(QueryParam qp, out int RecordCount)
{
PopulateDelegate mypd = new PopulateDelegate(base.Populatesys_Online);
return this.GetObjectList(mypd, qp, out RecordCount);
}
#endregion
#region "常用函数"
/// <summary>
/// 获取表中字段值
/// </summary>
/// <param name="table_name">表名</param>
/// <param name="table_fileds">字段</param>
/// <param name="where_fileds">查询条件字段</param>
/// <param name="where_value">查询值</param>
/// <returns></returns>
public override string get_table_fileds(string table_name, string table_fileds, string where_fileds, string where_value)
{
where_value = Common.inSQL(where_value);
string rStr = "";
using (OleDbConnection Conn = GetSqlConnection())
{
string strSql = string.Format("select {0} from {1} where ucase({2})='{3}'", table_fileds, table_name, where_fileds, where_value);
OleDbCommand cmd = new OleDbCommand(strSql, Conn);
cmd.CommandType = CommandType.Text;
Conn.Open();
OleDbDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
rStr = dr[0].ToString();
}
dr.Close();
dr.Dispose();
cmd.Dispose();
Conn.Dispose();
Conn.Close();
}
return rStr;
}
#endregion
#region "更新表中字段值"
/// <summary>
/// 更新表中字段值
/// </summary>
/// <param name="Table">表名</param>
/// <param name="Table_FiledsValue">需要更新值(不用带Set)</param>
/// <param name="Wheres">更新条件(不用带Where)</param>
/// <returns></returns>
public override int Update_Table_Fileds(string Table, string Table_FiledsValue, string Wheres)
{
int rInt = 0;
using (OleDbConnection Conn = GetSqlConnection())
{
string strSql = string.Format("Update {0} Set {1} Where {2}", Table, Table_FiledsValue, Wheres);
OleDbCommand cmd = new OleDbCommand(strSql, Conn);
cmd.CommandType = CommandType.Text;
Conn.Open();
rInt = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Dispose();
Conn.Dispose();
Conn.Close();
}
return rInt;
}
#endregion
#region "公共查询数据函数Access版"
/// <summary>
/// 公共查询数据函数Access版
/// </summary>
/// <param name="pd">委托对象</param>
/// <param name="pp">查询字符串</param>
/// <param name="RecordCount">返回记录总数</param>
/// <returns>返回记录集ArrayList</returns>
private ArrayList GetObjectList(PopulateDelegate pd, QueryParam pp, out int RecordCount)
{
ArrayList lst = new ArrayList();
RecordCount = 0;
using (OleDbConnection Conn = GetSqlConnection())
{
StringBuilder sb = new StringBuilder();
OleDbCommand cmd = new OleDbCommand();
OleDbDataReader dr = null;
cmd.Connection = Conn;
int TotalRecordForPageIndex = pp.PageIndex * pp.PageSize;
string OrderBy;
string CutOrderBy;
if (pp.OrderType == 1)
{
OrderBy = " Order by " + pp.Orderfld + " desc ";
CutOrderBy = " Order by " + pp.Orderfld + " asc ";
}
else
{
OrderBy = " Order by " + pp.Orderfld + " asc ";
CutOrderBy = " Order by " + pp.Orderfld + " desc ";
}
Conn.Open();
// 取记录总数
cmd.CommandText = string.Format("SELECT Count(1) From {0} {1}", pp.TableName, pp.Where);
RecordCount = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Parameters.Clear();
int CurrentPageSize = pp.PageSize;
if ((RecordCount - 1) / pp.PageSize + 1 == pp.PageIndex)
{
CurrentPageSize = RecordCount % pp.PageSize;
if (CurrentPageSize == 0)
CurrentPageSize = pp.PageSize;
}
//取记录值
sb.AppendFormat("SELECT * FROM (SELECT TOP {0} * FROM (SELECT TOP {1} {2} FROM {3} {4} {5}) TB2 {6}) TB3 {5} ", CurrentPageSize, TotalRecordForPageIndex, pp.ReturnFields, pp.TableName, pp.Where, OrderBy, CutOrderBy);
cmd.CommandText = sb.ToString();
dr = cmd.ExecuteReader();
while (dr.Read())
{
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -