public int InsertSMSPhoneNum(string stuID,
string familialName, string mobilePhoneNum,
string stuGrade, string stuClass)
{
int rowsAffected = 0;
try
{
DBCommandWrapper dbCom = db.GetStoredProcCommandWrapper("InsertSMS_Register_Info");
dbCom.AddInParameter("@studInfo_stuid ", DbType.String, stuID);
dbCom.AddInParameter("@familial_name ", DbType.String, familialName);
dbCom.AddInParameter("@mobilePhone_number ", DbType.String, mobilePhoneNum);
dbCom.AddInParameter("@stu_grade ", DbType.String, stuGrade);
dbCom.AddInParameter("@stu_class ", DbType.String, stuClass);
dbCom.AddOutParameter("@smsInfo_id", DbType.Int32, 4);
db.ExecuteNonQuery(dbCom);
rowsAffected = Convert.ToInt32(dbCom.GetParameterValue("@smsInfo_id"));
}
catch (Exception ex)
{
throw ex;
}
return(rowsAffected);
}
public DataTable GetFinanceStatHistory(string templateName, string className, DateTime date)
{
string findSql = string.Format("select * from syscolumns where id=" +
"(select max(id) from sysobjects where xtype='u' and name='{0}') order by colorder",
"FinanceStat_" + templateName);
DBCommandWrapper dbComm = dbContiAbs.GetStoredProcCommandWrapper("ExecuteDynamicScript");
dbComm.AddInParameter("@sql", DbType.String, findSql);
DataTable dtColumn = dbContiAbs.ExecuteDataSet(dbComm).Tables[0];
if (dtColumn == null || dtColumn.Rows.Count == 0)
{
throw new Exception(string.Format("table {0} not found", "FinanceStat_" + templateName));
}
else
{
string columnText = string.Empty;
string total = string.Empty;
for (int i = 4; i < dtColumn.Rows.Count - 1; i++)
{
columnText += string.Format("[{0}], ", dtColumn.Rows[i]["name"].ToString());
total += string.Format("ISNULL([{0}], 0) + ", dtColumn.Rows[i]["name"].ToString());
}
columnText += total.Substring(0, total.LastIndexOf("+")) + " AS '总计'";
string sql = string.Format("SELECT stuNumber AS '学号', stuName AS '姓名', className AS '班级', {3} " +
"FROM {0} WHERE className = '{1}' AND DATEDIFF(mm, logDate, '{2}') = 0",
"FinanceStat_" + templateName, className, date, columnText);
dbComm = dbContiAbs.GetStoredProcCommandWrapper("ExecuteDynamicScript");
dbComm.AddInParameter("@sql", DbType.String, sql);
return(dbContiAbs.ExecuteDataSet(dbComm).Tables[0]);
}
}
public int UpdateClassInfo(Classes classes)
{
int rowsAffected = 0;
try
{
DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper("UpdateClass_Info");
dbCommandWrapper.AddInParameter("@info_classNumber", DbType.Int32, classes.InfoClassNumber);
dbCommandWrapper.AddInParameter("@info_className", DbType.String, classes.InfoClassName);
dbCommandWrapper.AddInParameter("@info_gradeNumber", DbType.Int32, classes.ClassInfoGradeNumber);
dbCommandWrapper.AddInParameter("@info_machineAddr", DbType.Int32, classes.InfoMachineAddr);
dbCommandWrapper.AddInParameter("@info_classRemark", DbType.String, classes.InfoClassRemark);
dbCommandWrapper.AddOutParameter("@rowsAffected", DbType.Int32, 4);
db.ExecuteNonQuery(dbCommandWrapper);
rowsAffected = Convert.ToInt32(dbCommandWrapper.GetParameterValue("@rowsAffected"));
}
catch (Exception ex)
{
throw ex;
}
return(rowsAffected);
}
public int InsertTeaCardInfo(CardInfo card)
{
int rowsAffected = 0;
try
{
DBCommandWrapper dbCom = db.GetStoredProcCommandWrapper("InsertTeacher_CardInfo");
dbCom.AddInParameter("@info_teaBasicID", DbType.String, card.StuID);
dbCom.AddInParameter("@info_teaCardNumber", DbType.String, card.CardNumber);
dbCom.AddInParameter("@info_teaCardSendDate", DbType.DateTime, card.CardSendDate);
dbCom.AddInParameter("@info_teaCardState", DbType.Boolean, card.CardState);
dbCom.AddInParameter("@info_teaCardSeq", DbType.Int16, card.CardSequence);
dbCom.AddOutParameter("@rowsAffected", DbType.Int32, 4);
db.ExecuteNonQuery(dbCom);
rowsAffected = Convert.ToInt32(dbCom.GetParameterValue("@rowsAffected"));
}
catch (Exception ex)
{
throw ex;
}
return(rowsAffected);
}
/// <summary>
/// Delete a batch of user roles.
/// </summary>
/// <param name="batch">Table containing user role assignments to delete.</param>
public void DeleteUserRoleBatch(DataTable batch)
{
ValidateParam("batch", batch);
DataSet ds = new DataSet();
ds.Tables.Add(batch);
DBCommandWrapper cmd = this.securityDb.GetStoredProcCommandWrapper(SPDeleteUserRole);
cmd.AddInParameter("userName", DbType.String, "UserName", DataRowVersion.Default);
cmd.AddInParameter("roleName", DbType.String, "RoleName", DataRowVersion.Default);
try
{
this.securityDb.UpdateDataSet(ds, batch.TableName, cmd, null, null, UpdateBehavior.Transactional);
}
catch (SqlException e)
{
if (e.State == 99)
{
throw new InvalidOperationException(SR.BatchNoUserExists);
}
if (e.State == 98)
{
throw new InvalidOperationException(SR.BatchNoRoleExists);
}
throw;
}
}
public int UpdateDutyInfo(DataSet dutyInfoList)
{
int rowsEffected = 0;
try
{
DBCommandWrapper insertComm = db.GetStoredProcCommandWrapper("Insertteacher_duty");
insertComm.AddInParameter("@Teaduty_Begtime", DbType.String, "Teaduty_Begtime", DataRowVersion.Current);
insertComm.AddInParameter("@Teaduty_Endtime", DbType.String, "Teaduty_Endtime", DataRowVersion.Current);
insertComm.AddInParameter("@Teaduty_Name ", DbType.String, "Teaduty_Name", DataRowVersion.Current);
insertComm.AddInParameter("@Teaduty_Remark", DbType.String, "Teaduty_Remark", DataRowVersion.Current);
insertComm.AddOutParameter("@Teaduty_Dutynumber", DbType.Int32, 4);
DBCommandWrapper updateComm = db.GetStoredProcCommandWrapper("Updateteacher_duty");
updateComm.AddInParameter("@Teaduty_Dutynumber", DbType.Int32, "Teaduty_Dutynumber", DataRowVersion.Current);
updateComm.AddInParameter("@Teaduty_Begtime", DbType.String, "Teaduty_Begtime", DataRowVersion.Current);
updateComm.AddInParameter("@Teaduty_Endtime", DbType.String, "Teaduty_Endtime", DataRowVersion.Current);
updateComm.AddInParameter("@Teaduty_Name ", DbType.String, "Teaduty_Name", DataRowVersion.Current);
updateComm.AddInParameter("@Teaduty_Remark", DbType.String, "Teaduty_Remark", DataRowVersion.Current);
DBCommandWrapper deleteComm = db.GetStoredProcCommandWrapper("Deleteteacher_duty");
deleteComm.AddInParameter("@Teaduty_Dutynumber", DbType.Int32, "Teaduty_Dutynumber", DataRowVersion.Proposed);
rowsEffected = db.UpdateDataSet(dutyInfoList, dutyInfoList.Tables[0].TableName,
insertComm, updateComm, deleteComm, UpdateBehavior.Transactional);
}
catch (Exception ex)
{
throw ex;
}
return(rowsEffected);
}
/// <summary>
/// Delete an association of a user to a role.
/// </summary>
/// <param name="userName">User name.</param>
/// <param name="role">Role name.</param>
public void DeleteUserRole(string userName, string role)
{
ValidateParam("userName", userName);
ValidateParam("role", role);
DBCommandWrapper cmd = this.securityDb.GetStoredProcCommandWrapper(SPDeleteUserRole);
cmd.AddInParameter("userName", DbType.String, userName);
cmd.AddInParameter("roleName", DbType.String, role);
try
{
this.securityDb.ExecuteNonQuery(cmd);
}
catch (SqlException e)
{
if (e.State == 99)
{
throw new InvalidOperationException(SR.NoUserExists(userName));
}
if (e.State == 98)
{
throw new InvalidOperationException(SR.NoRoleExists(role));
}
throw;
}
}
public void InsertGrowUpCheckReportHistory(int stuNumber, string referrerID, DateTime date, out int reportID)
{
reportID = 0;
DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(
@"DECLARE @gradeName NVARCHAR(100)
DECLARE @className NVARCHAR(100)
DECLARE @stuName NVARCHAR(100)
IF NOT EXISTS(SELECT * FROM GrowUpCheckReportHistory WHERE stuNumber = @stuNumber AND DATEDIFF(dd, @date, recordTime) = 0)
BEGIN
SELECT @gradeName = info_gradeName, @className = info_className, @stuName = info_stuName
FROM Student_BasicInfo INNER JOIN Class_Info ON info_stuClass = info_classNumber AND info_stuGrade = info_gradeNumber INNER JOIN Grade_Info g ON info_stuGrade = g.info_gradeNumber
WHERE info_stuNumber = @stuNumber AND info_stuHasLeftSchool = 0
INSERT INTO GrowUpCheckReportHistory
VALUES(@gradeName, @className, @referrerID, @stuNumber, @stuName, @date)
END
SELECT MAX(idx) AS reportID FROM GrowUpCheckReportHistory WHERE stuNumber = @stuNumber AND DATEDIFF(mm, recordTime, @date) = 0");
dbCommandWrapper.AddInParameter("@referrerID", DbType.String, referrerID);
dbCommandWrapper.AddInParameter("@stuNumber", DbType.Int32, stuNumber);
dbCommandWrapper.AddInParameter("@date", DbType.DateTime, date);
reportID = Convert.ToInt32(db.ExecuteDataSet(dbCommandWrapper).Tables[0].Rows[0][0]);
}
private DataSet FillStuList_GradeClass(string getGrade, string getClass)
{
getStuList_GradeClassCommand = "GetStuList_GradeClass";
dbCommandWrapper = dbCheckInfo.GetStoredProcCommandWrapper(getStuList_GradeClassCommand);
dbCommandWrapper.AddInParameter("@getGrade", DbType.String, getGrade);
dbCommandWrapper.AddInParameter("@getClass", DbType.String, getClass);
return(dbCheckInfo.ExecuteDataSet(dbCommandWrapper));
}
private DataSet FillStuAmount(string getMachineAddr, DateTime getEndDate)
{
getStuAmountCommand = "GetStuAmount";
dbCommandWrapper = dbRealtime.GetStoredProcCommandWrapper(getStuAmountCommand);
dbCommandWrapper.AddInParameter("@machineAddr", DbType.String, getMachineAddr);
dbCommandWrapper.AddInParameter("@getEndDate", DbType.DateTime, getEndDate);
return(dbRealtime.ExecuteDataSet(dbCommandWrapper));
}
private DataSet FillFoodNutrition(string getFoodCateName, string getFoodNutName)
{
getFoodNutritionCommand = "GetFoodNutrition";
dbCommandWrapper = dbFoodMgmt.GetStoredProcCommandWrapper(getFoodNutritionCommand);
dbCommandWrapper.AddInParameter("@foodCateName", DbType.String, getFoodCateName);
dbCommandWrapper.AddInParameter("@foodNutName", DbType.String, getFoodNutName);
return(dbFoodMgmt.ExecuteDataSet(dbCommandWrapper));
}
public int GetAttendDays(DateTime beginDate, DateTime endDate)
{
DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper("GetDays");
dbCommandWrapper.AddInParameter("@beginDate", DbType.String, beginDate.ToString("yyyy-MM-dd 00:00:00"));
dbCommandWrapper.AddInParameter("@endDate", DbType.String, endDate.ToString("yyyy-MM-dd 23:59:59"));
return(Convert.ToInt32(db.ExecuteScalar(dbCommandWrapper)));
}
public void InsertMachine(string terminalNumbers, string machineVol)
{
insertMachineCommand = "InsertMachine_BatchCreate";
dbCommandWrapper = dbForBatchCreate.GetStoredProcCommandWrapper(insertMachineCommand);
dbCommandWrapper.AddInParameter("@terminalNumbers", DbType.String, terminalNumbers);
dbCommandWrapper.AddInParameter("@machineVol", DbType.String, machineVol);
dbForBatchCreate.ExecuteNonQuery(dbCommandWrapper);
}
private void CreateParameters(DBCommandWrapper dbCommandWrapper)
{
dbCommandWrapper.AddInParameter("FirstName", DbType.AnsiString, "FirstName", DataRowVersion.Current);
dbCommandWrapper.AddInParameter("Age", DbType.Int32, "Age", DataRowVersion.Current);
dbCommandWrapper.AddInParameter("Salary", DbType.Decimal, "Salary", DataRowVersion.Current);
dbCommandWrapper.AddInParameter("DOB", DbType.DateTime, "DOB", DataRowVersion.Current);
dbCommandWrapper.AddInParameter("Val", DbType.AnsiStringFixedLength, "Val", DataRowVersion.Current);
}
private DataSet FillGradeData(string gradeName, string gradeNumber)
{
getGradeInfoCommand = "GetGradeList";
dbCommandWrapper = dbAccess.GetStoredProcCommandWrapper(getGradeInfoCommand);
dbCommandWrapper.AddInParameter("@GradeName", DbType.String, gradeName);
dbCommandWrapper.AddInParameter("@GradeNumber", DbType.String, gradeNumber);
return(dbAccess.ExecuteDataSet(dbCommandWrapper));
}
public void AddBackupJob(int type, string root)
{
addBackupJobCommand = "jp_addbackupschedule";
dbCommandWrapper = dbForBatchCreate.GetStoredProcCommandWrapper(addBackupJobCommand);
dbCommandWrapper.AddInParameter("@root", DbType.String, root);
dbCommandWrapper.AddInParameter("@type", DbType.Int16, type);
dbForBatchCreate.ExecuteNonQuery(dbCommandWrapper);
}
public DataSet GetUploadDataForXDD(DateTime begTime, DateTime endTime)
{
DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper("GetUploadDataForXDD");
dbCommandWrapper.AddInParameter("@begTime", DbType.String, begTime.ToString("yyyy-MM-dd HH:mm:ss"));
dbCommandWrapper.AddInParameter("@endTime", DbType.String, endTime.ToString("yyyy-MM-dd HH:mm:ss"));
return(db.ExecuteDataSet(dbCommandWrapper));
}
private DataTable InternalGetClassNameByNumber(string getGradeNumber, string getClassNumber)
{
getClassNameByNumberCommand = "GetClassNameByNumber";
dbCommandWrapper = dbForBatchCreate.GetStoredProcCommandWrapper(getClassNameByNumberCommand);
dbCommandWrapper.AddInParameter("@gradeNumber", DbType.String, getGradeNumber);
dbCommandWrapper.AddInParameter("@classNumber", DbType.String, getClassNumber);
return(dbForBatchCreate.ExecuteDataSet(dbCommandWrapper).Tables[0]);
}
public void AddUniqueStatus(string gardenName, string gardenID)
{
DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper("AddUniqueGardenStatus");
dbCommandWrapper.AddInParameter("@gardenName", DbType.String, gardenName);
dbCommandWrapper.AddInParameter("@gardenID", DbType.String, gardenID);
db.ExecuteNonQuery(dbCommandWrapper);
}
private DataTable InternalIsExistClass(int getGradeNumber, int getClassNumber)
{
getIsExistClassCommand = "CheckIsExistsClass";
dbCommandWrapper = dbForBatchCreate.GetStoredProcCommandWrapper(getIsExistClassCommand);
dbCommandWrapper.AddInParameter("@gradeNumber", DbType.Int32, getGradeNumber);
dbCommandWrapper.AddInParameter("@classNumber", DbType.Int32, getClassNumber);
return(dbForBatchCreate.ExecuteDataSet(dbCommandWrapper).Tables[0]);
}
private void InsertProfile(string userName, byte[] serializedProfile, Data.Database securityDb, IDbTransaction transaction)
{
DBCommandWrapper cmd = securityDb.GetStoredProcCommandWrapper(SPAddProfile);
cmd.AddInParameter("userName", DbType.String, userName);
cmd.AddInParameter("profile", DbType.Binary, serializedProfile);
securityDb.ExecuteNonQuery(cmd, transaction);
}
private DataSet FillClassName(string getName, string getNumber)
{
getClassNameCommand = "GetClassName";
dbCommandWrapper = dbCheckInfo.GetStoredProcCommandWrapper(getClassNameCommand);
dbCommandWrapper.AddInParameter("@getName", DbType.String, getName);
dbCommandWrapper.AddInParameter("@getNumber", DbType.String, getNumber);
return(dbCheckInfo.ExecuteDataSet(dbCommandWrapper));
}
private void CreateParameters(DBCommandWrapper dbCommandWrapper)
{
dbCommandWrapper.AddInParameter("EMPLOYEEID", DbType.Decimal, "EMPLOYEEID", DataRowVersion.Current);
dbCommandWrapper.AddInParameter("DEPARTMENTID", DbType.Decimal, "DEPARTMENTID", DataRowVersion.Current);
dbCommandWrapper.AddInParameter("STARTDATE", DbType.DateTime, "STARTDATE", DataRowVersion.Current);
dbCommandWrapper.AddInParameter("ENDDATE", DbType.DateTime, "ENDDATE", DataRowVersion.Current);
dbCommandWrapper.AddInParameter("MODIFIEDDATE", DbType.DateTime, "MODIFIEDDATE", DataRowVersion.Current);
}
public DataTable GetStudentPresents(DateTime date, string className)
{
DBCommandWrapper dbComm = dbContiAbs.GetStoredProcCommandWrapper("GetStudentPresentsByMonth");
dbComm.AddInParameter("@date", DbType.DateTime, date);
dbComm.AddInParameter("@class", DbType.String, className);
return(dbContiAbs.ExecuteDataSet(dbComm).Tables[0]);
}
/// <summary>
/// Removes the item identified by the key from the database
/// </summary>
/// <param name="storageKey">Key of CacheItem to remove.</param>
/// <remarks>Exceptions thrown depend on the implementation of the underlying database.</remarks>
protected override void Remove(int storageKey)
{
DBCommandWrapper deleteCommand = database.GetStoredProcCommandWrapper("RemoveItem");
deleteCommand.AddInParameter("@partitionName", DbType.String, partitionName);
deleteCommand.AddInParameter("@storageKey", DbType.Int32, storageKey);
database.ExecuteNonQuery(deleteCommand);
}
private DataSet FillStuCheckInfo_Detail(string getNumber, DateTime getBegDate, DateTime getEndDate)
{
getStuCheckInfo_DetailCommand = "GetStuCheckInfo_Detail";
dbCommandWrapper = dbCheckInfo.GetStoredProcCommandWrapper(getStuCheckInfo_DetailCommand);
dbCommandWrapper.AddInParameter("@getNumber", DbType.String, getNumber);
dbCommandWrapper.AddInParameter("@getBegDate", DbType.DateTime, getBegDate);
dbCommandWrapper.AddInParameter("@getEndDate", DbType.DateTime, getEndDate);
return(dbCheckInfo.ExecuteDataSet(dbCommandWrapper));
}
public DataSet GetUploadDataForYlm_teacher(DateTime begTime, DateTime endTime)
{
DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(@"
SELECT info_gardenID AS gardenID, teaflow_id AS idx, teaflow_cardnumber, teaflow_date, teaflow_state, T_Number FROM dbo.Garden_Info, teacher_flow INNER JOIN Teach_Base_Info ON teaflow_teaid=T_ID WHERE teaflow_date BETWEEN @begTime AND @endTime");
dbCommandWrapper.AddInParameter("@begTime", DbType.String, begTime.ToString("yyyy-MM-dd HH:mm:ss"));
dbCommandWrapper.AddInParameter("@endTime", DbType.String, endTime.ToString("yyyy-MM-dd HH:mm:ss"));
return(db.ExecuteDataSet(dbCommandWrapper));
}
private DataSet Fill2DaysAbs(string getGrade, string getClass, DateTime getDate)
{
get2DaysAbsCommand = "Get2DaysAbsence";
dbCommandWrapper = dbVisit.GetStoredProcCommandWrapper(get2DaysAbsCommand);
dbCommandWrapper.AddInParameter("@stuGrade", DbType.String, getGrade);
dbCommandWrapper.AddInParameter("@stuClass", DbType.String, getClass);
dbCommandWrapper.AddInParameter("@getDate", DbType.DateTime, getDate);
return(dbVisit.ExecuteDataSet(dbCommandWrapper));
}
private DataSet FillDairy(string getBegDate, string getEndDate, int getMonth)
{
getDairyCommand = "GetDairy";
dbCommandWrapper = dbFoodMgmt.GetStoredProcCommandWrapper(getDairyCommand);
dbCommandWrapper.AddInParameter("@getBegDate", DbType.String, getBegDate);
dbCommandWrapper.AddInParameter("@getEndDate", DbType.String, getEndDate);
dbCommandWrapper.AddInParameter("@getMonth", DbType.Int32, getMonth);
return(dbFoodMgmt.ExecuteDataSet(dbCommandWrapper));
}
private DataSet FillFoodArrGrade(FoodMgmt foodMgmt, bool selStyle)
{
getFoodArrGradeCommand = "GetFoodArrGrade";
dbCommandWrapper = dbFoodMgmt.GetStoredProcCommandWrapper(getFoodArrGradeCommand);
dbCommandWrapper.AddInParameter("@foodArrID", DbType.Int32, foodMgmt.FoodArrID);
dbCommandWrapper.AddInParameter("@foodArrName", DbType.String, foodMgmt.FoodArrName);
dbCommandWrapper.AddInParameter("@selStyle", DbType.Boolean, selStyle);
return(dbFoodMgmt.ExecuteDataSet(dbCommandWrapper));
}
public static void CreateDataAdapterCommands(Database db, ref DBCommandWrapper insertCommand, ref DBCommandWrapper updateCommand, ref DBCommandWrapper deleteCommand)
{
insertCommand = db.GetStoredProcCommandWrapper("RegionInsert2");
updateCommand = db.GetStoredProcCommandWrapper("RegionUpdate");
deleteCommand = db.GetStoredProcCommandWrapper("RegionDelete2");
insertCommand.AddInParameter("vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
insertCommand.AddInParameter("vRegionDescription", DbType.String, "RegionDescription", DataRowVersion.Default);
updateCommand.AddInParameter("vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
updateCommand.AddInParameter("vRegionDescription", DbType.String, "RegionDescription", DataRowVersion.Default);
deleteCommand.AddInParameter("vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
}
