/// <summary> /// 得到字符串的拼音 /// </summary> /// <param name="data"></param> /// <returns></returns> public List <string> GetPinYin(string data) { data = data.Trim(); StringBuilder select = new StringBuilder(64); StringBuilder from = new StringBuilder(64); WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder(); OrderBySqlClauseBuilder orderByBuilder = new OrderBySqlClauseBuilder(); for (int i = 0; i < data.Length; i++) { char ch = data[i]; if (ch < ' ' || ch > 'z') { string strAlias = "P" + i.ToString(); select.AppendWithSplitChars(strAlias + ".PinYin", ", "); from.AppendWithSplitChars("SC.PinYin " + strAlias, ", "); wBuilder.AppendItem(strAlias + ".HZ", ch.ToString()); orderByBuilder.AppendItem(strAlias + ".Weight", FieldSortDirection.Ascending); } } List <string> result = new List <string>(); if (select.Length > 0) { string sql = string.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY {3}", select.ToString(), from.ToString(), wBuilder.ToSqlString(TSqlBuilder.Instance), orderByBuilder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0]; foreach (DataRow row in table.Rows) { result.Add(DataRowToPinYin(data, row)); } } else { result.Add(DataRowToPinYin(data, null)); } return(result); }
/// <summary> /// 获得指定ID的副本 和 它之前的一切副本 按照时间正排序 /// </summary> /// <param name="id">id</param> /// <returns>MaterialList</returns> public MaterialList GetPreMaterialsCopyVersion(string id) { ExceptionHelper.CheckStringIsNullOrEmpty(id, "id"); WhereSqlClauseBuilder whereSqlClause = new WhereSqlClauseBuilder(); whereSqlClause.AppendItem("PARENT_ID", string.Format("(SELECT PARENT_ID FROM WF.MATERIAL WHERE ID = {0})", TSqlBuilder.Instance.CheckQuotationMark(id, true)), "=", true); whereSqlClause.AppendItem("MODIFY_TIME", string.Format("(SELECT MODIFY_TIME FROM WF.MATERIAL WHERE ID = {0})", TSqlBuilder.Instance.CheckQuotationMark(id, true)), "<=", true); whereSqlClause.AppendTenantCode(typeof(Material)); OrderBySqlClauseBuilder orderBySqlClause = new OrderBySqlClauseBuilder(); orderBySqlClause.AppendItem("MODIFY_TIME", FieldSortDirection.Ascending); string sql = string.Format(@"SELECT * FROM WF.MATERIAL WHERE {0} AND VERSION_TYPE = {1} ORDER BY {2}" , whereSqlClause.ToSqlString(TSqlBuilder.Instance), Convert.ToInt16(MaterialVersionType.CopyVersion), orderBySqlClause.ToSqlString(TSqlBuilder.Instance)); MaterialList materials = new MaterialList(); using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName())) { using (IDataReader dr = DbHelper.RunSqlReturnDR(sql, GetConnectionName())) { while (dr.Read()) { Material material = new Material(); ORMapping.DataReaderToObject(dr, material); materials.Add(material); } } } return(materials); }
/// <summary> /// 由资源ID查询 /// </summary> /// <param name="resourceIDs">资源ID</param> /// <returns>查询结果</returns> public MaterialList LoadMaterialsByResourceID(params string[] resourceIDs) { ExceptionHelper.TrueThrow <ArgumentNullException>(resourceIDs == null, "resourceIDs"); MaterialList result = new MaterialList(); if (resourceIDs.Length != 0) { InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("RESOURCE_ID"); inBuilder.AppendItem(resourceIDs); OrderBySqlClauseBuilder orderClause = new OrderBySqlClauseBuilder(); orderClause.AppendItem("SORT_ID", FieldSortDirection.Ascending); string sql = string.Format("SELECT * FROM WF.MATERIAL WHERE {0} AND VERSION_TYPE = '{1}' ORDER BY {2}", inBuilder.AppendTenantCodeSqlClause(typeof(Material)).ToSqlString(TSqlBuilder.Instance), Convert.ToInt32(MaterialVersionType.Normal).ToString(), orderClause.ToSqlString(TSqlBuilder.Instance)); using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName())) { using (IDataReader dr = DbHelper.RunSqlReturnDR(sql, GetConnectionName())) { while (dr.Read()) { Material material = new Material(); ORMapping.DataReaderToObject(dr, material); result.Add(material); } } } DecorateMaterials(result); } return(result); }
/// <summary> /// 获得指定ID的副本 和 它之前的一切副本 按照时间正排序 /// </summary> /// <param name="id">id</param> /// <returns>MaterialList</returns> public MaterialList GetPreMaterialsCopyVersion(string id) { ExceptionHelper.CheckStringIsNullOrEmpty(id, "id"); WhereSqlClauseBuilder whereSqlClause = new WhereSqlClauseBuilder(); whereSqlClause.AppendItem("PARENT_ID", string.Format("(SELECT PARENT_ID FROM WF.MATERIAL WHERE ID = {0})", TSqlBuilder.Instance.CheckQuotationMark(id, true)), "=", true); whereSqlClause.AppendItem("MODIFY_TIME", string.Format("(SELECT MODIFY_TIME FROM WF.MATERIAL WHERE ID = {0})", TSqlBuilder.Instance.CheckQuotationMark(id, true)), "<=", true); whereSqlClause.AppendTenantCode(typeof(Material)); OrderBySqlClauseBuilder orderBySqlClause = new OrderBySqlClauseBuilder(); orderBySqlClause.AppendItem("MODIFY_TIME", FieldSortDirection.Ascending); string sql = string.Format(@"SELECT * FROM WF.MATERIAL WHERE {0} AND VERSION_TYPE = {1} ORDER BY {2}", whereSqlClause.ToSqlString(TSqlBuilder.Instance), Convert.ToInt16(MaterialVersionType.CopyVersion), orderBySqlClause.ToSqlString(TSqlBuilder.Instance)); MaterialList materials = new MaterialList(); using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName())) { using (IDataReader dr = DbHelper.RunSqlReturnDR(sql, GetConnectionName())) { while (dr.Read()) { Material material = new Material(); ORMapping.DataReaderToObject(dr, material); materials.Add(material); } } } return materials; }
/// <summary> /// 由资源ID查询 /// </summary> /// <param name="resourceIDs">资源ID</param> /// <returns>查询结果</returns> public MaterialList LoadMaterialsByResourceID(params string[] resourceIDs) { ExceptionHelper.TrueThrow<ArgumentNullException>(resourceIDs == null, "resourceIDs"); MaterialList result = new MaterialList(); if (resourceIDs.Length != 0) { InSqlClauseBuilder inBuilder = new InSqlClauseBuilder("RESOURCE_ID"); inBuilder.AppendItem(resourceIDs); OrderBySqlClauseBuilder orderClause = new OrderBySqlClauseBuilder(); orderClause.AppendItem("SORT_ID", FieldSortDirection.Ascending); string sql = string.Format("SELECT * FROM WF.MATERIAL WHERE {0} AND VERSION_TYPE = '{1}' ORDER BY {2}", inBuilder.AppendTenantCodeSqlClause(typeof(Material)).ToSqlString(TSqlBuilder.Instance), Convert.ToInt32(MaterialVersionType.Normal).ToString(), orderClause.ToSqlString(TSqlBuilder.Instance)); using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName())) { using (IDataReader dr = DbHelper.RunSqlReturnDR(sql, GetConnectionName())) { while (dr.Read()) { Material material = new Material(); ORMapping.DataReaderToObject(dr, material); result.Add(material); } } } DecorateMaterials(result); } return result; }
/// <summary> /// 得到字符串的拼音 /// </summary> /// <param name="data"></param> /// <returns></returns> public List<string> GetPinYin(string data) { data = data.Trim(); StringBuilder select = new StringBuilder(64); StringBuilder from = new StringBuilder(64); WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder(); OrderBySqlClauseBuilder orderByBuilder = new OrderBySqlClauseBuilder(); for (int i = 0; i < data.Length; i++) { char ch = data[i]; if (ch < ' ' || ch > 'z') { string strAlias = "P" + i.ToString(); select.AppendWithSplitChars(strAlias + ".PinYin", ", "); from.AppendWithSplitChars("SC.PinYin " + strAlias, ", "); wBuilder.AppendItem(strAlias + ".HZ", ch.ToString()); orderByBuilder.AppendItem(strAlias + ".Weight", FieldSortDirection.Ascending); } } List<string> result = new List<string>(); if (select.Length > 0) { string sql = string.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY {3}", select.ToString(), from.ToString(), wBuilder.ToSqlString(TSqlBuilder.Instance), orderByBuilder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0]; foreach (DataRow row in table.Rows) result.Add(DataRowToPinYin(data, row)); } else result.Add(DataRowToPinYin(data, null)); return result; }