public DateTime GetMaxStartTime()
{
object o = null;
using (OracleConnection conn = new OracleConnection(Connectionstr))
{
try
{
conn.Open();
string sql = "select max(startTime ) as MaxTime from CofeedProduct";
using (OracleCommand cmd = new OracleCommand(sql, conn))
{
o = cmd.ExecuteScalar();
}
}
catch
{
// ignored
}
finally
{
conn.Close();
}
}
return(Convert.ToDateTime(o));
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLString)
{
using (OracleConnection connection = new OracleConnection(conStr))
{
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return(null);
}
else
{
return(obj);
}
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
}
}
public void ImportDataTable(Cells cells, StringBuilder sb)
{
int insertCount = 0;
int updateCount = 0;
for (int i = 1; i < cells.Rows.Count; i++)
{
var curMonth = cells[i, 1].Value;
var curYear = cells[i, 0].Value;
if (curYear == null || curMonth == null || string.IsNullOrEmpty(curMonth.ToString()))
{
//if there is no month in this row,pass it.
continue;
}
var endDate = Convert.ToDateTime(curYear.ToString().Replace('年', '-') + curMonth.ToString().Replace('月', '-') + "01").ToString("dd-MMM-yyyy");
//the column of month can't be data like 01M,it should be like 1M,no zero
var rowDate = curYear.ToString() + curMonth;
using (var con = new OracleConnection(Connectionstr))
{
con.Open();
OracleCommand cmd = new OracleCommand("", con);
for (int j = 2; j <= cells.Rows[i].LastDataCell.Column; j++)
{
var code = "LZ00" + j;
var cnname = cells[0, j].Value.ToString();
var period = rowDate;
double yield;
if (cells[i, j].Value == null || !double.TryParse(cells[i, j].Value.ToString(), out yield))
{
continue;
}
string existStr = "select Yield from LONGZHONG_YIELD where code='" + code +
"' and CNNAME='" + cnname + "' and period='" + period + "'";
cmd.CommandText = existStr;
int cmdresult = 0;
try
{
object obj = cmd.ExecuteScalar();
if (Equals(obj, null) || (Equals(obj, DBNull.Value)))
{
cmdresult = 0;
}
else
{
if (Double.Parse(obj.ToString()) == yield)
{
continue;
}
cmdresult = 1;
}
}
catch (OracleException e)
{
con.Close();
throw new Exception(e.Message);
}
string operationSql;
if (cmdresult == 0)
{
insertCount++;
operationSql = "insert into LONGZHONG_YIELD values('" + code + "','" + cnname +
"','" + period + "','" + endDate + "'," + yield + ",sysdate)";
}
else
{
updateCount++;
operationSql = "update LONGZHONG_YIELD set yield=" + yield + ",FETCHUNIX=sysdate where code='" + code +
"' and CNNAME='" + cnname + "' and period='" + period + "'";
}
cmd.CommandText = operationSql;
try
{
cmd.ExecuteNonQuery();
}
catch (OracleException e)
{
con.Close();
throw new Exception(e.Message);
}
}
con.Close();
}
}
sb.Append("Table LONGZHONG_YIELD Insert Rows:" + insertCount + ",Update Rows:" + updateCount + "\n \r\n");
}
