/// <summary> /// 插入数据 /// </summary> /// <returns></returns> public static bool insert(ORM.Test.FreeLink FreeLink_obj) { StringBuilder cols = new StringBuilder(); StringBuilder parameters = new StringBuilder(); string sql = "INSERT INTO FreeLink({0}) values({1})"; if (cols.Length > 0) { cols.Append(",[LinkTitle]"); parameters.Append(",@LinkTitle"); } else { cols.Append("[LinkTitle]"); parameters.Append("@LinkTitle"); } if (cols.Length > 0) { cols.Append(",[LinkContent]"); parameters.Append(",@LinkContent"); } else { cols.Append("[LinkContent]"); parameters.Append("@LinkContent"); } if (cols.Length > 0) { cols.Append(",[LinkDescription]"); parameters.Append(",@LinkDescription"); } else { cols.Append("[LinkDescription]"); parameters.Append("@LinkDescription"); } if (cols.Length > 0) { cols.Append(",[LinkMark]"); parameters.Append(",@LinkMark"); } else { cols.Append("[LinkMark]"); parameters.Append("@LinkMark"); } sql = string.Format(sql, cols.ToString(), parameters.ToString()); SqlCommand cmd = new SqlCommand(sql); cmd = ParameterElse(cmd, FreeLink_obj); bool b = true; try { int QueryCount = new ORM.DBUtility.DBHelper(true).ExecNonQuery(cmd); if (QueryCount != 1) { b = false; } } catch { b = false; } return(b); }
/// <summary> /// 分页查询 /// </summary> /// <param name="sqlWhere">查询条件,如username='******' and password='******'</param> /// <param name="sqlSort">排序条件,如username desc</param> /// <param name="sqlCols">数据库字段名组,用逗号分割,例如:username,userpwd,userid</param> /// <param name="pageIndex">页码</param> /// <param name="pageSize">页大小</param> /// <param name="recordCount">记录行数</param> /// <returns></returns> public static DataTable GetTable(string sqlWhere, string sqlSort, string sqlCols, int pageIndex, int pageSize, out long recordCount, object[] ParamsList = null) { int SI = pageIndex * pageSize - pageSize + 1; int EI = pageIndex * pageSize; DataSet ds = new DataSet(); if (string.IsNullOrEmpty(sqlCols)) { sqlCols = "ID,LinkTitle,LinkContent,LinkDescription,LinkMark"; } StringBuilder sql = new StringBuilder("WITH PST(RN," + sqlCols + ") AS"); sql.Append("("); sql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ").Append(sqlSort).Append(") RN,").Append(sqlCols).Append(" "); sql.Append("FROM FreeLink(NOLOCK)"); if (!string.IsNullOrEmpty(sqlWhere)) { sql.Append(" WHERE ").Append(sqlWhere); } sql.Append(")"); sql.Append("SELECT RN,").Append(sqlCols).Append(" FROM PST WHERE RN BETWEEN @SI AND @EI"); object[] NewParamsList; if (ParamsList != null) { NewParamsList = new object[ParamsList.Length + 2]; for (int i = 0; i < ParamsList.Length; i++) { NewParamsList[i] = ParamsList[i]; } NewParamsList[ParamsList.Length] = SI; NewParamsList[ParamsList.Length + 1] = EI; } else { NewParamsList = new object[2]; NewParamsList[0] = SI; NewParamsList[1] = EI; } if (!string.IsNullOrEmpty(sqlWhere)) { sql.Append(";SELECT COUNT(*) FROM FreeLink(NOLOCK) WHERE ").Append(sqlWhere); } else { sql.Append(";SELECT COUNT(*) FROM FreeLink(NOLOCK)"); } SqlCommand cmd = BuildCommand(sql.ToString(), NewParamsList); ds = new ORM.DBUtility.DBHelper(true).ExecDataSet(cmd); if (ds != null && ds.Tables.Count > 1 && ds.Tables[1].Rows.Count > 0) { recordCount = Convert.ToInt64(ds.Tables[1].Rows[0][0]); } else { recordCount = 0; } if (ds != null && ds.Tables.Count > 1) { return(ds.Tables[0]); } else { return(new DataTable()); } }