C#数据路接口中获取SQL数据的用法
-
获取一条记录
string sql = string.Format(“”);
DataRow row;
if (GetFirstDataRow(sql, out row))
{
caseInfoBrief.TEST_TASK_STATUS = row["CASE_STATUS_FINISH"].ToString()
return true;
}
-
获取多条记录
string sql = string.Format("select * from T_TEST_TASK where TEST_USER_ID={0} and TASK_STATUS in ({1}, {2}) ", uID,(int)E_TEST_TASK_STATUS.已分派, (int)E_TEST_TASK_STATUS.待评价);
DataTable dataTable;
if (GetDataTable(sql, out dataTable))
{
foreach (DataRow row in dataTable.Rows)
{
T_TEST_TASK testTask = new T_TEST_TASK();
testTask.TASK_ID = row["TASK_ID"].ToString()
int.TryParse(row["TASK_TYPE"].ToString(), out testTask.TASK_TYPE);
DateTime.TryParse(row["CREATE_TIME"].ToString(), out caseInfoBrief.CREATE_TIME);
list.Add(testTask);
}
}
3.数据分页显示
var sql = string.Format("select * from (select rownum rn,T_CASE_INFO.* from T_CASE_INFO) where rn>={0}*{1} and rn<={0}*{1}+{1}", page,record);
4.批量执行SQL语句
var sql = string.Format("delete from T_CASE_INFO where CASE_ID='{0}'",caseID);
var sql2 = string.Format("delete from T_CASE_LOG where CASE_ID='{0}'", caseID);
List<string> sqlList = new List<string>();
sqlList.Add(sql);
sqlList.Add(sql2);
return ExecuteListNonQuery(sqlList);
5.统计个数
select sum( case Test_task_status when null then 0 else 1 end ) as test_total , sum( case Test_task_status when 5 then 1 else 0 end ) as test_finished,sum( case Suvey_Task_Status when null then 0 else 1 end ) as survey_total , sum( case Suvey_Task_Status when 5 then 1 else 0 end ) as survey_finished
from v_case_breif_info group by CASE_ID)
6.左连接查询
SELECT TotalScore,graped,giveup,Finished from
(select USER_SCORE_CURRENT AS TotalScore,ID from T_TEST_USER_INFO WHERE ID=1) t1 left join
(select USER_ID,sum(case USER_TASK_STATUS when 1 then 1 else 0 END) as graped, sum(case USER_TASK_STATUS when 2 then 1 else 0 END) as giveup, sum(case USER_TASK_STATUS when 3 then 1 else 0 END) as Finished
from T_TEST_TASK_USER where USER_ID=1 group by User_ID) t on t1.id = T.USER_ID
