/// <summary> /// 通过查询语句返回一个值 /// </summary> /// <param name="tsSql">查询语句</param> /// <returns>查询结果</returns> public static object ExecuteScalar(string tsSql, String dbStr) { OracleCommand loOraCommand = DBA.GetOraCommand(dbStr); loOraCommand.CommandText = tsSql; try { object loValue = loOraCommand.ExecuteScalar(); return(loValue); } finally { DBA.RealseOraCommand(loOraCommand); } }
public static DataTable PageDataTable(int intCurrentPage, int intPageSize, string strSql, String dbStr) { OracleCommand loOraCommand = DBA.GetOraCommand(dbStr); loOraCommand.CommandText = strSql; try { int StartIndex; StartIndex = intCurrentPage * intPageSize; DataSet ds = new DataSet(); OracleDataAdapter MyAdapter = new OracleDataAdapter(); MyAdapter.SelectCommand = loOraCommand; MyAdapter.Fill(ds, StartIndex, intPageSize, "Page"); return(ds.Tables["Page"]); } finally { DBA.RealseOraCommand(loOraCommand); } }
public string GenXML(string tsSelectCommandText) { string msTableName = "Item"; string msXmlScript = ""; OracleCommand loOraCommand = DBA.GetOraCommand(); loOraCommand.CommandText = tsSelectCommandText; try { OracleDataReader loDataReader = loOraCommand.ExecuteReader(); object[] loValues = new object[loDataReader.FieldCount]; int lnColIndex; System.IO.StringWriter loStringWriter = new System.IO.StringWriter(); XmlTextWriter loWriter = new XmlTextWriter(loStringWriter); while (loDataReader.Read()) { loDataReader.GetValues(loValues); loWriter.WriteStartElement(msTableName); for (lnColIndex = 0; lnColIndex < loDataReader.FieldCount; lnColIndex++) { loWriter.WriteAttributeString(loDataReader.GetName(lnColIndex), loValues[lnColIndex].ToString()); } loWriter.WriteEndElement(); } msXmlScript += loStringWriter.ToString(); loWriter.Close(); loDataReader.Close(); } finally { DBA.RealseOraCommand(loOraCommand); } return(msXmlScript); }
/// <summary> /// 根据查询语句填允数据表. /// </summary> /// <param name="toTable">数据表</param> /// <param name="tsSelectCommandText">查询语句</param> /// <param name="tsFields"> /// </param> private static void FillDataTable(DataTable toTable, string tsSelectCommandText, OracleCommand toOracleCommand, string tsFields, string tsKeyFields, bool tbFillSynchronize, int tnStartIndex, int tnAffectedRowCount) { OracleCommand loOraCommand = null; bool isUsedOtherCmd = false;//是否使用外部的Command if (toOracleCommand != null) { loOraCommand = toOracleCommand; isUsedOtherCmd = true; } else { loOraCommand = DBA.GetOraCommand(); } loOraCommand.CommandText = tsSelectCommandText; try { OracleDataReader loReader = loOraCommand.ExecuteReader(); // 初始化要填允的字段 string[] lsFields = null; System.Type[] loFieldTypes = null; if (tsFields != "" && tsFields != null) { lsFields = tsFields.Split(new char[] { ',' }); } else { lsFields = new string[loReader.FieldCount]; loFieldTypes = new Type[loReader.FieldCount]; for (int lnIndex = 0; lnIndex < loReader.FieldCount; lnIndex++) { lsFields[lnIndex] = loReader.GetName(lnIndex); loFieldTypes[lnIndex] = loReader.GetFieldType(lnIndex); } } // 找到主键. string[] lsKeyFields = null; if (tsKeyFields != null && tsKeyFields != "") { lsKeyFields = tsKeyFields.Split(new char[] { ',' }); } DataColumn[] loDataColumns = new DataColumn[lsFields.Length]; DataColumn loColumn = null; for (int lnIndex = 0; lnIndex < lsFields.Length; lnIndex++) { loColumn = toTable.Columns[lsFields[lnIndex]]; if (loColumn == null) { loColumn = new DataColumn(lsFields[lnIndex], loFieldTypes[lnIndex]); toTable.Columns.Add(loColumn); } loDataColumns[lnIndex] = loColumn; } DataRow loRow = null; int lnRowIndex = 0; while (loReader.Read()) { if (tnStartIndex < 0 || (tnStartIndex >= 0 && lnRowIndex >= tnStartIndex && lnRowIndex < tnStartIndex + tnAffectedRowCount)) { if (lsKeyFields == null) { loRow = toTable.NewRow(); } else { object[] loKeyValues = new object[lsKeyFields.Length]; for (int lnIndex = 0; lnIndex < lsKeyFields.Length; lnIndex++) { loKeyValues[lnIndex] = loReader[lsKeyFields[lnIndex]]; } Array loFoundRows = FindRows(toTable.Rows, lsKeyFields, loKeyValues, true); if (loFoundRows.Length > 0) { loRow = (DataRow)loFoundRows.GetValue(0); } else { if (tbFillSynchronize) { loReader.Close(); throw new Exception("不能找到要装载数据", null); } } } if (loRow != null) { for (int lnIndex = 0; lnIndex < loDataColumns.Length; lnIndex++) { loRow[loDataColumns[lnIndex]] = loReader[lnIndex]; } if (lsKeyFields == null) { toTable.Rows.Add(loRow); } } } else if (tnStartIndex >= 0 && lnRowIndex > tnStartIndex + tnAffectedRowCount) { break; } lnRowIndex++; } loReader.Close(); } finally { if (!isUsedOtherCmd) { DBA.RealseOraCommand(loOraCommand); } } toTable.AcceptChanges(); }