public static DataTable GetDataTable(string tsSelectCommandText)
{
DataTable dt = new DataTable();
DBA.FillDataTable(dt, tsSelectCommandText);
return(dt);
}
/// <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 Boolean DataTableUpdate(DataTable toDataTable)
{
String[] lsSQL = GenerateUpdateSql(toDataTable);
if (lsSQL == null)
{
return(false);
}
try
{
return(DBA.ExeuseSQL(lsSQL));
}
catch (Exception ex)
{
return(false);
}
}
public static DataTable DataTableSelect(String sql)
{
try
{
DataSet ds = new DataSet();
String lsError = "";
if (DBA.SelectSQL("", sql, ref ds, ref lsError) > 0)
{
return(ds.Tables[0]);
}
else
{
return(null);
}
}
catch (Exception e)
{
return(null);
}
}
/// <summary>
/// 执行一段代码(ExecuteNonQuery)
/// </summary>
/// <param name="tsSQL">语句</param>
/// <returns></returns>
public static int ExecuteNonQuery(string tsSQL)
{
OracleCommand loOraCommand = DBA.GetOraCommand();
loOraCommand.CommandText = tsSQL;
int lnReturnValue = -1;
try
{
lnReturnValue = loOraCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
RealseOraCommand(loOraCommand);
}
return(lnReturnValue);
}
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();
}
public static void FillDataTable(DataTable toTable, string tsSelectCommandText, string tsKeyFields, bool tbFillSynchronize)
{
DBA.FillDataTable(toTable, tsSelectCommandText, null, null, tsKeyFields, false, -1, -1);
}
public static void FillDataTable(DataTable toTable, string tsSelectCommandText, int tnStartIndex, int tnAffectedRowCount)
{
DBA.FillDataTable(toTable, tsSelectCommandText, null, null, null, false, tnStartIndex, tnAffectedRowCount);
}
public static void FillDataTable(DataTable toTable, string tsSelectCommandText, OracleCommand toOracleCommand)
{
DBA.FillDataTable(toTable, tsSelectCommandText, toOracleCommand, null, null, true, -1, -1);
}
public static void FillDataTable(DataTable toTable, string tsSelectCommandText, string tsFields)
{
DBA.FillDataTable(toTable, tsSelectCommandText, null, tsFields, null, true, -1, -1);
}