/// <summary>
/// 從資料庫擷取單一值
/// </summary>
/// <param name="cmdstr">
/// SELECT等SQL指令,且此SQL指令只會傳回一列一行的資料,例如：
/// string strSQL = "SELECT COUNT(*) FROM HinsUser";
/// stirng result = clsHintsDB.ExecuteScalar(strSQL,pList).ToString();
/// </param>
/// SQL指令中的變數以object[]傳入
/// string strSQL = "SELECT COUNT(*) FROM region WHERE cUserID=@cUserID AND cCaseID=@cCaseID";//在此SQL語法中@cUserID,@cCaseID分別為變數
/// object[] pList = {strUserID,strCaseID};//因為上面SQL語法有兩個變數,所以在此需按照SQL語法中的變數順序傳入兩個變數的值
/// stirng result = clsHintsDB.ExecuteScalar(strSQL,pList).ToString();
/// <returns>傳回直以object傳回,視需求轉型</returns>
public object ExecuteScalar(string cmdstr, object[] pList)
{
try
{
return(sqldb.ExecuteScalar(cmdstr, pList));
}
catch
{
return(new object());
}
}
public static int ChaFenShuXianCount(string strWhere, int SchoolProvinceId, int WenLi, int StudentProvinceId, int Year)
{
string strSql;
string strSqlTable = " WHERE FSYXLQ.SchoolId = S.Id ";
if (SchoolProvinceId > 0)
{
strSqlTable += " and S.ProvinceId = " + SchoolProvinceId;
}
if (WenLi > 0)
{
strSqlTable += " and FSYXLQ.KeLei = " + WenLi;
}
if (StudentProvinceId > 0)
{
strSqlTable += " and FSYXLQ.ProvinceId = " + StudentProvinceId;
}
if (Year > 0)
{
strSqlTable += " and FSYXLQ.DataYear = " + Year;
}
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql = "SELECT COUNT(1) FROM [" + DAL.Common.GetProvinceTableName(StudentProvinceId, "FenShengYuanXiaoLuQu", (Year > 0 ? Year.ToString() : "All")) + "] AS FSYXLQ,[School] AS S " + strSqlTable + " AND " + strWhere;
}
else
{
strSql = "SELECT COUNT(1) FROM [" + DAL.Common.GetProvinceTableName(StudentProvinceId, "FenShengYuanXiaoLuQu", (Year > 0 ? Year.ToString() : "All")) + "] AS FSYXLQ,[School] AS S " + strSqlTable;
}
return(Basic.Utils.StrToInt(SqlDB.ExecuteScalar(CommandType.Text, strSql).ToString(), 0));
}
public static int Join_Student_CreateBank(string StudentBank, string StudentPass)
{
SqlParameter[] prams =
{
SqlDB.MakeInParam("@StudentBank", SqlDbType.NVarChar, 50, StudentBank),
SqlDB.MakeInParam("@StudentPass", SqlDbType.NVarChar, 50, StudentPass),
};
return(Basic.Utils.StrToInt(SqlDB.ExecuteScalar(strCon, CommandType.StoredProcedure, "Join_Student_CreateBank", prams).ToString(), -1));
}
public static int JiChuGongGuCount(string TablePre, int ZhiShiDianId)
{
string strSql = "select count(1) from zhishidian left join " + TablePre + "_exam_question_index as kc "
+ " on zhishidian.name = kc.zh_knowledge and zhishidian.subject_id = kc.subject_id "
+ " left join " + TablePre + "_exam_question as kcq on kc.gid = kcq.gid "
+ " where zhishidian.Id = " + ZhiShiDianId;
return(Basic.Utils.StrToInt(SqlDB.ExecuteScalar(strCon, CommandType.Text, strSql).ToString(), 0));
}
/// <summary>
/// 题型_非选择题
/// </summary>
/// <param name="strWhere"></param>
/// <returns></returns>
public static int fTiMuCount(string strWhere)
{
string strSql = "select COUNT(*) from TiMuSuoYin ,TiMuNeiRong ,edu_question_type where TiMuSuoYin.gid = TiMuNeiRong.gid and edu_question_type.id = TiMuSuoYin.edu_question_type_Id and DataLength(TiMuNeiRong.content)>0 ";
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql += " AND " + strWhere;
}
return(Basic.Utils.StrToInt(SqlDB.ExecuteScalar(strCon, CommandType.Text, strSql).ToString(), 0));
}
/// <summary>
/// 试卷_选择题
/// </summary>
/// <param name="strFirstWhere"></param>
/// <param name="strWhere"></param>
/// <returns></returns>
public static int sjTiMuCount(string strWhere)
{
string strSql = "select COUNT(*) from shijuan,ShiJuan2TiMu ,TiMuNeiRong where ShiJuan2TiMu.exam_id = shijuan.OldId and ShiJuan2TiMu.question_id = TiMuNeiRong.gid ";
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql += " AND " + strWhere;
}
return(Basic.Utils.StrToInt(SqlDB.ExecuteScalar(strCon, CommandType.Text, strSql).ToString(), 0));
}
/// <summary>
/// 知识点_选择题
/// </summary>
/// <param name="strWhere"></param>
/// <returns></returns>
public static int zsdTiMuCount(string strWhere)
{
string strSql = "select COUNT(*) from TiMuSuoYin ,TiMuNeiRong ,edu_question_type,zhishidian,zhishidian2timusuoyin where TiMuSuoYin.gid = TiMuNeiRong.gid and edu_question_type.id = TiMuSuoYin.edu_question_type_Id and len(ltrim(rtrim(TiMuNeiRong.objective_answer))) = 1 and zhishidian2timusuoyin.ZhiShiDianId = zhishidian.Id and zhishidian2timusuoyin.TiMuSuoYinId = TiMuSuoYin.Id ";
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql += " AND " + strWhere;
}
return(Basic.Utils.StrToInt(SqlDB.ExecuteScalar(strCon, CommandType.Text, strSql).ToString(), 0));
}
/// <summary>
/// 错题本 条数
/// </summary>
/// <param name="strWhere">条件，可以为空</param>
/// <returns>返回DataTable</returns>
public static int CuoTiBenCount(string strWhere)
{
string strSql;
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql = "SELECT count(*) FROM [TiMuNeiRong],[cuotiben] WHERE TiMuNeiRong.id = cuotiben.TiMuId AND " + strWhere;
}
else
{
strSql = "SELECT count(*) FROM [TiMuNeiRong],[cuotiben] WHERE TiMuNeiRong.id = cuotiben.TiMuId ";
}
return(Basic.Utils.StrToInt(SqlDB.ExecuteScalar(strCon, CommandType.Text, strSql).ToString(), 0));
}
public static int Join_StudentAdd_WanShanXinXi(Entity.Join_Student info)
{
SqlParameter[] prams =
{
SqlDB.MakeInParam("@StudentName", SqlDbType.NVarChar, 50, info.StudentName),
SqlDB.MakeInParam("@Sex", SqlDbType.Int, 4, info.Sex),
SqlDB.MakeInParam("@CellPhone", SqlDbType.VarChar, 50, info.CellPhone),
SqlDB.MakeInParam("@SchoolName", SqlDbType.NVarChar, 150, info.SchoolName),
SqlDB.MakeInParam("@FuQinPhone", SqlDbType.NVarChar, 50, info.FuQinPhone),
SqlDB.MakeInParam("@MuQinPhone", SqlDbType.NVarChar, 50, info.MuQinPhone),
SqlDB.MakeInParam("@WenLi", SqlDbType.Int, 4, info.WenLi),
SqlDB.MakeInParam("@BanJi", SqlDbType.NVarChar, 100, info.BanJi),
SqlDB.MakeInParam("@BanZhuRen", SqlDbType.NVarChar, 100, info.BanZhuRen),
SqlDB.MakeInParam("@ProvinceId", SqlDbType.Int, 4, info.ProvinceId),
SqlDB.MakeInParam("@CityId", SqlDbType.Int, 4, info.CityId),
SqlDB.MakeInParam("@CountyId", SqlDbType.Int, 4, info.CountyId),
SqlDB.MakeInParam("@BanZhuRenMobile", SqlDbType.NVarChar, 100, info.BanZhuRenMobile),
SqlDB.MakeInParam("@GKYear", SqlDbType.Int, 4, info.GKYear),
SqlDB.MakeInParam("@StudentLevel", SqlDbType.Int, 4, info.StudentLevel),
};
return(Basic.Utils.StrToInt(SqlDB.ExecuteScalar(strCon, CommandType.StoredProcedure, "Join_StudentAdd_WanShanXinXi", prams).ToString(), -1));
}