public List <Smartphone> GetSmartphones() { List <Smartphone> phones = new List <Smartphone>(); using (SqlConnection con = new SqlConnection(DBConnectionString.Get())) { string query = "SELECT Id, Title, Description, Price FROM Smartphones"; var cmd = new SqlCommand(query, con); con.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Smartphone phone = new Smartphone() { Id = reader.GetInt32(0), Title = reader.GetString(1), Description = reader.GetString(2), Price = reader.GetDecimal(3) }; phones.Add(phone); } } } return(phones); }
public Smartphone GetById(int id) { using (SqlConnection con = new SqlConnection(DBConnectionString.Get())) { string query = "SELECT Id, Title, Description, Price FROM Smartphones WHERE Id = @Id"; var cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@Id", id); con.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { Smartphone phone = new Smartphone() { Id = reader.GetInt32(0), Title = reader.GetString(1), Description = reader.GetString(2), Price = reader.GetDecimal(3) }; return(phone); } } } return(null); }
public User LoginAttempt(UserViewModel userVM) { User user = null; using (SqlConnection con = new SqlConnection(DBConnectionString.Get())) { var query = "SELECT Id,Email, Password, IsAdmin FROM Users Where Email = @Email AND Password = @Password"; var cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@Email", userVM.Email); cmd.Parameters.AddWithValue("@Password", userVM.Password); con.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { User userDb = new User { Id = reader.GetInt32(0), Email = reader.GetString(1), Password = reader.GetString(2), IsAdmin = reader.GetInt32(3) }; user = userDb; } } return(user); } }
/// <summary> /// 获取应该解锁的任务 /// </summary> /// <returns></returns> public DataTable GetUnLockTaskList() { var sql = string.Format(@"SELECT tpur.TPId,tpur.TPUId FROM dbo.TaskProject tp INNER JOIN dbo.TaskProjectUserRelation tpur ON tp.TPId = tpur.TPId WHERE tp.Status=1 AND Relation=0 AND DATEDIFF(HOUR,UserBeginWorkTime,GETDATE())>={0}", ConfigurationManager.AppSettings["UnLockTime"]); return(SqlHelper.ExecuteDataset(DBConnectionString.Get(OutdoorMonitor), CommandType.Text, sql).Tables[0]); }
public List <User> GetUsers() { List <User> users = new List <User>(); using (SqlConnection con = new SqlConnection(DBConnectionString.Get())) { string query = "SELECT id, Email, Password FROM Users"; SqlCommand cmd = new SqlCommand(query, con); con.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { int id = reader.GetInt32(0); string email = reader.GetString(1); string password = reader.GetString(2); User tmpUser = new User() { Id = id, Email = email, Password = password }; users.Add(tmpUser); } } } return(users); }
/// <summary> /// 根据微信用户id获取cusid /// </summary> /// <param name="userId"></param> /// <returns></returns> public int GetCusIdByUserId(int userId) { string sql = "SELECT CusId FROM dbo.WerXinUser WHERE UserId=@UserId"; SqlParameter[] parameters = { new SqlParameter("@UserId", SqlDbType.Int) }; parameters[0].Value = userId; return(ConvertHelper.GetInteger(SqlHelper.ExecuteScalar(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters))); }
/// <summary> /// 根据openid获取用户 /// </summary> /// <param name="OpenId"></param> /// <returns></returns> public DataTable GetUserByOpenId(string OpenId) { string sql = "SELECT * FROM dbo.WerXinUser WHERE OpenId=@OpenId"; SqlParameter[] parameters = { new SqlParameter("@OpenId", SqlDbType.VarChar, 50) }; parameters[0].Value = OpenId; return(SqlHelper.ExecuteDataset(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters).Tables[0]); }
/// <summary> /// 根据微信用户id获取账号信息 /// </summary> /// <param name="UserId"></param> /// <returns></returns> public DataTable GetCustomerByUserId(int UserId) { string sql = "SELECT *,(SELECT FullName FROM dbo.Region r WHERE r.RegionId=c.RegionId) FullName FROM dbo.Customer c WHERE c.CusId=(SELECT TOP 1 CusId FROM dbo.WerXinUser WHERE UserId=@UserId)"; SqlParameter[] parameters = { new SqlParameter("@UserId", SqlDbType.Int) }; parameters[0].Value = UserId; return(SqlHelper.ExecuteDataset(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters).Tables[0]); }
/// <summary> /// 获取任务状态 /// </summary> /// <param name="TpId"></param> /// <returns></returns> public int GetTaskProjectStatus(int TpId) { string sql = "SELECT Status FROM dbo.TaskProject WHERE TPId=@TPId"; SqlParameter[] parameters = { new SqlParameter("@TPId", TpId) }; return(ConvertHelper.GetInteger(SqlHelper.ExecuteScalar(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters))); }
/// <summary> /// 根据区域名称获取区域id /// </summary> /// <param name="RegionName"></param> /// <returns></returns> public string GetRegionIdByRegionName(string RegionName) { string sql = @"SELECT RegionId FROM dbo.Region WHERE RegionName=@RegionName"; SqlParameter[] parameters = { new SqlParameter("@RegionName", RegionName) }; return(ConvertHelper.GetString(SqlHelper.ExecuteScalar(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters))); }
/// <summary> /// 删除图片 /// </summary> /// <param name="ImgPath"></param> /// <returns></returns> public int DeleteImageByImgPath(string ImgPath) { string sql = "DELETE FROM dbo.ImageDetail WHERE ImgPath=@ImgPath "; SqlParameter[] parameters = { new SqlParameter("@ImgPath", ImgPath) }; return(ConvertHelper.GetInteger(SqlHelper.ExecuteScalar(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters))); }
/// <summary> /// 获取任务图片 /// </summary> /// <param name="TpId"></param> /// <returns></returns> public DataTable GetImagesByTPUId(int TPUId) { string sql = @"SELECT TPUId,UploadTime,ImgPath,ThumbnailImgPath,Sort,ExportImgPath FROM dbo.ImageDetail WHERE TPUId=@TPUId"; SqlParameter[] parameters = { new SqlParameter("@TPUId", TPUId), }; return(SqlHelper.ExecuteDataset(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters).Tables[0]); }
/// <summary> /// 获取区域 /// </summary> /// <param name="RegionId"></param> /// <returns></returns> public DataTable GetRegionListByRegionId(string RegionId) { string sql = @"SELECT RegionId,RegionName FROM dbo.Region WHERE ParentId=@RegionId"; SqlParameter[] parameters = { new SqlParameter("@RegionId", RegionId) }; return(SqlHelper.ExecuteDataset(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters).Tables[0]); }
/// <summary> /// 查询我的任务项目列表 /// </summary> /// <param name="UserId"></param> /// <param name="CusId"></param> /// <param name="SqlWhere"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public DataTable GetTaskProjectList(int UserId, int CusId, string SqlWhere, int PageIndex, int PageSize) { string sql = string.Format("SELECT * FROM (select row_number() over ( order by tp.Status,tp.TId desc) rownum, tp.*,t.CusId,t.CusName,t.CreateDate,(SELECT TOP 1 AuditReason FROM dbo.TaskProjectUserRelation WHERE Relation=2 AND UserId=" + UserId + " AND TPId=tp.TPId ORDER BY AuditDate DESC) AuditReason,(SELECT TOP 1 ImgPath FROM dbo.TaskProjectUserRelation WHERE Relation=0 AND UserId=" + UserId + " AND TPId=tp.TPId) ImgPath FROM dbo.Task t INNER JOIN dbo.TaskProject tp ON t.TId = tp.TId where {0}) a where rownum > (@PageIndex-1)*@PageSize and rownum <= @PageIndex*@PageSize order by CreateDate DESC ", SqlWhere); SqlParameter[] parameters = { new SqlParameter("@PageIndex", PageIndex), new SqlParameter("@PageSize", PageSize) }; return(SqlHelper.ExecuteDataset(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters).Tables[0]); }
/// <summary> /// 绑定账号 /// </summary> /// <param name="CusId"></param> /// <param name="UserId"></param> /// <returns></returns> public int BindCustomer(int CusId, int UserId) { string sql = "UPDATE dbo.WerXinUser SET CusId=0 WHERE CusId=@CusId;UPDATE dbo.WerXinUser SET CusId=@CusId WHERE UserId=@UserId"; SqlParameter[] parameters = { new SqlParameter("@CusId", CusId), new SqlParameter("@UserId", UserId) }; return(SqlHelper.ExecuteNonQuery(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters)); }
/// <summary> /// 根据账号密码获取账号Id /// </summary> /// <param name="CusName"></param> /// <param name="PassWord"></param> /// <returns></returns> public int GetCusId(string CusName, string PassWord) { string sql = "SELECT CusId FROM dbo.Customer WHERE CusName=@CusName AND cPassWord=@cPassWord AND IsDisabled=0"; SqlParameter[] parameters = { new SqlParameter("@CusName", CusName), new SqlParameter("@cPassWord", PassWord) }; return(ConvertHelper.GetInteger(SqlHelper.ExecuteScalar(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters))); }
/// <summary> /// 领取任务 /// </summary> /// <param name="TpId"></param> /// <param name="UserId"></param> /// <returns></returns> public int ReceiveTask(int TpId, int UserId) { string sql = "INSERT INTO dbo.TaskProjectUserRelation (TPId, UserId,Relation) VALUES (@TPId,@UserId,0) SELECT @@IDENTITY;UPDATE dbo.TaskProject SET Status=1 WHERE TPId=@TPId"; SqlParameter[] parameters = { new SqlParameter("@TPId", TpId), new SqlParameter("@UserId", UserId) }; return(ConvertHelper.GetInteger(SqlHelper.ExecuteScalar(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters))); }
/// <summary> /// 修改主任务状态 /// </summary> /// <param name="TId"></param> /// <param name="TaskStatus"></param> /// <returns></returns> public int UpdateTaskStatus(int TId, int TaskStatus) { string sql = "UPDATE dbo.Task SET Status=@Status WHERE TId=@TId"; SqlParameter[] parameters = { new SqlParameter("@TId", TId), new SqlParameter("@Status", TaskStatus) }; return(ConvertHelper.GetInteger(SqlHelper.ExecuteScalar(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters))); }
/// <summary> /// 退回任务 /// </summary> /// <param name="TpId"></param> /// <param name="UserId"></param> /// <returns></returns> public int ReturnTask(int TpId, int UserId) { string sql = "DELETE FROM dbo.TaskProjectUserRelation WHERE TPId=@TPId AND UserId=@UserId AND Relation=0;UPDATE dbo.TaskProject SET Status=0 WHERE TPId=@TPId"; SqlParameter[] parameters = { new SqlParameter("@TPId", TpId), new SqlParameter("@UserId", UserId) }; return(ConvertHelper.GetInteger(SqlHelper.ExecuteScalar(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters))); }
public bool Delete(int id) { using (SqlConnection con = new SqlConnection(DBConnectionString.Get())) { string query = "DELETE FROM Smartphones WHERE Id = @Id"; var cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@Id", id); con.Open(); return(cmd.ExecuteNonQuery() == 1); } }
/// <summary> /// 查询任务项目列表 /// </summary> /// <param name="SqlWhere"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public DataTable GetTaskProjectList(string SqlWhere, int PageIndex, int PageSize) { string sql = string.Format("SELECT * FROM (select row_number() over ( order by tp.TId desc) rownum, tp.*,t.Status as TaskStatus,t.CusId,t.CusName,t.CreateDate FROM dbo.Task t INNER JOIN dbo.TaskProject tp ON t.TId = tp.TId where {0}) a where rownum > (@PageIndex-1)*@PageSize and rownum <= @PageIndex*@PageSize order by CreateDate DESC ", SqlWhere); SqlParameter[] parameters = { new SqlParameter("@PageIndex", PageIndex), new SqlParameter("@PageSize", PageSize) }; return(SqlHelper.ExecuteDataset(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters).Tables[0]); }
/// <summary> /// 保存图片路径 /// </summary> /// <param name="ImagePath"></param> /// <param name="ThumbnailImgPath"></param> /// <param name="OldImagePath"></param> /// <returns></returns> public int UpdateImagePath(string ImagePath, string ThumbnailImgPath, string OldImagePath) { string sql = @"UPDATE dbo.TaskProjectUserRelation SET ImgPath=@ImgPath,ThumbnailImgPath=@ThumbnailImgPath WHERE TPUId IN(SELECT TPUId FROM dbo.TaskProjectUserRelation WHERE ImgPath=@OldImgPath)"; SqlParameter[] parameters = { new SqlParameter("@ImgPath", ImagePath), new SqlParameter("@ThumbnailImgPath", ThumbnailImgPath), new SqlParameter("@OldImgPath", OldImagePath) }; return(SqlHelper.ExecuteNonQuery(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters)); }
/// <summary> /// 根据openid修改city /// </summary> /// <param name="OpenId"></param> /// <param name="City"></param> /// <param name="AreaName"></param> /// <returns></returns> public int UpdateCityByOpenId(string OpenId, string City, string AreaName) { string sql = "UPDATE dbo.WerXinUser SET City=@City,AreaName=@AreaName WHERE OpenId=@OpenId"; SqlParameter[] parameters = { new SqlParameter("@OpenId", OpenId), new SqlParameter("@City", City), new SqlParameter("@AreaName", AreaName) }; return(ConvertHelper.GetInteger(SqlHelper.ExecuteScalar(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters))); }
/// <summary> /// 获取用户领取任务信息 /// </summary> /// <param name="TpId"></param> /// <param name="UserId"></param> /// <param name="CusId"></param> /// <returns></returns> public DataTable GetTaskProjectUserRelation(int TpId, int UserId, int CusId) { string sql = @"SELECT ImgPath,ThumbnailImgPath,TPUId FROM dbo.TaskProjectUserRelation WHERE TPId=@TPId AND (UserId=@UserId OR CusId=@CusId) AND Relation in(0,3)"; SqlParameter[] parameters = { new SqlParameter("@TPId", TpId), new SqlParameter("@UserId", UserId), new SqlParameter("@CusId", CusId) }; return(SqlHelper.ExecuteDataset(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters).Tables[0]); }
public bool Create(Smartphone phone) { using (SqlConnection con = new SqlConnection(DBConnectionString.Get())) { string query = "INSERT INTO Smartphones(Title, Description, Price) VALUES(@Title, @Description, @Price)"; var cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@Title", phone.Title); cmd.Parameters.AddWithValue("@Description", phone.Description); cmd.Parameters.AddWithValue("@Price", phone.Price); con.Open(); return(cmd.ExecuteNonQuery() == 1); } }
/// <summary> /// 添加一条记录到图片路径表 /// </summary> /// <param name="TpId"></param> /// <param name="UploadTime"></param> /// <param name="ImagePath"></param> /// <param name="ThumbnailImgPath"></param> /// <param name="Sort"></param> /// <returns></returns> public void AddImagePath(int TPUId, DateTime UploadTime, string ImgPath, string ThumbnailImgPath, int Sort, string ExportImgPath) { string sql = "INSERT INTO dbo.ImageDetail (TPUId,UploadTime,ImgPath,ThumbnailImgPath,Sort,ExportImgPath) VALUES (@TPUId,@UploadTime,@ImgPath,@ThumbnailImgPath,@Sort,@ExportImgPath) SELECT @@IDENTITY"; SqlParameter[] parameters = { new SqlParameter("@TPUId", TPUId), new SqlParameter("@UploadTime", UploadTime), new SqlParameter("@ImgPath", ImgPath), new SqlParameter("@ThumbnailImgPath", ThumbnailImgPath), new SqlParameter("@Sort", Sort), new SqlParameter("@ExportImgPath", ExportImgPath) }; ConvertHelper.GetInteger(SqlHelper.ExecuteScalar(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters)); }
public bool Update(Smartphone phone) { using (SqlConnection con = new SqlConnection(DBConnectionString.Get())) { string query = "UPDATE Smartphones SET Title = @Title, Description = @Description, Price = @Price WHERE Id = @Id"; var cmd = new SqlCommand(query, con); cmd.Parameters.AddWithValue("@Title", phone.Title); cmd.Parameters.AddWithValue("@Description", phone.Description); cmd.Parameters.AddWithValue("@Price", phone.Price); cmd.Parameters.AddWithValue("@Id", phone.Id); con.Open(); return(cmd.ExecuteNonQuery() == 1); } }
/// <summary> /// 保存图片路径 /// </summary> /// <param name="TpId"></param> /// <param name="UserId"></param> /// <param name="ImagePath"></param> /// <returns></returns> public int SaveImagePath(int TpId, int UserId, int CusId, string ImagePath, string ThumbnailImgPath, DateTime ShootTime, string ShootPosition) { string sql = @"UPDATE dbo.TaskProjectUserRelation SET Relation=0,ImgPath=@ImgPath,ThumbnailImgPath=@ThumbnailImgPath,ShootTime=@ShootTime,ShootPosition=@ShootPosition WHERE TPId=@TPId AND (UserId=@UserId OR CusId=@CusId) AND Relation in(0,3);UPDATE dbo.TaskProject SET Status=2 WHERE TPId=@TPId"; SqlParameter[] parameters = { new SqlParameter("@TPId", TpId), new SqlParameter("@UserId", UserId), new SqlParameter("@CusId", CusId), new SqlParameter("@ImgPath", ImagePath), new SqlParameter("@ThumbnailImgPath", ThumbnailImgPath), new SqlParameter("@ShootTime", ShootTime), new SqlParameter("@ShootPosition", ShootPosition) }; return(SqlHelper.ExecuteNonQuery(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters)); }
protected override void Execute(CodeActivityContext context) { String dbconnStr = DBConnectionString.Get(context); String query = "SELECT * FROM users WHERE usertype='" + this.UserType.Get(context) + "'"; List <User> filteredUsers = new List <User>(); DataSet ds = new DataSet(); if (dbconnStr == null) { dbconnStr = ConfigurationManager.ConnectionStrings["ApprovalProcessDB"].ConnectionString; } using (SqlConnection sCon = new SqlConnection(dbconnStr)) { using (SqlDataAdapter sAda = new SqlDataAdapter(query, sCon)) { sAda.Fill(ds); if (ds.Tables["Table"] != null) { foreach (DataRow dr in ds.Tables["Table"].Rows) { // Ensure no selection of user from the user requesting approval if (!(new Guid((String)dr["guid"])).Equals(this.UserContext.Get(context).Id)) { filteredUsers.Add(new User((String)dr["username"], (String)dr["usertype"], (String)dr["addressrequest"], (String)dr["addressresponse"], (String)dr["guid"])); } } } } } // Only return as many users as is requested int remove = filteredUsers.Count - this.SelectXUsers.Get(context); for (int i = 0; i < remove; i++) { filteredUsers.Remove(filteredUsers.Last()); } this.SelectedUsers.Set(context, filteredUsers); }
/// <summary> /// 添加微信关注者用户 /// </summary> /// <param name="user"></param> /// <returns></returns> public int AddUser(User user) { const string sql = @"INSERT INTO dbo.WerXinUser (uPassWord ,NickName ,Sex , City ,OpenId,AreaName ) VALUES (@uPassWord,@NickName,@Sex,@City,@OpenId,@AreaName) SELECT @@IDENTITY"; SqlParameter[] parameters = { new SqlParameter("@uPassWord", SqlDbType.NVarChar, 50), new SqlParameter("@NickName", SqlDbType.NVarChar, 50), new SqlParameter("@Sex", SqlDbType.Int), new SqlParameter("@City", SqlDbType.NVarChar, 20), new SqlParameter("@OpenId", SqlDbType.VarChar, 50), new SqlParameter("@AreaName", SqlDbType.NVarChar, 20) }; parameters[0].Value = user.PassWord; parameters[1].Value = user.NickName; parameters[2].Value = user.Sex; parameters[3].Value = user.City; parameters[4].Value = user.OpenId; parameters[5].Value = user.AreaName; var userId = ConvertHelper.GetInteger(SqlHelper.ExecuteScalar(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters)); return(userId); }