public void Delete(string paperID)
{
SqlParameter[] parameters =
{
new SqlParameter("@paperID", SqlDbType.Int, 4)
};
parameters[0].Value = paperID;
DBHelperProcedure.Delete("UP_T_Paper_Delete", parameters);
}
public void Insert(Users obj)
{
SqlParameter[] parameters =
{
new SqlParameter("@paperName", SqlDbType.NVarChar, 50),
new SqlParameter("@paperType", SqlDbType.NVarChar, 30),
new SqlParameter("@deptID", SqlDbType.SmallInt, 2)
};
parameters[0].Value = obj.ID;
parameters[1].Value = obj.Name;
parameters[2].Value = obj.Password;
DBHelperProcedure.Insert("UP_T_Paper_ADD", parameters);
}
public void Update(Users obj)
{
SqlParameter[] parameters =
{
new SqlParameter("paperID", SqlDbType.Int, 4),
new SqlParameter("@paperName", SqlDbType.NVarChar, 50),
new SqlParameter("@paperType", SqlDbType.NVarChar, 30),
new SqlParameter("@content", SqlDbType.Text),
new SqlParameter("@answer", SqlDbType.Text),
new SqlParameter("@creator", SqlDbType.NVarChar, 10)
};
parameters[0].Value = obj.ID;
parameters[1].Value = obj.Name;
parameters[2].Value = obj.Password;
DBHelperProcedure.Update("UP_T_Paper_Update", parameters);
}
public List <Users> SelectList()
{
List <Users> paperList = new List <Users>();
Users paper = null;
using (SqlDataReader dr = DBHelperProcedure.Select("UP_Users_GetList", null))
{
while (dr.Read())
{
paper = new Users();
paper.Name = dr.GetString(dr.GetOrdinal("name"));
paperList.Add(paper);
}
}
return(paperList);
}
// CREATE PROCEDURE [dbo].[UP_User_Name]
//@deptID nvarchar(50) output,
//@deptName nvarchar(50)
// AS
// SET @deptID = '冉陶'
//GO
#endregion
#region 事务
//public void Insert(Test obj)
//{
// //------------插入test
// SqlParameter[] parameters = {
// new SqlParameter("@testID", SqlDbType.Int,4),
// new SqlParameter("@testName", SqlDbType.NVarChar,50),
// new SqlParameter("@paperID", SqlDbType.Int,4),
// new SqlParameter("@TotalScores", SqlDbType.Int,4),
// new SqlParameter("@neededMinutes", SqlDbType.TinyInt,1),
// new SqlParameter("@enableDate", SqlDbType.SmallDateTime),
// new SqlParameter("@unableDate", SqlDbType.SmallDateTime),
// new SqlParameter("@autoSaveInterval", SqlDbType.TinyInt,1),
// new SqlParameter("@creatorUserID", SqlDbType.VarChar,30),
// new SqlParameter("@creatorName", SqlDbType.NChar,10),
// new SqlParameter("@createdTime", SqlDbType.SmallDateTime),
// new SqlParameter("@paperType",SqlDbType.Int),
// new SqlParameter("@passScores",SqlDbType.Int,4)
// };
// parameters[0].Direction = ParameterDirection.Output;
// parameters[1].Value = obj.TestName;
// parameters[2].Value = obj.Paper.PaperID;
// parameters[3].Value = obj.TotalScores;
// parameters[4].Value = obj.NeededMinutes;
// parameters[5].Value = obj.EnableDate;
// parameters[6].Value = obj.UnabaleDate;
// parameters[7].Value = obj.AutoSaveInterval;
// parameters[8].Value = obj.Creator.UserID;
// parameters[9].Value = obj.Creator.Name;
// parameters[10].Value = obj.CreatedTime;
// parameters[11].Value = (int)obj.PaperType;
// parameters[12].Value = obj.PassScores;
// SqlConnection conn = new SqlConnection(DBHelper.connStr);
// SqlTransaction trans = null;
// try
// {
// conn.Open();
// trans = conn.BeginTransaction("insertTest");
// //------------插入test
// DBHelper.Insert(trans, "UP_T_Test_ADD", parameters);
// obj.TestID = Convert.ToInt32(parameters[0].Value);
// //-------------插入testRecorder
// foreach (Tester tester in obj.TesterList)
// {
// SqlParameter[] parameters2 = {
// new SqlParameter("@testID", SqlDbType.Int,4),
// new SqlParameter("@userID", SqlDbType.VarChar,30),
// new SqlParameter("@beginTestTime", SqlDbType.SmallDateTime),
// new SqlParameter("@submitTestTime", SqlDbType.SmallDateTime),
// new SqlParameter("@hasUsedMinutes", SqlDbType.SmallInt,2),
// new SqlParameter("@submitType", SqlDbType.NVarChar,10),
// new SqlParameter("@testerAnswer", SqlDbType.Text),
// new SqlParameter("@marked", SqlDbType.Bit,1),
// };
// parameters2[0].Value = obj.TestID;
// parameters2[1].Value = tester.UserID;
// parameters2[2].Value = null;
// parameters2[3].Value = null;
// parameters2[4].Value = 0;
// parameters2[5].Value = "未提交";
// parameters2[6].Value = string.Empty;
// parameters2[7].Value = false;
// DBHelper.Insert(trans, "UP_T_TestRecorder_ADD", parameters2);
// }
// trans.Commit();
// }
// catch
// {
// trans.Rollback();
// }
// finally
// {
// trans.Dispose();
// if (conn.State == ConnectionState.Open)
// conn.Close();
// }
//}
#endregion
#region 存储过程返回值
public int UpdatePwd(string userID, string oldPwd, string newPwd)
{
SqlParameter[] parms =
{
new SqlParameter("@reutrnValue", SqlDbType.Int, 4),
new SqlParameter("@userID", SqlDbType.VarChar, 30),
new SqlParameter("@oldPwd", SqlDbType.VarChar, 30),
new SqlParameter("@newPwd", SqlDbType.VarChar, 30)
};
parms[0].Direction = ParameterDirection.ReturnValue;
parms[1].Value = userID;
parms[2].Value = oldPwd;
parms[3].Value = newPwd;
DBHelperProcedure.ExecuteNonQuery("UP_T_User_ModifyPwd", parms);
return((int)parms[0].Value);
}
public void InsertProc()
{
SqlParameter[] parameters =
{
new SqlParameter("@deptID", SqlDbType.NVarChar, 50),
new SqlParameter("@TestId", SqlDbType.Int, 3),
new SqlParameter("@deptName", SqlDbType.NVarChar, 50),
new SqlParameter("@reutrnValue", SqlDbType.Int, 4)
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Direction = ParameterDirection.Output;
parameters[2].Value = "李四";
parameters[3].Direction = ParameterDirection.ReturnValue;
DBHelperProcedure.Insert("UP_User_Name", parameters);
int s = Convert.ToInt32(parameters[3].Value);
string ss = parameters[0].Value.ToString();
// int retValue = Convert.ToInt32(parameters["@RetValue"].Value.ToString());
}
public Users SelectByID(string paperID)
{
SqlParameter[] parameters =
{
new SqlParameter("@paperID", SqlDbType.Int, 4)
};
parameters[0].Value = paperID;
Users paper = null;
using (SqlDataReader dr = DBHelperProcedure.Select("UP_T_Paper_GetModel", parameters))
{
if (dr.Read())
{
paper = new Users();
paper.Name = dr.GetString(dr.GetOrdinal("paperName"));
}
}
return(paper);
}
public List <Users> SelectListByDeptID(int deptID)
{
SqlParameter[] parms =
{
new SqlParameter("@deptID", SqlDbType.Int)
};
parms[0].Value = deptID;
List <Users> paperList = new List <Users>();
Users paper = null;
using (SqlDataReader dr = DBHelperProcedure.Select("UP_T_Paper_GetListByDeptID", parms))
{
while (dr.Read())
{
paper = new Users();
paper.Name = dr.GetString(dr.GetOrdinal("paperName"));
paperList.Add(paper);
}
}
return(paperList);
}