public bool Modify(EmployerData dataEmployer)
{
int iRel = -1;
bool bRel = false;
CommonDB objDB = new CommonDB();
try
{
objDB.OpenConnection();
string strSql = "update agreement set EmployerName=@EmployerName,EmployerSite=@EmployerSite where EmployerId = @EmployerId";
objDB.Command.CommandType = System.Data.CommandType.Text;
objDB.Command.CommandText = strSql;
objDB.Command.Parameters.AddWithValue("@EmployerName", dataEmployer.EmployerName);
objDB.Command.Parameters.AddWithValue("@EmployerSite", dataEmployer.EmployerSite);
objDB.Command.Parameters.AddWithValue("@EmployerId", dataEmployer.EmployerId);
iRel = objDB.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
//iRel = -1;
throw new Exception(ex.ToString());
}
objDB.CloseConnection();
objDB.Dispose();
objDB = null;
bRel = (iRel.Equals(1) ? true : false); return(bRel);
}
/// <summary>
/// 更新数据表到数据源中
/// </summary>
/// <param name="dt"></param>
/// <param name="SQL"></param>
/// <returns></returns>
private static int UpdateDataTable(DataTable dt, string SQL)
{
CommonDB DB = MyDB.GetDBHelper();
string ParaChar = GetDBParaChar(DB);
SQL = SQL.Replace("@@", ParaChar);
int count = 0;
DB.BeginTransaction();
try
{
foreach (DataRow dr in dt.Rows)
{
IDataParameter[] paras = new IDataParameter[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
paras[i] = DB.GetParameter(ParaChar + dt.Columns[i].ColumnName, dr[i]);
}
count += DB.ExecuteNonQuery(SQL, CommandType.Text, paras);
if (DB.ErrorMessage != "")
{
throw new Exception(DB.ErrorMessage);
}
}
DB.Commit();
}
catch (Exception ex)
{
DB.Rollback();
throw ex;
}
return(count);
}
public bool Delete(string UserName)
{
int iRel = -1;
bool bRel = false;
CommonDB objDB = new CommonDB();
try
{
objDB.OpenConnection();
string strSql = "delete from [User] where UserName="******"";
objDB.Command.CommandType = System.Data.CommandType.Text;
objDB.Command.CommandText = strSql;
iRel = objDB.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
//iRel = -1;
throw new Exception(ex.ToString());
}
objDB.CloseConnection();
objDB.Dispose();
objDB = null;
bRel = (iRel.Equals(1) ? true : false);
return(bRel);
}
public bool Modify(UserData dataUser)
{
int iRel = -1;
bool bRel = false;
CommonDB objDB = new CommonDB();
try
{
objDB.OpenConnection();
string strSql = "update [User] set PassWord=@PassWord,UserId=@UserId,Remark=@Remark where UserName = @UserName";
objDB.Command.CommandType = System.Data.CommandType.Text;
objDB.Command.CommandText = strSql;
objDB.Command.Parameters.AddWithValue("@PassWord", dataUser.PassWord);
objDB.Command.Parameters.AddWithValue("@UserId", dataUser.UserId);
objDB.Command.Parameters.AddWithValue("@Remark", dataUser.Remark);
objDB.Command.Parameters.AddWithValue("@UserName", dataUser.UserName);
iRel = objDB.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
//iRel = -1;
throw new Exception(ex.ToString());
}
objDB.CloseConnection();
objDB.Dispose();
objDB = null;
bRel = (iRel.Equals(1) ? true : false); return(bRel);
}
private SQLiteParameter[] GetSqlParameters()
{
List <SQLiteParameter> SqlParmColl = new List <SQLiteParameter>();
try
{
SqlParmColl.Add(CommonDB.AddSqlParm("@RoundRectID", RoundRectID, DbType.Int64));
SqlParmColl.Add(CommonDB.AddSqlParm("@DisplayID", DisplayID, DbType.Int64));
SqlParmColl.Add(CommonDB.AddSqlParm("@oIndex", oIndex, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@bBrush", bBrush, DbType.Boolean));
SqlParmColl.Add(CommonDB.AddSqlParm("@bPen", bPen, DbType.Boolean));
SqlParmColl.Add(CommonDB.AddSqlParm("@LogpenLopnColor", LogpenLopnColor, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@LogpenLopnStyle", LogpenLopnStyle, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@LogpenLopnWidthX", LogpenLopnWidthX, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@LogpenLopnWidthY", LogpenLopnWidthY, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@LogbrushLbColor", LogbrushLbColor, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@LogbrushLbHatch", LogbrushLbHatch, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@LogbrushLbStyle", LogbrushLbStyle, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Left", Left, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Top", Top, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Right", Right, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Bottom", Bottom, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@RoundnessX", RoundnessX, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@RoundnessY", RoundnessY, DbType.Int32));
return(SqlParmColl.ToArray());
}
catch (SQLiteException Exc)
{
throw Exc;
}
}
/// <summary>
/// 根据程序集名称和数据访问对象类型创建一个新的数据访问对象实例。
/// </summary>
/// <param name="HelperAssembly">程序集名称</param>
/// <param name="HelperType">数据访问对象类型</param>
/// <param name="ConnectionString">连接字符串</param>
/// <returns>数据访问对象</returns>
public static AdoHelper GetDBHelper(string HelperAssembly, string HelperType, string ConnectionString)
{
AdoHelper helper = null;// CommonDB.CreateInstance(HelperAssembly, HelperType);
if (HelperAssembly == "PWMIS.Core")
{
switch (HelperType)
{
case "PWMIS.DataProvider.Data.SqlServer": helper = new SqlServer(); break;
case "PWMIS.DataProvider.Data.Oracle": helper = new Oracle(); break;
case "PWMIS.DataProvider.Data.OleDb": helper = new OleDb(); break;
case "PWMIS.DataProvider.Data.Odbc": helper = new Odbc(); break;
case "PWMIS.DataProvider.Data.Access": helper = new Access(); break;
//case "PWMIS.DataProvider.Data.SqlServerCe": helper = new SqlServerCe(); break;
default: helper = new SqlServer(); break;
}
}
else
{
helper = CommonDB.CreateInstance(HelperAssembly, HelperType);
}
helper.ConnectionString = ConnectionString;
return(helper);
}
/// <summary>
/// 根据数据集中在指定的表中，根据表中的指定列的值在数据源中删除数据
/// </summary>
/// <param name="ds">数据集</param>
/// <param name="tableName">表名称</param>
/// <param name="columnName">列名</param>
/// <returns>查询所影响的行数</returns>
public static int DeleteDataSet(DataSet ds, string tableName, string columnName)
{
DataTable dt = ds.Tables[tableName];
CommonDB DB = MyDB.GetDBHelper();
string ParaChar = GetDBParaChar(DB);
int count = 0;
string sqlDelete = "DELETE FROM " + tableName + " WHERE " + columnName + "=" + ParaChar + columnName;
DB.BeginTransaction();
try
{
foreach (DataRow dr in dt.Rows)
{
IDataParameter[] paras = { DB.GetParameter(ParaChar + columnName, dr[columnName]) };
count += DB.ExecuteNonQuery(sqlDelete, CommandType.Text, paras);
if (DB.ErrorMessage != "")
{
throw new Exception(DB.ErrorMessage);
}
if (count >= dt.Rows.Count)
{
break;
}
}
DB.Commit();
}
catch (Exception ex)
{
DB.Rollback();
throw ex;
}
return(count);
}
private SQLiteParameter[] GetSqlParameters()
{
List <SQLiteParameter> SqlParmColl = new List <SQLiteParameter>();
try
{
SqlParmColl.Add(CommonDB.AddSqlParm("@VarNameID", VarNameID, DbType.Int64));
SqlParmColl.Add(CommonDB.AddSqlParm("@DigitalID", DigitalID, DbType.Int64));
SqlParmColl.Add(CommonDB.AddSqlParm("@PointIndex", PointIndex, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@SampleTime", SampleTime, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@PointGroup", PointGroup, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@FOR", FOR, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@NR_EBL", NR_EBL, DbType.Boolean));
SqlParmColl.Add(CommonDB.AddSqlParm("@NR_TXT", NR_TXT, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@NR_AUD", NR_AUD, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@NR_TYP", NR_TYP, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@NR_PRN", NR_PRN, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@AB_EBL", AB_EBL, DbType.Boolean));
SqlParmColl.Add(CommonDB.AddSqlParm("@AB_TXT", AB_TXT, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@AB_AUD", AB_AUD, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@AB_TYP", AB_TYP, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@AB_PRN", AB_PRN, DbType.Int32));
return(SqlParmColl.ToArray());
}
catch (SQLiteException Exc)
{
throw Exc;
}
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
//Common.Utilities.UserValidate(context);
try
{
string eqid = context.Request.Form["eqID"];
if (string.IsNullOrEmpty(eqid))
{
context.Response.Write(null);
return;
}
string cmd = "select * from T_online where eqid=" + eqid;
//获取数据
DataTable dt = CommonDB.invokeProc_DataTable(cmd);
string jsonData = JsonHelper.ToJson(dt);
context.Response.Write(jsonData);
}
catch
{
context.Response.Write(null);
}
finally
{
context.Response.End();
}
}
public bool Insert(StudentData dataStudent)
{
int iRel = -1;
bool bRel = false;
CommonDB objDB = new CommonDB();
try
{
objDB.OpenConnection();
string strSql = " insert into Student (SId,Sname,Ssex,Sey,BCname,BCid,Class,Institute,BGid) values (@SId,@Sname,@Ssex,@Sey,@BCname,@BCid,@Class,@Institute,@BGid) ";
objDB.Command.CommandType = System.Data.CommandType.Text;
objDB.Command.CommandText = strSql;
objDB.Command.Parameters.AddWithValue("@SId", dataStudent.SId);
objDB.Command.Parameters.AddWithValue("@Sname", dataStudent.Sname);
objDB.Command.Parameters.AddWithValue("@Ssex", dataStudent.Ssex);
objDB.Command.Parameters.AddWithValue("@Sey", dataStudent.Sey);
objDB.Command.Parameters.AddWithValue("@BCname", dataStudent.BCname);
objDB.Command.Parameters.AddWithValue("@BCid", dataStudent.BCid);
objDB.Command.Parameters.AddWithValue("@Class", dataStudent.Class);
objDB.Command.Parameters.AddWithValue("@Institute", dataStudent.Institute);
objDB.Command.Parameters.AddWithValue("@BGid", dataStudent.SGid);
iRel = objDB.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
//iRel = -1;
throw new Exception(ex.ToString());
}
objDB.CloseConnection();
objDB.Dispose();
objDB = null;
bRel = (iRel.Equals(1) ? true : false);
return(bRel);
}
public static void WriteErrorLog(string message)
{
FileTarget target = new FileTarget();
target.Layout = "${longdate} ${logger} ${message}";
target.FileName = "${basedir}/Trace/ChemInformLog.txt";
target.KeepFileOpen = false;
target.Encoding = "iso-8859-2";
AsyncTargetWrapper wrapper = new AsyncTargetWrapper();
wrapper.WrappedTarget = target;
wrapper.QueueLimit = 5000;
wrapper.OverflowAction = AsyncTargetWrapperOverflowAction.Discard;
NLog.Config.SimpleConfigurator.ConfigureForTargetLogging(wrapper, NLog.LogLevel.Trace);
Logger logger = LogManager.GetLogger(GlobalVariables.ProjectName);
logger.Trace(message);
try
{
ApplicationError appError = new ApplicationError();
appError.UserName = GlobalVariables.UserName;
appError.RoleName = GlobalVariables.RoleName;
appError.AppError = message;
CommonDB.SaveApplicationErrors(appError);
}
catch
{ }
}
/// <summary>
/// 从数据库删除实体对象对应的记录
/// </summary>
/// <param name="entity"></param>
/// <param name="DB">数据访问对象实例</param>
/// <returns></returns>
private static int DeleteInnerByDB(T entity, CommonDB DB)
{
if (entity.PrimaryKeys.Count == 0)
{
throw new Exception("EntityQuery Error:当前实体类未指定主键字段");
}
int fieldCount = entity.PropertyNames.Length;
if (fieldCount == 0)
{
throw new Exception("EntityQuery Error:实体类属性字段数量为0");
}
//CommonDB DB = MyDB.GetDBHelper();
IDataParameter[] paras = new IDataParameter[fieldCount];
string sql = "DELETE FROM " + entity.TableName + " WHERE ";
string values = "";
string condition = "";
int index = 0;
foreach (string key in entity.PrimaryKeys)
{
string paraName = "@P" + index.ToString();
condition += " AND " + key + "=" + paraName;
paras[index] = DB.GetParameter(paraName, entity.PropertyList(key));
index++;
}
sql = sql + values.TrimStart(',') + " " + condition.Substring(" AND ".Length);
int count = DB.ExecuteNonQuery(sql, CommandType.Text, paras);
return(count);
}
private SQLiteParameter[] GetSqlParameters()
{
List <SQLiteParameter> SqlParmColl = new List <SQLiteParameter>();
try
{
SqlParmColl.Add(CommonDB.AddSqlParm("@VarNameID", VarNameID, DbType.Int64));
SqlParmColl.Add(CommonDB.AddSqlParm("@AnalogID", AnalogID, DbType.Int64));
SqlParmColl.Add(CommonDB.AddSqlParm("@PointIndex", PointIndex, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@SampleTime", SampleTime, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@PointGroup", PointGroup, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@UNI", UNI, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@FOR", FOR, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@IRL_LIM", IRL_LIM, DbType.Single));
SqlParmColl.Add(CommonDB.AddSqlParm("@IRL_EBL", IRL_EBL, DbType.Boolean));
SqlParmColl.Add(CommonDB.AddSqlParm("@IRL_TXT", IRL_TXT, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@IRL_AUD", IRL_AUD, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@IRL_TYP", IRL_TYP, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@IRL_PRN", IRL_PRN, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@IRH_LIM", IRH_LIM, DbType.Single));
SqlParmColl.Add(CommonDB.AddSqlParm("@IRH_EBL", IRH_EBL, DbType.Boolean));
SqlParmColl.Add(CommonDB.AddSqlParm("@IRH_TXT", IRH_TXT, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@IRH_AUD", IRH_AUD, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@IRH_TYP", IRH_TYP, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@IRH_PRN", IRH_PRN, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@LL_LIM", LL_LIM, DbType.Single));
SqlParmColl.Add(CommonDB.AddSqlParm("@LL_EBL", LL_EBL, DbType.Boolean));
SqlParmColl.Add(CommonDB.AddSqlParm("@LL_TXT", LL_TXT, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@LL_AUD", LL_AUD, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@LL_TYP", LL_TYP, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@LL_PRN", LL_PRN, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@HH_LIM", HH_LIM, DbType.Single));
SqlParmColl.Add(CommonDB.AddSqlParm("@HH_EBL", HH_EBL, DbType.Boolean));
SqlParmColl.Add(CommonDB.AddSqlParm("@HH_TXT", HH_TXT, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@HH_AUD", HH_AUD, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@HH_TYP", HH_TYP, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@HH_PRN", HH_PRN, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@L_LIM", L_LIM, DbType.Single));
SqlParmColl.Add(CommonDB.AddSqlParm("@L_EBL", L_EBL, DbType.Boolean));
SqlParmColl.Add(CommonDB.AddSqlParm("@L_TXT", L_TXT, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@L_AUD", L_AUD, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@L_TYP", L_TYP, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@L_PRN", L_PRN, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@H_LIM", H_LIM, DbType.Single));
SqlParmColl.Add(CommonDB.AddSqlParm("@H_EBL", H_EBL, DbType.Boolean));
SqlParmColl.Add(CommonDB.AddSqlParm("@H_TXT", H_TXT, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@H_AUD", H_AUD, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@H_TYP", H_TYP, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@H_PRN", H_PRN, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Hystersis", Hystersis, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@RTT", RTT, DbType.Boolean));
SqlParmColl.Add(CommonDB.AddSqlParm("@Interval", Interval, DbType.Int32));
return(SqlParmColl.ToArray());
}
catch (SQLiteException Exc)
{
throw Exc;
}
}
/// <summary>
/// 收集窗体中的智能控件，组合成能够直接用于数据库插入和更新 查询的 SQL语句
/// 一个窗体中可以同时处理多个表的数据操作
/// 如果控件的数据属性设置为只读，那么该控件的值不会更新到数据库；如果该控件的数据属性设置为主键，那么更新语句将附带该条件
/// 邓太华 2008.1.15
/// </summary>
/// <returns>
/// ArrayList 中的成员为 IBCommand 对象，包含具体的CRUD SQL
///</returns>
public static List <IBCommand> GetIBFormData(ControlCollection Controls, CommonDB DB)
{
List <IDataControl> IBControls = new List <IDataControl>();
findIBControls(IBControls, Controls);
return(MyDataForm.GetIBFormDataInner(IBControls, DB));
}
public bool OnExecuting(CommonDB db, ref string sql, System.Data.CommandType commandType, System.Data.IDataParameter[] parameters)
{
sql = sql.Replace("[", "").Replace("]", "").Replace("@", ":").ToUpper();
//设置SQLSERVER兼容性为假，避免命令对象真正执行的时候再进行Oracle的查询语句的预处理。
db.SqlServerCompatible = false;
//返回真，以便最终执行查询，否则将终止查询
return(true);
}
/// <summary>
/// 保存自实体类申明以来，所有做过的修改到数据库。
/// </summary>
/// <param name="db">数据库访问对象实例</param>
/// <returns>操作受影响的行数</returns>
public int SaveAllChanges(CommonDB db)
{
//List<string> list = new List<string>();
//foreach (string key in this.currEntity.PropertyList.Keys)
// if (this.currEntity.PropertyChangedList[key])//只修改属性更改过的字段
// list.Add(key);
return(InnerSaveAllChanges(db, this.currEntity.PropertyChangedList));
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity">Entidade a ser retornada</param>
/// <param name="filtro">Filtro para consulta</param>
/// <param name="orderBy">Função para ordenação</param>
/// <param name="page">Página atual</param>
/// <param name="pageSize">Itens por página</param>
/// <returns></returns>
public static List <T> GenericQuery <T>(this T entity,
Func <IQueryable <T>, IQueryable <T> > filter,
Func <IQueryable <T>, IQueryable <T> > orderBy,
Int32 page = -1,
Int32 pageSize = -1)
where T : Entity, IEntitySelect, new()
{
return(CommonDB.GenericSelect <T>(entity, filter, orderBy, page, pageSize));
}
public void Write(Blogs b)
{
Random random = new Random();
b.Blog_id = random.Next();
string sql = "insert into [blogs](blog_date,blog_title,blog_body,comment_num,user_id,blog_id) values('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','" + b.Blog_title + "','" + b.Blog_body + "','0','" + b.User_id + "','" + b.Blog_id + "');";
CommonDB.ExecuteNonQuery(sql);
}
private int DeleteDataInner(List <EntityBase> entityList, CommonDB DB)
{
//必须保证集合中的元素都是同一个类型
if (entityList == null || entityList.Count == 0)
{
return(0);
}
EntityBase entity = entityList[0];
if (entity.PrimaryKeys.Count == 0)
{
throw new Exception("EntityQuery Error:当前实体类未指定主键字段");
}
int fieldCount = entity.PropertyNames.Length;
if (fieldCount == 0)
{
throw new Exception("EntityQuery Error:实体类属性字段数量为0");
}
string tableName = entity.TableName;
for (int i = 1; i < entityList.Count; i++)
{
if (entityList[i].TableName != tableName)
{
throw new Exception("当前实体类集合的元素类型不一致，对应的表是：" + (entityList[i].TableName));
}
}
//先将主键对应的记录删除，再插入
#region 构造查询语句
EntityCommand ec = new EntityCommand(entity, DB);
ec.IdentityEnable = true;//导入数据，不考虑自增列问题
ec.TargetFields = GetTargetFields(tableName, entity.PropertyNames, DB).ToArray();
string sql_delte = ec.DeleteCommand;
IDataParameter[] paras_delete = ec.DeleteParameters;
#endregion
int count = 0;
foreach (EntityBase item in entityList)
{
//执行删除
foreach (IDataParameter para in paras_delete)
{
para.Value = item.PropertyList(para.SourceColumn);
}
count += DB.ExecuteNonQuery(sql_delte, CommandType.Text, paras_delete);
}
return(count);
}
private SQLiteParameter[] GetSqlParameters()
{
List <SQLiteParameter> SqlParmColl = new List <SQLiteParameter>();
try
{
SqlParmColl.Add(CommonDB.AddSqlParm("@FPName", FPName, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@NoOfPoint", NoOfPoint, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@SolutionName", SolutionName, DbType.Int64));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des1", Des1, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type1", Type1, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des2", Des2, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type2", Type2, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des3", Des3, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type3", Type3, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des4", Des4, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type4", Type4, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des5", Des5, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type5", Type5, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des6", Des6, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type6", Type6, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des7", Des7, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type7", Type7, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des8", Des8, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type8", Type8, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des9", Des9, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type9", Type9, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des10", Des10, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type10", Type10, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des11", Des11, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type11", Type11, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des12", Des12, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type12", Type12, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des13", Des13, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type13", Type13, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des14", Des14, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type14", Type14, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des15", Des15, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type15", Type15, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des16", Des16, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type16", Type16, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des17", Des17, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type17", Type17, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des18", Des18, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type18", Type18, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des19", Des19, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type19", Type19, DbType.Int32));
SqlParmColl.Add(CommonDB.AddSqlParm("@Des20", Des20, DbType.String));
SqlParmColl.Add(CommonDB.AddSqlParm("@Type20", Type20, DbType.Int32));
return(SqlParmColl.ToArray());
}
catch (SQLiteException Exc)
{
throw Exc;
}
}
/// <summary>
/// 使用带事务的数据库对象，保存(使用指定的属性)数据到数据库。
/// 如果属性值自使用本类以来没有改变过或者没有为本方法指定实体对象的属性字段，那么将不会更新任何数据。
/// </summary>
/// <param name="db">数据库对象，可以在外部开启事务</param>
/// <param name="fields">实体类属性值列表</param>
/// <returns>操作数据库影响的行数</returns>
public int Save(CommonDB db, params object[] fields)
{
List <string> objFields = fields.Length > 0 ? selectFields : changedFields;
if (objFields.Count == 0)
{
return(0);
}
return(InnerSaveAllChanges(db, objFields));
}
/// <summary>Retorna a lista de items</summary>
/// <param name="entity">Entidade a ser retornada</param>
/// <param name="filter">Filtro para consulta</param>
/// <param name="carregarEntidades">Entidades que devem ser carregadas na consulta</param>
/// <param name="orderBy">Ordem que deve ser retornada</param>
/// <param name="page">Página atual</param>
/// <param name="pageSize">Itens por página</param>
/// <param name="asc">Ordem ascendete/descendente</param>
/// <returns>Itens</returns>
public static List <T> Query <T, Tkey>(this T entity,
Expression <Func <T, Boolean> > filter,
Expression <Func <T, Tkey> > orderBy,
Int32 page = -1,
Int32 pageSize = -1,
Boolean asc = true)
where T : EntityDB, IEntitySelect, new()
{
return(CommonDB.Select <T, Tkey>(entity, filter, null, orderBy, page, pageSize, asc));
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity">Entidade a ser retornada</param>
/// <param name="filtro">Filtro para consulta</param>
/// <param name="orderBy">Função para ordenação</param>
/// <param name="page">Página atual</param>
/// <param name="pageSize">Itens por página</param>
/// <param name="loadEntities">load entities</param>
/// <returns></returns>
public static List <T1> GenericQueryWithEntities <T1, T2>(this T1 entity,
Func <IQueryable <T1>, IQueryable <T1> > filter,
Func <IQueryable <T1>, IQueryable <T1> > orderBy,
Int32 page = -1,
Int32 pageSize = -1,
params Expression <Func <T1, T2> >[] loadEntities)
where T1 : Entity, IEntitySelect, new()
{
return(CommonDB.GenericSelect <T1, T2>(entity, filter, orderBy, page, pageSize, loadEntities));
}
/// <summary>Efetua a inclusão do objeto</summary>
/// <param name="itens">Lista de itens para serem incluídos</param>
public static void Insert <T>(this List <T> itens)
where T : Entity, IEntityInsert, new()
{
if (itens == null || itens.Count == 0)
{
return;
}
CommonDB.Insert <T>(itens);
}
/// <summary>Exclui logicamente a lista de itens</summary>
/// <param name="itens">Lista de itens</param>
public static void Delete <T>(this List <T> itens)
where T : Entity, IEntityDelete, new()
{
if (itens == null || itens.Count == 0)
{
return;
}
CommonDB.Delete <T>(itens);
}
public void ConstructorTest()
{
// ARRANGE
string connectionString = @"";
// ACT
CommonDB dB = new CommonDB(connectionString);
// ASSERT
Assert.IsNotNull(dB, "db is null");
}
} //end function
/// <summary>
/// 自动将数据集中的数据更新或者插入到数据库
/// <remarks>更新时间：2011.11.16</remarks>
/// </summary>
/// <param name="ds"></param>
/// <param name="DB"></param>
/// <returns></returns>
public static int SaveDataSet(DataSet ds, CommonDB DB)
{
int count = 0;
foreach (DataTable dt in ds.Tables)
{
string insertSql = GetSqlInsert(dt);
string updateSql = GetSqlUpdate(dt);
count += SaveDataTable(dt, insertSql, updateSql, DB);
}//end for
return(count);
}
private static int UpdateInner(T entity, List <string> objFields, CommonDB DB)
{
if (entity.PrimaryKeys.Count == 0)
{
throw new Exception("EntityQuery Error:当前实体类未指定主键字段");
}
int fieldCount = entity.PropertyNames.Length;
if (fieldCount == 0)
{
throw new Exception("EntityQuery Error:实体类属性字段数量为0");
}
//CommonDB DB = MyDB.GetDBHelper();
IDataParameter[] paras = new IDataParameter[fieldCount];
string sql = "UPDATE " + entity.TableName + " SET ";
string values = "";
string condition = "";
int index = 0;
foreach (string field in objFields)
{
string paraName = "@P" + index.ToString();
if (entity.PrimaryKeys.Contains(field))
{
//当前字段为主键，不能被更新
condition += " AND " + field + "=" + paraName;
paras[index] = DB.GetParameter(paraName, entity.PropertyList(field));
}
else
{
values += "," + field + "=" + paraName;
paras[index] = DB.GetParameter(paraName, entity.PropertyList(field));
}
index++;
}
if (condition == "")
{
foreach (string key in entity.PrimaryKeys)
{
string paraName = "@P" + index.ToString();
condition += " AND " + key + "=" + paraName;
paras[index] = DB.GetParameter(paraName, entity.PropertyList(key));
index++;
}
}
sql = sql + values.TrimStart(',') + " WHERE " + condition.Substring(" AND ".Length);
int count = DB.ExecuteNonQuery(sql, CommandType.Text, paras);
return(count);
}
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
//Common.Utilities.UserValidate(context);
//获取数据
DataTable dt = CommonDB.invokeProc_DataTable("select * from t_equipment order by equipmentID desc");
//转换为json
string jsonData = JsonHelper.ToJson(dt);
//返回
context.Response.Write(jsonData);
}
public DataTable Get()
{
DataTable dt = new DataTable();
CommonDB objDB = new CommonDB();
string sql = "select * from [User]";
objDB.OpenConnection();
dt = objDB.QueryDataTable(sql, "tabname");
objDB.CloseConnection();
objDB.Dispose();
objDB = null;
return(dt);
}
