怎么把下面代码换成存储过程来处理？不会用那些declare呀之类的东西
我写的存储过程都是很简单那种的，刚项目经理说这样写以后维护起来不方便，郁闷，不懂写.
int pid = 0;
string pwd = "";
OutgoingWebResponseContext outgoingResponse = WebOperationContext.Current.OutgoingResponse;
outgoingResponse.Headers.Add("Cache-Control", "no-cache");
outgoingResponse.Headers.Add("Pragma", "no-cache");
DateTime now = DateTime.Now;
using (SqlDatabase db = new SqlDatabase())
{
//读取需考勤人员
if (Dekey.Eias.Security.SecurityManager.GetCurrentUserId() != 0)
{
Dekey.Eias.Security.User user = Dekey.Eias.Security.UserManager.Default.GetUser(Dekey.Eias.Security.SecurityManager.GetCurrentUserId());
if (user != null)
{
pid = user.UserID;
//pwd = user.Password;
}
}
//验证标识是否正确
db.CommandText = "SELECT COUNT(*) FROM [dk_Membership] WHERE [UserID] = @PID AND Status = 0";
db.CreateParameter<int>("@PID", pid, System.Data.SqlDbType.Int);
if ((int)db.ExecuteScalar() == 1)
{
//获取考勤参考时间
db.Parameters.Clear();
db.CommandText = "SELECT * FROM [dk_Attendance_Members] WHERE [MemberID] = @PID AND ([AttendanceType] = N'0' OR [AttendanceType] IS NULL)";
db.CreateParameter<int>("@PID", pid, System.Data.SqlDbType.Int);
using (DataReaderWrapper dr = db.ExecuteReader())
{
if (dr.Read())
{
DateTime checkInTimeStr = dr.GetDateTime("CheckInTime");
DateTime checkOutTimeStr = dr.GetDateTime("CheckOutTime");
dr.Close();
string today = now.ToString("yyyy-MM-dd");
//确定今天是否已有的考勤记录
db.Parameters.Clear();
db.CommandText = "SELECT CheckInTime, CheckOutTime FROM [dk_Attendance_Records] WHERE [MemberID] = @PID AND [AttendanceDate] = @Today";
db.CreateParameter<int>("@PID", pid, System.Data.SqlDbType.Int);
db.CreateParameter<string>("@Today", today, System.Data.SqlDbType.VarChar, 10);
bool hasRecord = false;
DateTime amTime = new DateTime();
DateTime pmTime = new DateTime();
using (DataReaderWrapper drw = db.ExecuteReader())
{
if (drw.Read())
{
//已有考勤记录，读取上下班的时间记录。
hasRecord = true;
amTime = drw.GetDateTime("CheckInTime");
pmTime = drw.GetDateTime("CheckOutTime");
}
}
bool isAM = now.Hour <= 12;//是否上午
if (isAM && amTime != DateTime.MinValue)
{
return "您已经进行上班考勤了！";
}
//else if (!string.IsNullOrEmpty(pmTime))
//{
// return "您已经进行下班考勤了！";
//}
DateTime checkTime = now.AddMinutes(-3);//TODO:正常应该就是当前时间，但是为了与公司的终端机兼容，于是减去3分钟。(如果终端机的程序修复了需要修改此处) //now;
string checkFiled;
string checkStatusField;
//0-未考勤
//1-正常
//2-临界
//3-迟到
//4-早退
int checkStatus = 0;
if (isAM)
{
checkFiled = "CheckInTime";
checkStatusField = "CheckInStatus";
DateTime checkInDateTime = DateTime.Parse(checkInTimeStr.ToString("HH:mm:ss"));
if (DateTime.Parse(checkTime.ToString("HH:mm:ss")) <= checkInDateTime)
{
//在规定时间内上班
checkStatus = 1;
}
else if (checkTime <= checkInDateTime.AddMinutes(15))
{
//在规定时间15分钟内上班，属于临界
checkStatus = 2;
}
else if (checkTime >= checkInDateTime.AddMinutes(30) || checkTime <= checkInDateTime.AddMinutes(180))
{
//在规定时间15分钟后上班，属于旷工
checkStatus = 20;
}
else
{
//迟到
checkStatus = 3;
}
}
else
{
checkFiled = "CheckOutTime";
checkStatusField = "CheckOutStatus";
DateTime checkOutDateTime = DateTime.Parse(checkOutTimeStr.ToString("HH:mm:ss"));
if (DateTime.Parse(checkTime.ToString("HH:mm:ss")) >= checkOutDateTime)
{
//在规定时间后下班
checkStatus = 1;
}
//else if (checkTime >= checkOutDateTime.AddMinutes(-15))
//{
// //在规定时间前15分钟内下班，临界
// checkStatus = 2;
//}
else
{
//早退
checkStatus = 4;
}
}
db.Parameters.Clear();
db.CreateParameter<int>("@PID", pid, System.Data.SqlDbType.Int);
db.CreateParameter<string>("@Today", today, System.Data.SqlDbType.VarChar, 10);
db.CreateParameter<string>("CheckTime", checkTime.ToString("yyyy-MM-dd HH:mm:ss"), System.Data.SqlDbType.VarChar, 30);
db.CreateParameter<int>("@CheckStatus", checkStatus, System.Data.SqlDbType.Int);
if (hasRecord)//已有记录
{
db.CommandText = string.Format("UPDATE [dk_Attendance_Records] SET [{0}] = @CheckTime, [{1}] = @CheckStatus WHERE [MemberID] = @PID AND [AttendanceDate] = @Today", checkFiled, checkStatusField);
}
else
{
db.CommandText = string.Format("INSERT INTO [dk_Attendance_Records] (AttendanceDate, MemberID, {0}, {1}) VALUES (@Today ,@PID, @CheckTime, @CheckStatus)", checkFiled, checkStatusField);
}
db.ExecuteNonQuery();
}
else
{
return "您不能通过网络方式进行考勤！";
}
}
}
else
{
return "标识ID或密码错误！";
}
}
return "ok";
[解决办法]
自己先看帮助，学习一下存储过程吧，就算告诉你了，你后面还是不会，有意思吗
[解决办法]
存储过程 里也是有if while 等函数的 你看一下存储过程的 常用函数 就知道 该怎么写了
[解决办法]
你不是写过简单的存储过程了吗。
那就像搭积木一点点往上放了。
存储过程基本讲法还得学啊
