public static Models.Player.Player ReadPlayerInfoSql(string playerNumber, bool isNew, Loader loader) { Models.Player.Player player = null; using (var sqlConnection = new SqlConnection(ConString)) { // 使用了Target占位符表示目标ID var sqlCommand = new SqlCommand("SELECT * FROM PlayerInfo WHERE PlayerNumber = @Target", sqlConnection); // 构造Parameter对象 var targetSqlParameter = new SqlParameter("@Target", SqlDbType.VarChar, 255); targetSqlParameter.Value = playerNumber; sqlCommand.Parameters.Add(targetSqlParameter); sqlConnection.Open(); var result = sqlCommand.ExecuteReader(); if (result.HasRows) { result.Read(); player = new Models.Player.Player(loader.FindStudio(result["PlayerStudioNumber"].ToString()), playerNumber); player.PlayerStudioNumber = result["PlayerStudioNumber"].ToString(); player.PlayerNumber = result["PlayerNumber"].ToString(); player.PlayerStartTime = DateTime.Parse(result["PlayerStartTime"].ToString()); player.PlayerNowTime = DateTime.Parse(result["PlayerNowTime"].ToString()); } result.Close(); } return(player); }
public Loader(LoginController loginController) { if (loginController.IsCorrespond) { StaffList = StaffSQLController.GenerateStaffList(); StudioList = StudioSQLController.GenerateStudioList(this); PlayerTarget = PlayerSqlController.ReadPlayerInfoSql(loginController.PlayerNumber, true, this); // 资源加载完毕 IsLoaded = true; } }
public static void RegisterPlayer(Models.Player.Player player, string password) { using (var sqlConnection = new SqlConnection(ConString)) { sqlConnection.Open(); var sqlCommand = new SqlCommand("insert_player", sqlConnection) { CommandType = CommandType.StoredProcedure }; sqlCommand.Parameters.Add(new SqlParameter("@studio", SqlDbType.VarChar, 255)); sqlCommand.Parameters.Add(new SqlParameter("@password", SqlDbType.VarChar, 255)); sqlCommand.Parameters.Add(new SqlParameter("@number", SqlDbType.VarChar, 255)); sqlCommand.Parameters["@studio"].Value = player.PlayerStudio.StudioNumber; sqlCommand.Parameters["@password"].Value = password; sqlCommand.Parameters["@number"].Value = player.PlayerNumber; sqlCommand.ExecuteNonQuery(); } }
/// <summary> /// 更新数据库的Player数据 /// </summary> /// <param name="origin">旧实例</param> /// <param name="target">新实例</param> public static void UpdatePlayerInfoSql(Models.Player.Player origin, Models.Player.Player target) { try { // 属性比对,生成的SQL语句只包含要修改的部分 var properties = origin.GetType().GetProperties(); var updateList = new List <PropertyInfo>(); foreach (var property in properties) { if (!Equals(origin.GetPropertyValue(property.Name), target.GetPropertyValue(property.Name))) { updateList.Add(property); } } // 生成SQL语句 var commandStringBuilder = new StringBuilder("UPDATE PlayerInfo SET "); var cur = 1; foreach (var targetProperty in updateList) { if (targetProperty.PropertyType.Name == "String" || targetProperty.PropertyType.Name == "DateTime") { commandStringBuilder.Append(targetProperty.Name + " = " + ConvertStringToSql(target .GetPropertyValue(targetProperty.Name) .ToString())); } else { commandStringBuilder.Append(targetProperty.Name + " = " + (int)target.GetPropertyValue(targetProperty.Name)); } if (cur++ < updateList.Count) { commandStringBuilder.Append(", "); } } commandStringBuilder.Append(" WHERE PlayerNumber = " + ConvertStringToSql(origin.PlayerNumber)); // 执行 try { using (var sqlConnection = new SqlConnection(ConString)) { var sqlCommand = new SqlCommand(commandStringBuilder.ToString(), sqlConnection); sqlConnection.Open(); sqlCommand.ExecuteNonQuery(); } } catch (Exception e) { Debug.WriteLine(e); Debug.WriteLine("无变化"); // throw; } } catch (Exception e) { Console.WriteLine(e); } }