/// <summary> /// 绑定下拉列表 /// </summary> /// <returns></returns> public List <Depart> Bind() { List <Depart> list = SqlDbHelper.GetList <Depart>("select * from Depart"); return(list); }
public static bool CreateAttendance(string UserId, DateTime Date, int Rostering) { string date_fmt = "yyyy-MM-dd"; // Date format string fmt = date_fmt + " HH:mm:ss"; // Date & time format string dt = Date.ToString(date_fmt); DateTime[,] arrTime = new DateTime[30, 2]; arrTime[0, 0] = Convert.ToDateTime(dt + " 08:50:32"); arrTime[0, 1] = Convert.ToDateTime(dt + " 08:50:32"); arrTime[1, 0] = Convert.ToDateTime(dt + " 11:50:25"); arrTime[1, 1] = Convert.ToDateTime(dt + " 11:50:25"); arrTime[2, 0] = Convert.ToDateTime(dt + " 08:48:32"); arrTime[2, 1] = Convert.ToDateTime(dt + " 11:52:25"); arrTime[3, 0] = Convert.ToDateTime(dt + " 08:42:00"); arrTime[3, 1] = Convert.ToDateTime(dt + " 08:42:00"); arrTime[4, 0] = Convert.ToDateTime(dt + " 08:42:00"); arrTime[4, 1] = Convert.ToDateTime(dt + " 08:42:00"); arrTime[5, 0] = Convert.ToDateTime(dt + " 08:42:30"); arrTime[5, 1] = Convert.ToDateTime(dt + " 08:42:30"); arrTime[6, 0] = Convert.ToDateTime(dt + " 08:45:38"); arrTime[6, 1] = Convert.ToDateTime(dt + " 08:45:38"); arrTime[7, 0] = Convert.ToDateTime(dt + " 08:48:22"); arrTime[7, 1] = Convert.ToDateTime(dt + " 08:48:22"); arrTime[8, 0] = Convert.ToDateTime(dt + " 08:50:07"); arrTime[8, 1] = Convert.ToDateTime(dt + " 08:50:07"); arrTime[9, 0] = Convert.ToDateTime(dt + " 08:50:43"); arrTime[9, 1] = Convert.ToDateTime(dt + " 08:50:44"); arrTime[10, 0] = Convert.ToDateTime(dt + " 08:51:20"); arrTime[10, 1] = Convert.ToDateTime(dt + " 08:51:20"); arrTime[11, 0] = Convert.ToDateTime(dt + " 08:51:31"); arrTime[11, 1] = Convert.ToDateTime(dt + " 08:51:31"); arrTime[12, 0] = Convert.ToDateTime(dt + " 09:12:22"); arrTime[12, 1] = Convert.ToDateTime(dt + " 09:12:22"); arrTime[13, 0] = Convert.ToDateTime(dt + " 09:13:43"); arrTime[13, 1] = Convert.ToDateTime(dt + " 09:13:43"); arrTime[14, 0] = Convert.ToDateTime(dt + " 09:16:21"); arrTime[14, 1] = Convert.ToDateTime(dt + " 09:16:21"); arrTime[15, 0] = Convert.ToDateTime(dt + " 09:16:33"); arrTime[15, 1] = Convert.ToDateTime(dt + " 09:16:33"); arrTime[16, 0] = Convert.ToDateTime(dt + " 09:45:15"); arrTime[16, 1] = Convert.ToDateTime(dt + " 09:45:15"); arrTime[17, 0] = Convert.ToDateTime(dt + " 10:13:15"); arrTime[17, 1] = Convert.ToDateTime(dt + " 10:17:04"); arrTime[18, 0] = Convert.ToDateTime(dt + " 10:18:41"); arrTime[18, 1] = Convert.ToDateTime(dt + " 10:43:38"); arrTime[19, 0] = Convert.ToDateTime(dt + " 10:44:02"); arrTime[19, 1] = Convert.ToDateTime(dt + " 10:44:03"); arrTime[20, 0] = Convert.ToDateTime(dt + " 10:46:12"); arrTime[20, 1] = Convert.ToDateTime(dt + " 10:46:13"); arrTime[21, 0] = Convert.ToDateTime(dt + " 10:47:26"); arrTime[21, 1] = Convert.ToDateTime(dt + " 10:47:26"); arrTime[22, 0] = Convert.ToDateTime(dt + " 10:47:59"); arrTime[22, 1] = Convert.ToDateTime(dt + " 10:47:59"); arrTime[23, 0] = Convert.ToDateTime(dt + " 10:49:18"); arrTime[23, 1] = Convert.ToDateTime(dt + " 10:49:19"); arrTime[24, 0] = Convert.ToDateTime(dt + " 10:53:24"); arrTime[24, 1] = Convert.ToDateTime(dt + " 10:53:25"); arrTime[25, 0] = Convert.ToDateTime(dt + " 10:54:03"); arrTime[25, 1] = Convert.ToDateTime(dt + " 10:54:03"); arrTime[26, 0] = Convert.ToDateTime(dt + " 10:55:23"); arrTime[26, 1] = Convert.ToDateTime(dt + " 10:55:23"); arrTime[27, 0] = Convert.ToDateTime(dt + " 10:56:32"); arrTime[27, 1] = Convert.ToDateTime(dt + " 10:56:32"); arrTime[28, 0] = Convert.ToDateTime(dt + " 10:57:46"); arrTime[28, 1] = Convert.ToDateTime(dt + " 10:57:46"); arrTime[29, 0] = Convert.ToDateTime(dt + " 10:58:00"); arrTime[29, 1] = Convert.ToDateTime(dt + " 10:58:00"); int offset = 0; if (Rostering == 0) { // 零点班 offset = -6; } else if (Rostering == 2) { // 四点班 offset = 8; } if (offset != 0) { for (int i = 0; i < 30; i++) { arrTime[i, 0] = arrTime[i, 0].AddHours(offset); arrTime[i, 1] = arrTime[i, 1].AddHours(offset); } } SqlParameter[] parameters = new SqlParameter[2]; parameters[0] = new SqlParameter("@UserId", SqlDbType.VarChar, 6); parameters[0].Value = UserId; parameters[1] = new SqlParameter("@Date", SqlDbType.VarChar, 10); parameters[1].Value = Date.ToString(date_fmt); StringBuilder sql = new StringBuilder(); sql.AppendLine("IF NOT EXISTS(SELECT * FROM AccessLog WHERE userId = @UserId AND date = @Date) BEGIN"); sql.AppendLine(" INSERT INTO AccessLog (userId, datetime, date, accessFlag, recResult, state, devNum, recPhotoPath)"); sql.AppendLine(" VALUES(@UserId, '" + arrTime[0, 0].ToString(fmt) + "', @Date, 0, 1, 0, 4, 'D:\\software\\服务端程序20120602\\SavePic\\2012-06-04\\4\\480_VPhoto_20126485032.jpg')"); sql.AppendLine(""); sql.AppendLine(" INSERT INTO AccessLog (userId, datetime, date, accessFlag, recResult, state, devNum, recPhotoPath)"); sql.AppendLine(" VALUES(@UserId, '" + arrTime[1, 0].ToString(fmt) + "', @Date, 0, 1, 0, 4, 'D:\\software\\服务端程序20120602\\SavePic\\2012-06-04\\4\\480_VPhoto_201264115025.jpg')"); sql.AppendLine(""); sql.AppendLine(" INSERT INTO WorkDuration VALUES (@UserId, @Date, 0, '3:0:5', '9184', 4, '1.0', 0, 0, '" + arrTime[2, 0].ToString(fmt) + "', '" + arrTime[2, 1].ToString(fmt) + "', 17)"); sql.AppendLine("END"); bool success = (int)helper.ExecuteNonQuery(sql.ToString(), parameters) > 0; if (success) { SqlDbHelper helper2 = new SqlDbHelper(ConfigurationManager.ConnectionStrings["conn0480"].ConnectionString); sql.Clear(); sql.AppendLine("insert into RegionReport values(898,1,5,20,'" + arrTime[3, 0].ToString(fmt) + "','" + arrTime[3, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,1,2,17,'" + arrTime[4, 0].ToString(fmt) + "','" + arrTime[4, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,1,4,19,'" + arrTime[5, 0].ToString(fmt) + "','" + arrTime[5, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,1,2,22,'" + arrTime[6, 0].ToString(fmt) + "','" + arrTime[6, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,2,4,24,'" + arrTime[7, 0].ToString(fmt) + "','" + arrTime[7, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,2,4,28,'" + arrTime[8, 0].ToString(fmt) + "','" + arrTime[8, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,3,5,29,'" + arrTime[9, 0].ToString(fmt) + "','" + arrTime[9, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,3,4,30,'" + arrTime[10, 0].ToString(fmt) + "','" + arrTime[10, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,4,2,37,'" + arrTime[11, 0].ToString(fmt) + "','" + arrTime[11, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,6,4,30,'" + arrTime[12, 0].ToString(fmt) + "','" + arrTime[12, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,4,3,49,'" + arrTime[13, 0].ToString(fmt) + "','" + arrTime[13, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,8,2,51,'" + arrTime[14, 0].ToString(fmt) + "','" + arrTime[14, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,9,3,52,'" + arrTime[15, 0].ToString(fmt) + "','" + arrTime[15, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,9,2,54,'" + arrTime[16, 0].ToString(fmt) + "','" + arrTime[16, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,11,1,53,'" + arrTime[17, 0].ToString(fmt) + "','" + arrTime[17, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,11,3,52,'" + arrTime[18, 0].ToString(fmt) + "','" + arrTime[18, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,9,2,51,'" + arrTime[19, 0].ToString(fmt) + "','" + arrTime[19, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,8,3,49,'" + arrTime[20, 0].ToString(fmt) + "','" + arrTime[20, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,4,4,30,'" + arrTime[21, 0].ToString(fmt) + "','" + arrTime[21, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,3,5,29,'" + arrTime[22, 0].ToString(fmt) + "','" + arrTime[22, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,3,2,26,'" + arrTime[23, 0].ToString(fmt) + "','" + arrTime[23, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,3,2,26,'" + arrTime[24, 0].ToString(fmt) + "','" + arrTime[24, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,3,4,28,'" + arrTime[25, 0].ToString(fmt) + "','" + arrTime[25, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,2,4,24,'" + arrTime[26, 0].ToString(fmt) + "','" + arrTime[26, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,3,2,22,'" + arrTime[27, 0].ToString(fmt) + "','" + arrTime[27, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,1,4,19,'" + arrTime[28, 0].ToString(fmt) + "','" + arrTime[28, 1].ToString(fmt) + "')"); sql.AppendLine("insert into RegionReport values(898,1,5,20,'" + arrTime[29, 0].ToString(fmt) + "','" + arrTime[29, 1].ToString(fmt) + "')"); success = (int)helper2.ExecuteNonQuery(sql.ToString()) > 0; } return success; }
public void UpdateTeacher(DataTable dtsource) { SqlDbHelper.UpdateDataTable(dtsource, "teacher"); }
public void Updateteacher(DataTable dtsource, string tablename) { SqlDbHelper.UpdateDataTable(dtsource, tablename); }
/// <summary> /// 成交额 /// </summary> /// <returns></returns> public int PostCjMoney() { string sql = "select Sum(Cbj) from RepayMent join VehicleInfo on VehicleInfo.Cid=RepayMent.Pcid"; return(Convert.ToInt32(SqlDbHelper.ExecuteScalar(sql))); }
public DataTable getAllClass() { string strSql = "select * from class"; return(SqlDbHelper.ExecuteDataTable(strSql, CommandType.Text)); }
/// <summary> /// 用户数量 /// </summary> /// <returns></returns> public int PostUserValue() { string sql = " select count(*) from Users"; return(Convert.ToInt32(SqlDbHelper.ExecuteScalar(sql))); }
/// <summary> /// 根据ID获取职位表 /// </summary> /// <returns></returns> public List <Position> GetPositionsById(int positionId) { List <Position> positions = new List <Position>(); string sqlText = $"SELECT * FROM dbo.Position WHERE PositionId = '{positionId}'"; positions = JsonConvert.DeserializeObject <List <Position> >(JsonConvert.SerializeObject(SqlDbHelper.ExecuteDataTable(sqlText))); //截取时间 foreach (var item in positions) { string dt = item.PositionAddTime; string date = dt.Substring(0, 10); string time = dt.Substring(11, 5); string datatime = $"{date} {time}"; item.PositionAddTime = datatime; } return(positions); }
public int Update(articleInfo model, ref string resultMsg) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("update article set "); strSql.Append(" seo_description = @seo_description , "); strSql.Append(" zhaiyao = @zhaiyao , "); strSql.Append(" content = @content , "); strSql.Append(" orderby = @orderby , "); strSql.Append(" click = @click , "); strSql.Append(" status = @status , "); strSql.Append(" is_top = @is_top , "); strSql.Append(" is_red = @is_red , "); strSql.Append(" is_hot = @is_hot , "); strSql.Append(" is_slide = @is_slide , "); strSql.Append(" update_time = @update_time , "); strSql.Append(" category_id = @category_id , "); strSql.Append(" call_index = @call_index , "); strSql.Append(" title = @title , "); strSql.Append(" link_url = @link_url , "); strSql.Append((model.img_url != null && model.img_url.Trim().Length > 0 ? "[img_url]='" + model.img_url + "'," : "")); strSql.Append(" seo_title = @seo_title , "); strSql.Append(" seo_keywords = @seo_keywords, "); strSql.Append(" companyid = @companyid "); strSql.Append(" where id=@id "); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int, 4), new SqlParameter("@seo_description", SqlDbType.NVarChar, 255), new SqlParameter("@zhaiyao", SqlDbType.NVarChar, 255), new SqlParameter("@content", SqlDbType.Text), new SqlParameter("@orderby", SqlDbType.Int, 4), new SqlParameter("@click", SqlDbType.Int, 4), new SqlParameter("@status", SqlDbType.TinyInt, 1), new SqlParameter("@is_top", SqlDbType.TinyInt, 1), new SqlParameter("@is_red", SqlDbType.TinyInt, 1), new SqlParameter("@is_hot", SqlDbType.TinyInt, 1), new SqlParameter("@is_slide", SqlDbType.TinyInt, 1), new SqlParameter("@update_time", SqlDbType.DateTime), new SqlParameter("@category_id", SqlDbType.Int, 4), new SqlParameter("@call_index", SqlDbType.NVarChar, 50), new SqlParameter("@title", SqlDbType.NVarChar, 100), new SqlParameter("@link_url", SqlDbType.NVarChar, 255), new SqlParameter("@seo_title", SqlDbType.NVarChar, 255), new SqlParameter("@seo_keywords", SqlDbType.NVarChar, 255), new SqlParameter("@companyid", SqlDbType.Int, 4), }; parameters[0].Value = model.id; parameters[1].Value = model.seo_description; parameters[2].Value = model.zhaiyao; parameters[3].Value = model.content; parameters[4].Value = model.orderby; parameters[5].Value = model.click; parameters[6].Value = model.status; parameters[7].Value = model.is_top; parameters[8].Value = model.is_red; parameters[9].Value = model.is_hot; parameters[10].Value = model.is_slide; parameters[11].Value = DateTime.Now; parameters[12].Value = model.category_id; parameters[13].Value = model.call_index; parameters[14].Value = model.title; parameters[15].Value = model.link_url; parameters[16].Value = model.seo_title; parameters[17].Value = model.seo_keywords; parameters[18].Value = model.companyid; int rows = SqlDbHelper.ExecuteNonQuery(Config.SqlConnection, strSql.ToString(), CommandType.Text, parameters); return(rows); } catch (Exception exc) { resultMsg = exc.Message; } return(0); }
public void UpdateStudent(DataTable dtsource) { SqlDbHelper.UpdateDataTable(dtsource, "student"); }
public int Add(articleInfo model, ref string resultMsg) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into article("); strSql.Append("seo_description,zhaiyao,content,orderby,click,status,is_top,is_red,is_hot,channel_id,is_slide,userid,username,add_time,update_time,category_id,call_index,title,link_url,img_url,seo_title,seo_keywords,companyid"); strSql.Append(") values ("); strSql.Append("@seo_description,@zhaiyao,@content,@orderby,@click,@status,@is_top,@is_red,@is_hot,@channel_id,@is_slide,@userid,@username,@add_time,@update_time,@category_id,@call_index,@title,@link_url,@img_url,@seo_title,@seo_keywords,@companyid"); strSql.Append(") "); strSql.Append(";select @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@seo_description", SqlDbType.NVarChar, 255), new SqlParameter("@zhaiyao", SqlDbType.NVarChar, 255), new SqlParameter("@content", SqlDbType.Text), new SqlParameter("@orderby", SqlDbType.Int, 4), new SqlParameter("@click", SqlDbType.Int, 4), new SqlParameter("@status", SqlDbType.TinyInt, 1), new SqlParameter("@is_top", SqlDbType.TinyInt, 1), new SqlParameter("@is_red", SqlDbType.TinyInt, 1), new SqlParameter("@is_hot", SqlDbType.TinyInt, 1), new SqlParameter("@channel_id", SqlDbType.Int, 4), new SqlParameter("@is_slide", SqlDbType.TinyInt, 1), new SqlParameter("@userid", SqlDbType.Int, 4), new SqlParameter("@username", SqlDbType.NVarChar, 100), new SqlParameter("@add_time", SqlDbType.DateTime), new SqlParameter("@update_time", SqlDbType.DateTime), new SqlParameter("@category_id", SqlDbType.Int, 4), new SqlParameter("@call_index", SqlDbType.NVarChar, 50), new SqlParameter("@title", SqlDbType.NVarChar, 100), new SqlParameter("@link_url", SqlDbType.NVarChar, 255), new SqlParameter("@img_url", SqlDbType.NVarChar, 255), new SqlParameter("@seo_title", SqlDbType.NVarChar, 255), new SqlParameter("@seo_keywords", SqlDbType.NVarChar, 255), new SqlParameter("@companyid", SqlDbType.Int, 4), }; parameters[0].Value = model.seo_description; parameters[1].Value = model.zhaiyao; parameters[2].Value = model.content; parameters[3].Value = model.orderby; parameters[4].Value = model.click; parameters[5].Value = model.status; parameters[6].Value = model.is_top; parameters[7].Value = model.is_red; parameters[8].Value = model.is_hot; parameters[9].Value = model.channel_id; parameters[10].Value = model.is_slide; parameters[11].Value = model.userid; parameters[12].Value = model.username; parameters[13].Value = model.add_time; parameters[14].Value = model.update_time; parameters[15].Value = model.category_id; parameters[16].Value = model.call_index; parameters[17].Value = model.title; parameters[18].Value = model.link_url; parameters[19].Value = model.img_url; parameters[20].Value = model.seo_title; parameters[21].Value = model.seo_keywords; parameters[22].Value = model.companyid; object obj = SqlDbHelper.ExecuteScalar(Config.SqlConnection, strSql.ToString(), CommandType.Text, parameters); if (obj == null) { return(0); } else { return(Convert.ToInt32(obj)); } } catch (Exception exc) { resultMsg = exc.Message; } return(0); }
/// <summary> /// 根据Id显示用户列表 /// </summary> /// <param name="userId"></param> /// <returns></returns> public List <UserJoinDepJoinPosJoinproductJoinJurisdiction> GetUserJoinsById(int userId) { List <UserJoinDepJoinPosJoinproductJoinJurisdiction> userJoin = new List <UserJoinDepJoinPosJoinproductJoinJurisdiction>(); string sqlText = $"SELECT * FROM dbo.UserInfo u LEFT JOIN dbo.Department d ON u.U_DepartmentId = d.DepartmentId LEFT JOIN dbo.Position po ON u.U_PositionId = po.PositionId LEFT JOIN dbo.Jurisdiction j ON po.PositionId = j.J_PositionId LEFT JOIN dbo.product pr ON u.ProductTeam = pr.PId WHERE u.UserId = {userId}"; //DataTable data = new DataTable(); //data = SqlDbHelper.ExecuteDataTable(sqlText); userJoin = JsonConvert.DeserializeObject <List <UserJoinDepJoinPosJoinproductJoinJurisdiction> >(JsonConvert.SerializeObject(SqlDbHelper.ExecuteDataTable(sqlText))); //截取时间 foreach (var item in userJoin) { string dt = item.UserAddTime; string date = dt.Substring(0, 10); string time = dt.Substring(11, 5); string datatime = $"{date} {time}"; item.UserAddTime = datatime; } return(userJoin); }
/// <summary> /// 获取职位和权限表 /// </summary> /// <returns></returns> public List <PositionJoinJurisdiction> GetPositionsJoinJurisdictionBypPosId(int positionId) { List <PositionJoinJurisdiction> posjoinjur = new List <PositionJoinJurisdiction>(); string sqlText = $"SELECT * FROM dbo.Position p LEFT JOIN dbo.Jurisdiction j ON p.PositionId = j.J_PositionId WHERE j.J_PositionId = '{positionId}'"; posjoinjur = JsonConvert.DeserializeObject <List <PositionJoinJurisdiction> >(JsonConvert.SerializeObject(SqlDbHelper.ExecuteDataTable(sqlText))); return(posjoinjur); }
public bool AddDataTable(string tableName, DataTable dt) { return(SqlDbHelper.SqlBulkCopyInsert(tableName, dt)); }
/// <summary> /// 成交量 /// </summary> /// <returns></returns> public int PostCjCount() { string sql = "select count(*) from VehicleInfo where Czt != 1"; return(Convert.ToInt32(SqlDbHelper.ExecuteScalar(sql))); }
public DataTable GetNotTeamHopeDisb() { string sql = "SELECT teamno,first,second,third FROM teamhope where teacherno is null;"; return(SqlDbHelper.ExecuteDataTable(sql, CommandType.Text)); }
/// <summary> /// 获取部门表 /// </summary> /// <returns></returns> public List <Department> GetDepartments() { List <Department> departments = new List <Department>(); string sqlText = "SELECT * FROM dbo.Department"; departments = JsonConvert.DeserializeObject <List <Department> >(JsonConvert.SerializeObject(SqlDbHelper.ExecuteDataTable(sqlText))); return(departments); }