1.写SQL的话直接写就好了,注意不要出现count聚合函数等,一定要用的话,用count(*) over()
public static ListQueryDate(string date, string sn)
{
try
{
Listlist = new List(); //DataTable2 自定义类,存储DB返回数据
string sql = @" select HISDATA,STARTTIME,SN,DEL from app001.DataTable2 where 1=1";
if (!string.IsNullOrEmpty(date))
{
sql += " and STARTTIME = to_date('" + date + "','yyyy-mm-dd hh24:mi:ss') ";
}
if (!string.IsNullOrEmpty(sn))
{
sql += " and sn = '" + sn + "'";
}
using (IDataReader dr = ExecDataReader(sql))
{
while (dr.Read())
{
DataTable2 model = new DataTable2();
model.HISDATA = dr["HISDATA"].ToString();
model.STARTTIME = dr["STARTTIME"].ToString();
model.SN = dr["SN"].ToString();
model.DEL = dr["DEL"].ToString();
list.Add(model);
}
}
return list;
}
public static List QueryDate(string ID, string User_Name, string Model_Name, string SN, string Start_Time, string End_Time, string Page, string PageNums)
{
try
{
List list = new List();
OracleConnection conn = CreateConnection();
OracleCommand cmd = new OracleCommand(“APP022.APP_OXYGEN_PRO.PRO_QUERY_DATA”, conn);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter(“ID_v”, OracleDbType.Varchar2, 100);
p1.Direction = System.Data.ParameterDirection.Input;
p1.Value = ID;
cmd.Parameters.Add(p1);
OracleParameter p2 = new OracleParameter(“User_Name_v”, OracleDbType.Varchar2, 100);
p2.Direction = System.Data.ParameterDirection.Input;
p2.Value = User_Name;
cmd.Parameters.Add(p2);
OracleParameter p3 = new OracleParameter(“Model_Name_v”, OracleDbType.Varchar2, 100);
p3.Direction = System.Data.ParameterDirection.Input;
p3.Value = Model_Name;
cmd.Parameters.Add(p3);
OracleParameter p4 = new OracleParameter(“SN_v”, OracleDbType.Varchar2, 100);
p4.Direction = System.Data.ParameterDirection.Input;
p4.Value = SN;
cmd.Parameters.Add(p4);
OracleParameter p5 = new OracleParameter("Start_Time_v", OracleDbType.Varchar2 , 100);
p5.Direction = System.Data.ParameterDirection.Input;
p5.Value = Start_Time;
cmd.Parameters.Add(p5);
OracleParameter p6 = new OracleParameter("End_Time_v", OracleDbType.Varchar2 , 100);
p6.Direction = System.Data.ParameterDirection.Input;
p6.Value = End_Time;
cmd.Parameters.Add(p6);
OracleParameter p7 = new OracleParameter("Page", OracleDbType.Varchar2, 100);
p7.Direction = System.Data.ParameterDirection.Input;
p7.Value = Page;
cmd.Parameters.Add(p7);
OracleParameter p8 = new OracleParameter("PageNums", OracleDbType.Varchar2, 100);
p8.Direction = System.Data.ParameterDirection.Input;
p8.Value = PageNums;
cmd.Parameters.Add(p8);
OracleParameter p9 = new OracleParameter("RET", OracleDbType.RefCursor);
p9.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(p9);
// cmd.ExecuteNonQuery();
// OracleDataReader dr = (OracleDataReader)cmd.ExecuteScalar();
// OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// OracleDataReader dr = cmd.ExecuteReader();
// IDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
HEARTRATEDATA model = new HEARTRATEDATA(); //自定义类
model.ID = ds.Tables[0].Rows[i]["ID"].ToString();
model.User_Name = ds.Tables[0].Rows[i]["USER_NAME"].ToString();
model.Model_Name = ds.Tables[0].Rows[i]["Model_Name"].ToString();
model.SN = ds.Tables[0].Rows[i]["SN"].ToString();
model.Start_Time = ds.Tables[0].Rows[i]["START_TIME"].ToString();
model.End_Time = ds.Tables[0].Rows[i]["END_TIME"].ToString();
model.Test_Time = ds.Tables[0].Rows[i]["TEST_TIME"].ToString();
model.Heart_Rate = ds.Tables[0].Rows[i]["HEART_RATE"].ToString();
list.Add(model);
}
conn.Close();
return list;
}
catch (Exception e)
{
JUSTEC_LogHelper.LogHelper.WriteLog("Disposes错误提示:", "Disposes.QueryDate方法出错:" + e.ToString());
//throw;
return null;
}
}
也可以用OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);接收存储过程返回结果集
while(dr.Read())
{
HEARTRATEDATA model = new HEARTRATEDATA();
model.ID = dr[“ID”].Tostring();
…
…
…
model.Heart_Rate = dr[“HEART_RATE”].Tostring();
list.Add(model);
}
查询不到结果的时候:
1.先看Oracle中 Clob字段类型是否已经保存了数据,用编辑器查
2.所需执行的SQL 在数据库中查出来的结果是否Clob字段有数据,要使用编辑器看